Re: Migration study, step 1: bulk write performanceoptimization

"Jim C. Nasby" <jnasby(at)pervasive(dot)com> writes:
> On Wed, Mar 22, 2006 at 10:04:49AM +0100, Mikael Carneholm wrote:
>> It does ("LOG: checkpoints are occurring too frequently (2 seconds apart)") However, I tried increasing checkpoint_segments to 32 (512Mb) making it checkpoint every 15 second or so, but that gave a more uneven insert rate than with checkpoint_segments=3. Maybe 64 segments (1024Mb) would be a better value? If I set checkpoint_segments to 64, what would a reasonable bgwriter setup be? I still need to improve my understanding of the relations between checkpoint_segments <-> shared_buffers <-> bgwriter... :/

> Probably the easiest way is to set checkpoint_segments to something like
> 128 or 256 (or possibly higher), and then make bg_writer more aggressive
> by increasing bgwriter_*_maxpages dramatically (maybe start with 200).

Definitely. You really don't want checkpoints happening oftener than
once per several minutes (five or ten if possible). Push
checkpoint_segments as high as you need to make that happen, and then
experiment with making the bgwriter parameters more aggressive in order
to smooth out the disk write behavior. Letting the physical writes
happen via bgwriter is WAY cheaper than checkpointing.

bgwriter parameter tuning is still a bit of a black art, so we'd be
interested to hear what works well for you.

regards, tom lane