Re: Postgres becoming slow, only full vacuum fixes it

Hi,

On 25 September 2012 21:10, Kiriakos Tsourapas <ktsour(at)gmail(dot)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(dot)ivanic(at)gmail(dot)com)