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

Re: Postgres becoming slow, only full vacuum fixes it

From: Kiriakos Tsourapas <ktsour(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: Postgres becoming slow, only full vacuum fixes it
Date: 2012-09-26 13:50:29
Message-ID: 34C83EA2-7004-4D37-9674-ADFB4A475C07@gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
Hi Kevin,

On Sep 26, 2012, at 14:39, Kevin Grittner wrote:
> 
> I am concerned that your initial email said that you had this
> setting:
> 
> autovacuum_naptime = 28800
> 
> This is much too high for most purposes; small, frequently-modified
> tables won't be kept in good shape with this setting.  Perhaps you're
> not having that problem at the moment, but it's risky to assume that
> you don't and never will.  When autovacuum wakes up and there is
> nothing to do it should go back to sleep very quickly.
> 

I used the 28800 (8hours) setting after I realized that the default 1min was not helping.
I also changed other parameters when I changed it to 8 hours, to make sure tables would be auto vacuumed.
The problem with my setting was that autovacuum gets stopped if a lock is needed on the table. So, it was very bad choice to run it every 8 hours, because usually it got stopped and never did anything.
So, I turned back to the original setting of 1min but changed the autovacuum_vacuum_scale_factor to 1% instead of 20%. Hopefully tables will be more frequently vacuumed now and the problem will not appear again.

> Don't expect too much from just making autovacuum run more often
> until you have eliminated existing bloat (autovacuum generally just
> limits further growth of bloat) and updated to the latest 8.4 minor
> release.  The following bugs fixes are among many you are living
> without until you upgrade:

Can you please suggest of a way to 
- find if there is existing bloat
- eliminate it

Thank you

Responses

pgsql-performance by date

Next:From: FFW_RudeDate: 2012-09-26 14:14:19
Subject: Re: Same query doing slow then quick
Previous:From: Julien CigarDate: 2012-09-26 13:49:22
Subject: Re: Same query doing slow then quick

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