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@xxxxxxxxxxxxxxx> wrote:
Dmitri Girski wrote: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.
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@xxxxxxxxxxxxxxx www.2ndQuadrant.com
--
@Gmail
