Autovacuum lock conflict
Hi,
I am using PostgreSQL 9.6.13.
I have some questions about auto vacuums.
I find it quite hard to have some info about vacuum locking in the
documentation,
but from what I found running the command myself is that simple vacuum
will hold a Share Update Exclusive lock.
We are using auto vacuums on our database and recently we had an issue
when rolling out a new version of our solution.
The new version included a migration creating a new index for a big table.
We do try to have no maintenance when migrating, thus we generally use
CREATE INDEX CONCURRENTLY to avoid locking a table for reading and
writing.
We went across an issue with our index creation being locked by an
auto vacuum, as both processes hold a Share Update Exclusive lock.
Thankfully for us, we run our database migration with a small lock
timeout so it was not too dangerous, but we are left with some doubt
about our "smooth" process.
In these cases, we want to have our migration run in priority as it
blocks our release and everything that goes along.
I've read - but I cannot find it anywhere - that a vacuum process
"should" kill itself if it finds out that it is blocking any other
query;
but only if the notice "to prevent wraparound" is not present. (have I
missed official documentation somewhere..?)
We would like to have inputs on how to cleanly prevent these issues in
the future.
1) What's the actual answer about a vacuum killing itself if it blocks
a query? Is that true?
How fast is it to kill itself? As we have a 2s lock timeout, if it
takes 5s to check that it should kill itself, we won't benefit from
this behaviour.
2) What can we do to prevent any concurrent index creation from being
locked out by auto vacuums?
We have thought of a dirty solution consisting of:
- disabling auto vacuums before a migration
- kill current pids with a vacuum
- do our migration
- enable back auto vacuum.
We find this solution awful, error-prone and "complex" to have.
Are there any other way except having manuals vacuum being played
during the night?
Thanks in advance for your help,
Regards,
--
Christophe Escobar
On 9/23/19 8:09 AM, Christophe Escobar wrote:
Hi,
I am using PostgreSQL 9.6.13.
I have some questions about auto vacuums.
I find it quite hard to have some info about vacuum locking in the
documentation,
but from what I found running the command myself is that simple vacuum
will hold a Share Update Exclusive lock.We are using auto vacuums on our database and recently we had an issue
when rolling out a new version of our solution.
The new version included a migration creating a new index for a big table.We do try to have no maintenance when migrating, thus we generally use
CREATE INDEX CONCURRENTLY to avoid locking a table for reading and
writing.We went across an issue with our index creation being locked by an
auto vacuum, as both processes hold a Share Update Exclusive lock.Thankfully for us, we run our database migration with a small lock
timeout so it was not too dangerous, but we are left with some doubt
about our "smooth" process.In these cases, we want to have our migration run in priority as it
blocks our release and everything that goes along.I've read - but I cannot find it anywhere - that a vacuum process
"should" kill itself if it finds out that it is blocking any other
query;
but only if the notice "to prevent wraparound" is not present. (have I
missed official documentation somewhere..?)
It will be, courtesy of PostgreSQL Weekly News - September 22, 2019:
/messages/by-id/20190922194743.GA24367@fetter.org
Amit Kapila pushed:
- Doc: document autovacuum interruption. It's important users be able to
know
(without looking at the source code) that running DDL or DDL-like
commands can
interrupt autovacuum which can lead to a lot of dead tuples and hence
slower
database operations. Reported-by: James Coleman Author: James Coleman
Reviewed-by: Amit Kapila Backpatch-through: 9.4 Discussion:
/messages/by-id/CAAaqYe-XYyNwML1=f=gnd0qWg46PnvD=BDrCZ5-L94B887XVxQ@mail.gmail.com
https://git.postgresql.org/pg/commitdiff/82fa3ff867219a212a467317a77011df29cb5903
And the diff:
We would like to have inputs on how to cleanly prevent these issues in
the future.1) What's the actual answer about a vacuum killing itself if it blocks
a query? Is that true?
How fast is it to kill itself? As we have a 2s lock timeout, if it
takes 5s to check that it should kill itself, we won't benefit from
this behaviour.2) What can we do to prevent any concurrent index creation from being
locked out by auto vacuums?
We have thought of a dirty solution consisting of:
- disabling auto vacuums before a migration
- kill current pids with a vacuum
- do our migration
- enable back auto vacuum.
We find this solution awful, error-prone and "complex" to have.
Are there any other way except having manuals vacuum being played
during the night?Thanks in advance for your help,
Regards,
--
Adrian Klaver
adrian.klaver@aklaver.com
Christophe Escobar wrote:
We do try to have no maintenance when migrating, thus we generally
use CREATE INDEX CONCURRENTLY to avoid locking a table for reading
and writing.We went across an issue with our index creation being locked by an
auto vacuum, as both processes hold a Share Update Exclusive lock.Thankfully for us, we run our database migration with a small lock
timeout so it was not too dangerous, but we are left with some doubt
about our "smooth" process.In these cases, we want to have our migration run in priority as it
blocks our release and everything that goes along.I've read - but I cannot find it anywhere - that a vacuum process
"should" kill itself if it finds out that it is blocking any other
query;
but only if the notice "to prevent wraparound" is not present. (have
I
missed official documentation somewhere..?)We would like to have inputs on how to cleanly prevent these issues
in the future.1) What's the actual answer about a vacuum killing itself if it
blocks
a query? Is that true?
How fast is it to kill itself? As we have a 2s lock timeout, if
it
takes 5s to check that it should kill itself, we won't benefit from
this behaviour.
It must be an anti-wraparound VACUUM, because a normal autovacuum would
terminate after about 1 second ("deadlock_timeout" parameter).
2) What can we do to prevent any concurrent index creation from being
locked out by auto vacuums?
We have thought of a dirty solution consisting of:
- disabling auto vacuums before a migration
- kill current pids with a vacuum
- do our migration
- enable back auto vacuum.
We find this solution awful, error-prone and "complex" to have.
Are there any other way except having manuals vacuum being played
during the night?
Yes, that is ugly and not a good idea. Unless done very carefully,
this has the potential to harm your database. VACUUM is important.
I would make sure that all tables are vacuumed in a timely fashion.
- Avoid explicit table locks or other operations that would
block autovacuum (and so prevent it, because it will terminate).
- For tables that receive UPDATEs and DELETEs, make sure that
"autovacuum_vacuum_cost_delay" is small enough so that autovacuum
finishes quickly.
- For tables that receive only INSERTs, schedule a regular VACUUM
with "cron" or similar. Unfortunately, PostgreSQL isn't very smart
about vacuuming insert-only tables.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
- For tables that receive only INSERTs, schedule a regular VACUUM
with "cron" or similar. Unfortunately, PostgreSQL isn't very smart
about vacuuming insert-only tables.
What is the need to vacuum on an insert only table? Does that just maintain
the freespace map?
On Mon, 2019-09-23 at 13:53 -0600, Michael Lewis wrote:
- For tables that receive only INSERTs, schedule a regular VACUUM
with "cron" or similar. Unfortunately, PostgreSQL isn't very
smart
about vacuuming insert-only tables.What is the need to vacuum on an insert only table? Does that just
maintain the freespace map?
No, the idea is to get rows frozen *before* autovacuum launches
an anti-wraparound vacuum.
You could launch VACUUM (FREEZE) for best effect.
This way you can schedule the operation so that it doesn't
interfere with your other activities.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com