Date: Thu, 28 Aug 2014 16:56:35 -0400
From: Alvaro Herrera <alvherre@2ndquadrant.com>
To: Robert Haas <robertmhaas@gmail.com>
Cc: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>,
 Amit Kapila <amit.kapila16@gmail.com>,
 Haribabu Kommi <kommi.haribabu@gmail.com>,
 "pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>
Subject: Re: Per table autovacuum vacuum cost limit behaviour
 strange
Message-ID: <20140828205635.GA7705@eldon.alvh.no-ip.org>
References: 
 <CAA4eK1JALkZOLnGdpqQgKbb+vq0eri1b7ytDfZLA8E9ZM35NHw@mail.gmail.com>
 <CAJrrPGeEwsezwA6+DpA2Y7MBJmEnNWwD5Gi4Ve_EqCxHoyHj3Q@mail.gmail.com>
 <CAA4eK1LwFs9iM-rUdfOrZE5MvjSxMM9ZbPbzv1SeWgGYu-O=VQ@mail.gmail.com>
 <53672F63.3080400@catalyst.net.nz>
 <CAA4eK1KDAUP=g59ku_0ekDoRjaGfEamyadQn5rJVt3j5oErLXg@mail.gmail.com>
 <53686F22.8030300@catalyst.net.nz>
 <20140826161911.GE6343@eldon.alvh.no-ip.org>
 <CA+Tgmoav37msTP0pVsrEYz4LBYU6SETMZGYTgPo5AEOC0yCMbA@mail.gmail.com>
 <20140828173646.GR7046@eldon.alvh.no-ip.org>
 <CA+TgmobKRho2y4tLSMQCqcRcdhVE7GMoATa=5JKo9wgV47Tc2Q@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: 
 <CA+TgmobKRho2y4tLSMQCqcRcdhVE7GMoATa=5JKo9wgV47Tc2Q@mail.gmail.com>
User-Agent: Mutt/1.5.21 (2010-09-15)
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org

Robert Haas wrote:

> I agree that you might not like that.  But you might not like having
> the table vacuumed slower than the configured rate, either.  My
> impression is that the time between vacuums isn't really all that
> negotiable for some people.  I had one customer who had horrible bloat
> issues on a table that was vacuumed every minute; when we changed the
> configuration so that it was vacuumed every 15 seconds, those problems
> went away.

Wow, that's extreme.  For that case you can set
autovacuum_vacuum_cost_limit to 0, which disables the whole thing and
lets vacuum run at full speed -- no throttling at all.  Would that
satisfy the concern?

> Now that is obviously more a matter for autovacuum_naptime
> than this option, but the point seems general to me: if you need the
> table vacuumed every N seconds, minutes, or hours, and it only gets
> vacuumed every 2N or 3N or 5N seconds, minutes, or hours because there
> are other autovacuum workers running, the table is going to bloat.

There might be another problem here which is that if you have all your
workers busy because they are vacuuming large tables that don't have
churn high enough to warrant disrupting the whole environment (thus low
cost_limit), then the table will bloat no matter what you set its cost
limit to.  So there's not only a matter of a low enough naptime (which
is a bad thing for the rest of the system, also) but also one of
something similar to priority inversion; should you speed up the
vacuuming of those large tables so that one worker is freed soon enough
to get to the high-churn table?

Was the solution for that customer to set an external tool running
vacuum on that table?

-- 
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers