From: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
To: Alvaro Herrera <alvherre@2ndquadrant.com>
To: Robert Haas <robertmhaas@gmail.com>
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: Fri, 29 Aug 2014 16:35:35 +1200
Message-ID: <54000317.6030604@catalyst.net.nz> (raw)
In-Reply-To: <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>
<20140828205635.GA7705@eldon.alvh.no-ip.org>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
On 29/08/14 08:56, Alvaro Herrera wrote:
> 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?
>
Well no - you might have a whole lot of big tables that you want vacuum
to not get too aggressive on, but a few small tables that are highly
volatile. So you want *them* vacuumed really fast to prevent them
becoming huge tables with only a few rows therein, but your system might
not be able to handle *all* your tables being vacuum full speed.
This is a fairly common scenario for (several) web CMS systems that tend
to want to have session and/cache tables that are small and extremely
volatile, plus the rest of the (real) data that is bigger and vastly
less volatile.
While there is a valid objection along the lines of "don't use a
database instead of memcache", it does seem reasonable that Postgres
should be able to cope with this type of workload.
Cheers
Mark
--
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: mark.kirkwood@catalyst.net.nz, alvherre@2ndquadrant.com, robertmhaas@gmail.com, amit.kapila16@gmail.com, kommi.haribabu@gmail.com
Subject: Re: Per table autovacuum vacuum cost limit behaviour strange
In-Reply-To: <54000317.6030604@catalyst.net.nz>
* 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