Thread: autovacuum locking question
Hi,
I am investigating a performance problem in our application and am seeing something unexpected in the postgres logs regarding the autovacuum.
2019-12-01 13:05:39.029 UTC,"wb","postgres",6966,"127.0.0.1:53976",5ddbd990.1b36,17099,"INSERT waiting",2019-11-25 13:39:28 UTC,12/1884256,12615023,LOG,00000,"process 6966 still waiting for RowExclusiveLock on relation 32938 of database 32768 after 1000.085 ms","Process holding the lock: 6045. Wait queue: 6966.",,,,,"INSERT INTO myschema.mytable (...) VALUES (...) RETURNING process.mytable.mytable_id",13,,""
2019-12-01 13:05:39.458 UTC,,,6045,,5de3b800.179d,1,,2019-12-01 12:54:24 UTC,10/417900,0,ERROR,57014,"canceling autovacuum task",,,,,"automatic vacuum of table ""postgres.myschema.mytable""",,,,""
My understanding from reading the documentation was that a vacuum can run concurrently with table inserts/updates, but from reading the logs it appears they are conflicting over a row lock. This particular table gets very frequent inserts/updates (10-100 inserts / sec) so I am concerned that if the autovacuum is constantly canceled, then the table never gets cleaned and its performance will continue to degrade over time. Is it expected for the vacuum to be canceled by an insert in this way?
We are using postgres 9.6.10.
Thanks,
Mike
This email is non-binding, is subject to contract, and neither Kulicke and Soffa Industries, Inc. nor its subsidiaries (each and collectively “K&S”) shall have any obligation to you to consummate the transactions herein or to enter into any agreement, other than in accordance with the terms and conditions of a definitive agreement if and when negotiated, finalized and executed between the parties. This email and all its contents are protected by International and United States copyright laws. Any reproduction or use of all or any part of this email without the express written consent of K&S is prohibited.
I am concerned that if the autovacuum is constantly canceled, then the table never gets cleaned and its performance will continue to degrade over time. Is it expected for the vacuum to be canceled by an insert in this way?
We are using postgres 9.6.10.
In this particular case autovacuum_count is 0. n_live_tup is 659,631 and n_dead_tup is 3,400,347.
We are using the default vacuum parameters.
From: Michael Lewis [mailto:mlewis@entrata.com]
Sent: Thursday, December 05, 2019 5:49 PM
To: Mike Schanne
Cc: pgsql-performance@postgresql.org
Subject: Re: autovacuum locking question
On Thu, Dec 5, 2019 at 3:26 PM Mike Schanne <mschanne@kns.com> wrote:
I am concerned that if the autovacuum is constantly canceled, then the table never gets cleaned and its performance will continue to degrade over time. Is it expected for the vacuum to be canceled by an insert in this way?
We are using postgres 9.6.10.
Have you checked when the table was last autovacuumed in pg_stat_user_tables? If the autovacuum count is high and timestamp of last run is relatively current, then no reason for concern as far as I can figure.
Have you already configured (non-default values) for autovacuum options for your system or this table?
This email is non-binding, is subject to contract, and neither Kulicke and Soffa Industries, Inc. nor its subsidiaries (each and collectively “K&S”) shall have any obligation to you to consummate the transactions herein or to enter into any agreement, other than in accordance with the terms and conditions of a definitive agreement if and when negotiated, finalized and executed between the parties. This email and all its contents are protected by International and United States copyright laws. Any reproduction or use of all or any part of this email without the express written consent of K&S is prohibited.
Mike Schanne <mschanne@kns.com> writes:
> I am investigating a performance problem in our application and am seeing something unexpected in the postgres logs
regardingthe autovacuum.
> 2019-12-01 13:05:39.029 UTC,"wb","postgres",6966,"127.0.0.1:53976",5ddbd990.1b36,17099,"INSERT waiting",2019-11-25
13:39:28UTC,12/1884256,12615023,LOG,00000,"process 6966 still waiting for RowExclusiveLock on relation 32938 of
database32768 after 1000.085 ms","Process holding the lock: 6045. Wait queue: 6966.",,,,,"INSERT INTO myschema.mytable
(...)VALUES (...) RETURNING process.mytable.mytable_id",13,,""
> 2019-12-01 13:05:39.458 UTC,,,6045,,5de3b800.179d,1,,2019-12-01 12:54:24 UTC,10/417900,0,ERROR,57014,"canceling
autovacuumtask",,,,,"automatic vacuum of table ""postgres.myschema.mytable""",,,,""
> My understanding from reading the documentation was that a vacuum can run concurrently with table inserts/updates,
butfrom reading the logs it appears they are conflicting over a row lock. This particular table gets very frequent
inserts/updates(10-100 inserts / sec) so I am concerned that if the autovacuum is constantly canceled, then the table
nevergets cleaned and its performance will continue to degrade over time. Is it expected for the vacuum to be canceled
byan insert in this way?
The main part of an autovacuum operation should go through OK. The only
part that would get canceled in response to somebody taking a
non-exclusive lock is the last step, which is truncation of unused blocks
at the end of the table; that requires an exclusive lock. Normally,
skipping that step isn't terribly problematic.
> We are using postgres 9.6.10.
IIRC, we've made improvements in this area since 9.6, to allow a
partial truncation to be done if someone wants the lock, rather
than just failing entirely.
regards, tom lane
Hi,
I am investigating a performance problem in our application and am seeing something unexpected in the postgres logs regarding the autovacuum.
2019-12-01 13:05:39.029 UTC,"wb","postgres",6966,"127.0.0.1:53976",5ddbd990.1b36,17099,"INSERT waiting",2019-11-25 13:39:28 UTC,12/1884256,12615023,LOG,00000,"process 6966 still waiting for RowExclusiveLock on relation 32938 of database 32768 after 1000.085 ms","Process holding the lock: 6045. Wait queue: 6966.",,,,,"INSERT INTO myschema.mytable (...) VALUES (...) RETURNING process.mytable.mytable_id",13,,""
2019-12-01 13:05:39.458 UTC,,,6045,,5de3b800.179d,1,,2019-12-01 12:54:24 UTC,10/417900,0,ERROR,57014,"canceling autovacuum task",,,,,"automatic vacuum of table ""postgres.myschema.mytable""",,,,""
My understanding from reading the documentation was that a vacuum can run concurrently with table inserts/updates, but from reading the logs it appears they are conflicting over a row lock. This particular table gets very frequent inserts/updates (10-100 inserts / sec) so I am concerned that if the autovacuum is constantly canceled, then the table never gets cleaned and its performance will continue to degrade over time. Is it expected for the vacuum to be canceled by an insert in this way?
We are using postgres 9.6.10.
Is this what you are referring to? - Prevent VACUUM from trying to freeze an old multixact ID involving a still-running transaction (Nathan Bossart, JeremySchneider) This case would lead to VACUUM failing until the old transaction terminates. https://www.postgresql.org/docs/release/9.6.16/ Thanks, Mike -----Original Message----- From: Tom Lane [mailto:tgl@sss.pgh.pa.us] Sent: Thursday, December 05, 2019 6:49 PM To: Mike Schanne Cc: 'pgsql-performance@postgresql.org' Subject: Re: autovacuum locking question Mike Schanne <mschanne@kns.com> writes: > I am investigating a performance problem in our application and am seeing something unexpected in the postgres logs regardingthe autovacuum. > 2019-12-01 13:05:39.029 UTC,"wb","postgres",6966,"127.0.0.1:53976",5ddbd990.1b36,17099,"INSERT waiting",2019-11-25 13:39:28UTC,12/1884256,12615023,LOG,00000,"process 6966 still waiting for RowExclusiveLock on relation 32938 of database32768 after 1000.085 ms","Process holding the lock: 6045. Wait queue: 6966.",,,,,"INSERT INTO myschema.mytable (...)VALUES (...) RETURNING process.mytable.mytable_id",13,,"" > 2019-12-01 13:05:39.458 UTC,,,6045,,5de3b800.179d,1,,2019-12-01 12:54:24 UTC,10/417900,0,ERROR,57014,"canceling autovacuumtask",,,,,"automatic vacuum of table ""postgres.myschema.mytable""",,,,"" > My understanding from reading the documentation was that a vacuum can run concurrently with table inserts/updates, butfrom reading the logs it appears they are conflicting over a row lock. This particular table gets very frequent inserts/updates(10-100 inserts / sec) so I am concerned that if the autovacuum is constantly canceled, then the table nevergets cleaned and its performance will continue to degrade over time. Is it expected for the vacuum to be canceled byan insert in this way? The main part of an autovacuum operation should go through OK. The only part that would get canceled in response to somebodytaking a non-exclusive lock is the last step, which is truncation of unused blocks at the end of the table; thatrequires an exclusive lock. Normally, skipping that step isn't terribly problematic. > We are using postgres 9.6.10. IIRC, we've made improvements in this area since 9.6, to allow a partial truncation to be done if someone wants the lock,rather than just failing entirely. regards, tom lane ________________________________ This email is non-binding, is subject to contract, and neither Kulicke and Soffa Industries, Inc. nor its subsidiaries (eachand collectively “K&S”) shall have any obligation to you to consummate the transactions herein or to enter into anyagreement, other than in accordance with the terms and conditions of a definitive agreement if and when negotiated, finalizedand executed between the parties. This email and all its contents are protected by International and United Statescopyright laws. Any reproduction or use of all or any part of this email without the express written consent of K&Sis prohibited.
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.
From: Jeff Janes [mailto:jeff.janes@gmail.com]
Sent: Thursday, December 05, 2019 6:55 PM
To: Mike Schanne
Cc: pgsql-performance@postgresql.org
Subject: Re: autovacuum locking question
On Thu, Dec 5, 2019 at 5:26 PM Mike Schanne <mschanne@kns.com> wrote:
Hi,
I am investigating a performance problem in our application and am seeing something unexpected in the postgres logs regarding the autovacuum.
2019-12-01 13:05:39.029 UTC,"wb","postgres",6966,"127.0.0.1:53976",5ddbd990.1b36,17099,"INSERT waiting",2019-11-25 13:39:28 UTC,12/1884256,12615023,LOG,00000,"process 6966 still waiting for RowExclusiveLock on relation 32938 of database 32768 after 1000.085 ms","Process holding the lock: 6045. Wait queue: 6966.",,,,,"INSERT INTO myschema.mytable (...) VALUES (...) RETURNING process.mytable.mytable_id",13,,""
2019-12-01 13:05:39.458 UTC,,,6045,,5de3b800.179d,1,,2019-12-01 12:54:24 UTC,10/417900,0,ERROR,57014,"canceling autovacuum task",,,,,"automatic vacuum of table ""postgres.myschema.mytable""",,,,""
My understanding from reading the documentation was that a vacuum can run concurrently with table inserts/updates, but from reading the logs it appears they are conflicting over a row lock. This particular table gets very frequent inserts/updates (10-100 inserts / sec) so I am concerned that if the autovacuum is constantly canceled, then the table never gets cleaned and its performance will continue to degrade over time. Is it expected for the vacuum to be canceled by an insert in this way?
We are using postgres 9.6.10.
If the vacuum finds a lot of empty pages at the end of the table, it will try to truncate them and takes a strong lock to do so. It is supposed to check every 20ms to see if anyone else is blocked on that lock, at which point it stops doing the truncation and releases the lock. So it should never get "caught" holding the lock in order to be cancelled. Is your setting for deadlock_timeout much lower than usual? Also, if the truncation is bogged down in very slow IO, perhaps it doesn't actually get around to checking ever 20ms despite its intentionsl
How often have you seen it in the logs?
Cheers,
Jeff
This email is non-binding, is subject to contract, and neither Kulicke and Soffa Industries, Inc. nor its subsidiaries (each and collectively “K&S”) shall have any obligation to you to consummate the transactions herein or to enter into any agreement, other than in accordance with the terms and conditions of a definitive agreement if and when negotiated, finalized and executed between the parties. This email and all its contents are protected by International and United States copyright laws. Any reproduction or use of all or any part of this email without the express written consent of K&S is prohibited.
Mike Schanne <mschanne@kns.com> writes: > Is this what you are referring to? > - Prevent VACUUM from trying to freeze an old multixact ID involving a still-running transaction (Nathan Bossart, JeremySchneider) > This case would lead to VACUUM failing until the old transaction terminates. > https://www.postgresql.org/docs/release/9.6.16/ Hmmm ... after digging through the commit log, it seems the improvements I was thinking of were all pre-9.6. The only post-9.6 vacuum truncation performance fix I can find is https://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=7e26e02ee which came in in v10. regards, tom lane
Mike Schanne <mschanne@kns.com> writes:
> The error is not actually showing up very often (I have 8 occurrences from 11/29 and none since then). So maybe I
shouldnot 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.
Well, it's also an inherently non-repeating problem: once some iteration
of autovacuum has managed to truncate away the large amount of trailing
dead space that the file presumably had, later runs won't need to do
that.
Of course, if you have a usage pattern that repeatedly bloats the table
with lots of stuff-to-be-vacuumed, the issue could recur that way.
regards, tom lane
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.
On Thu, Dec 05, 2019 at 06:49:06PM -0500, Tom Lane wrote: > The only part that would get canceled in response to somebody taking a > non-exclusive lock is the last step, which is truncation of unused blocks at > the end of the table; that requires an exclusive lock. On Thu, Dec 05, 2019 at 06:55:02PM -0500, Jeff Janes wrote: > If the vacuum finds a lot of empty pages at the end of the table, it will > try to truncate them and takes a strong lock to do so. Should the exclusive lock bit be documented ? https://www.postgresql.org/docs/12/explicit-locking.html
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> 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
... This email is non-binding, is subject to contract, and neither Kulicke and Soffa Industries, Inc. nor its subsidiaries (each and collectively “K&S”) shall have any obligation to you to consummate the transactions herein or to enter into any agreement, other than in accordance with the terms and conditions of a definitive agreement if and when negotiated, finalized and executed between the parties. This email and all its contents are protected by International and United States copyright laws. Any reproduction or use of all or any part of this email without the express written consent of K&S is prohibited.Hi,
I am investigating a performance problem...
Craig James <cjames@emolecules.com> writes:
> Sorry to be off topic, but this bugs me. Language is important. This isn't
> directed at you specifically, but I see these disclaimers all the time. How
> can you post to a public newsgroup that automatically reproduces your email
> to thousands of subscribers, and additionally publishes it on
> publicly accessible archives, in direct conflict with your company's policy
> appended to your email? And why on Earth do your company's lawyers think
> this sort of disclaimer is helpful and even legally useful? Not to mention,
> do they realize it's vaguely offensive to every customer and colleague who
> receives it?
Yeah, it's annoying, and the idea that such an addendum is legally
enforceable is just laughable (bearing in mind that IANAL --- but
without a pre-existing contract, it's laughable). But the folks
actually emailing to our lists are generally peons with no say over
corporate policies, so there's not much they can do about it. Might
as well chill out, or just ignore any mail with a disclaimer you
find particularly offensive.
regards, tom lane
Disclaimer: if you believe that email disclaimers have any legal
force whatsoever, you are required to immediately send me $1M USD.
On Fri, Dec 06, 2019 at 10:42:19AM -0800, Craig James wrote:
> (I've changed the original subject, "autovacuum locking question", of the
> sender's email so as not to hijack that thread.)
Note that threads are defined by these headers, not by "Subject".
References: <0871fcf35ceb4caa8a2204ca9c38e330@USEPRDEX1.corp.kns.com>
In-Reply-To: <0871fcf35ceb4caa8a2204ca9c38e330@USEPRDEX1.corp.kns.com>
https://www.postgresql.org/message-id/CAFwQ8rcEExxB8ZuE_CYj6u6FZbRZjyWn%2BPo31hrfLAw1uBnKMg%40mail.gmail.com
Justin
(now hijacking your thread)
Craig James <cjames@emolecules.com> writes: > (I've changed the original subject, "autovacuum locking question", of the > sender's email so as not to hijack that thread.) > > On Thu, Dec 5, 2019 at 2:26 PM Mike Schanne <mschanne@kns.com> wrote: > >> Hi, >> >> I am investigating a performance problem... >> ... This email is non-binding, is subject to contract, and neither Kulicke >> and Soffa Industries, Inc. nor its subsidiaries (each and collectively >> “K&S”) shall have any obligation to you to consummate the transactions >> herein or to enter into any agreement, other than in accordance with the >> terms and conditions of a definitive agreement if and when negotiated, >> finalized and executed between the parties. This email and all its contents >> are protected by International and United States copyright laws. Any >> reproduction or use of all or any part of this email without the express >> written consent of K&S is prohibited. >> > > Sorry to be off topic, but this bugs me. Language is important. This isn't > directed at you specifically, but I see these disclaimers all the time. How > can you post to a public newsgroup that automatically reproduces your email > to thousands of subscribers, and additionally publishes it on > publicly accessible archives, in direct conflict with your company's policy > appended to your email? And why on Earth do your company's lawyers think > this sort of disclaimer is helpful and even legally useful? Not to mention, > do they realize it's vaguely offensive to every customer and colleague who > receives it? > > Craig Oh how I hear you! This is what I was using as my email signature (but not for groups). I feel for the OP who probably has little choice (other than work for a different employer, which is a very valid choice given the 'organisational culture' exhibited by policies requiring such nonsense) Notice to all senders: If you send me a message, on receipt of that message I consider that message to be my property and I will copy, share and deceminate as I see fit. I will provide attribution when appropriate and I willl endeavour to comply with all reasonable requests. However, I reject all threats or implied threats of legal action arising from an error or mistake on your part. It is your responsibility to manage your communications appropriately, not mine. -- Tim Cross
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.
On 12/6/19 1:42 PM, Craig James wrote: > (I've changed the original subject, "autovacuum locking question", of > the sender's email so as not to hijack that thread.) > > On Thu, Dec 5, 2019 at 2:26 PM Mike Schanne <mschanne@kns.com > <mailto:mschanne@kns.com>> wrote: > > Hi,____ > > I am investigating a performance problem... > > ... This email is non-binding, is subject to contract, and neither > Kulicke and Soffa Industries, Inc. nor its subsidiaries (each and > collectively “K&S”) shall have any obligation to you to consummate > the transactions herein or to enter into any agreement, other than > in accordance with the terms and conditions of a definitive > agreement if and when negotiated, finalized and executed between the > parties. This email and all its contents are protected by > International and United States copyright laws. Any reproduction or > use of all or any part of this email without the express written > consent of K&S is prohibited. > > > Sorry to be off topic, but this bugs me. Language is important. This > isn't directed at you specifically, but I see these disclaimers all the > time. How can you post to a public newsgroup that automatically > reproduces your email to thousands of subscribers, and additionally > publishes it on publicly accessible archives, in direct conflict with > your company's policy appended to your email? And why on Earth do your > company's lawyers think this sort of disclaimer is helpful and even > legally useful? Not to mention, do they realize it's vaguely offensive > to every customer and colleague who receives it? > > Craig People should probably not post anything on newsgroups from computers owned by their employers. They are probably violating the terms of their employment. It would be perfectly acceptable to me if all news servers automatically deleted all such would-be posts instead of actually posting them. -- .~. Jean-David Beyer /V\ PGP-Key:166D840A 0C610C8B /( )\ Shrewsbury, New Jersey ^^-^^ 07:05:02 up 23 days, 7:00, 2 users, load average: 5.31, 4.71, 4.38
I apologize for the legalese; as others have suggested it’s corporate IT policy and I have no control over it. I certainly intended no offense to the community here. I will use my personal email for future inquiries on this mailing list.
Thanks,
Mike
From: Craig James [mailto:cjames@emolecules.com]
Sent: Friday, December 06, 2019 1:42 PM
To: Mike Schanne
Cc: pgsql-performance@postgresql.org
Subject: Legal disclaimers on emails to this group
(I've changed the original subject, "autovacuum locking question", of the sender's email so as not to hijack that thread.)
On Thu, Dec 5, 2019 at 2:26 PM Mike Schanne <mschanne@kns.com> wrote:
Hi,
I am investigating a performance problem...
... This email is non-binding, is subject to contract, and neither Kulicke and Soffa Industries, Inc. nor its subsidiaries (each and collectively “K&S”) shall have any obligation to you to consummate the transactions herein or to enter into any agreement, other than in accordance with the terms and conditions of a definitive agreement if and when negotiated, finalized and executed between the parties. This email and all its contents are protected by International and United States copyright laws. Any reproduction or use of all or any part of this email without the express written consent of K&S is prohibited.
Sorry to be off topic, but this bugs me. Language is important. This isn't directed at you specifically, but I see these disclaimers all the time. How can you post to a public newsgroup that automatically reproduces your email to thousands of subscribers, and additionally publishes it on publicly accessible archives, in direct conflict with your company's policy appended to your email? And why on Earth do your company's lawyers think this sort of disclaimer is helpful and even legally useful? Not to mention, do they realize it's vaguely offensive to every customer and colleague who receives it?
Craig
This email is non-binding, is subject to contract, and neither Kulicke and Soffa Industries, Inc. nor its subsidiaries (each and collectively “K&S”) shall have any obligation to you to consummate the transactions herein or to enter into any agreement, other than in accordance with the terms and conditions of a definitive agreement if and when negotiated, finalized and executed between the parties. This email and all its contents are protected by International and United States copyright laws. Any reproduction or use of all or any part of this email without the express written consent of K&S is prohibited.
Il 06/12/19 20:27, Tom Lane ha scritto: > > Disclaimer: if you believe that email disclaimers have any legal > force whatsoever, you are required to immediately send me $1M USD. > > > No legal force (so no 1M USD :-) ) but if you're caught not enforcing these disclaimers (even at the bottom of the signature, so leaving the user the choice to use it or not) in your corporate emails, you're fined (and in some cases, also fired).
Il 07/12/19 13:10, Jean-David Beyer ha scritto: > People should probably not post anything on newsgroups from computers > owned by their employers. They are probably violating the terms of their > employment. Are you sure? Imagine you are the DBA in your company and you need to ask a question to PostgreSQL mailing list.... I think it's perfectly legit that you do it from your corporate email, and not from your private one (since private email address use is not admitted, just like private messages from corporate email). I have my disclaimer at the bottom of my signature, so I can choose when to add it or not, it's my company policy; someone else's policy should be that the corporate mail server automatically appends the disclaimer to every mail message (so no user control). I agree with Tom to just ignore disclaimers and delete them when replying to threads: you can't blame someone for what's not under his control. Obvoiusly you have to be careful to not include sensitive data in your public email, but that's always under your control. Cheers Moreno.-