Thread: Why is postgres autovacuuming a table that is never updated?
I'm running 8.2.6. I have a log table that is always INSERTed to, and no updates or deletes are ever run on. For some reason the autovacuum decided it needed to vacuum this table, and it is slowing down my production server. So my questions are: 1) Why vacuum, if this table is never updated? 2) How can I use pg_autovacuum table to disable autovac for this table? The docs are not clear on how to do this.
Joseph S wrote: > I'm running 8.2.6. I have a log table that is always INSERTed to, and > no updates or deletes are ever run on. For some reason the autovacuum > decided it needed to vacuum this table, and it is slowing down my > production server. Perhaps it's because the table is close to Xid wraparound. Please see select age(relfrozenxid) from pg_class where oid = 'your-table'::regclass; If the age exceeds max freeze age, then this is the cause. (The freeze max age can come from the freeze_max_age param, or autovacuum_freeze_max_age, or pg_autovacuum.freeze_max_age). Hmm, maybe it's called max_freeze_age, I don't recall offhand. Beware of the pg_autovacuum column being zero. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
On Tue, Apr 29, 2008 at 02:52:39PM -0400, Joseph S wrote: > I'm running 8.2.6. I have a log table that is always INSERTed to, and no > updates or deletes are ever run on. For some reason the autovacuum decided > it needed to vacuum this table, and it is slowing down my production > server. > > So my questions are: > 1) Why vacuum, if this table is never updated? Do any INSERTs ever fail? If so, you still need to vacuum. They create dead tuples too. Also, every table in every database that accepts connections in your entire cluster (i.e. under one postmaster) MUST be vacuumed once every so many transactions. Autovacuum will notice this in 8.2 and do something about it; the docs say this: "Tables whose relfrozenxid value is more than autovacuum_freeze_max_age transactions old are always vacuumed." > 2) How can I use pg_autovacuum table to disable autovac for this table? > The docs are not clear on how to do this. I think that would be a bad idea, given that autovacuum seems to think you need to do it. Generally you want to alter autovacuum for a table only if autovacuum isn't keeping up. Recheck your assumptions before you do this. (The docs in fact tell you how to do it, but you have to read two parts of the docs to figure it out. I am trying to discourage you from doing what you're planning, so I'm unwilling to tell you how to do it.) A -- Andrew Sullivan ajs@commandprompt.com +1 503 667 4564 x104 http://www.commandprompt.com/
Andrew Sullivan wrote: > >> 2) How can I use pg_autovacuum table to disable autovac for this table? >> The docs are not clear on how to do this. > > I think that would be a bad idea, given that autovacuum seems to think > you need to do it. I don't want to totally disable it, but I want to change the numbers so it happens less frequently. Since there are no updates I can set freeze_min_page to be small and the max between transactions big to keep the autovacuum from happening so often.
Joseph S wrote: > Andrew Sullivan wrote: > >> >>> 2) How can I use pg_autovacuum table to disable autovac for this >>> table? The docs are not clear on how to do this. >> >> I think that would be a bad idea, given that autovacuum seems to think >> you need to do it. > > I don't want to totally disable it, but I want to change the numbers so > it happens less frequently. Since there are no updates I can set > freeze_min_page to be small and the max between transactions big to keep > the autovacuum from happening so often. Agreed, that's a good strategy. You only need to keep an eye on how often is pg_clog going to be trimmed. (The only disadvantage is how much spaces it occupies on disk.) -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support