From: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
To: Amit Kapila <amit.kapila16@gmail.com>
Cc: Haribabu Kommi <kommi.haribabu@gmail.com>
Cc: Alvaro Herrera <alvherre@2ndquadrant.com>
Cc: pgsql-hackers@postgresql.org <pgsql-hackers@postgresql.org>
Subject: Re: Per table autovacuum vacuum cost limit behaviour strange
Date: Tue, 06 May 2014 17:12:02 +1200
Message-ID: <53686F22.8030300@catalyst.net.nz> (raw)
In-Reply-To: <CAA4eK1KDAUP=g59ku_0ekDoRjaGfEamyadQn5rJVt3j5oErLXg@mail.gmail.com>
References: <52FACF15.8020507@catalyst.net.nz>
<CAJrrPGcvOD5OPoWCGcNdzdgbs-46gsbYHTibagy0xkRBg0bDaA@mail.gmail.com>
<52FC4AA6.50803@catalyst.net.nz>
<CAJrrPGeDEYpvHoR4m-cs2Ct9wTC3bqurreHNyVGC8smS7eig+g@mail.gmail.com>
<20140214213227.GF6342@eldon.alvh.no-ip.org>
<CAJrrPGcpZ5g5snz0PO4EEKFkTAD=uJi0VF6ruW25Mh0F34rPDw@mail.gmail.com>
<CAJrrPGdK=nhTHUCsURgXQyG6RH=rJ8b4CdYhzGhwH=UuzaO9Ng@mail.gmail.com>
<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>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
On 06/05/14 16:28, Amit Kapila wrote:
> On Mon, May 5, 2014 at 11:57 AM, Mark Kirkwood
> <mark.kirkwood@catalyst.net.nz> wrote:
>> On 05/05/14 15:22, Amit Kapila wrote:
>> Right, but have a look at the 1st message in this thread - the current
>> behavior (and to a large extent the above condition) means that setting
>> cost limits per table does not work in any remotely intuitive way.
>>
>> ITSM that the whole purpose of a per table setting in this context is to
>> override the behavior of auto vacuum throttling - and currently this does
>> not happen unless you get real brutal (i.e setting the cost delay to zero in
>> addition to setting cost limit...making the whole cost limit a bit
>> pointless).
> I think meaning of per table setting is just that it overrides the default
> value of autovacuum_vacuum_cost_limit for that table and the rest of
> calculation or concept remains same. This is what currently code does
> and the same is mentioned in docs as far as I can understand.
>
> As per current behaviour the per-table cost_limit is also adjusted based
> on the setting of GUC autovacuum_vacuum_cost_limit and right now it
> follows a simple principle that the total cost limit for all workers should be
> equal to autovacuum_vacuum_cost_limit. Even code has below comment:
>
> /*
> * Adjust cost limit of each active worker to balance the total of cost
> * limit to autovacuum_vacuum_cost_limit.
> */
>
> Now If you want to change for the case where user specifies value per
> table which is more than autovacuum_vacuum_cost_limit or otherwise,
> then I think the new definition should be bit more clear and it is better
> not to impact current calculation for default values.
>
> I could think of 2 ways to change this:
>
> a. if user has specified cost_limit value for table, then it just uses it
> rather than rebalancing based on value of system-wide guc variable
> autovacuum_vacuum_cost_limit
> b. another could be to restrict setting per-table value to be lesser than
> system-wide value?
>
> The former is used for auto vacuum parameters like scale_factor and
> later is used for parameters like freeze_max_age.
>
> Thoughts?
>
> Alvaro, do you think above options makes sense to solve this problem?
Yes indeed - the code currently working differently from what one would
expect. However the usual reason for handing knobs to the user for
individual object is so that special configurations can be applied to
them. The current method of operation of the per table knobs does not do
this (not without clubbing 'em on the head)
The (ahem) sensible way that one would expect (perhaps even need)
autovacuum throttling to work is:
- set sensible defaults for all the usual (well behaved) tables
- set a few really aggressive overrides for a handful of the naughty ones
Runaway free space bloat is one of the things that can really mangle a
postgres system (I've been called in to rescue a few in my time)...
there needs to be a way to control those few badly behaved tables ...
without removing the usefulness of throttling the others.
Regards
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, amit.kapila16@gmail.com, kommi.haribabu@gmail.com, alvherre@2ndquadrant.com
Subject: Re: Per table autovacuum vacuum cost limit behaviour strange
In-Reply-To: <53686F22.8030300@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