Обсуждение: Aggressive freezing in lazy-vacuum
"Jim C. Nasby" <jim@nasby.net> wrote: > > * Aggressive freezing > > we will use OldestXmin as the threshold to freeze tuples in > > dirty pages or pages that have some dead tuples. Or, many UNFROZEN > > pages still remain after vacuum and they will cost us in the next > > vacuum preventing XID wraparound. > > Another good idea. If it's not too invasive I'd love to see that as a > stand-alone patch so that we know it can get in. This is a stand-alone patch for aggressive freezing. I'll propose to use OldestXmin instead of FreezeLimit as the freeze threshold in the circumstances below: - The page is already dirty. - There are another tuple to be frozen in the same page. - There are another dead tuples in the same page. Freezing is delayed until the heap vacuum phase. Anyway we create new dirty buffers and/or write WAL then, so additional freezing is almost free. Keeping the number of unfrozen tuples low, we can reduce the cost of next XID wraparound vacuum and piggyback multiple freezing operations in the same page. The following test shows differences of the number of unfrozen tuples with or without the patch. Formerly, recently inserted tuples are not frozen immediately (1). Even if there are some dead tuples in the same page, unfrozen live tuples are not frozen (2). With patch, the number after first vacuum was already low (3), because the pages including recently inserted tuples were dirty and not written yet, so aggressive freeze was performed for it. Moreover, if there are dead tuples in a page, other live tuples in the same page are also frozen (4). # CREATE CAST (xid AS integer) WITHOUT FUNCTION AS IMPLICIT; [without patch] $ ./pgbench -i -s1 (including vacuum) # SELECT count(*) FROM accounts WHERE xmin > 2; => 100000 (1) # UPDATE accounts SET aid = aid WHERE aid % 20 = 0; => UPDATE 5000 # SELECT count(*) FROM accounts WHERE xmin > 2; => 100000 # VACUUM accounts; # SELECT count(*) FROM accounts WHERE xmin > 2; => 100000 (2) [with patch] $ ./pgbench -i -s1 (including vacuum) # SELECT count(*) FROM accounts WHERE xmin > 2; => 2135 (3) # UPDATE accounts SET aid = aid WHERE aid % 20 = 0; => UPDATE 5000 # SELECT count(*) FROM accounts WHERE xmin > 2; => 7028 # VACUUM accounts; # SELECT count(*) FROM accounts WHERE xmin > 2; => 0 (4) Regards, --- ITAGAKI Takahiro NTT Open Source Software Center
Вложения
ITAGAKI Takahiro <itagaki.takahiro@oss.ntt.co.jp> writes: > This is a stand-alone patch for aggressive freezing. I'll propose > to use OldestXmin instead of FreezeLimit as the freeze threshold > in the circumstances below: I think it's a really bad idea to freeze that aggressively under any circumstances except being told to (ie, VACUUM FREEZE). When you freeze, you lose history information that might be needed later --- for forensic purposes if nothing else. You need to show a fairly amazing performance gain to justify that, and I don't think you can. regards, tom lane
Tom Lane wrote: > ITAGAKI Takahiro <itagaki.takahiro@oss.ntt.co.jp> writes: >> This is a stand-alone patch for aggressive freezing. I'll propose >> to use OldestXmin instead of FreezeLimit as the freeze threshold >> in the circumstances below: > > I think it's a really bad idea to freeze that aggressively under any > circumstances except being told to (ie, VACUUM FREEZE). When you > freeze, you lose history information that might be needed later --- for > forensic purposes if nothing else. You need to show a fairly amazing > performance gain to justify that, and I don't think you can. There could be a GUC vacuum_freeze_limit, and the actual FreezeLimit would be calculated as GetOldestXmin() - vacuum_freeze_limit The default for vacuum_freeze_limit would be MaxTransactionId/2, just as it is now. greetings, Florian Pflug
Florian G. Pflug wrote: > There could be a GUC vacuum_freeze_limit, and the actual FreezeLimit > would be calculated as > GetOldestXmin() - vacuum_freeze_limit We already have that. It's called vacuum_freeze_min_age, and the default is 100 million transactions. IIRC we added it late in the 8.2 release cycle when we changed the clog truncation point to depend on freeze limit. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
Heikki Linnakangas wrote: > Florian G. Pflug wrote: >> There could be a GUC vacuum_freeze_limit, and the actual FreezeLimit >> would be calculated as >> GetOldestXmin() - vacuum_freeze_limit > > We already have that. It's called vacuum_freeze_min_age, and the default > is 100 million transactions. > > IIRC we added it late in the 8.2 release cycle when we changed the clog > truncation point to depend on freeze limit. Ok, that explains why I didn't find it when I checked the source - I checked the 8.1 sources by accident ;-) Anyway, thanks for pointing that out ;-) greetings, Florian Pflug