Re: Per table autovacuum vacuum cost limit behaviour strange

On Wed, Feb 12, 2014 at 12:32 PM, Mark Kirkwood <
mark(dot)kirkwood(at)catalyst(dot)net(dot)nz> wrote:

> A while back we were discussing rapid space bloat of tables under certain
> circumstances. One further case I am examining is a highly volatile single
> table, and how to tame its space blowout.
>
> I've got a nice simple example (attached). Making use of pgbench to run it
> as usual ():
>
> $ createdb cache
> $ psql cache < schema.sql
> $ pgbench -n -c8 -T300 -f volatile0.sql cache
>
> ...causes the table (imaginatively named 'cache0') to grow several GB with
> default autovacuum parameters. Some minimal changes will rein in the growth
> to about 100MB:
>
> $ grep -e naptime -e autovacuum_vacuum_cost_limit postgresql.conf
> autovacuum_naptime = 5s
> autovacuum_vacuum_cost_limit = 10000
>
> However the cost_limit setting is likely to be way too aggressive
> globally. No problem I figured, I'd leave it at the default (200) and use
> ALTER TABLE to change it for *just* the 'cache0' table:
>
> cache=# ALTER TABLE cache0 SET (autovacuum_vacuum_cost_limit=10000);
>
> However re-running the pgbench test results in several GB worth of space
> used by this table. Hmmm - looks like setting this parameter per table does
> not work how I expected. Looking at src/backend/postmaster/autovacuum.c I
> see some balancing calculations in autovac_balance_cost() and
> AutoVacuumUpdateDelay(), the effect which seems to be (after adding some
> debugging elogs) to reset the actual effective cost_limit back to 200 for
> this table: viz (rel 16387 is cache0):
>
>
> LOG: autovac_balance_cost(pid=24058 db=16384, rel=16387, cost_limit=200,
> cost_limit_base=10000, cost_delay=20)
> LOG: autovac_update_delay(pid=24058 db=16384, rel=16387, cost_limit=200,
> cost_delay=20)
>
> Is this working as intended? I did wonder if it was an artifact of only
> having 1 table (creating another one made no difference)...or perhaps only
> 1 active worker... I found I had to lobotomize the balancing calc by doing:
>
> cache=# ALTER TABLE cache0 SET (autovacuum_vacuum_cost_delay=0);
>
> before I got the same effect as just setting the cost_limit globally. I'm
> now a bit confused about whether I understand how setting cost_limit and
> cost_delay via ALTER TABLE works (or in fact if it is working properly for
> that matter).
>

When I go through the code for checking the same, I got the following
behavior.

The default values of vacuum parameters - cost_limit - 200 and cost_delay -
0
The default values of auto vacuum parameters - cost_limit - (-1) and
cost_delay - 20ms.

1. User is not provided any vacuum parameters to the table, so the vacuum
options for the table are cost_limit - 200 and cost_delay - 20
2. User is provided cost_limit as 1000 to the table, so the vacuum options
for the table are cost_limit - 1000 and cost_delay - 20

For the above two cases, the "autovac_balance_cost" function sets the cost
parameters as cost_limit - 200 and cost_delay - 20.

3. User is provided cost_limit as 1000 and cost_delay as 10 to the table,
so the vacuum options for the table are cost_limit - 1000 and cost_delay -
10

This case the cost_limit - 100 and cost_delay - 10.

4. User is provided cost_limit as 1000 and cost_delay as 100 to the table,
so the vacuum options for the table are cost_limit - 1000 and cost_delay -
100

This case the cost_limit - 1000 and cost_delay - 100

From the above observations, The cost parameters of vacuum are not working
as they specified.
please correct me if anything wrong in my observation.

Regards,
Hari Babu
Fujitsu Australia