Re: Impact of checkpoint_segments under continual load conditions

Поиск
Список
Период
Сортировка
От Tom Lane
Тема Re: Impact of checkpoint_segments under continual load conditions
Дата
Msg-id 7224.1121791328@sss.pgh.pa.us
обсуждение исходный текст
Ответ на Re: Impact of checkpoint_segments under continual load conditions  (Christopher Petrilli <petrilli@gmail.com>)
Ответы Re: Impact of checkpoint_segments under continual load conditions  (Christopher Petrilli <petrilli@gmail.com>)
Список pgsql-performance
Christopher Petrilli <petrilli@gmail.com> writes:
> On 7/19/05, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> I'm suddenly wondering if the performance dropoff corresponds to the
>> point where the indexes have grown large enough to not fit in shared
>> buffers anymore.  If I understand correctly, the 5000-iterations mark
>> corresponds to 2.5 million total rows in the table; with 5 indexes
>> you'd have 12.5 million index entries or probably a couple hundred MB
>> total.  If the insertion pattern is sufficiently random that the entire
>> index ranges are "hot" then you might not have enough RAM.

> This is entirely possible, currently:

> shared_buffers = 1000

Ah-hah --- with that setting, you could be seeing shared-buffer
thrashing even if only a fraction of the total index ranges need to be
touched.  I'd try some runs with shared_buffers at 10000, 50000, 100000.

You might also try strace'ing the backend and see if behavior changes
noticeably when the performance tanks.

FWIW I have seen similar behavior while playing with MySQL's sql-bench
test --- the default 1000 shared_buffers is not large enough to hold
the "hot" part of the indexes in some of their insertion tests, and so
performance tanks --- you can see this happening in strace because the
kernel request mix goes from almost all writes to a significant part
reads.  On a pure data insertion benchmark you'd like to see nothing
but writes.

            regards, tom lane

В списке pgsql-performance по дате отправления:

Предыдущее
От: Christopher Petrilli
Дата:
Сообщение: Re: Impact of checkpoint_segments under continual load conditions
Следующее
От: Robert Creager
Дата:
Сообщение: Re: Huge performance problem between 7.4.1 and 8.0.3 - CS issue?