Re: 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:
>
> 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.