Re: Migration study, step 1: bulk write performanceoptimization - Mailing list pgsql-performance

From Jim C. Nasby
Subject Re: Migration study, step 1: bulk write performanceoptimization
Date
Msg-id 20060322125516.GB15742@pervasive.com
Whole thread Raw
In response to Re: Migration study, step 1: bulk write performanceoptimization  ("Mikael Carneholm" <Mikael.Carneholm@WirelessCar.com>)
Responses Re: Migration study, step 1: bulk write performanceoptimization
List pgsql-performance
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).
You might want to up lru_percent as well, otherwise it will take a
minimum of 20 seconds to fully scan.

Basically, slowly start increasing settings until performance smooths
out.
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

pgsql-performance by date:

Previous
From: "Jim C. Nasby"
Date:
Subject: Re: WAL logging of SELECT ... INTO command
Next
From: Simon Riggs
Date:
Subject: Re: WAL logging of SELECT ... INTO command