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-owner@xxxxxxxxxxxxxx [mailto:pgsql-performance-owner@xxxxxxxxxxxxxx] On Behalf Of Joshua D. Drake Sent: Tuesday, July 09, 2013 8:42 PM To: Josh Berkus Cc: pgsql-performance@xxxxxxxxxxxxxx Subject: Re: 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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
