long-running autovacuum tasks

From: Mark Rostron <mrostron(at)ql2(dot)com>
To: "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org>
Subject: long-running autovacuum tasks
Date: 2010-09-22 22:18:02
Message-ID: FD020D3E50E7FA479567872E5F5F31E30459FB5232@ex01.corp.ql2.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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?

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?

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

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

Thanks in advance for your time
Mr

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Lukasz Brodziak 2010-09-23 12:37:57 Re: pgdview
Previous Message Plugge, Joe R. 2010-09-22 17:27:24 Re: postgresql logs