Обсуждение: [ADMIN] Can manual vacuuming conflict with auto vacuuming?
Hello All,
If autovacuum of a large PostgreSQL table is in progress, and I start a manual vacuum on the table, is there likely to be a conflict (locking etc.) between the two vacuums?
I did the following tests:
-
ALTER TABLE <table_name> SET (autovacuum_vacuum_scale_factor = 0.0);
ALTER TABLE <table_name> SET (autovacuum_vacuum_threshold = 1);
The deleted a few rows from table <table_name>. This triggered an autovacuum in a minute or two. While this autovacuum was in progress I started a manual vacuum from another session while monitoring pg_stat_activity.
-
My tests showed two conflicting results, one of which I am not able to repeat:
- Until the manual vacuum is started, pg_stat_activity shows the autovacuum as active. As soon as the manual vacuum is started, pg_stat_activity starts showing the manual vacuum as active, and stops showing the autovacuum. This suggests that a manual vacuum ‘replaces’ the auto vacuum. I was able to repeat this test.
- The manual vacuum does not progress, it seems to be blocked by the autovacuum. pg_stat_activity continues to show the autovacuum. I am not able to repeat this test. Could this be because the autovacuum is at a certain stage in the vacuuming process, and cannot stop at that point?
Thanks,
Mahesh
Mahesh Jhala wrote: > Hello All, > > If autovacuum of a large PostgreSQL table is in progress, and I start > a manual vacuum on the table, is there likely to be a conflict > (locking etc.) between the two vacuums? It depends on the autovacuum being for-wraparound or not. If not, then the conflict will be detected within one second and autovacuum will cancel itself, so the regular vacuum would be able to proceed. So there is a one-second period of time during which you would see the manual vacuum as blocked by autovacuum. If autovacuum is for wraparound protection, then it won't cancel itself, and the regular vacuum will just wait until autovacuum is finished. There is a final stage in vacuuming (both auto and regular) during which the table is truncated (the last few empty pages, if any). There's some heuristics to handle that case nowadays which I don't remember very clearly. -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On Thu, Jun 1, 2017 at 1:16 PM, Alvaro Herrera <alvherre@2ndquadrant.com> wrote: > Mahesh Jhala wrote: >> Hello All, >> >> If autovacuum of a large PostgreSQL table is in progress, and I start >> a manual vacuum on the table, is there likely to be a conflict >> (locking etc.) between the two vacuums? > > It depends on the autovacuum being for-wraparound or not. If not, then > the conflict will be detected within one second and autovacuum will > cancel itself, so the regular vacuum would be able to proceed. So there > is a one-second period of time during which you would see the manual > vacuum as blocked by autovacuum. > > If autovacuum is for wraparound protection, then it won't cancel itself, > and the regular vacuum will just wait until autovacuum is finished. > > > There is a final stage in vacuuming (both auto and regular) during which > the table is truncated (the last few empty pages, if any). There's some > heuristics to handle that case nowadays which I don't remember very > clearly. Note that if you cancel the autovacuum the manual one will then proceed, and the next autovacuum will have to wait behind that. This may be necessary if your autovacuum is not aggressive enough to keep up with some particularly heavy load.
Thanks very much, Scott, Alvaro! From what you said, I have another question: - Note that if you cancel the autovacuum the manual one will then proceed, and the next autovacuum will have to wait behindthat. This may be necessary if your autovacuum is not aggressive enough to keep up with some particularly heavy load. - If manual vacuum of a table is in progress, and autovacuum for the same table starts: a. Does autovacuum abort for that table and move on to the next table in it list. - or - b. Does autovacuum wait until the manual vacuum is completed (however long that takes), and then starts autovaccum on thattable. Thanks again, Mahesh -----Original Message----- From: Scott Marlowe [mailto:scott.marlowe@gmail.com] Sent: Thursday, June 1, 2017 12:21 PM To: Alvaro Herrera <alvherre@2ndquadrant.com> Cc: Mahesh Jhala <mjhala@carrentals.com>; pgsql-admin@postgresql.org Subject: Re: [ADMIN] Can manual vacuuming conflict with auto vacuuming? On Thu, Jun 1, 2017 at 1:16 PM, Alvaro Herrera <alvherre@2ndquadrant.com> wrote: > Mahesh Jhala wrote: >> Hello All, >> >> If autovacuum of a large PostgreSQL table is in progress, and I start >> a manual vacuum on the table, is there likely to be a conflict >> (locking etc.) between the two vacuums? > > It depends on the autovacuum being for-wraparound or not. If not, > then the conflict will be detected within one second and autovacuum > will cancel itself, so the regular vacuum would be able to proceed. > So there is a one-second period of time during which you would see the > manual vacuum as blocked by autovacuum. > > If autovacuum is for wraparound protection, then it won't cancel > itself, and the regular vacuum will just wait until autovacuum is finished. > > > There is a final stage in vacuuming (both auto and regular) during > which the table is truncated (the last few empty pages, if any). > There's some heuristics to handle that case nowadays which I don't > remember very clearly. Note that if you cancel the autovacuum the manual one will then proceed, and the next autovacuum will have to wait behindthat. This may be necessary if your autovacuum is not aggressive enough to keep up with some particularly heavy load.