Re: [HACKERS] HOT line pointer bloat and PageRepairFragmentation
"Zeugswetter Andreas ADI SD" <[EMAIL PROTECTED]> writes: > ...This looks like a problem, since we might end up with a page filled with > LP_DEAD slots, that all have no visibility info and can thus not be > cleaned by vacuum. No, it's the other way round: an LP_DEAD item pointer can *always* be cleaned by VACUUM. It would not have become LP_DEAD unless someone had confirmed that the pointed-to tuple was no longer visible to anyone. The only reason we have LP_DEAD at all is that we don't want HOT pruning to be required to remove the index entries that link to the item pointer. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] HOT line pointer bloat and PageRepairFragmentation
On 9/13/07, Zeugswetter Andreas ADI SD <[EMAIL PROTECTED]> wrote: > > > > The COLD updated (old) tuple would be pruned to dead line pointer > > once the tuple becomes DEAD. Normally that would let us reuse the > > tuple storage for other purposes. We do the same for DELETEd tuples. > > Oh, I thought only pruned tuples from HOT chains can produce a > "redirect dead" line pointer. > > This looks like a problem, since we might end up with a page filled with > LP_DEAD slots, that all have no visibility info and can thus not be > cleaned > by vacuum. > > It has nothing to do with visibility info. We already know the tuple is DEAD and thats why its line pointer is LP_DEAD. Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com
Re: [HACKERS] HOT line pointer bloat and PageRepairFragmentation
> The COLD updated (old) tuple would be pruned to dead line pointer > once the tuple becomes DEAD. Normally that would let us reuse the > tuple storage for other purposes. We do the same for DELETEd tuples. Oh, I thought only pruned tuples from HOT chains can produce a "redirect dead" line pointer. This looks like a problem, since we might end up with a page filled with LP_DEAD slots, that all have no visibility info and can thus not be cleaned by vacuum. Maybe PageRepairFragmentation when called from HOT should prune less aggressively. e.g. prune until a max of 1/2 the available slots are LP_DEAD, and not prune the rest. Andreas ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] HOT line pointer bloat and PageRepairFragmentation
On 9/13/07, Zeugswetter Andreas ADI SD <[EMAIL PROTECTED]> wrote: > > > > CREATE TABLE test (a int, b char(200)); > > CREATE UNIQUE INDEX testindx ON test(a); > > INSERT INTO test VALUES (1, 'foo'); > > > > Now, if we repeatedly update the tuple so that each update is a > > COLD update, we would bloat the page with redirect-dead line pointers. > > Um, sorry for not understanding, but why would a COLD update produce a > redirect-dead line pointer (and not two LP_NORMAL ones) ? > > The COLD updated (old) tuple would be pruned to dead line pointer once the tuple becomes DEAD. Normally that would let us reuse the tuple storage for other purposes. We do the same for DELETEd tuples. Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com
Re: [HACKERS] HOT line pointer bloat and PageRepairFragmentation
> CREATE TABLE test (a int, b char(200)); > CREATE UNIQUE INDEX testindx ON test(a); > INSERT INTO test VALUES (1, 'foo'); > > Now, if we repeatedly update the tuple so that each update is a > COLD update, we would bloat the page with redirect-dead line pointers. Um, sorry for not understanding, but why would a COLD update produce a redirect-dead line pointer (and not two LP_NORMAL ones) ? Andreas ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] HOT line pointer bloat and PageRepairFragmentation
On 9/13/07, Tom Lane <[EMAIL PROTECTED]> wrote: > > > > The difficulty with this is having to be 100% confident that noplace in > the system tries to dereference a TID without checking that the line > number (offset) is within range. At one time that was demonstrably > not so. I think we've cleaned up most if not all such places, but > I wouldn't want to swear to it. If there are such places, aren't we already in problem ? An unused line pointer can be reused for unrelated tuple. Dereferencing the TID can cause data corruption, isn't it ? If you want, I can do a quick search for all callers of PageGetItemId and confirm that the offset is checked and add any missing checks. In normal circumstances, line pointer bloat should not occur. But in some typical cases it may cause unrepairable damage. For example: CREATE TABLE test (a int, b char(200)); CREATE UNIQUE INDEX testindx ON test(a); INSERT INTO test VALUES (1, 'foo'); Now, if we repeatedly update the tuple so that each update is a COLD update, we would bloat the page with redirect-dead line pointers. Any other idea to recover from this ? Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com
Re: [HACKERS] HOT line pointer bloat and PageRepairFragmentation
"Pavan Deolasee" <[EMAIL PROTECTED]> writes: > How about passing a boolean to PageRepairFragmentation to > command it to reclaim unused line pointers ? The difficulty with this is having to be 100% confident that noplace in the system tries to dereference a TID without checking that the line number (offset) is within range. At one time that was demonstrably not so. I think we've cleaned up most if not all such places, but I wouldn't want to swear to it. I'm not convinced it's worth taking any risk for. regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
