RE: autovacuum locking question - Mailing list pgsql-performance

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.