From: | Mark Kirkwood <mark(dot)kirkwood(at)catalyst(dot)net(dot)nz> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Per table autovacuum vacuum cost limit behaviour strange |
Date: | 2014-02-12 01:32:05 |
Message-ID: | 52FACF15.8020507@catalyst.net.nz |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
Attachment | Content-Type | Size |
---|---|---|
schema.sql | text/x-sql | 54 bytes |
volatile0.sql | text/x-sql | 713 bytes |
From | Date | Subject | |
---|---|---|---|
Next Message | Andrew Dunstan | 2014-02-12 01:33:20 | Re: narwhal and PGDLLIMPORT |
Previous Message | Craig Ringer | 2014-02-12 01:04:18 | Re: narwhal and PGDLLIMPORT |