In our use case, the default autovacuum settings did not work, I guess we are
in the 5% group of users. The default settings were too aggressive when it ran
against some of our larger tables (example: 100M rows by 250 columns) in our
front end 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 slow at first, and gradually making it
more aggressive after you have a good handle on the impact (and observe it
running on the larger critical tables in your data inventory without impact).
You can start with the defaults, they aren't too bad. In our case - a backend
for a high performance, highly available website with a set of webservers that
are very sensitive to even slight query time changes, the default settings
simply consumed too much overhead. I think in the 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 autovacuumer will just start it back up
again a few minutes later. If you disable it permanently your query
performance will likely 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 manually vacuum analyze the older partitions to clean them up after
you get autovacuum running. (ditto for other old tables that are no longer
changing)
-Original Message-
From: pgsql-performance-ow...@postgresql.org
[mailto:pgsql-performance-ow...@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 is currently 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 and Development 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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
