Re: long-running autovacuum tasks - Mailing list pgsql-admin
From | Brad Nicholson |
---|---|
Subject | Re: long-running autovacuum tasks |
Date | |
Msg-id | 4C9B4DB1.7010701@ca.afilias.info Whole thread Raw |
In response to | long-running autovacuum tasks (Mark Rostron <mrostron@ql2.com>) |
Responses |
Re: long-running autovacuum tasks
|
List | pgsql-admin |
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:
1. Am I correct in assuming the following?
a. 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.
b. 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.
2. 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.
3. 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.
pgsql-admin by date: