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

Re: Migration study, step 1: bulk write performanceoptimization

From: "Mikael Carneholm" <Mikael(dot)Carneholm(at)WirelessCar(dot)com>
To: "Jim C(dot) Nasby" <jnasby(at)pervasive(dot)com>
Cc: "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 13:12:43
Message-ID: 7F10D26ECFA1FB458B89C5B4B0D72C2B0A02AF@sesrv12.wirelesscar.com (view raw or flat)
Thread:
Lists: pgsql-performance
Thanks, will try that. I'll report on the progress later, I have some unit tests to set up first but as soon as that is done I'll go back to optimizing insert performance.

Regards,
Mikael.

-----Original Message-----
From: Jim C. Nasby [mailto:jnasby(at)pervasive(dot)com]
Sent: den 22 mars 2006 13:55
To: Mikael Carneholm
Cc: Simon Riggs; pgsql-performance(at)postgresql(dot)org
Subject: Re: [PERFORM] Migration study, step 1: bulk write
performanceoptimization


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(at)pervasive(dot)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

Next:From: Jim C. NasbyDate: 2006-03-22 13:19:10
Subject: Re: WAL logging of SELECT ... INTO command
Previous:From: Simon RiggsDate: 2006-03-22 13:08:34
Subject: Re: WAL logging of SELECT ... INTO command

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