IDS Forum

Smart Blob Space Size

Posted By: Andrus, Gary
Date: Monday, 10 March 2008, at 3:20 p.m.

I'm reviewing some space monitors that were previously set up in our=0D=0As=
ystems for notification when we hit a percentage threshold for=0D=0Adiffere=
nt dbspaces=2E I'm curious about the calculations that were put in=0D=0Afo=
r Smart BLOB space usage=2E=0D=0A=0D=0AWhat "space usage" reports should ex=
ist for an sbspace and what is the=0D=0Acorrect formula for each? Do we ne=
ed separate one for each of the=0D=0Afollowing?=0D=0A=0D=0A Meta Data Us=
ed/Free=0D=0A User Data Used/Free=0D=0A Total Data Used/Free=0D=0A=0D=
=0AI looked online and I've seen several threads for space usage queries,=
=0D=0Abut didn't find any that clearly answered this item specifically=2E =
(If=0D=0Ayou know of one, just point me there=2E) I found this thread that=
uses=0D=0Audsize, but it's quite out of date (Dec=2E 2000, version 9=2E21)=
and I=0D=0Awasn't sure if the SQL was correct=2E=0D=0A=0D=0Ahttp://groups=
=2Egoogle=2Ecom/group/comp=2Edatabases=2Einformix/browse_thread/thr=0D=0Aea=
d/21dc4618526fa087/4ca3dfabf7d283f6?hl=3Den&lnk=3Dgst&q=3Dudsize#4ca3dfabf7=
d=0D=0A283f6=0D=0A=0D=0AI didn't see anything in the IIUG software reposito=
ry with the meta data=0D=0Acalculations=2E I also saw no mention of udsize=
in the Admin Guide or=0D=0AAdmin Reference Manual while looking for infor=
mation in the Informix=0D=0Alibrary:=0D=0A=0D=0Ahttp://www-306=2Eibm=2Ecom/=
software/data/informix/pubs/library/ids_100=2Ehtml=0D=0A=0D=0AMy environmen=
t information:=0D=0A=0D=0AIDS: 10=2E00=2EUC7X1=0D=0AO/S: SunOS 5=2E10 Gener=
ic_125100-05=0D=0A=0D=0ASysmaster Query (output for dbsnum 9, the only sbsp=
ace):=0D=0A=0D=0A SELECT C=2Edbsnum, name, is_sbspace, SUM(chksize) ChkS=
ize,=0D=0A SUM(nfree) NFree, SUM(mdsize) MdSize,=0D=0A =
SUM(udsize) UdSize, SUM(udfree) UdFree=0D=0A FROM sysmaster:sysdbsp=
aces d, sysmaster:syschunks c=0D=0A WHERE d=2Edbsnum=3Dc=2Edbsnum=0D=0A=

GROUP BY 1, 2, 3=0D=0A ORDER BY 1=0D=0A=0D=0A Dbs name is_=
sbspace chksize nfree mdsize udsize udfree=0D=0A =2E=2E=2E=0D=0A 9=

sbdefault 1 1050000 50505 141817 908121 318216=0D=0A =2E=
=2E=2E=0D=0A=0D=0Aonstat -d output:=0D=0A=0D=0A dbs offset size =

free bpages=0D=0A =2E=2E=2E=0D=0A 9 0 500000 =

49994 441831=0D=0A Metadata 58116 0 5811=
6=0D=0A 9 500000 250000 99992 233145=0D=0A =
Metadata 16852 8 16852=0D=0A 9 500000 250000 =
168230 233145=0D=0A Metadata 16852 500 16852=
=0D=0A 9 750000 50000 0 0=0D=0A Metad=
ata 49997 49997 49997=0D=0A =2E=2E=2E=0D=0A=0D=0AOnstat -d show=
s a total size of 1050000, same as chksize above=2E=0D=0AOnstat -d shows a =
total free space of 318216, same as udfree above=2E=0D=0AOnstat -d shows a =
Metadata size of 141817, same as mdsize above=2E=0D=0AOnstat -d shows a Met=
adata free space of 50505, same as nfree above=2E=0D=0A=0D=0AThere is no on=
stat -d output that matches the udsize (though subtracting=0D=0Amdsize from=
chksize leaves a difference of 8 pages - overhead maybe)=2E=0D=0A=0D=0AWhe=
n calculating metadata used space for an sbspace, this seems straight=0D=0A=
forward=2E=0D=0A=0D=0A Meta Data Used: (141817 - 50505) / 141817 =3D 64=
=2E39%=0D=0A=0D=0AShould there be a difference between user space used and =
total space=0D=0Aused?=0D=0AWhich of the following should be used for each =
calculation?=0D=0A=0D=0A A=2E (mdsize-nfree)/mdsize =3D> (141=
817-50505)/141817 =3D=0D=0A64=2E39%=0D=0A B=2E (chksize-udfree)/chksiz=
e =3D> (1050000-318216)/1050000=3D=0D=0A69=2E69%=0D=0A C=2E (chk=
size-(udfree+nfree))/chksize =3D> (1050000-368213)/1050000=3D=0D=0A64=2E93%=
=0D=0A D=2E (udsize -udfree)/udsize =3D> (908121-318216)/908121=
=3D=0D=0A64=2E96%=0D=0A E=2E (udsize -(udfree+nfree))/udsize =3D> (90=
8121-368213)/908121 =3D=0D=0A59=2E45%=0D=0A=0D=0AI made the assumption tha=
t calculation A is accurate for meta data=0D=0Aspace=2E=0D=0AI made the ass=
umption that calculation C is accurate for total space=0D=0Ausage=2E=0D=0AI=
made the assumption that calculation D is accurate for user space=0D=0Ausa=
ge=2E=0D=0A=0D=0ACalculation B seems to be looking at the full chunk size, =
but only=0D=0Asubtracting the user data space used=2E=0D=0A=0D=0ACalculatio=
n E seems to be using the user data size, but subtracting both=0D=0Auser an=
d meta data space used=2E=0D=0A=0D=0AAre my assumptions correct? If so, sp=
litting sbspaces into "META" and=0D=0A"USER" data (I didn't put in the TOTA=
L calculation for the sbspaces),=0D=0Aand all non sbspaces as "REGULAR" spa=
ces, does this UNION query bring=0D=0Aback accurate data for all of the dif=
ferent dbspaces on the server?=0D=0A=0D=0Aselect name dbspace,=0D=0A =
is_sbspace,=0D=0A "REG" space_type,=0D=0A sum(chksize) chunk_si=
ze,=0D=0A sum(nfree) chunk_free,=0D=0A sum(chksize)-sum(nfree=
) userdata_used,=0D=0A (((sum(chksize)-sum(nfree)) / sum(chksize)) * =
100)::DECIMAL(5,2)=0D=0Apct_userdata_used=0D=0A from sysmaster:sysdbspaces=
d, sysmaster:syschunks c=0D=0A where d=2Edbsnum=3Dc=2Edbsnum=0D=0A AND i=
s_sbspace <> 1=0D=0Agroup by 1,2=0D=0AUNION=0D=0Aselect name dbspace,=0D=0A=

is_sbspace,=0D=0A "USR" space_type,=0D=0A sum(udsize) us=
erdata_size,=0D=0A sum(udfree) userdata_free,=0D=0A sum(udsize)=
-sum(udfree) userdata_used,=0D=0A (((sum(udsize)-sum(udfree)) / sum(u=
dsize)) * 100)::DECIMAL(5,2)=0D=0Apct_userdata_used=0D=0A from sysmaster:s=
ysdbspaces d, sysmaster:syschunks c=0D=0A where d=2Edbsnum=3Dc=2Edbsnum=0D=
=0A and is_sbspace=3D1=0D=0Agroup by 1,2=0D=0AUNION=0D=0Aselect name dbsp=
ace,=0D=0A is_sbspace,=0D=0A "MET" space_type,=0D=0A sum(=
mdsize) metadata_size,=0D=0A sum(nfree) metadata_free,=0D=0A su=
m(mdsize)-sum(nfree) metadata_used,=0D=0A (((sum(mdsize)-sum(nfree)) =
/ sum(mdsize)) * 100)::DECIMAL(5,2)=0D=0Apct_userdata_used=0D=0A from sysm=
aster:sysdbspaces d, sysmaster:syschunks c=0D=0A where d=2Edbsnum=3Dc=2Edbs=
num=0D=0A and is_sbspace=3D1=0D=0Agroup by 1,2=0D=0Aorder by 1,2;=0D=0A=
=0D=0AAny thoughts or suggestions would be appreciated=2E=0D=0A=0D=0AThank =
you,=0D=0AGary Andrus=0D=0A=0D=0AThis e-mail may contain confidential or pr=
ivileged information=2E If=0Ayou think you have received this e-mail in err=
or, please advise the=0Asender by reply e-mail and then delete this e-mail =
immediately=2E=0AThank you=2E Aetna

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.