Re: Massive I/O spikes during checkpoint

От: David Kerr
Тема: Re: Massive I/O spikes during checkpoint
Дата: ,
Msg-id: FEA163D6-67FB-4636-B5F5-FEFC5825DAA3@mr-paradox.net
(см: обсуждение, исходный текст)
Ответ на: Re: Massive I/O spikes during checkpoint  (Jeff Janes)
Список: pgsql-performance

Скрыть дерево обсуждения

Massive I/O spikes during checkpoint  (David Kerr, )
 Re: Massive I/O spikes during checkpoint  (Maxim Boguk, )
  Re: Massive I/O spikes during checkpoint  (David Kerr, )
   Re: Massive I/O spikes during checkpoint  (Maxim Boguk, )
    Re: Massive I/O spikes during checkpoint  (David Kerr, )
    Re: Massive I/O spikes during checkpoint  (Andres Freund, )
     Re: Massive I/O spikes during checkpoint  (Jeff Janes, )
      Re: Massive I/O spikes during checkpoint  (Andres Freund, )
 Re: Massive I/O spikes during checkpoint  (Jeff Janes, )
  Re: Massive I/O spikes during checkpoint  (David Kerr, )

On Jul 9, 2012, at 10:52 PM, Jeff Janes wrote:

> On Mon, Jul 9, 2012 at 10:39 PM, David Kerr <> wrote:
>>
>> I thought that the idea of checkpoint_completion_target was that we try to
>> finish writing
>> out the data throughout the entire checkpoint (leaving some room to spare,
>> in my case 30%
>> of the total estimated checkpoint time)
>>
>> But what appears to be happening is that all of the data is being written
>> out at the end of the checkpoint.
>
> Postgres is writing data out to the kernel throughout the checkpoint.
> But the kernel is just buffering it up dirty, until the end of the
> checkpoint when the fsyncs start landing like bombs.

Ahh. duh!

I guess i assumed that the point of spreading the checkpoint I/O was
spreading the syncs out.

>
>>
>> This happens at every checkpoint while the system is under load.
>>
>> I get the feeling that this isn't the correct behavior and i've done
>> something wrong.
>>
>> Also, I didn't see this sort of behavior in PG 8.3, however unfortunately, I
>> don't have data to back that
>> statement up.
>
> Did you have less RAM back when you were running PG 8.3?
nope. I was on RHEL 5.5 back then though.

>
>> Any suggestions. I'm willing and able to profile, or whatever.
>
> Who much RAM do you have?  What are your settings for /proc/sys/vm/dirty_* ?

256G
and I've been running with this for a while now, but I think that's the default in RHEL 6+
echo 10 > /proc/sys/vm/dirty_ratio
echo 5 >/proc/sys/vm/dirty_background_ratio




В списке pgsql-performance по дате сообщения:

От: Pena Kupen
Дата:
Сообщение: Fw: Re: Custom function in where clause
От: Andres Freund
Дата:
Сообщение: Re: Massive I/O spikes during checkpoint