Informix

 View Only
Expand all | Collapse all

Facing some difference in size between 'onstat -d' , 'oncheck -ce' and 'syschunks' table values for 8k page size dbspaces and chunks

  • 1.  Facing some difference in size between 'onstat -d' , 'oncheck -ce' and 'syschunks' table values for 8k page size dbspaces and chunks

    Posted Wed March 30, 2022 09:11 AM

    Hi,

    I have written a query to find Total, Used and Free Space in all chunks.

    All the columns in query are fetched from syschunks and sysdbspaces tables from sysmaster database.

    I know onstat -d will show Total and Free Space Details.

    But I would like to see Total, Used and Free Space in KB, MB and GB format.

    That is why I have written my own query using SMI tables.

     

    Default Page Size in Linux is        - 2k

    Default Page Size in Windows is  - 4k

     

    In Windows default installation we will get 8k page size dbspace automatically for datadbs and tmpdbspace.

     

    Example:

     

    Dbspaces

    address                        number      flags      fchunk   nchunks   pgsize    flags      owner       name

    0000000080E0A9A0      7          0x20001       7                1          8192     N  BA     informix   data8dbs1

    0000000080E0ABF0      8          0x20001       8                1          8192     N  BA     informix   data8dbs2

    00000000825A5030       9          0x20001       9                1          8192     N  BA     informix   data8dbs3

    00000000825A5280      10         0x2001        10               2          8192     N TBA    informix   tmpdbspace

     

    I am facing some difference in value between onstat -d, oncheck -ce and syschunks table values

    for 8k page size dbspaces and chunks.

     

    • onstat -d and oncheck -ce command shows same value.
    • But in syschunks table it is showing double of that value.
    • Please find the details below and kindly let me know why this difference is there.

     

    Results From onstat -d Command:

    Dbspaces

    address                      number      flags      fchunk   nchunks   pgsize   flags      owner       name

    0000000080E0A9A0     7        0x20001       7              1            8192     N  BA    informix   data8dbs1

    0000000080E0ABF0     8        0x20001       8              1             8192    N  BA    informix   data8dbs2

    00000000825A5030      9        0x20001       9              1             8192    N  BA    informix   data8dbs3

     

    Chunks

    address                                 chunk/dbs    offset      size       free       bpages     flags                        pathname

    00000000825AB030               7      7           0          8192       8139                  PO-BED      D:\Informix\storage\ol_informix1210_1_data8dbs1_p_1

    00000000825AC030               8      8           0          8192       8139                  PO-BED      D:\Informix\storage\ol_informix1210_1_data8dbs2_p_1

    00000000825AD030               9      9           0          8192       8139                  PO-BED      D:\Informix\storage\ol_informix1210_1_data8dbs3_p_1

     

    Results From oncheck -ce Command:

    Validating extents for Space 'data8dbs1' ...

     

     Chunk                     Pathname                                                                   Pagesize(k)  Size(p)   Used(p)  Free(p)

       7         D:\Informix\storage\ol_informix1210_1_data8dbs1_p_1                      8             8192       53      8139

     

    Validating extents for Space 'data8dbs2' ...

     

    Chunk                     Pathname                                                                   Pagesize(k)  Size(p)   Used(p)  Free(p)

       8         D:\Informix\storage\ol_informix1210_1_data8dbs2_p_1                       8             8192       53      8139

     

    Validating extents for Space 'data8dbs3' ...

     

     Chunk                     Pathname                                                                   Pagesize(k)  Size(p)   Used(p)  Free(p)

       9         D:\Informix\storage\ol_informix1210_1_data8dbs3_p_1                       8              8192       53      8139

     

    Results From syschunks Table:

     

    chknum              7

    dbsnum              7

    nxchknum          0

    pagesize           8192

    chksize             16384

    offset                 0

    nfree                 16278

    mdsize               -1

    udsize                -1

    udfree                -1

    is_offline            0

    is_recovering     0

    is_blobchunk      0

    is_sbchunk         0

    is_inconsistent   0

    is_extendable    1

    flags                   139332

    fname                D:\Informix\storage\ol_informix1210_1_data8dbs1_p_1

    mfname

    moffset

    mis_offline          0

    mis_recovering   0

    mflags

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

    chknum                8

    dbsnum                8

    nxchknum            0

    pagesize              8192

    chksize                16384

    offset                    0

    nfree                    16278

    mdsize                  -1

    udsize                   -1

    udfree                   -1

    is_offline               0

    is_recovering        0

    is_blobchunk        0

    is_sbchunk           0

    is_inconsistent     0

    is_extendable       1

    flags                      139332

    fname                   D:\Informix\storage\ol_informix1210_1_data8dbs2_p_1

    mfname

    moffset

    mis_offline            0

    mis_recovering     0

    mflags

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

    chknum               9

    dbsnum               9

    nxchknum            0

    pagesize             8192

    chksize               16384

    offset                    0

    nfree                    16278

    mdsize                  -1

    udsize                   -1

    udfree                   -1

    is_offline               0

    is_recovering        0

    is_blobchunk        0

    is_sbchunk           0

    is_inconsistent     0

    is_extendable       1

    flags                      139332

    fname                    D:\Informix\storage\ol_informix1210_1_data8dbs3_p_1

    mfname

    moffset

    mis_offline            0

    mis_recovering     0

    mflags

     



    ------------------------------
    SHANGEETHA T
    ------------------------------

    #Informix


  • 2.  RE: Facing some difference in size between 'onstat -d' , 'oncheck -ce' and 'syschunks' table values for 8k page size dbspaces and chunks

    IBM Champion
    Posted Wed March 30, 2022 09:29 AM
    onstat -d shows the number of pages in units of the pagesize of the dbspace.  So a chunk with an 8K pagesize will show in onstat -d as the number of 8K pages.

    syschunks (for a regular dbspace) shows the number of pages in units of the DEFAULT pagessize of the system that you are on, regardless of the pagesize of the dbspace.  So on Windows with a 4K pagesize, syschunks will show as the number of 4K pages, which is why the values you see in syschunks are double what you see in onstat -d.

    If you need to get the system default pagesize (for example when writing a "free space" script to be used on a variety of OSes), then you can get this from sysmaster also:

    select sh_pagesize from sysshmvals;


    ------------------------------
    Mike Walker
    xDB Systems, Inc
    www.xdbsystems.com
    ------------------------------



  • 3.  RE: Facing some difference in size between 'onstat -d' , 'oncheck -ce' and 'syschunks' table values for 8k page size dbspaces and chunks

    IBM Champion
    Posted Wed March 30, 2022 09:42 AM
    Yeah, not intuitive what's stored in syschunks or syschunks_fast (querying syschunks will trigger recalculating free BLOBSPACE pages like running "onstat -d update", syschunks_fast is what onstat queries if you don't add the "update" option). Since version 11.10 the chksize and nfree columns in syschunks/syschunks_fast have reported in default page size (so 4K on Windows & AIX 2K elsewhere). Prior to that they reported in the pagesize of the chunk).

    You do know that this problem is solved already. Just download and build the dbsavail utility from my utils2_ak package (free & open source). Download from my web site at My Utilities. Here's a sample run:
    $ dbsavail
    Sort by: Free KB.

    Dbspace              Number 2K Pages   2K Pages Free    Total KB        Free KB      
    ------------------   ---------------   -------------    ------------    ------------
    indexdbs                     35000             880           70000            1760     (PgSz: 2K)
    plogspace                   111611            1617          223222            3234     (PgSz: 2K)
    llogspace                   397220            2167          794440            4334     (PgSz: 2K)
    indexdbs_1                  248304            8876          496608           17752     (PgSz: 8K)
    cdrspace                     50000           16641          100000           33282     (PgSz: 2K)
    rootdbs                     150000           77051          300000          154102     (PgSz: 2K)
    datadbs_2                   897496          195745         1794992          391490     (PgSz: 2K)
    datadbs_1                  1148531          205867         2297062          411734     (PgSz: 2K)
    sbspace                     550000          422241         1100000          844482 SBsp(PgSz: 2K)
    tempdbs                     602400          602336         1204800         1204672 Temp(PgSz: 2K)
    ------------------   ---------------   -------------    ------------    ------------
    Totals:                    4190562         1533421         8381124         3066842

    There are also several options including changing from free KB to free % and to sort on different columns:

    $ dbsavail -?
    Usage: dbsavail [-H server] [-f] [-p] [-u] [-R] [-F|P|S|N]
          dbsavail [-V|-h]
           -H - Connect to server.
           -p - print %free (Default: KB/MB/GB Free).
           -u - print KB/MB/GB Used or Percent Used instead of Total KB.
           -f - do not update blob chunk statistics (may be less accurate)
           -d - include chunk detail by dbspace.
           -F - sort the report by free size in KB/MB/GB (default).
           -P - sort the report by percent free.
           -S - sort the report by total size in KB/MB/GB.
           -N - sort the report by dbspace name.
           -R - Reverse sort order.
           -V - print copyright notice and exit.
           -K - Display sizes in KB (default).
           -M - Display sizes in MB.
           -G - Display sizes in GB.
           -h - print usage and exit.

           By default the report is ordered by free space in KB/MB/GB.

    I have not tried to compile this on Windows, though it should be compilable using the Windows release of the Informix CSDK. But, if you also have Linux, you could run it on a Linux box with INFORMIXSERVER pointed at the Windows based engine. That will work. 

    For what it's worth, here's what the percent free report looks like:

    $ dbsavail -p
    Sort by: Free KB.

    Dbspace              Number 2K Pages   2K Pages Free    Total KB        Pct Free     
    ------------------   ---------------   -------------    ------------    ------------
    indexdbs                     35000             880           70000            2.51     (PgSz: 2K)
    plogspace                   111611            1617          223222            1.45     (PgSz: 2K)
    llogspace                   397220            2167          794440            0.55     (PgSz: 2K)
    indexdbs_1                  248304            8876          496608            3.58     (PgSz: 8K)
    cdrspace                     50000           16641          100000           33.28     (PgSz: 2K)
    rootdbs                     150000           77051          300000           51.37     (PgSz: 2K)
    datadbs_2                   897496          195745         1794992           21.81     (PgSz: 2K)
    datadbs_1                  1148531          205867         2297062           17.92     (PgSz: 2K)
    sbspace                     550000          422241         1100000            4.89 SBsp(PgSz: 2K)
    tempdbs                     602400          602336         1204800           99.99 Temp(PgSz: 2K)
    ------------------   ---------------   -------------    ------------    ------------
    Totals:                    4190562         1533421         8381124




    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------



  • 4.  RE: Facing some difference in size between 'onstat -d' , 'oncheck -ce' and 'syschunks' table values for 8k page size dbspaces and chunks

    IBM Champion
    Posted Wed March 30, 2022 09:50 AM
    Oh, I forgot, BLOB spaces and SmartBlob spaces are different from normal dbspace chunks.
    BLOB spaces report in the number of blob pages which you will have to use the pagesize column in syschunks/syschunks_fast to convert to KB or to number of default pages.
    For SBSpaces use the 'udfree' column instead of the 'free' column and treat that the same as regular chunk 'free' count.

    Anyway, WAY easier to just take advantage of the madness I went through to get dbsavail reporting correctly and use that. 

    FWIW, dbsavail creates two stored procedures in sysmaster the first time it is run (or if it detects that the exiting versions of them are outdated due to an upgraded dbsavail.ec source): dbsavail() and dbsavail_fast() which do all the heavy lifting. So if you don't like the report formatting from the full utility, you could just run the procs and format the results yourself.

    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------