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 <g...@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 supportg...@2ndquadrant.com > www.2ndQuadrant.com > > -- @Gmail
