Message-ID: <52FACF15.8020507@catalyst.net.nz>
Date: Wed, 12 Feb 2014 14:32:05 +1300
From: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
 rv:24.0) Gecko/20100101 Thunderbird/24.2.0
MIME-Version: 1.0
To: pgsql-hackers@postgresql.org
Subject: Per table autovacuum vacuum cost limit behaviour strange
Content-Type: multipart/mixed; boundary="------------020800070207010305050805"
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org

This is a multi-part message in MIME format.
--------------020800070207010305050805
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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

--------------020800070207010305050805
Content-Type: text/x-sql;
 name="schema.sql"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
 filename="schema.sql"

CREATE TABLE cache0(id SERIAL PRIMARY KEY, val TEXT);

--------------020800070207010305050805
Content-Type: text/x-sql;
 name="volatile0.sql"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
 filename="volatile0.sql"

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;

--------------020800070207010305050805
Content-Type: text/plain
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
MIME-Version: 1.0


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

--------------020800070207010305050805--