Forum: >>> Magnum BBS <<<

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>
</head>
<body lang="EN-GB" link="#0563C1" vlink="#954F72">
<div class="WordSection1">
Hi All,<o:p></o:p>
<o:p> </o:p>
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.<o:p></o:p>
<o:p> </o:p>
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.<o:p>
</o:p>
<o:p> </o:p>
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.<o:p></o:p>
<o:p> </o:p>
Eg.<o:p></o:p>
modify ae_current_state to table_debug with table_option=1;<o:p></o:p>
<o:p> </o:p>
********************************************************************<o:p></o:p>
<o:p> </o:p>
FHDR for TABLE: ae_current_state @ Pageno: 11143, Highwater FMAP: 251, FMAP's: 251<o:p></o:p>
    Allocation: 4, Extend: 16, Number of Extends: 42147<o:p></o:p>
<o:p> </o:p>
Last disc pageno: 8025539, Last FHDR/FMAP(s) pageno: 8025539<o:p></o:p>
    Last used pageno 8025534, Pages never used: 5<o:p></o:p>
<o:p> </o:p>
FMAP[0] @ Pageno: 11144, Base Pageno: 0<o:p></o:p>
    First free bit: 32096, Highwater bit: 32095<o:p></o:p>
    Last Free bit: 32095, Free Pages: 0, FHDR Hint: NO<o:p></o:p>
<o:p> </o:p>
       32127    00000810 00000000 00000000 00000000      
32000<o:p></o:p>
<o:p> </o:p>
FMAP[1] @ Pageno: 32084, Base Pageno: 32096<o:p></o:p>
    First free bit: 32096, Highwater bit: 32095<o:p></o:p>
    Last Free bit: 32095, Free Pages: 0, FHDR Hint: NO<o:p></o:p>
<o:p> </o:p>
       64223    00000810 00000000 00000000 00000000      
64096<o:p></o:p>
<o:p> </o:p>
Or…modify ae_current_state to table_debug with table_option=2;<o:p></o:p>
<o:p> </o:p>
********************************************************************<o:p></o:p>
<o:p> </o:p>
PAGE type DUMP for TABLE: ae_current_state, Total pages: 8025540<o:p></o:p>
<o:p> </o:p>
           0 rLDDDDDDDD DLDDDDDDDD DDLDDDDDDD DDDDLDDDDD DDDDDDLDDD<o:p></o:p><

          50 DDDDDDDDLD DDDDDDDDDL DDDDDDDDDD LDDDDDDDDD DDLDDDDDDD<o:p></o:p>
         100 DDLDDDDDDD DDDLDDDDDD DDDDDLDDDD DDDDDDDLDD DDDDDDDDLD<o:p></o:p>
         150 DDDDDDDDDD LDDDDDDDDL DDDDDDDDDL DDDDDDDDDD LDDDDDDDDD<o:p></o:p>
         200 DLDDDDDDDD DDLDDDDDDD DDDLDDDDDD DDDDLDDDDD DDDDDDDLDD<o:p></o:p>
         250 DDDDDDDDLD DDDDDDDDLD DDDDDDDDLD DDDDDDDDLD DDDDDDDDLD<o:p></o:p>
         300 DDDDDDDDLD DDDDDDDDLD DDDDDDDDLD DDDDDDDDDL DDDDDDDDDD<o:p></o:p>
         350 LDDDDDDDDD DLDDDDDDDD DLDDDDDDDD DDDLDDDDDD DDDDLDDDDD<o:p></o:p>
         400 DDDDDLDDDD DDDDDDDLDD DDDDDDDLDD DDDDDDDDLD DDDDDDDDDL<o:p></o:p>
<o:p> </o:p>
Any ideas?<o:p></o:p>
<o:p> </o:p>
Martin Bowes<o:p></o:p>
</div>
</body>
</html>

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

Who's Online
Recent Visitors
- Bob Worm
  Thu Apr 25 22:17:10 2024
  from Wales, Uk via Telnet
- Bob Worm
  Fri Apr 26 10:09:36 2024
  from Wales, Uk via Telnet
- Bob Worm
  Fri Apr 26 08:24:20 2024
  from Wales, Uk via Telnet
- Bob Worm
  Fri Apr 26 06:40:30 2024
  from Wales, Uk via Telnet

System Info

Sysop:	Keyop
Location:	Huddersfield, West Yorkshire, UK
Users:	296
Nodes:	16 (2 / 14)
Uptime:	72:42:40
Calls:	6,657
Calls today:	3
Files:	12,203
Messages:	5,332,378
Posted today:	1

Re: [Info-ingres] Empty pages

Who's Online

Recent Visitors

System Info