From: Jim C. Nasby <jnasby@pervasive.com>
To: Mikael Carneholm <Mikael.Carneholm@WirelessCar.com>
Cc: Simon Riggs <simon@2ndquadrant.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Migration study, step 1: bulk write performanceoptimization
Date: Wed, 22 Mar 2006 06:55:17 -0600
Message-ID: <20060322125516.GB15742@pervasive.com> (raw)
In-Reply-To: <7F10D26ECFA1FB458B89C5B4B0D72C2B08828E@sesrv12.wirelesscar.com>
References: <7F10D26ECFA1FB458B89C5B4B0D72C2B08828E@sesrv12.wirelesscar.com>
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).
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: jnasby@pervasive.com, Mikael.Carneholm@WirelessCar.com, simon@2ndquadrant.com
Subject: Re: Migration study, step 1: bulk write performanceoptimization
In-Reply-To: <20060322125516.GB15742@pervasive.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox