From: Tom Lane <tgl@sss.pgh.pa.us>
To: Mike Schanne <mschanne@kns.com>
Cc: 'pgsql-performance@postgresql.org' <pgsql-performance@postgresql.org>
Subject: Re: autovacuum locking question
Date: Thu, 05 Dec 2019 18:49:06 -0500
Message-ID: <26221.1575589746@sss.pgh.pa.us> (raw)
In-Reply-To: <0871fcf35ceb4caa8a2204ca9c38e330@USEPRDEX1.corp.kns.com>
References: <0871fcf35ceb4caa8a2204ca9c38e330@USEPRDEX1.corp.kns.com>
Mike Schanne <mschanne@kns.com> writes:
> 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?
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
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: tgl@sss.pgh.pa.us, mschanne@kns.com
Subject: Re: autovacuum locking question
In-Reply-To: <26221.1575589746@sss.pgh.pa.us>
* 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