Re: autovacuum locking question
MichaelDBA <[email protected]> Fri, 6 Dec 2019 12:50:44 -0500
| Newsgroups | gmane.comp.db.postgresql.performance |
|---|---|
| Message-ID | <[email protected]> |
This is a multi-part message in MIME format. --------------764CD427BDB1714990A0ADDB Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit And Just to reiterate my own understanding of this... autovacuum priority is less than a user-initiated request, so issuing a manual vacuum (user-initiated request) will not result in being cancelled. Regards, Michael Vitale Jeff Janes wrote on 12/6/2019 12:47 PM: > On Fri, Dec 6, 2019 at 10:55 AM Mike Schanne <[email protected] > <mailto:[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 > --------------764CD427BDB1714990A0ADDB Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit <html theme="default-light"><head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8"> </head><body text="#000000">And Just to reiterate my own understanding of this...<br> <br> autovacuum priority is less than a user-initiated request, so issuing a manual vacuum (user-initiated request) will not result in being cancelled.<br> <br> Regards,<br> Michael Vitale<br> <br> <span>Jeff Janes wrote on 12/6/2019 12:47 PM:</span><br> <blockquote type="cite" cite="mid:CAMkU=1zGKub0OjwFYkbcck7-7YmNEbj1HRpzdOMHnAvFY_tppw@mail.gmail.com"> <meta http-equiv="content-type" content="text/html; charset=utf-8"> <div dir="ltr"><div dir="ltr">On Fri, Dec 6, 2019 at 10:55 AM Mike Schanne <<a href="mailto:[email protected]" moz-do-not-send="true">[email protected]</a>> wrote:<br></div><div class="gmail_quote"><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div lang="EN-US"> <div class="gmail-m_4809708625898426011WordSection1"> <p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">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.</span></p></div></div></blockquote><div><br></div><div>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.</div><div> </div><div>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.</div><div><br></div><div>Cheers,</div><div><br></div><div>Jeff</div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div lang="EN-US"> </div></blockquote></div></div> </blockquote> <br> </body></html> --------------764CD427BDB1714990A0ADDB--