IDS Forum

Re: Smart Blob Space Size

Posted By: GARY ANDRUS
Date: Monday, 10 March 2008, at 8:28 p.m.

In Response To: Re: Smart Blob Space Size (Walter Lowich)

Thanks for the response Walter. Sorry about the original format. I guess that's what I get for sending email through the firewall instead of just posting on the IIUG website.

To clear things up, here's the original message, hopefully formatted in a readable fashion

----------------------------

I'm reviewing some space monitors that were previously set up in our systems for notification when we hit a percentage threshold for different dbspaces. I'm curious about the calculations that were put in for Smart BLOB space usage.

What "space usage" reports should exist for an sbspace and what is the correct formula for each? Do we need separate one for each of the following?

Meta Data Used/Free

User Data Used/Free

Total Data Used/Free

I looked online and I've seen several threads for space usage queries, but didn't find any that clearly answered this item specifically. (If you know of one, just point me there.) I found this thread that uses udsize, but it's quite out of date (Dec. 2000, version 9.21) and I wasn't sure if the SQL was correct.

http://groups.google.com/group/comp.databases.informix/browse_thread/thr
ead/21dc4618526fa087/4ca3dfabf7d283f6?hl=en&lnk=gst&q=udsize#4ca3dfabf7d
283f6

I didn't see anything in the IIUG software repository with the meta data calculations. I also saw no mention of udsize in the Admin Guide or Admin Reference Manual while looking for information in the Informix
library:

http://www-306.ibm.com/software/data/informix/pubs/library/ids_100.html

My environment information:

IDS: 10.00.UC7X1
O/S: SunOS 5.10 Generic_125100-05

Sysmaster Query (output for dbsnum 9, the only sbspace):

SELECT C.dbsnum, name, is_sbspace, SUM(chksize) ChkSize,

SUM(nfree) NFree, SUM(mdsize) MdSize,

SUM(udsize) UdSize, SUM(udfree) UdFree

FROM sysmaster:sysdbspaces d, sysmaster:syschunks c

WHERE d.dbsnum=c.dbsnum

GROUP BY 1, 2, 3

ORDER BY 1

Dbs name is_sbspace chksize nfree mdsize udsize udfree

...

9 sbdefault 1 1050000 50505 141817 908121 318216

...

onstat -d output:

dbs offset size free bpages

...

9 0 500000 49994 441831

Metadata 58116 0 58116

9 500000 250000 99992 233145

Metadata 16852 8 16852

9 500000 250000 168230 233145

Metadata 16852 500 16852

9 750000 50000 0 0

Metadata 49997 49997 49997

...

Onstat -d shows a total size of 1050000, same as chksize above.
Onstat -d shows a total free space of 318216, same as udfree above.
Onstat -d shows a Metadata size of 141817, same as mdsize above.
Onstat -d shows a Metadata free space of 50505, same as nfree above.

There is no onstat -d output that matches the udsize (though subtracting mdsize from chksize leaves a difference of 8 pages - overhead maybe).

When calculating metadata used space for an sbspace, this seems straight forward.

Meta Data Used: (141817 - 50505) / 141817 = 64.39%

Should there be a difference between user space used and total space used?
Which of the following should be used for each calculation?

A. (mdsize-nfree)/mdsize => (141817-50505)/141817 =
64.39%

B. (chksize-udfree)/chksize => (1050000-318216)/1050000=
69.69%

C. (chksize-(udfree+nfree))/chksize => (1050000-368213)/1050000= 64.93%

D. (udsize -udfree)/udsize => (908121-318216)/908121 =
64.96%

E. (udsize -(udfree+nfree))/udsize => (908121-368213)/908121 = 59.45%

I made the assumption that calculation A is accurate for meta data space.
I made the assumption that calculation C is accurate for total space usage.
I made the assumption that calculation D is accurate for user space usage.

Calculation B seems to be looking at the full chunk size, but only subtracting the user data space used.

Calculation E seems to be using the user data size, but subtracting both user and meta data space used.

Are my assumptions correct? If so, splitting sbspaces into "META" and "USER" data (I didn't put in the TOTAL calculation for the sbspaces), and all non sbspaces as "REGULAR" spaces, does this UNION query bring back accurate data for all of the different dbspaces on the server?

select name dbspace,

is_sbspace,

"REG" space_type,

sum(chksize) chunk_size,

sum(nfree) chunk_free,

sum(chksize)-sum(nfree) userdata_used,

(((sum(chksize)-sum(nfree)) / sum(chksize)) * 100)::DECIMAL(5,2) pct_userdata_used
from sysmaster:sysdbspaces d, sysmaster:syschunks c where d.dbsnum=c.dbsnum

AND is_sbspace <> 1
group by 1,2
UNION
select name dbspace,

is_sbspace,

"USR" space_type,

sum(udsize) userdata_size,

sum(udfree) userdata_free,

sum(udsize)-sum(udfree) userdata_used,

(((sum(udsize)-sum(udfree)) / sum(udsize)) * 100)::DECIMAL(5,2) pct_userdata_used
from sysmaster:sysdbspaces d, sysmaster:syschunks c where d.dbsnum=c.dbsnum

and is_sbspace=1
group by 1,2
UNION
select name dbspace,

is_sbspace,

"MET" space_type,

sum(mdsize) metadata_size,

sum(nfree) metadata_free,

sum(mdsize)-sum(nfree) metadata_used,

(((sum(mdsize)-sum(nfree)) / sum(mdsize)) * 100)::DECIMAL(5,2) pct_userdata_used
from sysmaster:sysdbspaces d, sysmaster:syschunks c where d.dbsnum=c.dbsnum

and is_sbspace=1
group by 1,2
order by 1,2;

Any thoughts or suggestions would be appreciated.

Thank you,
Gary Andrus

Messages In This Thread

• Smart Blob Space Size
  Andrus, Gary -- Monday, 10 March 2008, at 3:20 p.m.
  • Re: Smart Blob Space Size
    Walter Lowich -- Monday, 10 March 2008, at 7:24 p.m.
    • Re: Smart Blob Space Size
      GARY ANDRUS -- Monday, 10 March 2008, at 8:28 p.m.
      • Re: Smart Blob Space Size
        EDUARDO OCHOA -- Friday, 14 July 2017, at 5:27 a.m.
        • Re: Smart Blob Space Size
          Art Kagel -- Friday, 14 July 2017, at 7:26 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.