Re: Massive I/O spikes during checkpoint

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

> On Mon, Jul 9, 2012 at 10:39 PM, David Kerr <dmk(at)mr-paradox(dot)net> 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