|
Home > Archive > PostgreSQL Performance > March 2006 > Re: Migration study, step 1: bulk write performanceoptimization
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Re: Migration study, step 1: bulk write performanceoptimization
|
|
| Mikael Carneholm 2006-03-22, 3:29 am |
| >>On Mon, 2006-03-20 at 15:59 +0100, Mikael Carneholm wrote:
[color=darkred]
[color=darkred]
>With those settings, you'll be checkpointing every 48 Mb, which will be
>every about once per second. Since the checkpoint will take a reasonable
>amount of time, even with fsync off, you'll be spending most of your
>time checkpointing. bgwriter will just be slowing you down too because
>you'll always have more clean buffers than you can use, since you have
>132MB of shared_buffers, yet flushing all of them every checkpoint.
>Please read you're logfile, which should have relevant WARNING messages.
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... :
/
- Mikael
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
| |
| Jim C. Nasby 2006-03-22, 7:33 am |
| 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
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly
| |
| Tom Lane 2006-03-22, 9:31 am |
| "Jim C. Nasby" <jnasby@pervasive.com> writes:
> On Wed, Mar 22, 2006 at 10:04:49AM +0100, Mikael Carneholm wrote:
=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...
:/
[color=darkred]
> 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
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
|
|
|
|
|