[Mark Kirkwood <mark.kirkwood@catalyst.net.nz>]

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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Attachments:

  [text/x-sql] schema.sql (53B, ../52FACF15.8020507@catalyst.net.nz/2-schema.sql)
  download | inline:
CREATE TABLE cache0(id SERIAL PRIMARY KEY, val TEXT);

  [text/x-sql] volatile0.sql (712B, ../52FACF15.8020507@catalyst.net.nz/3-volatile0.sql)
  download | inline:
BEGIN;
\set nrows 100
INSERT INTO cache0 (val) SELECT s.val FROM ( SELECT generate_series(1, :nrows), 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx' AS val) AS s;
DELETE FROM cache0 WHERE id IN (SELECT id FROM cache0 ORDER BY id DESC LIMIT :nrows);
COMMIT;