Message-ID: <52FC4AA6.50803@catalyst.net.nz>
Date: Thu, 13 Feb 2014 17:31:34 +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: 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
References: <52FACF15.8020507@catalyst.net.nz>
 <CAJrrPGcvOD5OPoWCGcNdzdgbs-46gsbYHTibagy0xkRBg0bDaA@mail.gmail.com>
In-Reply-To: 
 <CAJrrPGcvOD5OPoWCGcNdzdgbs-46gsbYHTibagy0xkRBg0bDaA@mail.gmail.com>
Content-Type: multipart/mixed; boundary="------------060901010606080500040001"
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org

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

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


--------------060901010606080500040001
Content-Type: text/x-patch;
 name="autovacuum.c.diff"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
 filename="autovacuum.c.diff"

diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c
index 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);

--------------060901010606080500040001
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

--------------060901010606080500040001--