From: Dmitri Girski <mitek17@gmail.com>
To: Greg Smith <greg@2ndquadrant.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: pg_connect takes 3.0 seconds
Date: Wed, 6 Jan 2010 15:26:30 +1100
Message-ID: <daed64561001052026s1009626rdeaa6aa33fae5e19@mail.gmail.com> (raw)
In-Reply-To: <4B42ABA2.4010103@2ndquadrant.com>
References: <daed64561001041812s602a3098l686f630d6448835f@mail.gmail.com>
<4B42ABA2.4010103@2ndquadrant.com>
Hi Greg,
Thank you for idea, reading about checkpints & tuning was very useful.
I had a checkpoints logging turned on. I studied a couple of days logs and I
there is no clear dependency on checkpoint write. Sometimes it is within a
vicinity of 3 seconds CONNECT, sometimes well off it.
Also the postgres log file does not show any long operations, which inclines
me to think that this is a network connectivity/apache/php issue rather than
postgres.
Here is the excerpts from logs:
Log file from WWW server
===========================================================
[06-01-10 14:58:16] UserId=15 Time=3.0032 Req=DB CONNECT
===========================================================
Log file from DB server:
===========================================================
[2010-01-06 14:58:13 EST] idleLOG: 00000: disconnection: session time:
0:00:00.027 user=pri_user database=data host=192.168.1.10 port=50087
[2010-01-06 14:58:13 EST] idleLOCATION: log_disconnections, postgres.c:3982
[2010-01-06 14:58:18 EST] /usr/lib64/postgresql-8.3/bin/postgresLOG: 00000:
connection received: host=192.168.1.10 port=52425
[2010-01-06 14:58:18 EST] /usr/lib64/postgresql-8.3/bin/postgresLOCATION:
BackendInitialize, postmaster.c:3027
[2010-01-06 14:58:18 EST] authenticationLOG: 00000: connection authorized:
user=pri_user database=data
[2010-01-06 14:58:18 EST] authenticationLOCATION: BackendInitialize,
postmaster.c:3097
[2010-01-06 14:58:18 EST] idleLOG: 00000: statement: SELECT
"fIsLoggedIn"(15)
[2010-01-06 14:58:18 EST] idleLOCATION: exec_simple_query, postgres.c:845
[2010-01-06 14:58:18 EST] SELECTLOG: 00000: duration: 39.233 ms
[2010-01-06 14:58:18 EST] SELECTLOCATION: exec_simple_query,
postgres.c:1056
[2010-01-06 14:58:18 EST] idleLOG: 00000: statement: START TRANSACTION
[2010-01-06 14:58:18 EST] idleLOCATION: exec_simple_query, postgres.c:845
[2010-01-06 14:58:18 EST] START TRANSACTIONLOG: 00000: duration: 0.050 ms
===========================================================
Cheers,
Dmitri.
On Tue, Jan 5, 2010 at 2:01 PM, Greg Smith <greg@2ndquadrant.com> wrote:
> 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 Supportgreg@2ndQuadrant.com www.2ndQuadrant.com
>
>
--
@Gmail
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mitek17@gmail.com, greg@2ndquadrant.com
Subject: Re: pg_connect takes 3.0 seconds
In-Reply-To: <daed64561001052026s1009626rdeaa6aa33fae5e19@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox