MIME-Version: 1.0
References: 
 <CAFpL5Vyb4dx=nqBZ_GB1M_TxA-3+E-bSav3fDQnVHW2udXEV5g@mail.gmail.com>
 <1972046.1623436429@sss.pgh.pa.us>
In-Reply-To: <1972046.1623436429@sss.pgh.pa.us>
From: Nikhil Shetty <nikhil.dba04@gmail.com>
Date: Sat, 12 Jun 2021 12:34:38 +0530
Message-ID: 
 <CAFpL5VwDxPpwuMxzOAMo7cYfgNsMzBU2ob_N7xcsDS_VBgyF-Q@mail.gmail.com>
Subject: Re: vacuumdb idle processes
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-admin@postgresql.org
Content-Type: multipart/alternative; boundary="0000000000007f165205c48c3d9d"
Archived-At: 
 <https://www.postgresql.org/message-id/CAFpL5VwDxPpwuMxzOAMo7cYfgNsMzBU2ob_N7xcsDS_VBgyF-Q%40mail.gmail.com>
Precedence: bulk

--0000000000007f165205c48c3d9d
Content-Type: text/plain; charset="UTF-8"

Hi Tom,

Apparently not all that well, if it failed to keep us out of an
> autovacuum-to-prevent-wraparound situation.  I suppose you had autovacuum
> disabled because you thought this lashup was sufficient?


 No, autovacuum was enabled but seems it was not able to catch up with the
amount of transaction or it might have been delayed due to postgres
favouring other txns that conflict -- Need to check this though

Perhaps this is the last remaining table so vacuumdb has nothing
> else for them to do.
>

Does this mean vacuumdb will release all db connections(jobs - 8 in this
case) only  after all connections have performed their vacuum and then
disconnected? Even if one is pending the others will be still connected but
in idle state? Is my understanding correct?

Thanks and Regards,
Nikhil

On Sat, Jun 12, 2021 at 12:03 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Nikhil Shetty <nikhil.dba04@gmail.com> writes:
> > I have done a setup to run vacuumdb with 8 parallel jobs daily. It has
> been
> > running quite well.
>
> Apparently not all that well, if it failed to keep us out of an
> autovacuum-to-prevent-wraparound situation.  I suppose you had autovacuum
> disabled because you thought this lashup was sufficient?
>
> > Just today I saw there is an aggressive autovacuum process running(to
> > prevent wraparound) on one of the table. vacuumdb which started later
> > spawned the 8 connections. One connection (doing vacuum on the table on
> > which an aggressive autovacuum is running) is waiting for "autovacuum(to
> > prevent wraparound)" to complete while the other 7 connections are just
> > sitting idle.
>
> > I am okay that one connection is waiting since an aggressive autovacuum
> is
> > running on that table but how come other connections have not released
> the
> > sesion yet? Any reason for this?
>
> Perhaps this is the last remaining table so vacuumdb has nothing
> else for them to do.
>
>                         regards, tom lane
>

--0000000000007f165205c48c3d9d
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr"><div dir=3D"ltr"><div dir=3D"ltr">Hi Tom,=
<div><br></div></div><blockquote class=3D"gmail_quote" style=3D"margin:0px =
0px 0px 0.8ex;border-left-width:1px;border-left-style:solid;border-left-col=
or:rgb(204,204,204);padding-left:1ex">Apparently not all that well, if it f=
ailed to keep us out of an<br>autovacuum-to-prevent-wraparound situation.=
=C2=A0 I suppose you had autovacuum<br>disabled because you thought this la=
shup was sufficient?</blockquote><div><br></div><div>=C2=A0No, autovacuum w=
as enabled but seems it was not able to catch up with the amount of transac=
tion or it might have been delayed due to postgres favouring other txns tha=
t conflict -- Need to check this though</div><div><br></div><blockquote cla=
ss=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left-width:1px;=
border-left-style:solid;border-left-color:rgb(204,204,204);padding-left:1ex=
">Perhaps this is the last remaining table so vacuumdb has nothing<br>else =
for them to do.<br></blockquote><div><br></div><div>Does this mean vacuumdb=
 will release all db connections(jobs - 8 in this case) only =C2=A0after al=
l connections have performed their vacuum and then disconnected? Even if on=
e is pending the others will be still connected but in idle state? Is my un=
derstanding correct?</div><div><br></div><div>Thanks and Regards,</div><div=
>Nikhil</div></div></div></div><br><div class=3D"gmail_quote"><div dir=3D"l=
tr" class=3D"gmail_attr">On Sat, Jun 12, 2021 at 12:03 AM Tom Lane &lt;<a h=
ref=3D"mailto:tgl@sss.pgh.pa.us">tgl@sss.pgh.pa.us</a>&gt; wrote:<br></div>=
<blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-=
left-width:1px;border-left-style:solid;border-left-color:rgb(204,204,204);p=
adding-left:1ex">Nikhil Shetty &lt;<a href=3D"mailto:nikhil.dba04@gmail.com=
" target=3D"_blank">nikhil.dba04@gmail.com</a>&gt; writes:<br>
&gt; I have done a setup to run vacuumdb with 8 parallel jobs daily. It has=
 been<br>
&gt; running quite well.<br>
<br>
Apparently not all that well, if it failed to keep us out of an<br>
autovacuum-to-prevent-wraparound situation.=C2=A0 I suppose you had autovac=
uum<br>
disabled because you thought this lashup was sufficient?<br>
<br>
&gt; Just today I saw there is an aggressive autovacuum process running(to<=
br>
&gt; prevent wraparound) on one of the table. vacuumdb which started later<=
br>
&gt; spawned the 8 connections. One connection (doing vacuum on the table o=
n<br>
&gt; which an aggressive autovacuum is running) is waiting for &quot;autova=
cuum(to<br>
&gt; prevent wraparound)&quot; to complete while the other 7 connections ar=
e just<br>
&gt; sitting idle.<br>
<br>
&gt; I am okay that one connection is waiting since an aggressive autovacuu=
m is<br>
&gt; running on that table but how come other connections have not released=
 the<br>
&gt; sesion yet? Any reason for this?<br>
<br>
Perhaps this is the last remaining table so vacuumdb has nothing<br>
else for them to do.<br>
<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 regards, tom lane<br>
</blockquote></div>

--0000000000007f165205c48c3d9d--