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).
Regards
Mark
