Re: Massive I/O spikes during checkpoint

От: David Kerr
Тема: Re: Massive I/O spikes during checkpoint
Дата: ,
Msg-id: 07928283-528B-4499-BEC2-7E9098D6D734@mr-paradox.net
(см: обсуждение, исходный текст)
Ответ на: Re: Massive I/O spikes during checkpoint  (Maxim Boguk)
Ответы: Re: Massive I/O spikes during checkpoint  (Maxim Boguk)
Список: 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:51 PM, Maxim Boguk wrote:



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

So use bla_bytes instead of bla_ratio?

(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)

I'll take a look.


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.

yeah, I saw that I guess I didn't put 2+2 together. thanks.




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

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