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
