[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

[Roy Hann]

Monday, October 25, 2021, 1:48:29 PM, you wrote:

> 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.

Deleted rows on the associated data page should get reused. My
suspicion is MVCC is enabled on the database. That defeats the free
page management in Btrees.

> 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.

I have written various scripts over the years to join iifile_info and
iitables with the output from ls or dir, as appropriate.

Roy