Re: autovacuum locking question

--00000000000006900205990ca661
Content-Type: text/plain; charset="UTF-8"

On Fri, Dec 6, 2019 at 10:55 AM Mike Schanne <[email protected]> wrote:

> The error is not actually showing up very often (I have 8 occurrences from
> 11/29 and none since then).  So maybe I should not be concerned about it.
> I suspect we have an I/O bottleneck from other logs (i.e. long checkpoint
> sync times), so this error may be a symptom rather than the cause.
>

I think that at the point it is getting cancelled, it has done all the work
except the truncation of the empty pages, and reporting the results (for
example, updating n_live_tup  and n_dead_tup).  If this happens
every single time (neither last_autovacuum nor last_vacuum ever advances)
it will eventually cause problems.  So this is mostly a symptom, but not
entirely.  Simply running a manual vacuum should fix the reporting
problem.  It is not subject to cancelling, so it will detect it is blocking
someone and gracefully bow.  Meaning it will suspend the truncation, but
will still report its results as normal.

Reading the table backwards in order to truncate it might be contributing
to the IO problems as well as being a victim of those problems.  Upgrading
to v10 might help with this, as it implemented a prefetch where it reads
the table forward in 128kB chunks, and then jumps backwards one chunk at a
time.  Rather than just reading backwards 8kB at a time.

Cheers,

Jeff

>

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

<div dir=3D"ltr"><div dir=3D"ltr">On Fri, Dec 6, 2019 at 10:55 AM Mike Scha=
nne &lt;<a href=3D"mailto:[email protected]">[email protected]</a>&gt; wrote:=
<br></div><div class=3D"gmail_quote"><blockquote class=3D"gmail_quote" styl=
e=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);paddin=
g-left:1ex">





<div lang=3D"EN-US">
<div class=3D"gmail-m_4809708625898426011WordSection1">
<p class=3D"MsoNormal"><span style=3D"font-size:11pt;font-family:Calibri,sa=
ns-serif;color:rgb(31,73,125)">The error is not actually showing up very of=
ten (I have 8 occurrences from 11/29 and none since then).=C2=A0 So maybe I=
 should not be concerned about it.=C2=A0 I suspect
 we have an I/O bottleneck from other logs (i.e. long checkpoint sync times=
), so this error may be a symptom rather than the cause.</span></p></div></=
div></blockquote><div><br></div><div>I think that at the point it is gettin=
g cancelled, it has done all the work except the truncation of the empty pa=
ges, and reporting the results (for example, updating n_live_tup=C2=A0 and =
n_dead_tup).=C2=A0 If this happens every=C2=A0single time (neither last_aut=
ovacuum nor last_vacuum ever advances) it will eventually cause problems.=
=C2=A0 So this is mostly a symptom, but not entirely.=C2=A0 Simply running =
a manual vacuum should fix the reporting problem.=C2=A0 It is not subject t=
o cancelling, so it will detect it is blocking someone and gracefully bow.=
=C2=A0 Meaning it will suspend the truncation, but will still report its re=
sults as normal.</div><div>=C2=A0</div><div>Reading the table backwards in =
order to truncate it might be contributing to the IO problems as well as be=
ing a victim of those problems.=C2=A0 Upgrading to v10 might help with this=
, as it implemented a prefetch where it reads the table forward in 128kB ch=
unks, and then jumps backwards one chunk at a time.=C2=A0 Rather than just =
reading backwards 8kB at a time.</div><div><br></div><div>Cheers,</div><div=
><br></div><div>Jeff</div><blockquote class=3D"gmail_quote" style=3D"margin=
:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"=
><div lang=3D"EN-US">
</div>

</blockquote></div></div>

--00000000000006900205990ca661--