Re: long-running autovacuum tasks

From: pgsql-admin-owner(at)postgresql(dot)org [mailto:pgsql-admin-owner(at)postgresql(dot)org] On Behalf Of Brad Nicholson
Sent: Thursday, September 23, 2010 5:53 AM
To: pgsql-admin(at)postgresql(dot)org
Subject: Re: [ADMIN] long-running autovacuum tasks

On 10-09-22 06:18 PM, Mark Rostron wrote:
Version of pg server is 8.3.10
We have some very large tables (78 gb/ 60m rows, 132 gb/90m rows).
Storage is mounted NFS on a netapp 3160 (pretty fast I/O).
However, the tables have been autovacuuming for over 3 days (from querying pg-stat-activity).
The current_query column value is : "autovacuum: VACUUM public.error (to prevent wraparound)", with similar messages on the other two.

I have read the section in the manual describing auto-vacuum behavior:
http://www.postgresql.org/docs/8.3/interactive/routine-vacuuming.html#AUTOVACUUM

Ok - so here are my questions:

Am I correct in assuming the following?

Auto-vacuum workers are throttled to minimize system load - this would account for the long running time of the auto-vacuum workers which I am currently observing?

What are the values you have autovacuum_cost_delay and autovacuum_cost_limit? If they are throttled, then yes, it will be increasing the time of the vacuums.

Also, too low of a value for maintenance_work_mem can cause vacuums to run long as well.

While it is possible to turn off auto-vacuum for most reasons, it is not desirable (or possible) to avoid auto-vacuum when the table is in a potential transaction wraparound state: hence the auto-vacuum message "to prevent wraparound" in the pg_stat_activity table?

You usually don't want to turn autovacuum off. And you really don't want to do anything to prevent it from doing it's job when transaction wraparound is the reason, unless you want to lose data.

Would a regular (i.e. daily) cron-scheduled job of "vacuum analyze verbose" on the tables in question do this task any better?

Usually not. Tuning autovacuum is the best way to go in most cases. I'd only go down the road of cron driven vacuums if you have a solid understanding of the access patterns in your database, how autovacuum is processing tables according to those, and evidence that autovacuum is not working for you.

Not that you can specify custom autovacuum settings on a table by table basis, which is often needed.

Am I correct to be concerned about the long running time of the auto-vacuum workers?

I would be looking at that. 3 days is too long. That's 3 days where routine vacuuming (if needed in that time frame) is not happening, and 3 days where you have one of your autovacuum workers tied up on that table and not available to process other tables as needed.

What you can do to get out of this state now, assuming that you have autovacuum_delay_costs are set, is cancel the autovacuum's vacuum and trigger a manual vacuum against the tables. Be sure that the vacuum_delay_cost features are off (note, there are autovacuum_delay and vacuum_delay - the plain vacuum_delay_* are for manual vacuums).

Be sure you have the IO to spare though, as it can be IO intensive.

--

Brad Nicholson 416-673-4106

Database Administrator, Afilias Canada Corp.

Thanks for the reply.

The autovacuums are still running, however, I can see progress through one of the workers because the table is changing.

Our current parameters are:

autovacuum | on | Starts the autovacuum subprocess.

autovacuum_analyze_scale_factor | 0.1 | Number of tuple inserts, updates or deletes prior to analyze as a fraction of reltuples.

autovacuum_analyze_threshold | 50 | Minimum number of tuple inserts, updates or deletes prior to analyze.

autovacuum_freeze_max_age | 200000000 | Age at which to autovacuum a table to prevent transaction ID wraparound.

autovacuum_max_workers | 3 | Sets the maximum number of simultaneously running autovacuum worker processes.

autovacuum_naptime | 1min | Time to sleep between autovacuum runs.

autovacuum_vacuum_cost_delay | 20ms | Vacuum cost delay in milliseconds, for autovacuum.

autovacuum_vacuum_cost_limit | -1 | Vacuum cost amount available before napping, for autovacuum.

autovacuum_vacuum_scale_factor | 0.1 | Number of tuple updates or deletes prior to vacuum as a fraction of reltuples.

autovacuum_vacuum_threshold | 50 | Minimum number of tuple updates or deletes prior to vacuum.

maintenance_work_mem | 256MB | Sets the maximum memory to be used for maintenance operations.