Thread: Autovacuum lock conflict

Autovacuum lock conflict

From
Christophe Escobar
Date:
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



Re: Autovacuum lock conflict

From
Adrian Klaver
Date:
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:

https://www.postgresql.org/message-id/20190922194743.GA24367%40fetter.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:
 
https://postgr.es/m/CAAaqYe-XYyNwML1=f=gnd0qWg46PnvD=BDrCZ5-L94B887XVxQ@mail.gmail.com
 
https://git.postgresql.org/pg/commitdiff/82fa3ff867219a212a467317a77011df29cb5903

And the diff:

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=70377cf4c6bf4eb4b2d1209752a300d5f3571145

> 
> 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



Re: Autovacuum lock conflict

From
Laurenz Albe
Date:
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




Re: Autovacuum lock conflict

From
Michael Lewis
Date:
- 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?

Re: Autovacuum lock conflict

From
Laurenz Albe
Date:
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