Re: Checkpoint Tuning Question

On Sun, 2009-07-12 at 13:10 -0400, Tom Lane wrote:

> It's hard to see how it could have continuing effects over several
> seconds, especially in a system that has CPU to spare.

Any queueing situation takes a while to resolve and over-damped systems
can take a long time to resolve themselves. We simply don't know
anything at all about how well damped the system is. Regrettably
physicists spend a lot of time looking at oscillating systems...
http://en.wikipedia.org/wiki/Vibration so my viewpoint is that the
system is much more dynamic than we have yet considered it to be.

You've not commented on the double-take on the WALInsertLock which seems
to be the initiating factor here. I think multiple CPU systems may
simply exacerbate the queueing problem and spare CPU is usually an
indication of contention. As ever, this is conjecture to attempt to
explain the problem, not firm black and white knowledge.

Anyway, I agree that turning synchronous_commit = off and setting
full_page_writes = off will get us closer here.

Also, I'm very happy that the delay is only 1 sec. We had much greater
effects two years ago, so we've definitely moved forwards.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support