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