From: Brian Herlihy <btherl@yahoo.com.au>
To: Postgresql Performance <pgsql-performance@postgresql.org>
Subject: Re: strange pauses
Date: Thu, 17 Jan 2008 01:16:40 -0800 (PST)
Message-ID: <913219.85206.qm@web52302.mail.re2.yahoo.com> (raw)
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@careerjunction.co.za>
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@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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: btherl@yahoo.com.au
Subject: Re: strange pauses
In-Reply-To: <913219.85206.qm@web52302.mail.re2.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox