On 06/05/14 16:28, Amit Kapila wrote:
On Mon, May 5, 2014 at 11:57 AM, Mark Kirkwood
<mark.kirkw...@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
