Hosting Provided By

Re: [PERFORM] strange pauses

From: Brian Herlihy <btherl(at)yahoo.com.au>
Date: Thu Jan 17 2008 - 04:16:40 EST

We had the same situation, and did two things

Reduce checkpoint timeout
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@careerjunction.co.za>

>> 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

Do you need help?

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@careerjunction.co.za
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@postgresql.org 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

Received on Thu Jan 17 04:18:45 2008

This message: [ Message body ]
Next message: Scott Marlowe: "Re: [PERFORM] strange pauses"
Previous message: Adrian Moisey: "Re: [PERFORM] strange pauses"
Maybe in reply to: Adrian Moisey: "[PERFORM] strange pauses"

Contemporary messages sorted: [ By Date ] [ By Thread ] [ By Subject ] [ By Author ] [ By messages with attachments ]

This archive was generated by hypermail 2.1.8 : Wed Jun 18 2008 - 13:55:04 EDT