Re: Migration study, step 1: bulk write performanceoptimization

Поиск
Список
Период
Сортировка
От Tom Lane
Тема Re: Migration study, step 1: bulk write performanceoptimization
Дата
Msg-id 16142.1143038095@sss.pgh.pa.us
обсуждение исходный текст
Ответ на Re: Migration study, step 1: bulk write performanceoptimization  ("Jim C. Nasby" <jnasby@pervasive.com>)
Список pgsql-performance
"Jim C. Nasby" <jnasby@pervasive.com> 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 по дате отправления:

Предыдущее
От: "Spiegelberg, Greg"
Дата:
Сообщение: Intel C/C++ Compiler Tests
Следующее
От: Tom Lane
Дата:
Сообщение: Re: WAL logging of SELECT ... INTO command