Freezing without cleanup lock
While warning a client that just did a Slony-based version upgrade to
make sure to freeze the new database, it occurred to me that it should
be safe to freeze without the cleanup lock. This is interesting because
it would allow a scan_all vacuum to do it's job without blocking on the
cleanup lock.
Does anyone have a feel for whether scan_all vacuums blocking on the
cleanup lock is an actual problem?
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Jim Nasby <Jim.Nasby@BlueTreble.com> writes:
While warning a client that just did a Slony-based version upgrade to
make sure to freeze the new database, it occurred to me that it should
be safe to freeze without the cleanup lock.
What's your argument for that being safe?
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On October 21, 2015 9:47:45 PM GMT+02:00, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Jim Nasby <Jim.Nasby@BlueTreble.com> writes:
While warning a client that just did a Slony-based version upgrade to
make sure to freeze the new database, it occurred to me that it
should
be safe to freeze without the cleanup lock.
What's your argument for that being safe?
It doesn't affect tuple contents and thus backends having a pin can continue looking at tuple contents. The reason we need a cleanup lock is IIRC repairing page fragmentation / hot pruning, not freezing.
Andres
---
Please excuse brevity and formatting - I am writing this on my mobile phone.--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Jim Nasby wrote:
While warning a client that just did a Slony-based version upgrade to make
sure to freeze the new database, it occurred to me that it should be safe to
freeze without the cleanup lock. This is interesting because it would allow
a scan_all vacuum to do it's job without blocking on the cleanup lock.Does anyone have a feel for whether scan_all vacuums blocking on the cleanup
lock is an actual problem?
Yeah, I remember we discussed this and some other possible improvements
related to freezing. I think other ideas proposed were that (1) during
an emergency (uncancellable) autovacuum run, we process only the tables
that are past the age limit, and (2) we remove the cost-based sleep so
that it finishes as quickly as possible. (Yours is (3) only freeze and
not do any actual pruning -- did I get that right?)
--
�lvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 10/21/15 3:14 PM, Alvaro Herrera wrote:
Jim Nasby wrote:
While warning a client that just did a Slony-based version upgrade to make
sure to freeze the new database, it occurred to me that it should be safe to
freeze without the cleanup lock. This is interesting because it would allow
a scan_all vacuum to do it's job without blocking on the cleanup lock.Does anyone have a feel for whether scan_all vacuums blocking on the cleanup
lock is an actual problem?Yeah, I remember we discussed this and some other possible improvements
related to freezing. I think other ideas proposed were that (1) during
an emergency (uncancellable) autovacuum run, we process only the tables
that are past the age limit, and (2) we remove the cost-based sleep so
that it finishes as quickly as possible. (Yours is (3) only freeze and
not do any actual pruning -- did I get that right?)
That would be the minimal-impact version, yes. But I suspect if we went
through the trouble to do that, it would be just as easy to attempt the
freeze regardless of what scan_all is set to.
What I wish I knew is whether this problem was worth worrying about or
not. Hopefully the extra logging in 9.5 will shed some light at some
point...
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Jim Nasby wrote:
That would be the minimal-impact version, yes. But I suspect if we went
through the trouble to do that, it would be just as easy to attempt the
freeze regardless of what scan_all is set to.
You mean if !scan_all we conditional-get the cleanup lock, if we get it
then prune, if not then freeze? That seems nice on paper but I think
it's useless because unless scan_all is true, then relfrozenxid doesn't
advance anyway.
What I wish I knew is whether this problem was worth worrying about or not.
Hopefully the extra logging in 9.5 will shed some light at some point...
As I recall, Andres says it isn't, but I have recollections of scans
that take a very long time to finish because they keep running into a
vacuum that has a page locked.
--
�lvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 10/22/15 6:39 PM, Alvaro Herrera wrote:
Jim Nasby wrote:
That would be the minimal-impact version, yes. But I suspect if we went
through the trouble to do that, it would be just as easy to attempt the
freeze regardless of what scan_all is set to.You mean if !scan_all we conditional-get the cleanup lock, if we get it
then prune, if not then freeze? That seems nice on paper but I think
it's useless because unless scan_all is true, then relfrozenxid doesn't
advance anyway.
Actually, advancing relfrozenxid only depends on having hit all pages in
the table, which can happen even if !scan_all. Aside from that, once the
freeze map hits this would be useful in setting bits there.
What I wish I knew is whether this problem was worth worrying about or not.
Hopefully the extra logging in 9.5 will shed some light at some point...As I recall, Andres says it isn't, but I have recollections of scans
that take a very long time to finish because they keep running into a
vacuum that has a page locked.
I guess lets see if the new logging we have on this sheds some light then.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Mon, Oct 26, 2015 at 4:39 AM, Jim Nasby <Jim.Nasby@bluetreble.com> wrote:
On 10/22/15 6:39 PM, Alvaro Herrera wrote:
Jim Nasby wrote:
That would be the minimal-impact version, yes. But I suspect if we went
through the trouble to do that, it would be just as easy to attempt the
freeze regardless of what scan_all is set to.You mean if !scan_all we conditional-get the cleanup lock, if we get it
then prune, if not then freeze? That seems nice on paper but I think
it's useless because unless scan_all is true, then relfrozenxid doesn't
advance anyway.Actually, advancing relfrozenxid only depends on having hit all pages in the
table, which can happen even if !scan_all. Aside from that, once the freeze
map hits this would be useful in setting bits there.
Yeah, when the all pages have frozen totally (that is, when
scanned_page + vm_skipped_frozen_page equals relpages), relfrozenxid
will be updated. But the item pointers of page that are frozen by such
logic are never pruned.
Regards,
--
Masahiko Sawada
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers