Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lAefh-0000mm-NB for pgsql-hackers@arkaria.postgresql.org; Fri, 12 Feb 2021 20:04:10 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1lAeff-0005Kc-3u for pgsql-hackers@arkaria.postgresql.org; Fri, 12 Feb 2021 20:04:07 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lAefe-0005KV-P1 for pgsql-hackers@lists.postgresql.org; Fri, 12 Feb 2021 20:04:06 +0000 Received: from sender4-op-o13.zoho.com ([136.143.188.13]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lAefZ-0000ts-MY for pgsql-hackers@postgresql.org; Fri, 12 Feb 2021 20:04:06 +0000 ARC-Seal: i=1; a=rsa-sha256; t=1613160233; cv=none; d=zohomail.com; s=zohoarc; b=fos6NMeZ535t9ukw7hKDs5XMYXYlKq0r/uh0R4xTVm7VN5QKU4hczGCVBWgorEIJqjIZviJcEAH6R8/dh04qkk7SDuctpd9AdyufWQCUhqIKrVZxAJflOR8Uoy5rd1K1HXwWoNTZRXPbCP6hkQ88ZS1fE2thChCTMoD5JiTMAtY= ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=zohomail.com; s=zohoarc; t=1613160233; h=Content-Type:Date:From:In-Reply-To:MIME-Version:Message-ID:References:Subject:To; bh=R/pm2EYFagiZHOzQwosiuZQmfGR+mvHnrvlm5TpFI2A=; b=NvFk68J/faW1JUsc2dQsQuwbmcihIXjlI1bEyaquEg6cFgoLUBRZSh99uChTWgjekMuQaD4w6k2/r/Ru1NYcC7HJIYsB9ZNnkCLmWtyHMtF67/73YWhm1RP3LWm9gMyjeMsC72gOxWKTueLTZkffPMpuJGOB47m8vYow22Ucd2c= ARC-Authentication-Results: i=1; mx.zohomail.com; dkim=pass header.i=highgo.ca; spf=pass smtp.mailfrom=david.zhang@highgo.ca; dmarc=pass header.from= header.from= DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; t=1613160233; s=zoho; d=highgo.ca; i=david.zhang@highgo.ca; h=Subject:To:References:From:Message-ID:Date:MIME-Version:In-Reply-To:Content-Type; bh=R/pm2EYFagiZHOzQwosiuZQmfGR+mvHnrvlm5TpFI2A=; b=Y/tiUJpr2Xfpc+RasdsoFvCAc4geTiie+UWpmyxDD3y+ThamoZr19rWcKUJZt4hh gCa5ZPak5LYIDz2fxMknNPceFTB8Gm22x8QN0N+s4IWLLe6QU0tvu7NWavHpLUG3kiO IIg4sl2W0CbhsSXedwpCFxwg1BH3nMP1M+JYpK5k= Received: from david.highgo.ca (d142-179-59-33.bchsia.telus.net [142.179.59.33]) by mx.zohomail.com with SMTPS id 1613160231363552.9804713513922; Fri, 12 Feb 2021 12:03:51 -0800 (PST) Subject: Re: [BUG] Autovacuum not dynamically decreasing cost_limit and cost_delay To: "Mead, Scott" , "pgsql-hackers@postgresql.org" References: <0A3F8A3C-4328-4A4B-80CF-14CEBE0B695D@amazon.com> From: David Zhang Message-ID: <4e980c7d-9997-5ab7-472c-75f377b26a76@highgo.ca> Date: Fri, 12 Feb 2021 12:03:56 -0800 User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.16; rv:78.0) Gecko/20100101 Thunderbird/78.7.1 MIME-Version: 1.0 In-Reply-To: <0A3F8A3C-4328-4A4B-80CF-14CEBE0B695D@amazon.com> Content-Type: multipart/alternative; boundary="------------6F19AF286FBDE4648A773EA9" Content-Language: en-US X-ZohoMailClient: External List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------6F19AF286FBDE4648A773EA9 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: quoted-printable Thanks for the patch, Mead. For 'MAXVACUUMCOSTLIMIT", it would be nice to follow the current GUC=20 pattern to do define a constant. For example, the constant "MAX_KILOBYTES" is defined in guc.h, with a=20 pattern like, "MAX_" to make it easy to read. Best regards, David On 2021-02-08 6:48 a.m., Mead, Scott wrote: > Hello, > =C2=A0=C2=A0 I recently looked at what it would take to make a running=20 > autovacuum pick-up a change to either cost_delay or cost_limit.=C2=A0 Use= rs=20 > frequently will have a conservative value set, and then wish to change=20 > it when autovacuum initiates a freeze on a relation.=C2=A0 Most users end= =20 > up finding out they are in =E2=80=98to prevent wraparound=E2=80=99 after = it has=20 > happened, this means that if they want the vacuum to take advantage of=20 > more I/O, they need to stop and then restart the currently running=20 > vacuum (after reloading the GUCs). > =C2=A0 Initially, my goal was to determine feasibility for making this=20 > dynamic.=C2=A0 I added debug code to vacuum.c:vacuum_delay_point(void) an= d=20 > found that changes to cost_delay and cost_limit are already processed=20 > by a running vacuum.=C2=A0 There was a bug preventing the cost_delay or= =20 > cost_limit from being configured to allow higher throughput however. > I believe this is a bug because currently, autovacuum will dynamically=20 > detect and /increase/ the cost_limit or cost_delay, but it can never=20 > decrease those values=C2=A0beyond their setting when the vacuum began. = =C2=A0The=20 > current behavior is for vacuum to limit the maximum throughput of=20 > currently running vacuum processes to the cost_limit that was set when=20 > the vacuum process began. > I changed this (see attached) to allow the cost_limit to be=20 > re-calculated up to the maximum allowable (currently 10,000).=C2=A0 This= =20 > has the effect of allowing users to reload a configuration change and=20 > an in-progress vacuum can be =E2=80=98sped-up=E2=80=99 by setting either = the=20 > cost_limit or cost_delay. > The problematic piece is: > diff --git a/src/backend/postmaster/autovacuum.c=20 > b/src/backend/postmaster/autovacuum.c > index c6ec657a93..d3c6b0d805 100644 > --- a/src/backend/postmaster/autovacuum.c > +++ b/src/backend/postmaster/autovacuum.c > @@ -1834,7 +1834,7 @@ autovac_balance_cost(void) > * cost_limit to more than the base value. > */ > worker->wi_cost_limit =3D *Max(Min(limit,* > *- worker->wi_cost_limit_base*), > + =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0MAXVACUUMCOSTLIMIT), > 1); > } > We limit the worker to the max cost_limit that was set at the=20 > beginning of the vacuum.=C2=A0 I introduced the MAXVACUUMCOSTLIMIT consta= nt=20 > (currently defined to 10000, which is the currently max limit already=20 > defined) in miscadmin.h so that vacuum will now be able to adjust the=20 > cost_limit up to 10000 as the upper limit in a currently running vacuum. > > The tests that I=E2=80=99ve run show that the performance of an existing= =20 > vacuum can be increased=C2=A0commensurate=C2=A0with the parameter change.= =20 > =C2=A0Interestingly, /autovac_balance_cost(void) /is only updating the=20 > cost_limit, even if the cost_delay is modified. =C2=A0This is done=20 > correctly, it was just a surprise to see the behavior. > > > 2021-02-01 13:36:52.346 EST [37891] DEBUG: =C2=A0VACUUM Sleep: Delay:=20 > 20.000000, CostBalance: 207, CostLimit: *200*, msec: 20.700000 > 2021-02-01 13:36:52.346 EST [37891] CONTEXT: =C2=A0while scanning block= =20 > 1824 of relation "public.blah" > 2021-02-01 13:36:52.362 EST [36460] LOG: =C2=A0received SIGHUP, reloading= =20 > configuration files > * > * > *2021-02-01 13:36:52.364 EST [36460] LOG: =C2=A0parameter=20 > "autovacuum_vacuum_cost_delay" changed to "2"* > \ > 2021-02-01 13:36:52.365 EST [36463] DEBUG: =C2=A0checkpointer updated=20 > shared memory configuration values > 2021-02-01 13:36:52.366 EST [36466] DEBUG:=20 > =C2=A0autovac_balance_cost(pid=3D37891 db=3D13207, rel=3D16384, dobalance= =3Dyes=20 > cost_limit=3D2000, cost_limit_base=3D200, cost_delay=3D20) > > 2021-02-01 13:36:52.366 EST [36467] DEBUG: =C2=A0received inquiry for=20 > database 0 > 2021-02-01 13:36:52.366 EST [36467] DEBUG: =C2=A0writing stats file=20 > "pg_stat_tmp/global.stat" > 2021-02-01 13:36:52.366 EST [36467] DEBUG: =C2=A0writing stats file=20 > "pg_stat_tmp/db_0.stat" > 2021-02-01 13:36:52.388 EST [37891] DEBUG: =C2=A0VACUUM Sleep: Delay:=20 > 20.000000, CostBalance: 2001, CostLimit: 2000, msec: 20.010000 > --=20 David Software Engineer Highgo Software Inc. (Canada) www.highgo.ca --------------6F19AF286FBDE4648A773EA9 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable Thanks for the patch, Mead.

For 'MAXVACUUMCOSTLIMIT", it would be nice to follow the current GUC pattern to do define a constant.

For example, the constant "MAX_KILOBYTES" is defined in guc.h, with a pattern like, "MAX_" to make it easy to read.

Best regards,

David

On 2021-02-08 6:48 a.m., Mead, Scott wrote:
Hello,
=C2=A0=C2=A0 I recently= looked at what it would take to make a running autovacuum pick-up a change to either cost_delay or cost_limit.=C2=A0 Users frequently will have a conservative value set, and then wish to change it when autovacuum initiates a freeze on a relation.=C2=A0 Most user= s end up finding out they are in =E2=80=98to prevent wraparound=E2= =80=99 after it has happened, this means that if they want the vacuum to take advantage of more I/O, they need to stop and then restart the currently running vacuum (after reloading the GUCs).=C2=A0=C2= =A0
=C2=A0
=C2=A0 Initially, my go= al was to determine feasibility for making this dynamic.=C2=A0 I added debug code to vacuum.c:vacuum_delay_point(void) and found that changes to cost_delay and cost_limit are already processed by a running vacuum.=C2=A0 There was a bug preventing the cost_delay or cost_limit from being configured to allow higher throughput however.
=C2=A0
I believe this is a bug because currently, autovacuum will dynamically detect and increase the cost_limit or cost_delay, but it can never decrease those values=C2=A0beyond their setting wh= en the vacuum began.=C2=A0=C2=A0The current behavior is for vacuum to limit the maximum throughput of currently running vacuum processes to the cost_limit that was set when the vacuum process began.=C2=A0
=C2=A0
I changed this (see attached) to allow the cost_limit to be re-calculated up to the maximum allowable (currently 10,000).=C2=A0 This has the effe= ct of allowing users to reload a configuration change and an in-progress vacuum can be =E2=80=98sped-up=E2=80=99 by setting ei= ther the cost_limit or cost_delay.
=C2=A0
The problematic piece is:
=C2=A0
di= ff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c
in= dex c6ec657a93..d3c6b0d805 100644
--= - a/src/backend/postmaster/autovacuum.c
++= + b/src/backend/postmaster/autovacuum.c
@@ -1834,7 +1834,7 @@ autovac_balance_cost(void)
= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 * cost_limit to more than the base value.
= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 */
= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 worker->wi_cost_limit =3D=C2=A0Max(Min(limit,
-=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 worker->wi_cost_limit_base),
+= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0MAXVACUUMCOSTLIMIT),<= o:p class=3D"">
= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0 1);
= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 }
=C2=A0
We limit the worker to the max cost_limit that was set at the beginning of the vacuum.=C2=A0 I introduced the MAXVACUUMCOSTLIMIT constant (currently defined to 10000, which is the currently max limit already defined) in miscadmin.h so that vacuum will now be able to adjust the cost_limit up to 10000 as the upper limit in a currently running vacuum.

The tests that I=E2=80=99ve run show tha= t the performance of an existing vacuum can be increased=C2=A0commensurate=C2=A0with the parameter change. =C2=A0Interestingly,=C2=A0autovac_balance_cost(vo= id)=C2=A0is only updating the cost_limit, even if the cost_delay is modified. =C2=A0This is done correctly, it was just a surprise to see the behavior.


<= span class=3D"" style=3D"font-size: 14.666666984558105px;">2021-02= -01 13:36:52.346 EST [37891] DEBUG: =C2=A0VACUUM Sleep: Delay: 20.000000, CostBalance: 207, CostLimit:=C2=A020= 0, msec: 20.700000
<= span class=3D"" style=3D"font-size: 14.666666984558105px;">2021-02= -01 13:36:52.346 EST [37891] CONTEXT: =C2=A0while scanning block 1824 of relation "public.blah"
<= span class=3D"" style=3D"font-size: 14.666666984558105px;">2021-02= -01 13:36:52.362 EST [36460] LOG: =C2=A0received SIGHUP, reloadin= g configuration files
<= span class=3D"" style=3D"font-size: 14.666666984558105px;">
<= span class=3D"" style=3D"font-size: 14.666666984558105px;">2021-02-01 13:36:52.364 EST [36460] LOG: =C2=A0parameter "autovacuum_vacuum_cost_delay" changed to "= 2"
<= span class=3D"" style=3D"font-size: 14.666666984558105px;">\
<= span class=3D"" style=3D"font-size: 14.666666984558105px;">2021-02= -01 13:36:52.365 EST [36463] DEBUG: =C2=A0checkpointer updated shared memory configuration values
<= span class=3D"" style=3D"font-size: 14.666666984558105px;">2021-02= -01 13:36:52.366 EST [36466] DEBUG: =C2=A0autovac_balance_cost(pid=3D37891 db=3D13207, rel=3D1638= 4, dobalance=3Dyes cost_limit=3D2000, cost_limit_base=3D200, cost_delay=3D20)
<= span class=3D"" style=3D"font-size: 14.666666984558105px;">
<= span class=3D"" style=3D"font-size: 14.666666984558105px;">2021-02= -01 13:36:52.366 EST [36467] DEBUG: =C2=A0received inquiry for database 0
<= span class=3D"" style=3D"font-size: 14.666666984558105px;">2021-02= -01 13:36:52.366 EST [36467] DEBUG: =C2=A0writing stats file "pg_stat_tmp/global.stat"
<= span class=3D"" style=3D"font-size: 14.666666984558105px;">2021-02= -01 13:36:52.366 EST [36467] DEBUG: =C2=A0writing stats file "pg_stat_tmp/db_0.stat"
<= span class=3D"" style=3D"font-size: 14.666666984558105px;">2021-02= -01 13:36:52.388 EST [37891] DEBUG: =C2=A0VACUUM Sleep: Delay: 20.000000, CostBalance: 2001, CostLimit: 2000, msec: 20.010000
<= span class=3D"" style=3D"font-size: 14.666666984558105px;">
<= span class=3D"" style=3D"font-size: 14.666666984558105px;">=
--
David

Software Engineer
Highgo Software Inc. (Canada)
w= ww.highgo.ca
--------------6F19AF286FBDE4648A773EA9--