Re: Per table autovacuum vacuum cost limit behaviour strange - Mailing list pgsql-hackers

On 13/02/14 17:13, Haribabu Kommi wrote:
> On Wed, Feb 12, 2014 at 12:32 PM, Mark Kirkwood <
> mark.kirkwood@catalyst.net.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.
>

FWIW - I can confirm these calculations in 9.4devel. I found the
attached patch handy for logging what the balanced limit and delay was.

Regards

Mark