Alvaro Herrera wrote:
> Basically, if you are on 9.3.5 or earlier any per-table options for
> autovacuum cost delay will misbehave (meaning: any such table will be
> processed with settings flattened according to balancing of the standard
> options, _not_ the configured ones). If you are on 9.3.6 or newer they
> will behave as described in the docs.
Another thing to note is that if you have configured a table to have
cost_limit *less* than the default (say 150 instead of the default 200),
the balance system will again break that and process the table at 200
instead; in other words, the balancing system has completely broken the
ability to tweak the cost system for individual tables in autovacuum.
With the current code you can change the *ratios* of cost_limit that
would be applied (by setting one table to 5000 and another to 150, when
they are vacuumed in parallel you will get them at 194 and 5 instead of
the values you set, but the total limit allowed will never be exceeded
no matter what you do.)
With the v5 patch, the example tables above will be vacuumed at exactly
5000 and 150 instead. The more complex patch I produced earlier would
have them vacuumed at something like 4900 and 100 instead, so you
wouldn't exceed the total of 5000. I think there is some value to that
idea, but it seems the complexity of managing this is too high.
The ill effect of the patch is that it will be possible to have
autovacuum utilize a much larger fraction of the I/O bandwidth. This
was also possible before, but only by setting the cost delay to 0 (in
effect disabling throttling for the table). If somebody is thinking
that you could mount an attack to a server by having tables that use all
the I/O bandwidth, well -- it's not a new problem so I don't think we
need to solve it with this patch.
I am rather surprised that nobody has reported this problem before. I
am now of the mind that this is clearly a bug that should be fixed all
the way back.
--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services