Extents Monitoring

Posted in 2009

Summary

A production table raised "no more pages" alarms even though the frcnt-based formula suggested ~137 extents were still available on the partition page. Respondents said the formula wasn't the issue: the oncheck -pt output showed neither the table nor index fragment was out of extent slots, so the error meant no physical space was left — either the dbspace/blobspace needs more chunks, or the fragment had hit the ~16 million page limit, requiring fragmenting the table or moving it to a larger-page dbspace. The thread ends with questions about free dbspace, with no confirmed fix recorded.

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

Topics: Storage & Space Management

SHAHZAD SALAM KASI — Tue, 27 Oct 2009 12:16:00 — source: IIUG Forums & Mailing Lists

Hi, We have a critical situation in our production db as far as remaining extents for a table is concerned. The error message shows that there are no more extents remaining in a table. Problem Statement: ------------------- According to the above mentioned formula, it means that there can be 137 more extents but it is showing the below error. ---------------------------------------------------------------------- 04:35:59 partition 'mydb:test_user.test-table': no more pages 04:35:59 Process exited with return code 1: /bin/sh /bin/sh -c /u/informix11.5-3/etc/alarmprogram.sh 3 46 "partition 'mydb:test-table': no more pages" "" "" How TO CHECK the extents ========================= oncheck -pt mydb:test-table|more Get the following 1. Physical Address 7:16203 (chunk#:page#) 2. Number of extents 90 covert the physical address INTO HEX ------------------------------------ oncheck -pP 0x7 0x3F4B [informix]$ oncheck -pP 0x7 0x3F4B |more addr stamp chksum nslots flag type frptr frcnt next prev 7:16203 1545026007 5a8c 5 802 PARTN 924 1100 0 10 slot ptr len flg 1 24 104 0 2 128 44 0 3 172 24 0 4 196 0 0 5 196 728 0 --To find the remaining extents ================================ Additional_extents = trunc (frcnt / 8) = trunc (1100 /8 ) = 137 --To find the total extents ============================ Maximum_number_extents = Additional_extents + Number_of_extents = 137 + 90 = 227 So what's the issue here ? How can we identify the remaining extents in a table ? Is there any other (correct) method to identify the remaining, consumed and total number of extents for a particular table ? Regards Kasi

↪ replying to SHAHZAD SALAM KASI

MIKE MAGIE — Tue, 27 Oct 2009 14:59:00 — source: IIUG Forums & Mailing Lists

What version of Informix are you using? Are their detached indexes on the table in question? If you could - post the oncheck -pt output. MM

↪ replying to MIKE MAGIE

KAMRAN HAQ — Tue, 27 Oct 2009 17:31:45 — source: IIUG Forums & Mailing Lists

Hi, I am sending the information on behalf of Kasi. IDS version: 11.50.FC3W1 OS information: SunOS 5.10 Generic_125100-09 sun4v sparc SUNW,Sun-Fire-T200 oncheck -pt output: ----------------------------------------------------------------------TBLspace Report for cards_1003:mcp.file_store_binary Physical Address 54:10 Creation date 10/27/2009 04:32:14 TBLspace Flags d02 Row Locking TBLspace contains VARCHARS TBLspace contains TBLspace BLOBs TBLspace use 4 bit bit-maps Maximum row size 199 Number of special columns 3 Number of keys 0 Number of extents 2 Current serial value 477610 Current SERIAL8 value 1 Current BIGSERIAL value 1 Current REFID value 1 Pagesize (k) 4 First extent size 512000 Next extent size 512000 Number of pages allocated 1536000 Number of pages used 1340388 Number of data pages 3988 Number of rows 127607 Partition partnum 7340034 Partition lockid 7340034 Extents Logical Page Physical Page Size Physical Pages 0 54:106 512000 1024000 512000 54:1091000 1024000 2048000 Index 1562_24862 fragment partition filestoredbs_mcp03 in DBspace filestoredbs_mcp03 Physical Address 54:12 Creation date 10/27/2009 04:32:15 TBLspace Flags 802 Row Locking TBLspace use 4 bit bit-maps Maximum row size 199 Number of special columns 0 Number of keys 1 Number of extents 1 Current serial value 1 Current SERIAL8 value 1 Current BIGSERIAL value 1 Current REFID value 1 Pagesize (k) 4 First extent size 33447 Next extent size 33447 Number of pages allocated 33447 Number of pages used 1223 Number of data pages 0 Number of rows 0 Partition partnum 7340035 Partition lockid 7340034 Extents Logical Page Physical Page Size Physical Pages 0 54:1024106 33447 66894 ---------------------------------------------------------------

↪ replying to KAMRAN HAQ

MIKE MAGIE — Tue, 27 Oct 2009 19:19:54 — source: IIUG Forums & Mailing Lists

I looked at the error again - it is not the error you receive when the partition page is full and can not track another extent. The error you got indicates that there is no physical room to create more pages for the table. Add chunks to the dbspace where the table resides and/or the blobspace that stores the blobs. MM

↪ replying to SHAHZAD SALAM KASI

Art Kagel — Tue, 27 Oct 2009 20:55:58 — source: IIUG Forums & Mailing Lists

How many pages are in the existing extents? A single fragment of a table cannot have more than 16million pages (6^24 actually). You may either have to move the table to a dbspace with larger pages or fragment it across two or more fragments. Art Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. 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 Tue, Oct 27, 2009 at 8:16 AM, SHAHZAD SALAM KASI <skasi@i2cinc.com>wrote: > Hi, > We have a critical situation in our production db as far as remaining > extents > for a table is concerned. The error message shows that there are no more > extents remaining in a table. > > Problem Statement: > ------------------- > According to the above mentioned formula, it means that there can be 137 > more > extents but it is showing the below error. > > ---------------------------------------------------------------------- > 04:35:59 partition 'mydb:test_user.test-table': no more pages > 04:35:59 Process exited with return code 1: /bin/sh /bin/sh -c > /u/informix11.5-3/etc/alarmprogram.sh 3 46 "partition 'mydb:test-table': no > more pages" "" "" > > How TO CHECK the extents > ========================= > > oncheck -pt mydb:test-table|more> > Get the following > 1. Physical Address 7:16203 (chunk#:page#) > 2. Number of extents 90 > > covert the physical address INTO HEX > ------------------------------------ > oncheck -pP 0x7 0x3F4B> > [informix]$ oncheck -pP 0x7 0x3F4B |more > addr stamp chksum nslots flag type frptr frcnt next prev > 7:16203 1545026007 5a8c 5 802 PARTN 924 1100 0 10 > > slot ptr len flg > > 1 24 104 0 > > 2 128 44 0 > > 3 172 24 0 > > 4 196 0 0 > > 5 196 728 0 > > --To find the remaining extents > ================================ > Additional_extents = trunc (frcnt / 8) > > = trunc (1100 /8 ) > > = 137 > > --To find the total extents > ============================ > Maximum_number_extents = Additional_extents + Number_of_extents > > = 137 + 90 > > = 227 > > So what's the issue here ? How can we identify the remaining extents in a > table ? Is there any other (correct) method to identify the remaining, > consumed and total number of extents for a particular table ? > > Regards > Kasi > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --00235453093874643a0476f0e7f4

↪ replying to Art Kagel

SHAHZAD SALAM KASI — Wed, 28 Oct 2009 06:09:07 — source: IIUG Forums & Mailing Lists

Hi, Can we increase the number of pages for a single fragment ? Or is it the only way to survive this type of scenario by having our table in multiple fragments ! Regards Kasi

↪ replying to SHAHZAD SALAM KASI

Art Kagel — Wed, 28 Oct 2009 07:14:27 — source: IIUG Forums & Mailing Lists

What version are you running? Multiple fragments or wider pages. Art Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. 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 Wed, Oct 28, 2009 at 2:09 AM, SHAHZAD SALAM KASI <skasi@i2cinc.com>wrote: > Hi, > Can we increase the number of pages for a single fragment ? > Or is it the only way to survive this type of scenario by having our table > in > multiple fragments ! > > Regards > Kasi > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001517473434331c8a0476f98b92

↪ replying to Art Kagel

SHAHZAD SALAM KASI — Wed, 28 Oct 2009 07:17:53 — source: IIUG Forums & Mailing Lists

Hi, We are using "IDS 11.50.FC3W1". The table configuration is having a single fragment (defauit). Regards Kasi

↪ replying to KAMRAN HAQ

MIKE MAGIE — Wed, 28 Oct 2009 14:38:05 — source: IIUG Forums & Mailing Lists

Based on the oncheck info that you sent it is clear that you are not running out of extents for either the table fragment or the index fragment. How much space is available in the dbspace where the table and index resides?