<html><head><title>Re: [pgpool-general: 5589] Fwd: insanely slow first query, then normal speed</title>
</head>
<body>
<span style=" font-family:'Courier New'; font-size: 10pt;">Hello.<br>
<br>
Do you have "log_hostname" in postgresql.conf turned on? <br>
<br>
What happens if you connect from pgpool's ip address to postgresql server directly, not through pgpool - do you observe the same timeout?<br>
<br>
I guess you can try capturing/viewing packets on postgresql server on udp port 53, perhaps this is really some kind of dns lookup timeout, then you'll see what name is being requested from dns server and you'll be able to create this record either on dns server or in /etc/hosts file.<br>
<br>
You wrote 25 июня 2017 г., 18:44:47:<br>
<br>
</span><table>
<tr>
<td width=2 bgcolor= #0000ff><br>
</td>
<td><span style=" font-family:'courier new'; font-size: 10pt;">Problem still exists<br>
- Very slow to login<br>
- First query very slow - one thing i found, was that <u>even if the SQL statement is completely wrong/invalid</u> 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)<br>
<br>
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"<br>
<br>
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<br>
- also tried compiling with ssl - didn't help<br>
- have played around with idle/timeout settings - didn't help<br>
<br>
Any suggestions would be greatly appreciated.<br>
<br>
root@db-slave:~# sudo -u postgres psql -h127.0.0.1 -p5433 mydb # This is pgpool port<br>
could not change directory to "/root": Permission denied<br>
psql (9.6.3)<br>
Type "help" for help.<br>
mydb=# \timing<br>
Timing is on.<br>
mydb=# select * from users where id-23421;<br>
ERROR: argument of WHERE must be type boolean, not type integer<br>
LINE 1: select * from users where id-23421;<br>
^<br>
<span style=" color: #ff0000;"><b>Time: 72044.497 ms<br>
</b><span style=" color: #000000;">mydb=# select * from users where id-23421;<br>
ERROR: argument of WHERE must be type boolean, not type integer<br>
LINE 1: select * from users where id-23421;<br>
^<br>
<span style=" color: #ff0000;"><b>Time: 1.733 ms<br>
</b><span style=" color: #000000;">mydb=# ^Z<br>
[1]+ Stopped sudo -u postgres psql -h127.0.0.1 -p5433 mydb<br>
root@maker-china-db-slave:~# sudo -u postgres psql -h127.0.0.1 -p5432 mydb # this is just the postgres<br>
could not change directory to "/root": Permission denied<br>
psql (9.6.3)<br>
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)<br>
Type "help" for help.<br>
picto=# \timing<br>
Timing is on.<br>
picto=# select * from users where id-23421;<br>
ERROR: argument of WHERE must be type boolean, not type integer<br>
LINE 1: select * from users where id-23421;<br>
^<br>
Time: 1.231 ms<br>
picto=# <br>
<br>
---------- Forwarded message ----------<br>
From: <b>Jon</b> <</span></span></span></span></span><a style=" font-family:'courier new'; font-size: 10pt;" href="mailto:jon@jojati.com">jon@jojati.com</a><span style=" font-family:'courier new'; font-size: 10pt;">><br>
Date: Sun, Jun 25, 2017 at 1:09 PM<br>
Subject: Fwd: insanely slow first query, then normal speed<br>
To: </span><a style=" font-family:'courier new'; font-size: 10pt;" href="mailto:pgpool-general@pgpool.net">pgpool-general@pgpool.net</a><br>
<br>
<br>
<span style=" font-family:'courier new'; font-size: 10pt;">Sorry to answer my own question - i *think* one problem was the hostname not resolving properly.. i guess it was trying to lookup dns...<br>
<br>
I'm still testing..!<br>
<br>
<br>
---------- Forwarded message ----------<br>
From: <b>Jon</b> <</span><a style=" font-family:'courier new'; font-size: 10pt;" href="mailto:jon@jojati.com">jon@jojati.com</a><span style=" font-family:'courier new'; font-size: 10pt;">><br>
Date: Sun, Jun 25, 2017 at 12:08 PM<br>
Subject: insanely slow first query, then normal speed<br>
To: </span><a style=" font-family:'courier new'; font-size: 10pt;" href="mailto:pgpool-general@pgpool.net">pgpool-general@pgpool.net</a><br>
<br>
<br>
<span style=" font-family:'courier new'; font-size: 10pt;">Hi,<br>
<br>
When I connect to pgpool on the slave, the first request is insanely slow, subsequent requests are fast.<br>
<br>
Does anyone know why this might be?<br>
<br>
Pgpool: 3.6.4, Postgres, 9.6.3<br>
<br>
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<br>
<br>
I've tried:<br>
</span>
<ul style="text-indent: 0px; margin-left: 40px; list-style-position: outside; list-style-type: disc;">
<li class=rvps0><span style=" font-family:'courier new'; font-size: 10pt;">with pool_hba turned on and off - no difference</span></li>
<li class=rvps0><span style=" font-family:'courier new'; font-size: 10pt;">i compiled pgpool (3.6.4) from source - no difference (was using the pgdg ubuntu 16.04 binaries before</span></li>
</ul>
<span style=" font-family:'courier new'; font-size: 10pt;"><b><u>The below example showing the first request taking 58000ms! Subsequent queries take 72ms...<br>
<br>
</u></b>e.g.<br>
root@db-slave:~# sudo -u postgres psql -h127.0.0.1 -p5433 mydb<br>
sudo: unable to resolve host db-slave<br>
could not change directory to "/root": Permission denied<br>
psql (9.6.3)<br>
Type "help" for help.<br>
mydb=# \timing<br>
Timing is on.<br>
mydb=# select * from users where id=90000;<br>
<span style=" color: #ff0000;"><b>Time: 58593.186 ms<br>
</b><span style=" color: #000000;">mydb=# select * from users where id=90000;<br>
<span style=" color: #ff0000;"><b>Time: 0.750 ms<br>
</b><span style=" color: #000000;">mydb=# select * from users where id=80000;<br>
<span style=" color: #ff0000;"><b>Time: 72.081ms<br>
</b><span style=" color: #000000;">mydb=# select * from designs where id=78884;<br>
<span style=" color: #ff0000;"><b>Time: 1.648 ms<br>
</b><span style=" color: #000000;">Time: 72.081 ms<br>
picto=# </td>
</tr>
</table>
<br><br>
<br>
<br>
<span style=" font-family:'arial'; color: #c0c0c0;"><i>-- <br>
Best regards,<br>
Pavel </i></span><a style=" font-family:'arial';" href="mailto:balroga3@yandex.ru">mailto:balroga3@yandex.ru</a></body></html>