Skip site navigation (1) Skip section navigation (2)

Re: Migration study, step 1: bulk write performanceoptimization

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Jim C(dot) Nasby" <jnasby(at)pervasive(dot)com>
Cc: Mikael Carneholm <Mikael(dot)Carneholm(at)WirelessCar(dot)com>, Simon Riggs <simon(at)2ndquadrant(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: Migration study, step 1: bulk write performanceoptimization
Date: 2006-03-22 14:34:55
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-performance
"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

In response to

pgsql-performance by date

Next:From: Tom LaneDate: 2006-03-22 15:06:05
Subject: Re: WAL logging of SELECT ... INTO command
Previous:From: Spiegelberg, GregDate: 2006-03-22 13:56:04
Subject: Intel C/C++ Compiler Tests

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group