From: | Dan Armbrust <daniel(dot)armbrust(dot)list(at)gmail(dot)com> |
---|---|
To: | pgsql general <pgsql-general(at)postgresql(dot)org> |
Subject: | Checkpoint Tuning Question |
Date: | 2009-07-08 17:35:06 |
Message-ID: | 82f04dc40907081035k4329979vd576f02ccfa27b4c@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I'm running a steady state test where I am pushing about 600 queries
per second through a Posgres 8.3 system on an 8 CPU Linux system.
It's a mix of inserts, updates, and deletes on a few tables - the two
biggest ones probably have about 200,000 rows.
Harddrive is just a simple, run-of-the-mill desktop drive.
Here are parameters that I have changed from defaults:
shared_buffers =100MB
synchronous_commit=off
And, after noting complaints in the log file about checkpoint intervals, I set:
checkpoint_segments=10
Then I turned on slow query logging for queries that take more than 1
second, and checkpoint logging.
Typically, I see no slow queries. The system handles the load just fine.
Once in a while, I'll see a query that takes 3 - 5 seconds.
However, once the checkpoint process begins, I get a whole flood of
queries that take between 1 and 10 seconds to complete. My throughput
crashes to near nothing. The checkpoint takes between 45 seconds and
a minute to complete.
After the checkpoint completes - the system returns to having very few
slow queries, and the keeps up with the load fine.
Is there anything I can do to prevent the occasional slow query?
Is there anything I can do to prevent (or minimize) the performance
impact of the checkpoint?
Thanks,
Dan
From | Date | Subject | |
---|---|---|---|
Next Message | Raymond O'Donnell | 2009-07-08 17:36:34 | Re: [Re: Password?] |
Previous Message | Steve Atkins | 2009-07-08 17:30:14 | Re: [Re: Password?] |