Обсуждение: [DOC] Document auto vacuum interruption
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
Вложения
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
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
Вложения
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
Вложения
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
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
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
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