Actual Number of Pages In-Use by a Table

Posted in 2015

Summary

Dan asked how to find the number of pages a large table (IDS 11.70 on Linux) is really using, since oncheck -pt "pages used" and systables.npused never decrease when rows are purged and pages freed; his table has hit the 16,777,215-page limit. Replies confirmed the used count is not decremented, and suggested oncheck -T (empty/partial/full page counts), OAT's bitmap bar chart, or querying sysmaster:sysptnbit by partnum — Andrew Ford posted the full bitmap value list from sysmaster.sql (0 free, 4 data with room, 12 full data, 8 index/bitmap, plus remainder and PBLOB codes) so pages can be tallied. Richard Kofler also pointed to IBM's doc on TABLE REPACK SHRINK to reclaim space while fragmentation is implemented.

Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.

Topics: General Discussion

DAN MUELLER — Thu, 12 Feb 2015 19:14:09 — source: IIUG Forums & Mailing Lists

Hey folks, OS = Linux IDS = 11.70.FC8 I have a question that I am a bit unsure about and need a level set. I need to know how to accurately tell hom many pages a tables is actually using. My table has detached indexes and I am not concerned about them. I also have partition blobs. When I look at oncheck -pt I can see Number of pages allocated 16777215 Number of pages used 16777215 Number of data pages 329079 Yes, this table has completely filled up before. Does the Number of pages used decrement when data is purged and pages freed? I know that npused in systables does not. Anyway, i am looking for a way to monitor this until I can fragment it and when it fills up, we are in hot water. Thanx, Dan

↪ replying to DAN MUELLER

Art Kagel — Thu, 12 Feb 2015 20:40:28 — source: IIUG Forums & Mailing Lists

Dan: The oncheck -T report shows the number of empty, semi-full, full, & very full pages. That may be the only way to tell. The number of pages used is not decremented when a page is emptied, no. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Feb 12, 2015 at 2:14 PM, DAN MUELLER <ddmueller@intercall.com> wrote: > Hey folks, > OS = Linux > IDS = 11.70.FC8 > > I have a question that I am a bit unsure about and need a level set. I > need to > know how to accurately tell hom many pages a tables is actually using. My > table has detached indexes and I am not concerned about them. I also have > partition blobs. When I look at oncheck -pt I can see > > Number of pages allocated 16777215 > > Number of pages used 16777215 > > Number of data pages 329079 > > Yes, this table has completely filled up before. Does the Number of pages > used > decrement when data is purged and pages freed? I know that npused in > systables > does not. > > Anyway, i am looking for a way to monitor this until I can fragment it and > when it fills up, we are in hot water. > > Thanx, > Dan > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11c3486472197d050eea2005

↪ replying to DAN MUELLER

Andrew Ford — Thu, 12 Feb 2015 21:04:13 — source: IIUG Forums & Mailing Lists

One way would be to look at sysmaster.sysptnbit by partnum for bitmap values of 4 or 12 (used or full data page bitmap value). Andrew -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of DAN MUELLER Sent: Thursday, February 12, 2015 1:14 PM To: ids@iiug.org Subject: Actual Number of Pages In-Use by a Table [34648] Hey folks, OS = Linux IDS = 11.70.FC8 I have a question that I am a bit unsure about and need a level set. I need to know how to accurately tell hom many pages a tables is actually using. My table has detached indexes and I am not concerned about them. I also have partition blobs. When I look at oncheck -pt I can see Number of pages allocated 16777215 Number of pages used 16777215 Number of data pages 329079 Yes, this table has completely filled up before. Does the Number of pages used decrement when data is purged and pages freed? I know that npused in systables does not. Anyway, i am looking for a way to monitor this until I can fragment it and when it fills up, we are in hot water. Thanx, Dan **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.

↪ replying to DAN MUELLER

RICHARD KOFLER — Fri, 13 Feb 2015 07:17:45 — source: IIUG Forums & Mailing Lists

Hi Dan, maybe this document can help http://www-01.ibm.com/support/docview.wss?uid=swg21330735 Start reading after V11.50 - all about "table repack shrink". AFAIK, if you do not use COMPRESS then you'll need no additional licensing. [ Warning! I used 'repack shrink' only for smaller tables/fragments, like having 500K rows. So maybe it is too disruptive on huge tables. ] If you have variable lenght records in this table, check setting of MAX_FILL_DATA_PAGES in your $ONCONFIG and set it to 1 before repacking. Hope this buys you some time to implement fragmentation. dic_k

↪ replying to Andrew Ford

MARK SCRANTON — Sat, 14 Feb 2015 03:28:07 — source: IIUG Forums & Mailing Lists

To add to Andrew's comment ... looking at the bitmap values can be very telling. A "4" is partially full, and has room for a max size row. A "12", or "C" if you dump the bitmap page, means "full", and has no room for a max size row even though there are most likely free bytes on the page. A bitmap value of "8" means that page is either a bitmap page or an index page. A "0" means unused or free. These values are way back from 7 or earlier days. Anyone know if any new values have been added since way back then? Mark Scranton The Mark Scranton Group www.markscranton.com "All Informix ... all the time." mark@markscranton.com

↪ replying to MARK SCRANTON

John Miller — Sat, 14 Feb 2015 08:06:50 — source: IIUG Forums & Mailing Lists

Values have not changed. OAT will graphically display the bitmap pages in a nice bar chart showing you each of the 4 classifications of pages and how many there are of each class. John F. Miller III STSM, Lead Architect miller3@us.ibm.com 503-747-1366 IBM Informix Dynamic Server (IDS) ids-bounces@iiug.org wrote on 02/13/2015 07:28:08 PM: > From: "MARK SCRANTON" <mark@markscranton.com> > To: ids@iiug.org > Date: 02/13/2015 07:28 PM > Subject: Re: RE: Actual Number of Pages In-Use by a Table [34670] > Sent by: ids-bounces@iiug.org > > To add to Andrew's comment ... looking at the bitmap values can be very > telling. A "4" is partially full, and has room for a max size row. A"12", or > "C" if you dump the bitmap page, means "full", and has no room for amax size > row even though there are most likely free bytes on the page. A bitmap value > of "8" means that page is either a bitmap page or an index page. A "0" means > unused or free. These values are way back from 7 or earlier days. Anyone know > if any new values have been added since way back then? > > Mark Scranton > The Mark Scranton Group > www.markscranton.com > "All Informix ... all the time." > mark@markscranton.com > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

↪ replying to MARK SCRANTON

Fernando Nunes — Sat, 14 Feb 2015 14:58:59 — source: IIUG Forums & Mailing Lists

I don't think the bitmap values were extended, specialy because that would require more bits. AFAIK all the possibilities provided by 4 bits are already used. I would like to see this expanded, specifically to define if a page needs to be backed up in an L1 or L2 backup.... that would make these backups much more faster.... or eventually have a different "bitmap" zone for that.... Regards Em 14/02/2015 03:28, "MARK SCRANTON" <mark@markscranton.com> escreveu: > To add to Andrew's comment ... looking at the bitmap values can be very > telling. A "4" is partially full, and has room for a max size row. A "12", > or > "C" if you dump the bitmap page, means "full", and has no room for a max > size > row even though there are most likely free bytes on the page. A bitmap > value > of "8" means that page is either a bitmap page or an index page. A "0" > means > unused or free. These values are way back from 7 or earlier days. Anyone > know > if any new values have been added since way back then? > > Mark Scranton > The Mark Scranton Group > www.markscranton.com > "All Informix ... all the time." > mark@markscranton.com > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a113d2c2017b718050f0d97e2

↪ replying to Andrew Ford

Mueller, Daniel D. — Thu, 19 Feb 2015 00:18:25 — source: IIUG Forums & Mailing Lists

Andrew, Following that logic, wouldn't anything besides a 0 be a used page? Thanx, Dan -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Andrew Ford Sent: Thursday, February 12, 2015 4:04 PM To: ids@iiug.org Subject: RE: Actual Number of Pages In-Use by a Table [34650] One way would be to look at sysmaster.sysptnbit by partnum for bitmap values of 4 or 12 (used or full data page bitmap value). Andrew -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of DAN MUELLER Sent: Thursday, February 12, 2015 1:14 PM To: ids@iiug.org Subject: Actual Number of Pages In-Use by a Table [34648] Hey folks, OS = Linux IDS = 11.70.FC8 I have a question that I am a bit unsure about and need a level set. I need to know how to accurately tell hom many pages a tables is actually using. My table has detached indexes and I am not concerned about them. I also have partition blobs. When I look at oncheck -pt I can see Number of pages allocated 16777215 Number of pages used 16777215 Number of data pages 329079 Yes, this table has completely filled up before. Does the Number of pages used decrement when data is purged and pages freed? I know that npused in systables does not. Anyway, i am looking for a way to monitor this until I can fragment it and when it fills up, we are in hot water. Thanx, Dan **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

↪ replying to Mueller, Daniel D.

Andrew Ford — Thu, 19 Feb 2015 15:18:51 — source: IIUG Forums & Mailing Lists

I guess I thought you only cared about non empty data pages which would have a bitmap value of 4 or 12 and I assumed your row size was small enough to not require remainder pages or had any BLOBs. Here is the full list of bitmap values and what they mean from $INFORMIXDIR/etc/sysmaster.sql that you can use to tally up how many pages your table is actually using. { Bitmap } insert into flags_text values ('sysptnbit',0,'Free Page'); insert into flags_text values ('sysptnbit',1,'Remainder Page - freeSpace = Pagesize'); insert into flags_text values ('sysptnbit',2,'PBLOB Page - free Space =Pagesize'); insert into flags_text values ('sysptnbit',4,'Data Page with Room foranother Row'); insert into flags_text values ('sysptnbit',5,'Remainder Page - freeSpace between Pagesize and 2/3*Pagesize'); insert into flags_text values ('sysptnbit',6,'PBLOB Page - free Space between Pagesize and 2/3*Pagesize'); insert into flags_text values ('sysptnbit',8,'Index Page or BitmapPage'); insert into flags_text values ('sysptnbit',9,'Remainder Page - freeSpace between 2/3*Pagesize and 1/10*Pagesize'); insert into flags_text values ('sysptnbit',10,'PBLOB Page - free Space between 2/3*Pagesize and 1/10*Pagesize'); insert into flags_text values ('sysptnbit',12,'Data Page without Roomfor another Row'); insert into flags_text values ('sysptnbit',13,'Remainder Page full -free Space < 1/10*Pagesize'); insert into flags_text values ('sysptnbit',14,'PBLOB Page full - freeSpace < 1/10*Pagesize'); Andrew -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Mueller, Daniel D. Sent: Wednesday, February 18, 2015 6:18 PM To: ids@iiug.org Subject: RE: Actual Number of Pages In-Use by a Table [34700] Andrew, Following that logic, wouldn't anything besides a 0 be a used page? Thanx, Dan -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Andrew Ford Sent: Thursday, February 12, 2015 4:04 PM To: ids@iiug.org Subject: RE: Actual Number of Pages In-Use by a Table [34650] One way would be to look at sysmaster.sysptnbit by partnum for bitmap values of 4 or 12 (used or full data page bitmap value). Andrew -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of DAN MUELLER Sent: Thursday, February 12, 2015 1:14 PM To: ids@iiug.org Subject: Actual Number of Pages In-Use by a Table [34648] Hey folks, OS = Linux IDS = 11.70.FC8 I have a question that I am a bit unsure about and need a level set. I need to know how to accurately tell hom many pages a tables is actually using. My table has detached indexes and I am not concerned about them. I also have partition blobs. When I look at oncheck -pt I can see Number of pages allocated 16777215 Number of pages used 16777215 Number of data pages 329079 Yes, this table has completely filled up before. Does the Number of pages used decrement when data is purged and pages freed? I know that npused in systables does not. Anyway, i am looking for a way to monitor this until I can fragment it and when it fills up, we are in hot water. Thanx, Dan **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum. **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum. **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.