How to estimate the free size of smart blob dbspaces?

Dear community members, In his presentation on the sysmaster database ( https://advancedatatools.com/webcasts/managing-and-optimizing-the-ibm-informix-server-using-the-sysmaster-database/ ), Lester Knutsen has published an SQL query (dbspace_free.sql) that determines the free space in the created dbspaces. For the sblob dbspaces, it shows me that only about 5 percent of the space are still free. dbspace pages_size pages_used pages_free percent_free sblobdbs 73400320 69724227 3676093 5.01 sbtempdb 2621440 2490134 131306 5.01 However, when I look at the output from onstat -d, I see significantly different occupancy rates. Chunks address chunk/dbs offset size free bpages flags pathname . . . 597b9028 10 10 0 2621440 2444930 2444930 POSB-- /dev/informix/sbtempdbs Metadata 176457 131306 176457 597d8028 20 20 0 73400320 48810097 68460115 POSB-- /dev/informix/sblobdbs Metadata 4940152 3676093 4940152 This gives about 66.5 % space free for sblobdbs and more than 93 % for sbtempdbs. Which of the two outputs is correct? Can the SQL query be modified to return the same values as the onstat command? Kind regards ------------------------------ Roland Wintgen DBA, Genero/4GL Developer EVG Martens GmbH & Co. KG Moenchengladbach ------------------------------

Roland: Go to my web site and download the utils2_ak package ( My Utilities ). Build the package and check out the dbsavail utility which handles the complexities of the syschunks table and its encoding. Witness the line for sbspace2: $ dbsavail Sort by: Free KB.Dbspace Number 2K Pages 2K Pages Free Total KB Free KB ------------------ --------------- ------------- ------------ ------------ extslob1 500 347 1000 694 SBsp(PgSz: 2K) adisbls 6144 4096 12288 8192 Blob(BlbPg: 4096K) adisdbs 10120 4672 20240 9344 (PgSz: 2K) llogspace 484442 9389 968884 18778 (PgSz: 2K) sbspace2 50000 46428 100000 92856 SBsp(PgSz: 2K) tmp_sbspace 50000 46428 100000 92856 TSBs(PgSz: 2K) batdbs 50000 48650 100000 97300 (PgSz: 2K) datadbs_2 2042708 85686 4085416 171372 (PgSz: 4K) tempdbs3 100000 99939 200000 199878 Temp(PgSz: 2K) tempdbs2 100000 99947 200000 199894 Temp(PgSz: 2K) tempdbs 100000 99947 200000 199894 Temp(PgSz: 2K) rootdbs 150000 120292 300000 240584 (PgSz: 2K) datadbs_1 14437206 311340 28874412 622680 (PgSz: 2K) sbspace 500000 464862 1000000 929724 SBsp(PgSz: 2K) plogspace 1560187 511558 3120374 1023116 (PgSz: 2K) hist2dbs 1048576 1015755 2097152 2031510 (PgSz: 2K) hist1dbs 1048576 1015755 2097152 2031510 (PgSz: 2K) hist4dbs 1048576 1019851 2097152 2039702 (PgSz: 2K) hist3dbs 1048576 1019851 2097152 2039702 (PgSz: 2K) hist0dbs 1048576 1022077 2097152 2044154 (PgSz: 2K) hist5dbs 1048576 1023947 2097152 2047894 (PgSz: 2K) datadbs_32k 1048576 1047728 2097152 2095456 (PgSz: 32K) acctdbs 9437184 9340831 18874368 18681662 (PgSz: 2K) ------------------ --------------- ------------- ------------ ------------ Totals: 36418523 18459376 72837046 36918752 And here from onstat -d: address number flags fchunk nchunks pgsize flags owner name ... 474fdcb0 8 0x40008021 8 1 2048 N sBA informix sbspace2 ... address chunk/dbs offset size free bpages flags pathname ... 47509030 8 8 0 50000 46428 46428 POsB-D /opt/chks/sbspace2.chk.001 Metadata 3519 2523 3519 For an sbspace you have to use the udfree column instead of the free column. The free value is the number of metadata free pages, not total free pages. If you need to process these numbers for your own reporting, you can use one of the two stored procedures that dbsavail installs for you in sysmaster: dbsavail() and dbsavail_free(). The latter does avoids updating the dumb blobspace free pages - which is the default behavior for onstat -d if you do not provide the "update" option. ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

Thanks Art, I had downloaded the file earlier, but wasn't aware that it already contained a tool for calculating the size of all dbspaces However, the output from dbsavail shows the same values as Lester's SQL query. Dbspace Number 2K Pages 2K Pages Free Total KB Pct Free ------------------ --------------- ------------- ------------ ------------ sbtempdbs 2621440 2444930 5242880 5.01 TSBs(PgSz: 2K) sblobdbs 73400320 48809371 146800640 5.01 SBsp(PgSz: 2K) Does this mean that I can't rely on the values output by onstat -d?. Kind regards ------------------------------ Roland Wintgen DBA, Genero/4GL Developer EVG Martens GmbH & Co. KG Moenchengladbach ------------------------------

The sblob space is split into user size and metadata size, which does make things complicated. You have the size of the space, and then how much is assigned to the blob pages and how much is assigned to the metadata, then you have how much is free in each of those partitions. Here is an extract of onstat -d showing that I have 2 chunks for "sbspace": Dbspaces address number flags fchunk nchunks pgsize flags owner name 47a26700 6 0x4048001 12 2 2048 N SBA informix sbspace Chunks address chunk/dbs offset size free bpages flags pathname 47a36028 12 6 0 5000 2205 4587 POSB-- /cooked_space/sbspace Metadata 360 0 360 47a53028 42 6 0 500000 414447 466319 POSB-- /cooked_space/sbspace2 Metadata 33678 24660 33678 Here is the sysmaster query that I use to determine size and free space: select d.dbsnum dbsnum, d.name dbspace, d.pagesize, sum(chksize) Pages_size, -- sum of all chunk size pages round(sum(chksize) - sum(udfree) - sum(nfree)) pages_used, round(sum(udfree)) Pages_free, -- sum of all chunks free pages round ((sum(udfree)) / (sum(udsize)) * 100, 2) percent_free, sum(mdsize) metadata_size, sum(mdsize) - sum(nfree) metadata_used, sum(nfree) metadata_free, round((sum(nfree)/sum(mdsize))*100,2) percent_metadata_free, sum(udsize) blobpages from sysdbspaces d, syschunks c where d.dbsnum = c.dbsnum and is_sbchunk=1 group by 1,2,3 It shows the following which matches the pages shown across the 2 chunks in onstat -d: dbsnum 6 dbspace sbspace pagesize 2048 pages_size 505000 pages_used 63688 pages_free 416652 percent_free 88.48 metadata_size 34038 metadata_used 9378 metadata_free 24660 percent_metadata_+ 72.45 blobpages 470906 Formatted with actual sizes: DBSpaceName PgSz Size(pg) Size(MB) Used(MB) Free(MB) UserSz(MB) MSz(MB) MFree% Free% ------------------- ---- -------- ----------- ----------- ----------- ------------ ------- ------ ------ sbspace 2K 505000 986.33 124.39 861.94 919.74 66.48 72.45 88.48 ------------------------------ Mike Walker xDB Systems, Inc www.xdbsystems.com ------------------------------

Roland: Huh, the numbers are correct, but the percent free should be around 66% roughly, as you noted initially, not 5.01%. I'll dig in and see what is wrong with that calculation or if the obvious calculation is the wrong one. I had not looked at the PCT free calc originally when I looked the code over before responding. 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.

Thanks Mike, that's what I was looking for. Kind regards ------------------------------ Roland Wintgen DBA, Genero/4GL Developer EVG Martens GmbH & Co. KG Moenchengladbach ------------------------------

onstat -d simply reflects the system's own numbers, so it can be expected to be as correct as those number are (typically they are very much).Those 5pct seem to be matching the free Metadata area against the chunk's total size: 3676093 / 73400320 =~ 0.0501 This is the result of applying a regular chunk's logic to an sbspace chunk, so it ignores the fact that also Mike mentions: sbspace chunks fall into two (actually three, or even four) areas, with two separate "free space" systems - Metadata using the chunk's general free space management while the User Data area has its own free space accounting. One could argue that there is no single "percent free" for the typical sbspace chunk, there usually would be two such numbers, one for Metadata, one for User Data ("blob pages"). And, dependent on your sblobs' sizes, either available Metadata or available User Data might be limiting. ------------------------------ Andreas Legner Informix Dev HCL Software ------------------------------

Roland: OK, I have a handle on what dbsavail is getting wrong with the pct calculation for sbspaces and the free pages may be wrong as well. These are the numbers from my server for sbspace2: Total pages: 50000 Metadata pages: 3519 Smart blob pages: 46428 Free metadata pgs: 2523 Free sblob pages: 46428 Reported by dbsavail: Free pct: 2523/50000 = 0.05046 = 5.05% Now, here is the question(s): Do I report the free pages as 46428 or as (46428+2523) and so including metadata pages? That yields free pages of 48951 and free pct: (46428+2523)/50000 = 97.90% free. This probably makes the most sense. Other options: Only report on the smart blob pages. In my case that means that the pct free is: 46428/46428*100 = 100% free or 46428/ 5 0000 *100 = 92.86 % free and this last, for Roland, would likely match what he calculates from the onstat -d output. Not sure that's a meaningful number though as it doesn't take into consideration the meta data. I am partial to the first one. Any thoughts? Roland, if you send me your email address I can send you an updated dbsavail to test on your system. My email address is no secret: art.kagel@gmail.com . 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.