Re: Massive I/O spikes during checkpoint

>
>
>
> But what appears to be happening is that all of the data is being written
> out at the end of the checkpoint.
>
> 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.
>
>
>
It's not an actual checkpoints.
It's is a fsync after checkpoint which create write spikes hurting server.

You should set sysctl vm.dirty_background_bytes and vm.dirty_bytes to
reasonable low values
(for 512MB raid controller with cache I would suggest to sometning like
vm.dirty_background_bytes = 33554432
vm.dirty_bytes = 268435456
32MB and 256MB respectively)

If youre server doesn't have raid with BBU cache - then you should tune
these values to much lower values.

Please read http://blog.2ndquadrant.com/tuning_linux_for_low_postgresq/
and related posts.

--
Maxim Boguk
Senior Postgresql DBA.
http://www.postgresql-consulting.com/

Phone RU: +7 910 405 4718
Phone AU: +61 45 218 5678

Skype: maxim.boguk
Jabber: maxim(dot)boguk(at)gmail(dot)com
МойКруг: http://mboguk.moikrug.ru/

"People problems are solved with people.
If people cannot solve the problem, try technology.
People will then wish they'd listened at the first stage."