Idea for getting rid of VACUUM FREEZE on cold pages
in [PgSql]
|
Prev: [HACKERS] beta testing - planner bug - ERROR: XX000: failed to build any 2-way joins
Next: [HACKERS] beta testing - pg_upgrade bug fix - double free
From: Robert Haas on 2 Jun 2010 21:39 On Wed, Jun 2, 2010 at 3:10 PM, Alvaro Herrera <alvherre(a)commandprompt.com> wrote: > Excerpts from Robert Haas's message of mié jun 02 14:16:33 -0400 2010: > >> We could, but I think we'd be better off just freezing at the time we >> mark the page PD_ALL_VISIBLE and then using the visibility map for >> both purposes. Keeping around the old xmin values after every tuple >> on the page is visible to every running transaction is useful only for >> forensics, and building a whole new freeze map just to retain that >> information longer (and eventually force a massive anti-wraparound >> vacuum) seems like overkill. > > Reducing the xid wraparound horizon "a bit" is reasonable, but moving it > all the way forward to OldestXmin is a bit much, methinks. Why? If it's just for forensics, those are some pretty expensive forensics - it eventually costs you an additional complete rewrite of every page. > Besides, there's another argument for not freezing tuples immediately: > they may be updated shortly thereafter, causing extra churn for no gain. But if you were going to update PD_ALL_VISIBLE, then you were going to write the page anyway. You might as well freeze everything at the same time so you don't have to come back. Alternatively, you could do what I suggested upthread and just believe PD_ALL_VISIBLE over the individual tuple xmins. Then you don't have to freeze the page until it's next written, but you still get to keep your forensic info. > I'd prefer a setting that would tell the system to freeze all tuples > that fall within a safety range whenever any tuple in the page is frozen > -- weren't you working on a patch to do this? (was it Jeff Davis?) Not me. I don't think that's going to help a whole lot, though. In many of the painful scenarios, every tuple on the page will have the same XID, and therefore they'll all be frozen at the same time anyway. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Greg Stark on 3 Jun 2010 06:41 So I think the scheme in the original post of this thread is workable. Not as described but could be made to work. In which case I think it's preferable to a freeze map -- which I had previously assumed we would need eventually. The problem with the scheme originally described is that it assumed you could have an cycle counter and then arrange that all the xids on the page are within that cycle. That doesn't work because you could easily have two live xids on the page that belong to two cycles -- one FirstNormalTransactionId and one MaxTransactionId. I think to make it work you need to store a whole 64-bit reference transaction id consisting of both a cycle counter and a transaction id. The invariant for the page is that every xid on the page can be compared to that reference transaction id using normal transactionid semantics. Actually I think the easiest way to do that is to set it to the oldest xid on the page. The first thing to do before comparing any transaction id on the page with a real transaction id would be to figure out whether the reference xid is comparable to the live xid, which if it's the oldest xid on the page implies they'll all be comparable. The way to maintain that invariant would be that any xid insertion on the page must advance the reference xid if it's not comparable to the newly inserted xid. It has to be advanced to the oldest xid that's still comparable with the newly inserted xid. Any xids on the page that are older than the new refernce xid have to be frozen or removed. I'm not sure how to do that without keeping clog forever though. -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Jan Wieck on 4 Jun 2010 07:47 On 6/2/2010 2:16 PM, Robert Haas wrote: > On Wed, Jun 2, 2010 at 2:05 PM, Tom Lane <tgl(a)sss.pgh.pa.us> wrote: >> Alvaro Herrera <alvherre(a)commandprompt.com> writes: >>> The problem is that vacuum doesn't know that a certain part of the table >>> is already frozen. It needs to scan it completely anyways. If we had a >>> "frozen" map, we could mark pages that are completely frozen and thus do >>> not need any vacuuming; but we don't (I don't recall the reasons for >>> this. Maybe it's just that no one has gotten around to it, or maybe >>> there's something else). >> >> Offhand I think the reason is that you'd have to trust the frozen bit >> to be 100% correct (or at least never set to 1 in error). Currently, >> both the FSM and visibility forks are just hints, and we won't suffer >> data corruption if they're wrong; so we don't get too tense about WAL >> logging or fsync'ing updates. I believe Heikki is looking into what >> it'd take to make the visibility map 100% reliable, in connection with >> the desire for index-only scans. If we get that and the overhead isn't >> too terrible maybe we could build a frozen-status map the same way. > > We could, but I think we'd be better off just freezing at the time we > mark the page PD_ALL_VISIBLE and then using the visibility map for > both purposes. Keeping around the old xmin values after every tuple > on the page is visible to every running transaction is useful only for > forensics, and building a whole new freeze map just to retain that > information longer (and eventually force a massive anti-wraparound > vacuum) seems like overkill. Agreed. The whole business of minimum freeze age always struck me as leaving bread crumbs behind. Other than forensics, what is the actual value of that overhead? Jan -- Anyone who trades liberty for security deserves neither liberty nor security. -- Benjamin Franklin -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Tom Lane on 4 Jun 2010 10:18 Jan Wieck <JanWieck(a)Yahoo.com> writes: > On 6/2/2010 3:10 PM, Alvaro Herrera wrote: >> I'd prefer a setting that would tell the system to freeze all tuples >> that fall within a safety range whenever any tuple in the page is frozen >> -- weren't you working on a patch to do this? (was it Jeff Davis?) > I just see a lot of cost caused by this "safety range". I yet have to > see its real value, other than "feel good". Jan, you don't know what you're talking about. I have repeatedly had cases where being able to look at xmin was critical to understanding a bug. I *will not* hold still for a solution that effectively reduces min_freeze_age to zero. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Robert Haas on 4 Jun 2010 10:57
On Fri, Jun 4, 2010 at 10:18 AM, Tom Lane <tgl(a)sss.pgh.pa.us> wrote: > Jan Wieck <JanWieck(a)Yahoo.com> writes: >> On 6/2/2010 3:10 PM, Alvaro Herrera wrote: >>> I'd prefer a setting that would tell the system to freeze all tuples >>> that fall within a safety range whenever any tuple in the page is frozen >>> -- weren't you working on a patch to do this? (was it Jeff Davis?) > >> I just see a lot of cost caused by this "safety range". I yet have to >> see its real value, other than "feel good". > > Jan, you don't know what you're talking about. I have repeatedly had > cases where being able to look at xmin was critical to understanding > a bug. I *will not* hold still for a solution that effectively reduces > min_freeze_age to zero. So, we're talking in circles here. I've already proposed a method that would avoid the need to wipe out the xmins: http://archives.postgresql.org/pgsql-hackers/2010-05/msg01485.php And you said that if we were going to do that we might as well just freeze sooner: http://archives.postgresql.org/pgsql-hackers/2010-05/msg01548.php If you don't want to freeze sooner, let's go back to the method described in the first email. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers |