From: MichaelDBA <MichaelDBA@sqlexec.com>
To: Jeff Janes <jeff.janes@gmail.com>
Cc: Mike Schanne <mschanne@kns.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: autovacuum locking question
Date: Fri, 6 Dec 2019 12:50:44 -0500
Message-ID: <3b91b82c-6fa0-04db-80ad-24b7939d80c7@sqlexec.com> (raw)
In-Reply-To: <CAMkU=1zGKub0OjwFYkbcck7-7YmNEbj1HRpzdOMHnAvFY_tppw@mail.gmail.com>
References: <0871fcf35ceb4caa8a2204ca9c38e330@USEPRDEX1.corp.kns.com>
<CAMkU=1wmmOvKGC+_QqHBE=nM=nqPBq=DJrX8Sk3CeQ+krAcEkA@mail.gmail.com>
<ad5b3b6459354e3ea65c33cc9fca14c0@USEPRDEX1.corp.kns.com>
<CAMkU=1zGKub0OjwFYkbcck7-7YmNEbj1HRpzdOMHnAvFY_tppw@mail.gmail.com>
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 <mschanne@kns.com
> <mailto:mschanne@kns.com>> 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
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: MichaelDBA@sqlexec.com, jeff.janes@gmail.com, mschanne@kns.com
Subject: Re: autovacuum locking question
In-Reply-To: <3b91b82c-6fa0-04db-80ad-24b7939d80c7@sqlexec.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox