[Info-ingres] Empty pages

[Martin Bowes]

Hi All,

I've recently had a btree table which had grown to 8025535 pages with zero overflow. But after a modify it shrank to a little over 5000 pages.

My suspicion is that this table had grown to this size because it is daily getting a few hundred thousand rows inserted and then deleted. The key of the table is  increasing and so deleted rows on the pages are never reused.

Is there an easy way to determine the number of 'empty' pages? I've started experimenting with modify table to table_debug [with table_option = 1|2|3], but I'm not seeing a smoking gun.

Eg.
modify ae_current_state to table_debug with table_option=1;

********************************************************************

FHDR for TABLE: ae_current_state @ Pageno: 11143, Highwater FMAP: 251, FMAP's: 251
    Allocation: 4, Extend: 16, Number of Extends: 42147

Last disc pageno: 8025539, Last FHDR/FMAP(s) pageno: 8025539
    Last used pageno 8025534, Pages never used: 5

FMAP[0] @ Pageno: 11144, Base Pageno: 0
    First free bit: 32096, Highwater bit: 32095
    Last Free bit: 32095, Free Pages: 0, FHDR Hint: NO

       32127    00000810 00000000 00000000 00000000       32000

FMAP[1] @ Pageno: 32084, Base Pageno: 32096
    First free bit: 32096, Highwater bit: 32095
    Last Free bit: 32095, Free Pages: 0, FHDR Hint: NO

       64223    00000810 00000000 00000000 00000000       64096

Or...modify ae_current_state to table_debug with table_option=2;

********************************************************************

PAGE type DUMP for TABLE: ae_current_state, Total pages: 8025540

           0 rLDDDDDDDD DLDDDDDDDD DDLDDDDDDD DDDDLDDDDD DDDDDDLDDD
          50 DDDDDDDDLD DDDDDDDDDL DDDDDDDDDD LDDDDDDDDD DDLDDDDDDD
         100 DDLDDDDDDD DDDLDDDDDD DDDDDLDDDD DDDDDDDLDD DDDDDDDDLD
         150 DDDDDDDDDD LDDDDDDDDL DDDDDDDDDL DDDDDDDDDD LDDDDDDDDD
         200 DLDDDDDDDD DDLDDDDDDD DDDLDDDDDD DDDDLDDDDD DDDDDDDLDD
         250 DDDDDDDDLD DDDDDDDDLD DDDDDDDDLD DDDDDDDDLD DDDDDDDDLD
         300 DDDDDDDDLD DDDDDDDDLD DDDDDDDDLD DDDDDDDDDL DDDDDDDDDD
         350 LDDDDDDDDD DLDDDDDDDD DLDDDDDDDD DDDLDDDDDD DDDDLDDDDD
         400 DDDDDLDDDD DDDDDDDLDD DDDDDDDLDD DDDDDDDDLD DDDDDDDDDL

Any ideas?

Martin Bowes
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.planetingres.org/pipermail/info-ingres/attachments/20211025/406a9c26/attachment.html>