Thread: Disable autovacuum on specific tables
In general, autovacuum seems to work well on most of the tables I deal with. However, in a couple of specific cases, it seems to fail miserably. I would like to switch to manual vacuuming on those tables and disable auto-vacuuming for those tables alone. Is this possible? I searched the docs and the mailing list archives, but it seems any time someone asks, their question is side-stepped with something along the lines of "you are probably doing something wrong, let's look at your situation" and it turns out they generally are or they simply don't understand what vacuuming is all about in the first place, so the question is never answered. So, before we go further, if someone could just answer for the same of future searches whether it is even possible, that would be great.
Here are the details about my specific reasoning, however, since I know you are going to want to know:
I have a daemon which listens to events from a 3rd party system and keeps a materialized view of the state of that 3rd party system in a small set of tables. In certain use cases, this 3rd party system could generate as many as 15,000 events per second which affect the materialized state of the records in the tables.
The daemon must process these events and determine whether the event inserts, updates, or deletes a record from the materialized view. It could potentially do 50 - 100 inserts or deletes per second, or as many as 500 updates per second.
The materialized tables, if vacuumed properly, stay around 20 - 30 MB in size. However, when usage spikes on the 3rd party system, there are so many transactions going against these tables that the autovacuum process constantly cancels itself so it doesn't block the pending transactions. The usage spike can persist for a long duration of time and become so intense that the size of the tables grow to 300 - 500 MB in size, each.
The database resides on a network appliance which has only a single 7200 RPM disk. When the table size grows to several hundred MB, the system becomes I/O bound due to the disk seeking around everywhere inside those large tables. Manually vacuuming the tables resolves the I/O bound state and the system behaves "normally" again.
While the state updates generally need to be real-time, we do have some windows of opportunity in which a vacuum can occur. The thread which writes to the database maintains a queue of events to process and it can vacuum the tables in between event processing. If we delay event processing every 30 - 60 seconds for the 100ms - 2s needed to vacuum analyze the tables, it does not cause any particular problem for the system. And if we can guarantee that the vacuum analyze will succeed (because nothing is writing to those tables at the time the vacuum analyze runs), then it is worth the small delay in processing the events to ensure the table is not growing out of proportion. Assuming the tables have not grown to over 100 MB each, there is enough performance available at currently conceivable loads to ensure that any delay of 10s or less in processing events will quickly be caught up.
However, this makes it unnecessary for the auto vacuum process to run on these tables. So, is there some way I can disable the automatic vacuuming of these tables?
I know I can disable auto vacuum on all tables and switch to manually vacuuming all tables, but that then requires that all developers working on this system remember to add any new tables to the list of tables to vacuum, and it requires additional code to decide when it is appropriate to vacuum those tables. The auto vacuum already does a great job on those tables, so I do not see why I should disable it on those.
Thanks in advance.
Eliot Gable
"We do not inherit the Earth from our ancestors: we borrow it from our children." ~David Brower
"I decided the words were too conservative for me. We're not borrowing from our children, we're stealing from them--and it's not even considered to be a crime." ~David Brower
"Esse oportet ut vivas, non vivere ut edas." (Thou shouldst eat to live; not live to eat.) ~Marcus Tullius Cicero
On 10/24/2012 02:57 PM, Eliot Gable wrote: > In general, autovacuum seems to work well on most of the tables I deal > with. However, in a couple of specific cases, it seems to fail > miserably. I would like to switch to manual vacuuming on those tables > and disable auto-vacuuming for those tables alone. Is this possible? ALTER TABLE foo SET (autovacuum_enabled = false, toast.autovacuum_enabled = false); -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-444-8534 sthomas@optionshouse.com ______________________________________________ See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email
Eliot Gable escribió: > In general, autovacuum seems to work well on most of the tables I deal > with. However, in a couple of specific cases, it seems to fail miserably. I > would like to switch to manual vacuuming on those tables and disable > auto-vacuuming for those tables alone. Is this possible? Of course. Exactly how you do it depends on the server version. In versions earlier than 8.4 you had to manually insert a tuple in the pg_autovacuum catalog, with its "enabled" flag set to false and ensure that all other settings are -1 (not zero). In 8.4 and up, just do ALTER TABLE foo SET (autovacuum_enabled = false) See http://www.postgresql.org/docs/8.4/static/sql-createtable.html#SQL-CREATETABLE-STORAGE-PARAMETERS for docs. -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Shaun Thomas escribió: > On 10/24/2012 02:57 PM, Eliot Gable wrote: > > >In general, autovacuum seems to work well on most of the tables I deal > >with. However, in a couple of specific cases, it seems to fail > >miserably. I would like to switch to manual vacuuming on those tables > >and disable auto-vacuuming for those tables alone. Is this possible? > > ALTER TABLE foo SET (autovacuum_enabled = false, > toast.autovacuum_enabled = false); Note that if you don't set toast.autovacuum_enabled, the toast table inherits from the main table setting. So you turn it off for both just by setting autovacuum_enabled=false. The toast setting is there so that you can set them differently if necessary. -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services