Re: [HACKERS] Freezing without cleanup lock

2015-10-26 Thread Masahiko Sawada 
On Mon, Oct 26, 2015 at 4:39 AM, Jim Nasby  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

Re: [HACKERS] Freezing without cleanup lock

2015-10-25 Thread Jim Nasby 

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

Re: [HACKERS] Freezing without cleanup lock

2015-10-22 Thread Jim Nasby 

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

Re: [HACKERS] Freezing without cleanup lock

2015-10-22 Thread Alvaro Herrera 
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 Herrerahttp://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

[HACKERS] Freezing without cleanup lock

2015-10-21 Thread Jim Nasby 
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

Re: [HACKERS] Freezing without cleanup lock

2015-10-21 Thread Tom Lane 
Jim Nasby  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

Re: [HACKERS] Freezing without cleanup lock

2015-10-21 Thread Andres Freund 
On October 21, 2015 9:47:45 PM GMT+02:00, Tom Lane  wrote:
>Jim Nasby  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

Re: [HACKERS] Freezing without cleanup lock

2015-10-21 Thread Alvaro Herrera 
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 Herrerahttp://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