Re: pg_connect takes 3.0 seconds

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(at)2ndquadrant(dot)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(at)2ndQuadrant(dot)com www.2ndQuadrant.com
>
>

--
@Gmail