• Re: [Info-ingres] Empty pages

    From Roy Hann@21:1/5 to Martin Bowes on Mon Oct 25 14:08:32 2021
    To: info-ingres@lists.planetingres.org (info-ingres@lists.planetingres.org)

    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

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Martin Bowes@21:1/5 to All on Mon Oct 25 12:48:29 2021
    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

    <html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=us-ascii">
    <meta name="Generator" content="Microsoft Word 15 (filtered medium)"> <style><!--
    /* Font Definitions */
    @font-face
    {font-family:"Cambria Math";
    panose-1:2 4 5 3 5 4 6 3 2 4;}
    @font-face
    {font-family:Calibri;
    panose-1:2 15 5 2 2 2 4 3 2 4;}
    @font-face
    {font-family:"Lucida Console";
    panose-1:2 11 6 9 4 5 4 2 2 4;}
    /* Style Definitions */
    p.MsoNormal, li.MsoNormal, div.MsoNormal
    {margin:0cm;
    margin-bottom:.0001pt;
    font-size:11.0pt;
    font-family:"Calibri",sans-serif;
    mso-fareast-language:EN-US;}
    a:link, span.MsoHyperlink
    {mso-style-priority:99;
    color:#0563C1;
    text-decoration:underline;}
    a:visited, span.MsoHyperlinkFollowed
    {mso-style-priority:99;
    color:#954F72;
    text-decoration:underline;}
    span.EmailStyle17
    {mso-style-type:personal-compose;
    font-family:"Calibri",sans-serif;
    color:windowtext;}
    .MsoChpDefault
    {mso-style-type:export-only;
    font-family:"Calibri",sans-serif;
    mso-fareast-language:EN-US;}
    @page WordSection1
    {size:612.0pt 792.0pt;
    margin:72.0pt 72.0pt 72.0pt 72.0pt;}
    div.WordSection1
    {page:WordSection1;}
    </style><!--[if gte mso 9]><xml>
    <o:shapedefaults v:ext="edit" spidmax="1026" />
    </xml><![endif]--><!--[if gte mso 9]><xml>
    <o:shapelayout v:ext="edit">
    <o:idmap v:ext="edit" data="1" />
    </o:shapelayout></xml><![endif]-->
    </head>
    <body lang="EN-GB" link="#0563C1" vlink="#954F72">
    <div class="WordSection1">
    <p class="MsoNormal">Hi All,<o:p></o:p></p>
    <p class="MsoNormal"><o:p>&nbsp;</o:p></p>
    <p class="MsoNormal">I&#8217;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.<o:p></o:p></p>
    <p class="MsoNormal"><o:p>&nbsp;</o:p></p>
    <p class="MsoNormal">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&nbsp; increasing and so deleted rows on the pages are never reused.<o:p>
    </o:p></p>
    <p class="MsoNormal"><o:p>&nbsp;</o:p></p>
    <p class="MsoNormal">Is there an easy way to determine the number of &#8216;empty&#8217; pages? I&#8217;ve started experimenting with modify table to table_debug [with table_option = 1|2|3], but I&#8217;m not seeing a smoking gun.<o:p></o:p></p>
    <p class="MsoNormal"><o:p>&nbsp;</o:p></p>
    <p class="MsoNormal">Eg.<o:p></o:p></p>
    <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:&quot;Lucida Console&quot;">modify ae_current_state to table_debug with table_option=1;<o:p></o:p></span></p>
    <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:&quot;Lucida Console&quot;"><o:p>&nbsp;</o:p></span></p>
    <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:&quot;Lucida Console&quot;">********************************************************************<o:p></o:p></span></p>
    <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:&quot;Lucida Console&quot;"><o:p>&nbsp;</o:p></span></p>
    <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:&quot;Lucida Console&quot;">FHDR for TABLE: ae_current_state @ Pageno: 11143, Highwater FMAP: 251, FMAP's: 251<o:p></o:p></span></p>
    <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:&quot;Lucida Console&quot;">&nbsp;&nbsp;&nbsp; Allocation: 4, Extend: 16, Number of Extends: 42147<o:p></o:p></span></p>
    <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:&quot;Lucida Console&quot;"><o:p>&nbsp;</o:p></span></p>
    <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:&quot;Lucida Console&quot;">Last disc pageno: 8025539, Last FHDR/FMAP(s) pageno: 8025539<o:p></o:p></span></p>
    <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:&quot;Lucida Console&quot;">&nbsp;&nbsp;&nbsp; Last used pageno 8025534, Pages never used: 5<o:p></o:p></span></p>
    <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:&quot;Lucida Console&quot;"><o:p>&nbsp;</o:p></span></p>
    <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:&quot;Lucida Console&quot;">FMAP[0] @ Pageno: 11144, Base Pageno: 0<o:p></o:p></span></p>
    <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:&quot;Lucida Console&quot;">&nbsp;&nbsp;&nbsp; First free bit: 32096, Highwater bit: 32095<o:p></o:p></span></p>
    <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:&quot;Lucida Console&quot;">&nbsp;&nbsp;&nbsp; Last Free bit: 32095, Free Pages: 0, FHDR Hint: NO<o:p></o:p></span></p>
    <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:&quot;Lucida Console&quot;"><o:p>&nbsp;</o:p></span></p>
    <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:&quot;Lucida Console&quot;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 32127&nbsp;&nbsp;&nbsp; 00000810 00000000 00000000 00000000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
    32000<o:p></o:p></span></p>
    <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:&quot;Lucida Console&quot;"><o:p>&nbsp;</o:p></span></p>
    <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:&quot;Lucida Console&quot;">FMAP[1] @ Pageno: 32084, Base Pageno: 32096<o:p></o:p></span></p>
    <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:&quot;Lucida Console&quot;">&nbsp;&nbsp;&nbsp; First free bit: 32096, Highwater bit: 32095<o:p></o:p></span></p>
    <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:&quot;Lucida Console&quot;">&nbsp;&nbsp;&nbsp; Last Free bit: 32095, Free Pages: 0, FHDR Hint: NO<o:p></o:p></span></p>
    <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:&quot;Lucida Console&quot;"><o:p>&nbsp;</o:p></span></p>
    <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:&quot;Lucida Console&quot;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 64223&nbsp;&nbsp;&nbsp; 00000810 00000000 00000000 00000000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
    64096<o:p></o:p></span></p>
    <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:&quot;Lucida Console&quot;"><o:p>&nbsp;</o:p></span></p>
    <p class="MsoNormal" style="text-autospace:none">Or&#8230;<span style="font-size:10.0pt;font-family:&quot;Lucida Console&quot;">modify ae_current_state to table_debug with table_option=2;<o:p></o:p></span></p>
    <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:&quot;Lucida Console&quot;"><o:p>&nbsp;</o:p></span></p>
    <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:&quot;Lucida Console&quot;">********************************************************************<o:p></o:p></span></p>
    <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:&quot;Lucida Console&quot;"><o:p>&nbsp;</o:p></span></p>
    <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:&quot;Lucida Console&quot;">PAGE type DUMP for TABLE: ae_current_state, Total pages: 8025540<o:p></o:p></span></p>
    <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:&quot;Lucida Console&quot;"><o:p>&nbsp;</o:p></span></p>
    <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:&quot;Lucida Console&quot;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0 rLDDDDDDDD DLDDDDDDDD DDLDDDDDDD DDDDLDDDDD DDDDDDLDDD<o:p></o:p></span><

    <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:&quot;Lucida Console&quot;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 50 DDDDDDDDLD DDDDDDDDDL DDDDDDDDDD LDDDDDDDDD DDLDDDDDDD<o:p></o:p></span></p>
    <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:&quot;Lucida Console&quot;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 100 DDLDDDDDDD DDDLDDDDDD DDDDDLDDDD DDDDDDDLDD DDDDDDDDLD<o:p></o:p></span></p>
    <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:&quot;Lucida Console&quot;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 150 DDDDDDDDDD LDDDDDDDDL DDDDDDDDDL DDDDDDDDDD LDDDDDDDDD<o:p></o:p></span></p>
    <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:&quot;Lucida Console&quot;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 200 DLDDDDDDDD DDLDDDDDDD DDDLDDDDDD DDDDLDDDDD DDDDDDDLDD<o:p></o:p></span></p>
    <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:&quot;Lucida Console&quot;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 250 DDDDDDDDLD DDDDDDDDLD DDDDDDDDLD DDDDDDDDLD DDDDDDDDLD<o:p></o:p></span></p>
    <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:&quot;Lucida Console&quot;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 300 DDDDDDDDLD DDDDDDDDLD DDDDDDDDLD DDDDDDDDDL DDDDDDDDDD<o:p></o:p></span></p>
    <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:&quot;Lucida Console&quot;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 350 LDDDDDDDDD DLDDDDDDDD DLDDDDDDDD DDDLDDDDDD DDDDLDDDDD<o:p></o:p></span></p>
    <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:&quot;Lucida Console&quot;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 400 DDDDDLDDDD DDDDDDDLDD DDDDDDDLDD DDDDDDDDLD DDDDDDDDDL<o:p></o:p></span></p>
    <p class="MsoNormal"><o:p>&nbsp;</o:p></p>
    <p class="MsoNormal">Any ideas?<o:p></o:p></p>
    <p class="MsoNormal"><o:p>&nbsp;</o:p></p>
    <p class="MsoNormal">Martin Bowes<o:p></o:p></p>
    </div>
    </body>
    </html>

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)