[pgpool-general: 5589] Fwd: insanely slow first query, then normal speed

[Jon]

Problem still exists
 - Very slow to login
 - First query very slow - one thing i found, was that *even if the SQL
statement is completely wrong/invalid* it hangs for that amount of time..
always around 70000ms (i've had times of: 70551.220 ms,  71829.095 ms,
72044.497 ms, 69779.030 ms, 74701.804 ms etc)

Does this sound like a timeout issue? I checked the state of these queries
during the 70 seconds they were running and they were in "idle"

I was using the Ubuntu binaries but have now tried re-compiling 3.6.4 and
3.6.3 from source which didn't help
- also tried compiling with ssl - didn't help
- have played around with idle/timeout settings - didn't help

Any suggestions would be greatly appreciated.

root at db-slave:~# sudo -u postgres psql -h127.0.0.1 -p5433  mydb # This is
pgpool port

could not change directory to "/root": Permission denied

psql (9.6.3)

Type "help" for help.

mydb=# \timing

Timing is on.

mydb=# select * from users where id-23421;

ERROR:  argument of WHERE must be type boolean, not type integer

LINE 1: select * from users where id-23421;

                                  ^

*Time: 72044.497 ms*

mydb=# select * from users where id-23421;

ERROR:  argument of WHERE must be type boolean, not type integer

LINE 1: select * from users where id-23421;

                                  ^

*Time: 1.733 ms*

mydb=# ^Z

[1]+  Stopped                 sudo -u postgres psql -h127.0.0.1 -p5433 mydb

root at maker-china-db-slave:~# sudo -u postgres psql -h127.0.0.1 -p5432  mydb
# this is just the postgres

could not change directory to "/root": Permission denied

psql (9.6.3)

SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384,
bits: 256, compression: off)

Type "help" for help.

picto=# \timing

Timing is on.

picto=# select * from users where id-23421;

ERROR:  argument of WHERE must be type boolean, not type integer

LINE 1: select * from users where id-23421;

                                ^

Time: 1.231 ms

picto=#

---------- Forwarded message ----------
From: Jon <jon at jojati.com>
Date: Sun, Jun 25, 2017 at 1:09 PM
Subject: Fwd: insanely slow first query, then normal speed
To: pgpool-general at pgpool.net


Sorry to answer my own question - i *think* one problem was the hostname
not resolving properly.. i guess it was trying to lookup dns...

I'm still testing..!


---------- Forwarded message ----------
From: Jon <jon at jojati.com>
Date: Sun, Jun 25, 2017 at 12:08 PM
Subject: insanely slow first query, then normal speed
To: pgpool-general at pgpool.net


Hi,

When I connect to pgpool on the slave, the first request is insanely slow,
subsequent requests are fast.

Does anyone know why this might be?

Pgpool: 3.6.4, Postgres, 9.6.3

NOTE: it also seems to take a long time to connect to pgpool using psql.. i
seem to remember an older version not having this delay

I've tried:

   - with pool_hba turned on and off - no difference
   - i compiled pgpool (3.6.4) from source - no difference (was using the
   pgdg ubuntu 16.04 binaries before

*The below example showing the first request taking 58000ms! Subsequent
queries take 72ms...*

e.g.

root at db-slave:~# sudo -u postgres psql -h127.0.0.1 -p5433 mydb

sudo: unable to resolve host db-slave

could not change directory to "/root": Permission denied

psql (9.6.3)

Type "help" for help.

mydb=# \timing

Timing is on.

mydb=# select * from users where id=90000;

*Time: 58593.186 ms*

mydb=# select * from users where id=90000;

*Time: 0.750 ms*

mydb=# select * from users where id=80000;

*Time: 72.081ms*

mydb=# select * from designs where id=78884;

*Time: 1.648 ms*

Time: 72.081 ms

picto=#
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20170626/c5c03446/attachment.htm>