From: Alvaro Herrera <alvherre@2ndquadrant.com>
To: Robert Haas <robertmhaas@gmail.com>
Cc: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
Cc: Amit Kapila <amit.kapila16@gmail.com>
Cc: Haribabu Kommi <kommi.haribabu@gmail.com>
Cc: pgsql-hackers@postgresql.org <pgsql-hackers@postgresql.org>
Subject: Re: Per table autovacuum vacuum cost limit behaviour strange
Date: Thu, 28 Aug 2014 16:56:35 -0400
Message-ID: <20140828205635.GA7705@eldon.alvh.no-ip.org> (raw)
In-Reply-To: <CA+TgmobKRho2y4tLSMQCqcRcdhVE7GMoATa=5JKo9wgV47Tc2Q@mail.gmail.com>
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>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: alvherre@2ndquadrant.com, robertmhaas@gmail.com, mark.kirkwood@catalyst.net.nz, amit.kapila16@gmail.com, kommi.haribabu@gmail.com
Subject: Re: Per table autovacuum vacuum cost limit behaviour strange
In-Reply-To: <20140828205635.GA7705@eldon.alvh.no-ip.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox