Horton

Thread

Commits

  1. Doc: document autovacuum interruption.

  1. [DOC] Document auto vacuum interruption

    James Coleman <jtc331@gmail.com> — 2019-07-25T20:14:48Z

    We've discussed this internally many times, but today finally decided
to write up a doc patch.

Autovacuum holds a SHARE UPDATE EXCLUSIVE lock, but other processes
can cancel autovacuum if blocked by that lock unless the autovacuum is
to prevent wraparound.This can result in very surprising behavior:
imagine a system that needs to run ANALYZE manually before batch jobs
to ensure reasonable query plans. That ANALYZE will interrupt attempts
to run autovacuum, and pretty soon the table is far more bloated than
expected, and query plans (ironically) degrade further.

Attached is a patch to document that behavior (as opposed to just in
the code at src/backend/storage/lmgr/proc.c:1320-1321).

James Coleman

  2. Re: [DOC] Document auto vacuum interruption

    Amit Kapila <amit.kapila16@gmail.com> — 2019-09-01T02:51:18Z

    On Fri, Jul 26, 2019 at 1:45 AM James Coleman <jtc331@gmail.com> wrote:
>
> We've discussed this internally many times, but today finally decided
> to write up a doc patch.
>

Thanks, I think something on the lines of what you have written can
help some users to understand the behavior in this area and there
doesn't seem to be any harm in giving such information to the user.

> Autovacuum holds a SHARE UPDATE EXCLUSIVE lock, but other processes
> can cancel autovacuum if blocked by that lock unless the autovacuum is
> to prevent wraparound.This can result in very surprising behavior:
> imagine a system that needs to run ANALYZE manually before batch jobs
> to ensure reasonable query plans. That ANALYZE will interrupt attempts
> to run autovacuum, and pretty soon the table is far more bloated than
> expected, and query plans (ironically) degrade further.
>

+    If a process attempts to acquire a <literal>SHARE UPDATE
EXCLUSIVE</literal>
+    lock (the lock type held by autovacuum), lock acquisition will interrupt
+    the autovacuum.

I think it is not only for a process that tries to acquire a lock in
SHARE UPDATE EXCLUSIVE mode, rather when a process tries to acquire
any lock mode that conflicts with SHARE UPDATE EXCLUSIVE.  For the
conflicting lock modes, you can refer docs [1] (See Table 13.2.
Conflicting Lock Modes).

[1] - https://www.postgresql.org/docs/devel/explicit-locking.html

-- 
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

  3. Re: [DOC] Document auto vacuum interruption

    James Coleman <jtc331@gmail.com> — 2019-09-13T18:29:28Z

    On Sat, Aug 31, 2019 at 10:51 PM Amit Kapila <amit.kapila16@gmail.com> wrote:
>
> On Fri, Jul 26, 2019 at 1:45 AM James Coleman <jtc331@gmail.com> wrote:
> >
> > We've discussed this internally many times, but today finally decided
> > to write up a doc patch.
> >
>
> Thanks, I think something on the lines of what you have written can
> help some users to understand the behavior in this area and there
> doesn't seem to be any harm in giving such information to the user.
>
> > Autovacuum holds a SHARE UPDATE EXCLUSIVE lock, but other processes
> > can cancel autovacuum if blocked by that lock unless the autovacuum is
> > to prevent wraparound.This can result in very surprising behavior:
> > imagine a system that needs to run ANALYZE manually before batch jobs
> > to ensure reasonable query plans. That ANALYZE will interrupt attempts
> > to run autovacuum, and pretty soon the table is far more bloated than
> > expected, and query plans (ironically) degrade further.
> >
>
> +    If a process attempts to acquire a <literal>SHARE UPDATE
> EXCLUSIVE</literal>
> +    lock (the lock type held by autovacuum), lock acquisition will interrupt
> +    the autovacuum.
>
> I think it is not only for a process that tries to acquire a lock in
> SHARE UPDATE EXCLUSIVE mode, rather when a process tries to acquire
> any lock mode that conflicts with SHARE UPDATE EXCLUSIVE.  For the
> conflicting lock modes, you can refer docs [1] (See Table 13.2.
> Conflicting Lock Modes).
>
> [1] - https://www.postgresql.org/docs/devel/explicit-locking.html

Updated patch attached. I changed the wording to be about conflicting
locks rather than a single lock type, added a link to the conflicting
locks table, and fixed a few sgml syntax issues in the original.

James Coleman

  4. Re: [DOC] Document auto vacuum interruption

    Amit Kapila <amit.kapila16@gmail.com> — 2019-09-17T06:21:34Z

    On Fri, Sep 13, 2019 at 11:59 PM James Coleman <jtc331@gmail.com> wrote:
>
> On Sat, Aug 31, 2019 at 10:51 PM Amit Kapila <amit.kapila16@gmail.com> wrote:
> >
>
> Updated patch attached. I changed the wording to be about conflicting
> locks rather than a single lock type, added a link to the conflicting
> locks table, and fixed a few sgml syntax issues in the original.
>

I see error while compiling this patch on HEAD.  See the below error:
/usr/bin/xmllint --path . --noout --valid postgres.sgml
postgres.sgml:833: element xref: validity error : IDREF attribute
linkend references an unknown ID
"mvcc-locking-tables-table-lock-compatibility"
make: *** [check] Error 4

The tag id mvcc-locking-tables-table-lock-compatibility is wrong.  The
other problem I see is the wrong wording in one of the literals. I
have fixed both of these issues and slightly tweaked one of the
sentence.  See the updated patch attached.  On which version, are you
preparing this patch?  I see both HEAD and 9.4 has the problems fixed
by me.

Let me know what you think of attached?  I think we can back-patch
this patch.  What do you think?  Does anyone else have an opinion on
this patch especially if we see any problem in back-patching this?

-- 
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

  5. Re: [DOC] Document auto vacuum interruption

    James Coleman <jtc331@gmail.com> — 2019-09-17T12:18:41Z

    On Tue, Sep 17, 2019 at 2:21 AM Amit Kapila <amit.kapila16@gmail.com> wrote:
>
> On Fri, Sep 13, 2019 at 11:59 PM James Coleman <jtc331@gmail.com> wrote:
> >
> > On Sat, Aug 31, 2019 at 10:51 PM Amit Kapila <amit.kapila16@gmail.com> wrote:
> > >
> >
> > Updated patch attached. I changed the wording to be about conflicting
> > locks rather than a single lock type, added a link to the conflicting
> > locks table, and fixed a few sgml syntax issues in the original.
> >
>
> I see error while compiling this patch on HEAD.  See the below error:
> /usr/bin/xmllint --path . --noout --valid postgres.sgml
> postgres.sgml:833: element xref: validity error : IDREF attribute
> linkend references an unknown ID
> "mvcc-locking-tables-table-lock-compatibility"
> make: *** [check] Error 4
>
> The tag id mvcc-locking-tables-table-lock-compatibility is wrong.

My apologies; I'd fixed that on my local copy before sending my last
email, but I must have somehow grabbed the wrong patch file to attach
to the email.

> The
> other problem I see is the wrong wording in one of the literals. I
> have fixed both of these issues and slightly tweaked one of the
> sentence.  See the updated patch attached.  On which version, are you
> preparing this patch?  I see both HEAD and 9.4 has the problems fixed
> by me.
>
> Let me know what you think of attached?  I think we can back-patch
> this patch.  What do you think?  Does anyone else have an opinion on
> this patch especially if we see any problem in back-patching this?

The attached looks great!

I was working on HEAD for the patch, but this concern has been an
issue for quite a long time. We were running into it on 9.6 in
production, for example. And given how frequently it seems like there
are large-scale production issues related to auto vacuum, I think any
amount of back patching we can do to make that footgun less likely
would be a good thing.

James Coleman

  6. Re: [DOC] Document auto vacuum interruption

    Amit Kapila <amit.kapila16@gmail.com> — 2019-09-18T04:55:06Z

    On Tue, Sep 17, 2019 at 5:48 PM James Coleman <jtc331@gmail.com> wrote:
>
> On Tue, Sep 17, 2019 at 2:21 AM Amit Kapila <amit.kapila16@gmail.com> wrote:
> >
> >
> > Let me know what you think of attached?  I think we can back-patch
> > this patch.  What do you think?  Does anyone else have an opinion on
> > this patch especially if we see any problem in back-patching this?
>
> The attached looks great!
>
> I was working on HEAD for the patch, but this concern has been an
> issue for quite a long time. We were running into it on 9.6 in
> production, for example. And given how frequently it seems like there
> are large-scale production issues related to auto vacuum, I think any
> amount of back patching we can do to make that footgun less likely
> would be a good thing.
>

Okay, I will commit this tomorrow unless someone has any comments or objections.

-- 
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

  7. Re: [DOC] Document auto vacuum interruption

    Amit Kapila <amit.kapila16@gmail.com> — 2019-09-19T09:34:25Z

    On Wed, Sep 18, 2019 at 10:25 AM Amit Kapila <amit.kapila16@gmail.com> wrote:
>
> On Tue, Sep 17, 2019 at 5:48 PM James Coleman <jtc331@gmail.com> wrote:
> >
> > On Tue, Sep 17, 2019 at 2:21 AM Amit Kapila <amit.kapila16@gmail.com> wrote:
> > >
> > >
> > > Let me know what you think of attached?  I think we can back-patch
> > > this patch.  What do you think?  Does anyone else have an opinion on
> > > this patch especially if we see any problem in back-patching this?
> >
> > The attached looks great!
> >
> > I was working on HEAD for the patch, but this concern has been an
> > issue for quite a long time. We were running into it on 9.6 in
> > production, for example. And given how frequently it seems like there
> > are large-scale production issues related to auto vacuum, I think any
> > amount of back patching we can do to make that footgun less likely
> > would be a good thing.
> >
>
> Okay, I will commit this tomorrow unless someone has any comments or objections.
>

Pushed with minor changes.  There was one extra space in a few lines
and the tag for back-branches (from 10~9.4) was slightly different.

-- 
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

  8. Re: [DOC] Document auto vacuum interruption

    James Coleman <jtc331@gmail.com> — 2020-02-14T21:14:01Z

    On Thu, Sep 19, 2019 at 5:34 AM Amit Kapila <amit.kapila16@gmail.com> wrote:
>
> On Wed, Sep 18, 2019 at 10:25 AM Amit Kapila <amit.kapila16@gmail.com> wrote:
> >
> > On Tue, Sep 17, 2019 at 5:48 PM James Coleman <jtc331@gmail.com> wrote:
> > >
> > > On Tue, Sep 17, 2019 at 2:21 AM Amit Kapila <amit.kapila16@gmail.com> wrote:
> > > >
> > > >
> > > > Let me know what you think of attached?  I think we can back-patch
> > > > this patch.  What do you think?  Does anyone else have an opinion on
> > > > this patch especially if we see any problem in back-patching this?
> > >
> > > The attached looks great!
> > >
> > > I was working on HEAD for the patch, but this concern has been an
> > > issue for quite a long time. We were running into it on 9.6 in
> > > production, for example. And given how frequently it seems like there
> > > are large-scale production issues related to auto vacuum, I think any
> > > amount of back patching we can do to make that footgun less likely
> > > would be a good thing.
> > >
> >
> > Okay, I will commit this tomorrow unless someone has any comments or objections.
> >
>
> Pushed with minor changes.  There was one extra space in a few lines
> and the tag for back-branches (from 10~9.4) was slightly different.

I completely forgot to reply to this; thanks Amit for working on this.

James