Re: Performance autovaccum

In our use case, the default autovacuum settings did not work, I guess we are in the 5% group of users.  The default
settingswere too aggressive when it ran against some of our larger tables (example:  100M rows by 250 columns) in our
frontend OLTP database causing severe performance degradation.   We had to throttle it back (and haven't had problems
since).

You should definitely run autovacuum.  If you are only able to experiment with it in production, I recommend taking it
slowat first, and gradually making it more aggressive after you have a good handle on the impact (and observe it
runningon the larger critical tables in your data inventory without impact).  You can start with the defaults, they
aren'ttoo bad.  In our case - a backend for a high performance, highly available website with a set of webservers that
arevery sensitive to even slight query time changes, the default settings simply consumed too much overhead.  I think
inthe end all we had to change was the autovacuum_vacuum_cost_delay to make enough difference to keep our site up and
running. You should review the tuning options though.
 

The problem is if you kill the autovacuum process because you suspect it is causing issues during a crisis, the
autovacuumerwill just start it back up again a few minutes later.  If you disable it permanently your query performance
willlikely slowly degrade.  You need to find somewhere in between.
 

Oh - one last note:  If you have a partitioned table (partitioned by date), autovacuum will not run against the older
partitions(because they are no longer changing).  If you've had autovacuum off for a while, you may need to go back and
manuallyvacuum analyze the older partitions to clean them up after you get autovacuum running.  (ditto for other old
tablesthat are no longer changing)
 


-----Original Message-----
From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Joshua D.
Drake
Sent: Tuesday, July 09, 2013 8:42 PM
To: Josh Berkus
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Performance autovaccum


On 07/09/2013 03:14 PM, Josh Berkus wrote:
>
> On 07/08/2013 09:14 AM, Jeison Bedoya wrote:
>> Hi, i have a postgresql 9.2.2, but i don´t use autovaccum but i want 
>> to begin to use it.  some recommendation about the optimal configuration?
>> or some link to explain it.
>
> Initial configuration:
>
> autovacuum = on
>
> There, you're done.  You only do something else if the default 
> configuraiton is proven not to work for you.
>

Well, and a restart of PostgreSQL. It should also be noted that autovacuum by default is on. You can check to see if it
iscurrently running for you by issuing the following command from psql:
 

show autovacuum;

Other than that JoshB is correct. The default settings for autovacuum work for the 95% of users out there.

JD



--
Command Prompt, Inc. - http://www.commandprompt.com/  509-416-6579 PostgreSQL Support, Training, Professional Services
andDevelopment High Availability, Oracle Conversion, Postgres-XC, @cmdpromptinc For my dreams of your image that
blossoms
    a rose in the deeps of my heart. - W.B. Yeats


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance