On Tue, 11 Aug 2020 at 07:56, Alvaro Herrera <alvhe...@2ndquadrant.com> wrote: > > Last week, James reported to us that after promoting a replica, some > seqscan was taking a huge amount of time; on investigation he saw that > there was a high rate of FPI_FOR_HINT wal messages by the seqscan. > Looking closely at the generated traffic, HEAP_XMIN_COMMITTED was being > set on some tuples. > > Now this may seem obvious to some as a drawback of the current system, > but I was taken by surprise. The problem was simply that when a page is > examined by a seqscan, we do HeapTupleSatisfiesVisibility of each tuple > in isolation; and for each tuple we call SetHintBits(). And only the > first time the FPI happens; by the time we get to the second tuple, the > page is already dirty, so there's no need to emit an FPI. But the FPI > we sent only had the bit on the first tuple ... so the standby will not > have the bit set for any subsequent tuple. And on promotion, the > standby will have to have the bits set for all those tuples, unless you > happened to dirty the page again later for other reasons. > > So if you have some table where tuples gain hint bits in bulk, and > rarely modify the pages afterwards, and promote before those pages are > frozen, then you may end up with a massive amount of pages that will > need hinting after the promote, which can become troublesome.
Did the case you observed not use hot standby? I thought the impact of this issue could be somewhat alleviated in hot standby cases since read queries on the hot standby can set hint bits. > > One simple idea to try to forestall this problem would be to modify the > algorithm so that all tuples are scanned and hinted if the page is going > to be dirtied -- then send a single FPI setting bits for all tuples, > instead of just on the first tuple. > This idea seems good to me but I'm concerned a bit that the probability of concurrent processes writing FPI for the same page might get higher since concurrent processes could set hint bits at the same time. If it's true, I wonder if we can advertise hint bits are being updated to prevent concurrent FPI writes for the same page. Regards, -- Masahiko Sawada http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
