Received: from maia.hub.org (maia-5.hub.org [200.46.204.29])
	by mail.postgresql.org (Postfix) with ESMTP id 1A3E11337B4A
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Fri, 21 Jan 2011 19:04:44 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024)
	with ESMTP id 94788-07 for
	<pgsql-admin-postgresql.org@mail.postgresql.org>;
	Fri, 21 Jan 2011 23:04:38 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130])
	by mail.postgresql.org (Postfix) with ESMTP id 5B3161337990
	for <pgsql-admin@postgresql.org>; Fri, 21 Jan 2011 19:04:35 -0400 (AST)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id p0LN4607001428;
	Fri, 21 Jan 2011 18:04:06 -0500 (EST)
To: "John Lister" <john.lister-ps@kickstone.com>
cc: pgsql-admin@postgresql.org
Subject: Re: Should autovacuum do a database wide vacuum near transaction
	limit?
In-reply-to: <927BF3A3F54247FF8C63219A00CE98B5@squarepi.com> 
References: <927BF3A3F54247FF8C63219A00CE98B5@squarepi.com>
Comments: In-reply-to "John Lister" <john.lister-ps@kickstone.com>
	message dated "Fri, 21 Jan 2011 21:48:24 +0000"
Date: Fri, 21 Jan 2011 18:04:06 -0500
Message-ID: <1427.1295651046@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.91 tagged_above=-10 required=5 tests=BAYES_00=-1.9,
	T_RP_MATCHES_RCVD=-0.01
X-Spam-Level: 
X-Archive-Number: 201101/142
X-Sequence-Number: 36582

"John Lister" <john.lister-ps@kickstone.com> writes:
> Hi,  I'm running 8.3.9 on ubuntu with autovacuum enabled and seemingly working properly., however I've started getting messages saying that I'm near the transaction limit and I need to do a database wide vacuum, which I've started.
> From reading the docs, though I thought that autovacuum would do this on my behalf, is this true?

It's supposed to.  Did you look around to see if AV was trying to do
anything?

It's possible you got bit by this recently-fixed bug, which caused any
large-scale autovacuum operation to take unreasonably long to complete:

Author: Tom Lane <tgl@sss.pgh.pa.us>
Branch: master [b58c25055] 2010-11-19 22:28:20 -0500
Branch: REL9_0_STABLE Release: REL9_0_2 [b5efc0940] 2010-11-19 22:28:25 -0500
Branch: REL8_4_STABLE Release: REL8_4_6 [fab2af30d] 2010-11-19 22:28:30 -0500
Branch: REL8_3_STABLE Release: REL8_3_13 [6cb9d5113] 2010-11-19 22:28:35 -0500

    Fix leakage of cost_limit when multiple autovacuum workers are active.
    
    When using default autovacuum_vac_cost_limit, autovac_balance_cost relied
    on VacuumCostLimit to contain the correct global value ... but after the
    first time through in a particular worker process, it didn't, because we'd
    trashed it in previous iterations.  Depending on the state of other autovac
    workers, this could result in a steady reduction of the effective
    cost_limit setting as a particular worker processed more and more tables,
    causing it to go slower and slower.  Spotted by Simon Poole (bug #5759).
    Fix by saving and restoring the GUC variables in the loop in do_autovacuum.
    
    In passing, improve a few comments.
    
    Back-patch to 8.3 ... the cost rebalancing code has been buggy since it was
    put in.

			regards, tom lane