Re: Load distributed checkpoint

ITAGAKI Takahiro wrote:
> "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
>
>> ...the file system cache will collapse repeated writes to the
>> same location until things settle ...
>> If we just push dirty pages out to the OS as soon as possible,
>> and let the file system do its job, I think we're in better
>> shape...
>
> Maybe we have two entirely different tuning approaches:
> 1. Retain dirty buffers in database, and keep OS buffers clean.
> 2. Keep database clean, and entrust OS to manage dirty buffers.
>
> I suggested the 1st one, and you did the 2nd. Bottle-neck in checkpoints
> vary in the approaches; write() will be worse in 1st, fsync() in 2nd.

The fsync() won't necessarily be worse in the second approach. OS's have
quite a few tunable parameters that can encourage the system to physically
write the pending write()s before the fsync() - either in the background
or by the process doing the write() itself when there are too many
dirty pages.

For checkpoints, I think the main question is whether postgresql's
background writer is smarter or less smart than pdflush or the
equivalent on your system for database workloads.

> Also, database has own access-frequency information for its buffers,
> so I think 1st approach behaves better in handling re-dirty of buffers.

I'm curious what access-frequency info the OS and the database has.

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.

I'm guesing if you use approach 1. you might be better off
turning down the amount of buffering that the OS does with
dirty pages - and if you use approach 2, you might be better
off turning down the amount of delays that postgresql adds.