pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedCan manual vacuuming conflict with auto vacuuming?
4+ messages / 3 participants
[nested] [flat]
* Can manual vacuuming conflict with auto vacuuming?
@ 2017-06-01 19:11 Mahesh Jhala <mjhala@carrentals.com>
0 siblings, 1 reply; 4+ messages in thread
From: Mahesh Jhala @ 2017-06-01 19:11 UTC (permalink / raw)
To: pgsql-admin
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:
1. 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.
2. 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Can manual vacuuming conflict with auto vacuuming?
@ 2017-06-01 19:16 Alvaro Herrera <alvherre@2ndquadrant.com>
parent: Mahesh Jhala <mjhala@carrentals.com>
0 siblings, 1 reply; 4+ messages in thread
From: Alvaro Herrera @ 2017-06-01 19:16 UTC (permalink / raw)
To: Mahesh Jhala <mjhala@carrentals.com>; +Cc: pgsql-admin
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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Can manual vacuuming conflict with auto vacuuming?
@ 2017-06-01 19:20 Scott Marlowe <scott.marlowe@gmail.com>
parent: Alvaro Herrera <alvherre@2ndquadrant.com>
0 siblings, 1 reply; 4+ messages in thread
From: Scott Marlowe @ 2017-06-01 19:20 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@2ndquadrant.com>; +Cc: Mahesh Jhala <mjhala@carrentals.com>; pgsql-admin
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.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Can manual vacuuming conflict with auto vacuuming?
@ 2017-06-01 19:46 Mahesh Jhala <mjhala@carrentals.com>
parent: Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Mahesh Jhala @ 2017-06-01 19:46 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; Alvaro Herrera <alvherre@2ndquadrant.com>; +Cc: pgsql-admin
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 behind that. 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 that table.
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 behind that. This may be necessary if your autovacuum is not aggressive enough to keep up with some particularly heavy load.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2017-06-01 19:46 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-06-01 19:11 Can manual vacuuming conflict with auto vacuuming? Mahesh Jhala <mjhala@carrentals.com>
2017-06-01 19:16 ` Alvaro Herrera <alvherre@2ndquadrant.com>
2017-06-01 19:20 ` Scott Marlowe <scott.marlowe@gmail.com>
2017-06-01 19:46 ` Mahesh Jhala <mjhala@carrentals.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox