Re: Migration study, step 1: bulk write performanceoptimization

From: Tom Lane
Subject: Re: Migration study, step 1: bulk write performanceoptimization
Date: ,
Msg-id: 16142.1143038095@sss.pgh.pa.us
(view: Whole thread, Raw)
In response to: Re: Migration study, step 1: bulk write performanceoptimization  ("Jim C. Nasby")
List: pgsql-performance

Tree view

Re: Migration study, step 1: bulk write performanceoptimization  ("Mikael Carneholm", )
 Re: Migration study, step 1: bulk write  (Simon Riggs, )
 Re: Migration study, step 1: bulk write performanceoptimization  ("Jim C. Nasby", )
  Re: Migration study, step 1: bulk write performanceoptimization  (Tom Lane, )

"Jim C. Nasby" <> 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_segmentsto 32 (512Mb) making it checkpoint every 15 second or so, but that gave a more uneven insert rate
thanwith checkpoint_segments=3. Maybe 64 segments (1024Mb) would be a better value? If I set checkpoint_segments to 64,
whatwould 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


pgsql-performance by date:

From: Kris Jurka
Date:
Subject: Re: WAL logging of SELECT ... INTO command
From: "Qingqing Zhou"
Date:
Subject: Re: WAL logging of SELECT ... INTO command