Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 0A94263356B for ; Mon, 4 Jan 2010 23:03:06 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 34320-08 for ; Tue, 5 Jan 2010 03:02:55 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from moutng.kundenserver.de (moutng.kundenserver.de [212.227.17.10]) by mail.postgresql.org (Postfix) with ESMTP id 64C0463379B for ; Mon, 4 Jan 2010 23:02:32 -0400 (AST) Received: from [192.168.1.3] (pool-96-244-14-10.bltmmd.fios.verizon.net [96.244.14.10]) by mrelayeu.kundenserver.de (node=mrbap1) with ESMTP (Nemesis) id 0LzpYp-1NwQYI19il-014TMg; Tue, 05 Jan 2010 04:02:30 +0100 Message-ID: <4B42ABA2.4010103@2ndquadrant.com> Date: Mon, 04 Jan 2010 22:01:54 -0500 From: Greg Smith User-Agent: Thunderbird 2.0.0.23 (X11/20090817) MIME-Version: 1.0 To: Dmitri Girski CC: pgsql-performance@postgresql.org Subject: Re: pg_connect takes 3.0 seconds References: In-Reply-To: Content-Type: multipart/alternative; boundary="------------020500080207090008060608" X-Provags-ID: V01U2FsdGVkX1+ZvCXlMcqXy6tGEwyzm6O2LvewvcQDI1VcYQ6 r9Dw/mM/ec4JfOuhWhUVA39eQKfkq25T9uZ+//S8wVwlWrXy0i z8CDuPOA5G0r1av3LPxoMRcb20RirFG X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=3.004 tagged_above=-10 required=5 tests=BAYES_40=-0.185, FH_DATE_PAST_20XX=3.188, HTML_MESSAGE=0.001 X-Spam-Level: *** X-Archive-Number: 201001/37 X-Sequence-Number: 36917 This is a multi-part message in MIME format. --------------020500080207090008060608 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Dmitri Girski wrote: > 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. First thing to check for intermittent multi-second delays is whether a checkpoint is happening at that time. See http://wiki.postgresql.org/wiki/Logging_Checkpoints for an intro, you'd want to see if the checkpoints are around the same time as the delays each time. The default configuration makes checkpoints happen all the time if there's any significant write traffic on your database. -- Greg Smith 2ndQuadrant Baltimore, MD PostgreSQL Training, Services and Support greg@2ndQuadrant.com www.2ndQuadrant.com --------------020500080207090008060608 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Dmitri Girski wrote:
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.

First thing to check for intermittent multi-second delays is whether a checkpoint is happening at that time.  See http://wiki.postgresql.org/wiki/Logging_Checkpoints for an intro, you'd want to see if the checkpoints are around the same time as the delays each time.  The default configuration makes checkpoints happen all the time if there's any significant write traffic on your database.

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