Re: Performance autovaccum
От | Rick Otten |
---|---|
Тема | Re: Performance autovaccum |
Дата | |
Msg-id | 7A1B720615F7D9458BE37FA699EF0794154D5E7A@cmhvm033.ecnext03.local обсуждение исходный текст |
Ответ на | Re: Performance autovaccum ("Joshua D. Drake" <jd@commandprompt.com>) |
Список | pgsql-performance |
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
В списке pgsql-performance по дате отправления: