>>> On Tue, Dec 12, 2006 at 3:22 AM, in message
<E1539E0ED7043848906A8FF995BDA5790198ECC8(at)m0143(dot)s-mxs(dot)net>,
"Zeugswetter
Andreas ADI SD" <ZeugswetterA(at)spardat(dot)at> wrote:
>> > One thing I do worry about is if both postgresql and the OS
>> > are both delaying write()s in the hopes of collapsing them
>> > at the same time. If so, this would cause both to be experience
>> > bigger delays than expected, and make checkpoints worse.
>>
>> That is my concern. Letting 30 seconds worth of dirty pages
> accumulate
>> between checkpoints and then trying to smooth the writes within
>> checkpoint code seems like a doomed effort.
>
> 30 seconds ??? You are supposed to avoid excessive checkpoints.
> If you are reducing checkpoint_timeout to avoid the spike, you are
imho
> definitely tuning the wrong knob.
Sorry for the fuzzy language -- I was intending to describe a situation
where dirty pages accumulate by checkpoint time which would take 30
seconds to write to disk. We were into this situation (and worse) with
the default bgwriter settings.
> you have to make bgwriter more aggressive.
This is what I've been saying.
I've also been saying that if the PostgreSQL way is to let the file
system handle the caching and I/O scheduling, we should trust it to know
what to do with dirty pages, and not try to second-guess it. (Of course
there are knobs to tune the file system if needed.) Our checkpoint
performance issues went away when we went to settings which basically
never leave a dirty page hidden from the file system for more than two
seconds.
-Kevin