Re: Autovacuum lock conflict
От | Adrian Klaver |
---|---|
Тема | Re: Autovacuum lock conflict |
Дата | |
Msg-id | 831a0e2e-e6b2-e12b-8ba4-bfed20a99775@aklaver.com обсуждение исходный текст |
Ответ на | Autovacuum lock conflict (Christophe Escobar <christophe.esco@gmail.com>) |
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: