Skip site navigation (1) Skip section navigation (2)

Re: long-running autovacuum tasks

From: Brad Nicholson <bnichols(at)ca(dot)afilias(dot)info>
To: pgsql-admin(at)postgresql(dot)org
Subject: Re: long-running autovacuum tasks
Date: 2010-09-23 12:53:05
Message-ID: 4C9B4DB1.7010701@ca.afilias.info (view raw or flat)
Thread:
Lists: pgsql-admin
  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.


In response to

Responses

pgsql-admin by date

Next:From: Kevin GrittnerDate: 2010-09-23 17:01:53
Subject: Re: FATAL: the database system is starting up
Previous:From: Fujii MasaoDate: 2010-09-23 12:48:05
Subject: Re: FATAL: the database system is starting up

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group