From: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
To: Haribabu Kommi <kommi.haribabu@gmail.com>
Cc: pgsql-hackers@postgresql.org <pgsql-hackers@postgresql.org>
Subject: Re: Per table autovacuum vacuum cost limit behaviour strange
Date: Thu, 13 Feb 2014 17:31:34 +1300
Message-ID: <52FC4AA6.50803@catalyst.net.nz> (raw)
In-Reply-To: <CAJrrPGcvOD5OPoWCGcNdzdgbs-46gsbYHTibagy0xkRBg0bDaA@mail.gmail.com>
References: <52FACF15.8020507@catalyst.net.nz>
<CAJrrPGcvOD5OPoWCGcNdzdgbs-46gsbYHTibagy0xkRBg0bDaA@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackersAttachments:
[text/x-patch] autovacuum.c.diff (1.1K, ../52FC4AA6.50803@catalyst.net.nz/2-autovacuum.c.diff)
download | inline diff:diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.cindex 77e683a..246607c 100644--- a/src/backend/postmaster/autovacuum.c+++ b/src/backend/postmaster/autovacuum.c@@ -1739,6 +1739,12 @@ AutoVacuumUpdateDelay(void)
{
VacuumCostDelay = MyWorkerInfo->wi_cost_delay;
VacuumCostLimit = MyWorkerInfo->wi_cost_limit;
+ elog(LOG, "autovac_update_delay(pid=%u db=%u, rel=%u, cost_limit=%d, cost_delay=%d)",+ MyWorkerInfo->wi_proc->pid,+ MyWorkerInfo->wi_dboid, + MyWorkerInfo->wi_tableoid,+ MyWorkerInfo->wi_cost_limit,+ MyWorkerInfo->wi_cost_delay);
}
}
@@ -1811,7 +1817,7 @@ autovac_balance_cost(void)
worker->wi_cost_limit_base),
1);
- elog(DEBUG2, "autovac_balance_cost(pid=%u db=%u, rel=%u, cost_limit=%d, cost_limit_base=%d, cost_delay=%d)",+ elog(LOG, "autovac_balance_cost(pid=%u db=%u, rel=%u, cost_limit=%d, cost_limit_base=%d, cost_delay=%d)",
worker->wi_proc->pid, worker->wi_dboid, worker->wi_tableoid,
worker->wi_cost_limit, worker->wi_cost_limit_base,
worker->wi_cost_delay);
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: mark.kirkwood@catalyst.net.nz, kommi.haribabu@gmail.com
Subject: Re: Per table autovacuum vacuum cost limit behaviour strange
In-Reply-To: <52FC4AA6.50803@catalyst.net.nz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox