User-Agent: Cyrus-JMAP/3.5.0-alpha0-4458-g51a91c06b2-fm-20211130.004-g51a91c06
Mime-Version: 1.0
Message-Id: <d4c7e393-b8be-4879-ad4b-4e80994e5763@www.fastmail.com>
In-Reply-To: <A285A823-0AF2-4376-838E-847FA4710F9A@amazon.com>
References: <C1EE64B0-D4DB-40F3-98C8-0CED324D34CB@amazon.com>
 <20211201225632.wrbrrofm4cbxvnqw@alap3.anarazel.de>
 <A285A823-0AF2-4376-838E-847FA4710F9A@amazon.com>
Date: Wed, 01 Dec 2021 23:05:03 -0300
From: "Euler Taveira" <euler@eulerto.com>
To: "Bossart, Nathan" <bossartn@amazon.com>,
 "Andres Freund" <andres@anarazel.de>
Cc: pgsql-hackers <pgsql-hackers@postgresql.org>
Subject: Re: O(n) tasks cause lengthy startups and checkpoints
Content-Type: multipart/alternative;
 boundary=e3cbb1d61b064f6a943aaa052bd8da75
Archived-At: 
 <https://www.postgresql.org/message-id/d4c7e393-b8be-4879-ad4b-4e80994e5763%40www.fastmail.com>
Precedence: bulk

--e3cbb1d61b064f6a943aaa052bd8da75
Content-Type: text/plain

On Wed, Dec 1, 2021, at 9:19 PM, Bossart, Nathan wrote:
> On 12/1/21, 2:56 PM, "Andres Freund" <andres@anarazel.de> wrote:
> > On 2021-12-01 20:24:25 +0000, Bossart, Nathan wrote:
> >> I realize adding a new maintenance worker might be a bit heavy-handed,
> >> but I think it would be nice to have somewhere to offload tasks that
> >> really shouldn't impact startup and checkpointing.  I imagine such a
> >> process would come in handy down the road, too.  WDYT?
> >
> > -1. I think the overhead of an additional worker is disproportional here. And
> > there's simplicity benefits in having a predictable cleanup interlock as well.
> 
> Another idea I had was to put some upper limit on how much time is
> spent on such tasks.  For example, a checkpoint would only spend X
> minutes on CheckPointSnapBuild() before giving up until the next one.
> I think the main downside of that approach is that it could lead to
> unbounded growth, so perhaps we would limit (or even skip) such tasks
> only for end-of-recovery and shutdown checkpoints.  Perhaps the
> startup tasks could be limited in a similar fashion.
Saying that a certain task is O(n) doesn't mean it needs a separate process to
handle it. Did you have a use case or even better numbers (% of checkpoint /
startup time) that makes your proposal worthwhile?

I would try to optimize (1) and (2). However, delayed removal can be a
long-term issue if the new routine cannot keep up with the pace of file
creation (specially if the checkpoints are far apart).

For (3), there is already a GUC that would avoid the slowdown during startup.
Use it if you think the startup time is more important that disk space occupied
by useless files.

For (4), you are forgetting that the on-disk state of replication slots is
stored in the pg_replslot/SLOTNAME/state. It seems you cannot just rename the
replication slot directory and copy the state file. What happen if there is a
crash before copying the state file?

While we are talking about items (1), (2) and (4), we could probably have an
option to create some ephemeral logical decoding files into ramdisk (similar to
statistics directory). I wouldn't like to hijack this thread but this proposal
could alleviate the possible issues that you pointed out. If people are
interested in this proposal, I can start a new thread about it.


--
Euler Taveira
EDB   https://www.enterprisedb.com/

--e3cbb1d61b064f6a943aaa052bd8da75
Content-Type: text/html
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE html><html><head><title></title><style type=3D"text/css">p.Mso=
Normal,p.MsoNoSpacing{margin:0}</style></head><body><div>On Wed, Dec 1, =
2021, at 9:19 PM, Bossart, Nathan wrote:<br></div><blockquote type=3D"ci=
te" id=3D"qt" style=3D""><div>On 12/1/21, 2:56 PM, "Andres Freund" &lt;<=
a href=3D"mailto:andres@anarazel.de">andres@anarazel.de</a>&gt; wrote:<b=
r></div><div>&gt; On 2021-12-01 20:24:25 +0000, Bossart, Nathan wrote:<b=
r></div><div>&gt;&gt; I realize adding a new maintenance worker might be=
 a bit heavy-handed,<br></div><div>&gt;&gt; but I think it would be nice=
 to have somewhere to offload tasks that<br></div><div>&gt;&gt; really s=
houldn't impact startup and checkpointing.&nbsp; I imagine such a<br></d=
iv><div>&gt;&gt; process would come in handy down the road, too.&nbsp; W=
DYT?<br></div><div>&gt;<br></div><div>&gt; -1. I think the overhead of a=
n additional worker is disproportional here. And<br></div><div>&gt; ther=
e's simplicity benefits in having a predictable cleanup interlock as wel=
l.<br></div><div><br></div><div>Another idea I had was to put some upper=
 limit on how much time is<br></div><div>spent on such tasks.&nbsp; For =
example, a checkpoint would only spend X<br></div><div>minutes on CheckP=
ointSnapBuild() before giving up until the next one.<br></div><div>I thi=
nk the main downside of that approach is that it could lead to<br></div>=
<div>unbounded growth, so perhaps we would limit (or even skip) such tas=
ks<br></div><div>only for end-of-recovery and shutdown checkpoints.&nbsp=
; Perhaps the<br></div><div>startup tasks could be limited in a similar =
fashion.<br></div></blockquote><div>Saying that a certain task is O(n) d=
oesn't mean it needs a separate process to<br></div><div>handle it. Did =
you have a use case or even better numbers (% of checkpoint /<br></div><=
div>startup time) that makes your proposal worthwhile?<br></div><div><br=
></div><div>I would try to optimize (1) and (2). However, delayed remova=
l can be a<br></div><div>long-term issue if the new routine cannot keep =
up with the pace of file<br></div><div>creation (specially if the checkp=
oints are far apart).</div><div><br></div><div>For (3), there is already=
 a GUC that would avoid the slowdown during startup.<br></div><div>Use i=
t if you think the startup time is more important that disk space occupi=
ed<br></div><div>by useless files.<br></div><div><br></div><div>For (4),=
 you are forgetting that the on-disk state of replication slots is<br></=
div><div>stored in the pg_replslot/SLOTNAME/state. It seems you cannot j=
ust rename the<br></div><div>replication slot directory and copy the sta=
te file. What happen if there is a<br></div><div>crash before copying th=
e state file?<br></div><div><br></div><div>While we are talking about it=
ems (1), (2) and (4), we could probably have an<br></div><div>option to =
create some ephemeral logical decoding files into ramdisk (similar to<br=
></div><div>statistics directory). I wouldn't like to hijack this thread=
 but this proposal<br></div><div>could alleviate the possible issues tha=
t you pointed out. If people are<br></div><div>interested in this propos=
al, I can start a new thread about it.<br></div><div><br></div><div><br>=
</div><div id=3D"sig112409080"><div class=3D"signature">--<br></div><div=
 class=3D"signature">Euler Taveira<br></div><div class=3D"signature">EDB=
&nbsp;&nbsp; <a href=3D"https://www.enterprisedb.com/">https://www.enter=
prisedb.com/</a><br></div></div><div><br></div></body></html>
--e3cbb1d61b064f6a943aaa052bd8da75--