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