We had the same situation, and did two things
1. Reduce checkpoint timeout
2. Reduce quantity of data going into database (nice if it's possible!)
1 alone wasn't enough to eliminate the delays, but it did make each delay small enough that the user interface was only minimally affected. Previously, the delays were causing timeouts in the user interface.
Our symptoms were that the queries finishing "at the same time" were appearing in clusters every 5 minutes + some seconds, which happens to be the checkpoint timeout. Seems a new checkpoint timeout is started only after the checkpoint is complete, hence 5 minute plus, rather than exactly 5 minutes.
Brian
----- Original Message ----
From: Adrian Moisey <adrian@xxxxxxxxxxxxxxxxxxxx>
Hi
>> Search this list for references to "checkpoints". If you run
>> vmstat/iostat for a bit you should see bursts of disk activity at
>> those times.
>
> The most straightforward way to prove or disprove that the slow
queries
> line up with checkpoints is to set to checkpoint_warning to a high
value
> (3600 should work), which should log every checkpoint, and then see
if
> they show up at the same time in the logs.
You guys were spot on. During these pauses the IO goes up high.
I've got the following set:
checkpoint_timeout = 5min
checkpoint_warning = 3600s
log_min_messages = info
But I see nothing in the logs about checkpoints
--
Adrian Moisey
System Administrator | CareerJunction | Your Future Starts Here.
Web: www.careerjunction.co.za | Email: adrian@xxxxxxxxxxxxxxxxxxxx
Phone: +27 21 686 6820 | Mobile: +27 82 858 7830 | Fax: +27 21 686 6842
---------------------------(end of
broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that
your
message can get through to the mailing list cleanly
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
