Skip site navigation (1) Skip section navigation (2)

Re: Postgres becoming slow, only full vacuum fixes it

From: Ondrej Ivanič <ondrej(dot)ivanic(at)gmail(dot)com>
To: Kiriakos Tsourapas <ktsour(at)gmail(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Postgres becoming slow, only full vacuum fixes it
Date: 2012-09-25 23:14:06
Message-ID: CAM6mieKe8U=m4+q7usOLA8pw2KSBmR1QVVqHAgPbgVxj+ujUNA@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
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)


In response to

Responses

pgsql-performance by date

Next:From: Scott MarloweDate: 2012-09-26 00:56:45
Subject: Re: Memory issues
Previous:From: Kevin GrittnerDate: 2012-09-25 12:40:55
Subject: Re: Postgres becoming slow, only full vacuum fixes it

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group