Thread: Randomly slow queries over TCP, but not on localhost

Randomly slow queries over TCP, but not on localhost

From
Ramiro Barreca
Date:
We have a 
* PG 8.4 server 
* 16GB RAM
* 16 CPU cores 
* Linux Centos 5 (2.6.18-406.el5.centos.plus kernel)
* max_connections: 800 (but never over 500. usually 300 to 400)

WE have detected randomly slow connections when accessing through the network (not at localhost)
We use a script with a loop calling the psql command running a simple "select 1 from table".

At localhost, the conection is a flash, showing in very few ms the result on screen.
When passing the parameter "-h" with its own IP addres it begin answering immediately, but suddenly it has a delay of 10 seconds or more to answer on 1 or 2 of 10 commands.

Our suspect is that ther is a problem with the network stack parameter, perhaps at kernel level.
May it be the truth?

Ramiro Barreca
rbarreca@gmail.com

Re: Randomly slow queries over TCP, but not on localhost

From
Scott Whitney
Date:

Some of the Big Brains on this list might well have some better ideas...but...


I have to tell you how many times with how many products (Apache, PostgreSQL, MySQL, Lighttpd, Tomcat, the list goes on) I have been completely screwed by forgetting to check my DNS.


You might want to ensure that:


a) The PG server has DNS properly setup. Both forward and reverse.

b) Your test client is seeing the DNS records properly, both forward and reverse.


That might not be it, but I have lost many hours of my life to forgetting about that.


From: pgsql-admin-owner@postgresql.org <pgsql-admin-owner@postgresql.org> on behalf of Ramiro Barreca <rbarreca@gmail.com>
Sent: Tuesday, October 6, 2015 10:39 AM
To: pgsql-admin
Subject: [ADMIN] Randomly slow queries over TCP, but not on localhost
 
We have a 
* PG 8.4 server 
* 16GB RAM
* 16 CPU cores 
* Linux Centos 5 (2.6.18-406.el5.centos.plus kernel)
* max_connections: 800 (but never over 500. usually 300 to 400)

WE have detected randomly slow connections when accessing through the network (not at localhost)
We use a script with a loop calling the psql command running a simple "select 1 from table".

At localhost, the conection is a flash, showing in very few ms the result on screen.
When passing the parameter "-h" with its own IP addres it begin answering immediately, but suddenly it has a delay of 10 seconds or more to answer on 1 or 2 of 10 commands.

Our suspect is that ther is a problem with the network stack parameter, perhaps at kernel level.
May it be the truth?

Ramiro Barreca
rbarreca@gmail.com


Journyx, Inc.
7600 Burnet Road #300
Austin, TX 78757
www.journyx.com

p 512.834.8888 
f 512-834-8858 

Do you receive our promotional emails? You can subscribe or unsubscribe to those emails at http://go.journyx.com/emailPreference/e/4932/714/ 

Re: Randomly slow queries over TCP, but not on localhost

From
Ramiro Barreca
Date:
I don't think is this case.
We test this by using the IP address, not the hostname.

What we can see is the extremely low speed of connection in 2 of 10 cases, when using a psql at command line with a simple "select 1 from...." inside a loop of a shell script FROM THE PG SERVER!!!

If we do the same, but using a pipe connection (no IP address), there is no delay.





Ramiro Barreca
rbarreca@gmail.com

2015-10-06 12:48 GMT-03:00 Scott Whitney <scott@journyx.com>:

Some of the Big Brains on this list might well have some better ideas...but...


I have to tell you how many times with how many products (Apache, PostgreSQL, MySQL, Lighttpd, Tomcat, the list goes on) I have been completely screwed by forgetting to check my DNS.


You might want to ensure that:


a) The PG server has DNS properly setup. Both forward and reverse.

b) Your test client is seeing the DNS records properly, both forward and reverse.


That might not be it, but I have lost many hours of my life to forgetting about that.


From: pgsql-admin-owner@postgresql.org <pgsql-admin-owner@postgresql.org> on behalf of Ramiro Barreca <rbarreca@gmail.com>
Sent: Tuesday, October 6, 2015 10:39 AM
To: pgsql-admin
Subject: [ADMIN] Randomly slow queries over TCP, but not on localhost
 
We have a 
* PG 8.4 server 
* 16GB RAM
* 16 CPU cores 
* Linux Centos 5 (2.6.18-406.el5.centos.plus kernel)
* max_connections: 800 (but never over 500. usually 300 to 400)

WE have detected randomly slow connections when accessing through the network (not at localhost)
We use a script with a loop calling the psql command running a simple "select 1 from table".

At localhost, the conection is a flash, showing in very few ms the result on screen.
When passing the parameter "-h" with its own IP addres it begin answering immediately, but suddenly it has a delay of 10 seconds or more to answer on 1 or 2 of 10 commands.

Our suspect is that ther is a problem with the network stack parameter, perhaps at kernel level.
May it be the truth?

Ramiro Barreca
rbarreca@gmail.com


Journyx, Inc.
7600 Burnet Road #300
Austin, TX 78757
www.journyx.com

p 512.834.8888 
f 512-834-8858 

Do you receive our promotional emails? You can subscribe or unsubscribe to those emails at http://go.journyx.com/emailPreference/e/4932/714/ 

Re: Randomly slow queries over TCP, but not on localhost

From
Scott Marlowe
Date:
On Wed, Oct 7, 2015 at 5:18 AM, Ramiro Barreca <rbarreca@gmail.com> wrote:
> I don't think is this case.
> We test this by using the IP address, not the hostname.
>
> What we can see is the extremely low speed of connection in 2 of 10 cases,
> when using a psql at command line with a simple "select 1 from...." inside a
> loop of a shell script FROM THE PG SERVER!!!
>
> If we do the same, but using a pipe connection (no IP address), there is no
> delay.
>

OK from your host, do:

ping clientmachinename

where clientmachinename is the name of your client, then using the IP
you get from that type

host 10.0.0.1

replaceing 10.0.0.1 with the IP you got from the ping command. If that
takes several seconds, you've got a reverse lookup problem.