Checkpoint Tuning Question

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

Responses

Browse pgsql-general by date

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