Thread: pg_connect takes 3.0 seconds

I am running a PostgreSQL server 8.3.5 with a pretty much standard config.

The web application server which runs Apache 1.3/PHP2.9  has an intermittent problem:

pg_connect takes exactly 3.0 seconds. The usual connection time is 0.0045.

The long request happens at approximate rate 1:100.

-- 
Greg Smith    2ndQuadrant   Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com  www.2ndQuadrant.com

Dmitri Girski <mitek17@gmail.com> writes:
> I am running a PostgreSQL server 8.3.5 with a pretty much standard config.

> The web application server which runs Apache 1.3/PHP2.9  has an intermittent
> problem:
> pg_connect takes exactly 3.0 seconds. The usual connection time is 0.0045.
> The long request happens at approximate rate 1:100.

Sounds a lot like a dropped-packets problem.  The exact timing would be
explained if that is the retransmit timeout in your client-side TCP
stack.  If that's what it is, you need some network engineers, not us
database geeks ...

            regards, tom lane

On 1/4/2010 8:12 PM, Dmitri Girski wrote:
> Hi everybody,
>
> I am running a PostgreSQL server 8.3.5 with a pretty much standard config.
>
> The web application server which runs Apache 1.3/PHP2.9  has an
> intermittent problem:
> pg_connect takes exactly 3.0 seconds. The usual connection time is 0.0045.
> The long request happens at approximate rate 1:100.
>
> I turned on logs on postgres server side, and there is
> nothing suspicious for me there. When a connection request comes, it is
> being served without any delay.
>
> Could anyone point me to the direction in which I should investigate
> this problem further?
> Thank you in advance!
>
>
> PS The hardware is: Dell SC1435/4Gb/2x2.0GHz/Gentoo Linux.
> The database & web servers are in the 2 local subnets.
>
>
> Dmitri.
>

How do you have the connect string?  With an IP or a name?  Maybe its a
DNS lookup timeout?  You could switch to IP or drop the name in the
hosts file and see if that makes a difference.

-Andy

Delays that are almost exactly 3 seconds over a network are almost always
some sort of network configuration issue.

Inside a datacenter, mis-configured load balancers or routers can cause low
level network issues that result in intermittent network delays of exactly 3
seconds (a loop in a routing network?).
DNS timeouts are often 3 seconds.

Not sure if any of the above is it, but this sounds like a network
configuration problem to me.

On 1/4/10 6:12 PM, "Dmitri Girski" <mitek17@gmail.com> wrote:

> Hi everybody,
>
> I am running a PostgreSQL server 8.3.5 with a pretty much standard config.
>
> The web application server which runs Apache 1.3/PHP2.9  has an intermittent
> problem:
> pg_connect takes exactly 3.0 seconds. The usual connection time is 0.0045.
> The long request happens at approximate rate 1:100.
>
> I turned on logs on postgres server side, and there is nothing suspicious for
> me there. When a connection request comes, it is being served without any
> delay. 
>
> Could anyone point me to the direction in which I should investigate this
> problem further?
> Thank you in advance!
>
>
> PS The hardware is: Dell SC1435/4Gb/2x2.0GHz/Gentoo Linux.
> The database & web servers are in the 2 local subnets. 
>
>
> Dmitri.
>
>

On Tue, Jan 5, 2010 at 8:49 PM, Dmitri Girski <mitek17@gmail.com> wrote:
> Hi Tom,
> The timing is around 3.0 seconds
> Time=3.0037
> Time=3.4038
> Time=3.0038
> Time=3.004
> Time=3.2037
> Time=3.0039
> Time=3.0034
> Time=3.0034
> Time=3.2039
> Time=3.0044
> Time=3.8044
> Time=3.2034
>
> I don't think that it could relate to DNS problem as I tried 2 methods which
> does not use name resolution ( hosts file & ip address)
> I will definitely seek the help from network geeks and I will check all TCP
> stack settings.

Have you looked at the various logs inn /var/log on both machines?

Dmitri Girski wrote:
> Hi Andy,
>
> I tried  2 connections strings:
> - server name (DB1),  which is listed in all machines hosts files.
> - ip address.
>
> There is no difference in both methods, still I have 5-7 pg_connects which
> last around 3 seconds.

Don't rule out reverse DNS issues (such as a negative cache entry
expiring and being re-checked), packet loss, nss issues on the server
related to NIS, LDAP etc user directories, and so on.

Wireshark is your friend.

--
Craig Ringer

On Wed, 6 Jan 2010, Dmitri Girski wrote:
> On the other hand, if I use ip addresses this should not attract any possible issues with
> DNS, right?

Not true. It is likely that the server program you are connecting to will
perform a reverse DNS lookup to work out who the client is, for logging or
authentication purposes.

Matthew

--
"To err is human; to really louse things up requires root
 privileges."                 -- Alexander Pope, slightly paraphrased

On Tue, Jan 5, 2010 at 11:50 PM, Craig Ringer
<craig@postnewspapers.com.au> wrote:
> Wireshark is your friend.

+1.  I think if you put a packet sniffer on the interface you are
connecting from it will become clear what the problem is in short
order.

...Robert

Dave Crooke wrote:
> The fact that the delays are clustered at (3 + 0.2 n) seconds, rather
> than a distributed range, strongly indicates a timeout and not
> (directly) a resource issue.
>
> 3 seconds is too fast for a timeout on almost any DNS operation, unless
> it has been modified, so I'd suspect it's the TCP layer, e.g. perhaps
> the SYN packet goes awol and it has to retry.
>
> I'd second the vote for investigation with a packet sniffing tool
> (Wireshark, tcpdump, etc)

If you have a PC (Windows), pingplotter is a remarkable and simple tool to use that quickly identifies problems, and
givesresults that are convincing when you show them to your network admin.  Wireshark and tcpdump have a pretty steep
learningcurve and are overkill if your problem is simple. 

Craig

On Wed, Jan 6, 2010 at 7:44 PM, Dmitri Girski <mitek17@gmail.com> wrote:
> Hi everybody,
> Many thanks to everyone replied, I think we are on the right way.
> I've used tcpdump to generate the logs and there are a lot of dropped
> packets due to the bad checksum. Network guy is currently looking at the
> problem and most likely this is hardware issue.

95% of these problems are a bad NIC or a bad cable. Since cables are
easy to change I'd try those first, then NICs.  Since lots of servers
have dual nics that's a pretty easy change too.

 Every now and then it's a bad switch / router.

On 7/01/2010 10:44 AM, Dmitri Girski wrote:
> Hi everybody,
>
> Many thanks to everyone replied, I think we are on the right way.
> I've used tcpdump to generate the logs and there are a lot of dropped
> packets due to the bad checksum. Network guy is currently looking at the
> problem and most likely this is hardware issue.

Hang on a sec. You need to ignore bad checksums on *outbound* packets,
because many (most?) Ethernet drivers implement some level of TCP
offloading, and this will result in packet sniffers seeing invalid
checksums for transmitted packets - the checksums haven't been generated
by the NIC yet.

Unless you know for sure that your NIC doesn't do TSO, ignore bad
checksums on outbound packets from the local interface.

--
Craig Ringer