bgwriter, checkpoints, curious (seeing delays)

Okay ladies and gents and the rest of you :)

It's time I dig into another issue, and that's a curious 5 second
delay on connect, on occasion. Now, I believe the network to be sound
and there are zero errors on any servers, no retrans, no runts, nada
nada nada. However I will continue to run tests with/without dns,
tcpdumps, tracking communications, handshakes, however.

I've been doing some reading and what did I find. I found "Checkpoints
are very disrupting to your database performance and can cause
connections to stall for up to a few seconds while they occur."

Now I'm reaching here, but wondered.

My issue is what appears to be a random 5 second connection delay.

Connection times over a 24 hour period from my application servers,
which connect to my query servers (postgres 8.3.4, slony 1.2.15), big
8 way boxes with 16-32gb ram

Conn time in seconds and count. (50 kind of a round even number eh!)
'1.0-2.0' => 4,
'5.0-' => 50,
'0.5-1.0' => 6,
'0.0-0.5' => 155,632

So the 5 second makes us instantly think network but I want to hit
this from multiple angles, so figured I would reach out to the brain
trust here for some ideas.

Here is what may be interesting and may point me into a direction of
further tuning and adjusting.

postgres=# select * from pg_stat_bgwriter;
checkpoints_timed | checkpoints_req | buffers_checkpoint |
buffers_clean | maxwritten_clean | buffers_backend | buffers_alloc
-------------------+-----------------+--------------------+---------------+------------------+-----------------+---------------
34820 | 207 | 444406118 |
214634102 | 1274873 | 333375850 | 3370607455
(1 row)

Now I'll be honest, I have nothing special in my configs for
bg_writer, in fact other than enabling log_checkpoints today, I have
nothing but defaults for bg_*. but the fact that checkpoints_timed is
so high, seams to maybe point to some required tuning?

Things of interest.

Memory 24gb
8 way processor
postgres 8.3.4

shared_buffers = 1500MB # min 128kB or max_connections*16kB
max_prepared_transactions = 0 # can be 0 or more
work_mem = 100MB # min 64kB
maintenance_work_mem = 128MB # min 1MB
max_fsm_pages = 500000 # min max_fsm_relations*16, 6 bytes each
max_fsm_relations = 225 # min 100, ~70 bytes each
fsync = off # turns forced
synchronization on or off
checkpoint_segments = 100 # in logfile segments, min 1, 16MB each
checkpoint_warning = 3600s # 0 is off

So nothing exciting in my configs (300 connections)..

So my guess is "IF" a really big if, the delay is actually due to
checkpoints (perfect lil storm, timing) than there should be ways for
me to tune this ya?

Thanks "again" for your assistance

Tory