Re: Postgres becoming slow, only full vacuum fixes it
От | Ondrej Ivanič |
---|---|
Тема | Re: Postgres becoming slow, only full vacuum fixes it |
Дата | |
Msg-id | CAM6mieKe8U=m4+q7usOLA8pw2KSBmR1QVVqHAgPbgVxj+ujUNA@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: Postgres becoming slow, only full vacuum fixes it (Kiriakos Tsourapas <ktsour@gmail.com>) |
Ответы |
Re: Postgres becoming slow, only full vacuum fixes it
|
Список | pgsql-performance |
Hi, On 25 September 2012 21:10, Kiriakos Tsourapas <ktsour@gmail.com> wrote: > Suggestion noted. > Nevertheless, I cannot imagine what it would help. Actually, the cost_delay > makes autovacuum freeze when it takes more time than expected, therefore, > having it enabled should help the system. Yes, and I think that "freeze" might be part of your problem. You can: - turn of auto cost based vacuum - or properly set cost parameters: vacuum_cost_page_hit (1), vacuum_cost_page_miss (10), vacuum_cost_page_dirty (20) and vacuum_cost_limit (200) In order to "freeze" ie. reach vacuum_cost_limit auto vacuum needs to: - vacuum up to 200 buffers found in the shared buffer cache (200 / vacuum_cost_page_hit = 200) - or vacuum up to 20 buffers that have to be read from disk (200 / vacuum_cost_page_miss = 20) - or when vacuum modifies up to 10 blocks that were previously clean (200 / vacuum_cost_page_dirty = 10) Basically, you can fiddle with all three parameters until the cows come home or just disable cost based auto vacuum. I think your configuration can handle agressive auto vacuum. -- Ondrej Ivanic (ondrej.ivanic@gmail.com)
В списке pgsql-performance по дате отправления: