Estimating Pages That Smart Large Objects Occupy

Enterprise Edition Home | Express Edition Home | Previous Page | Next Page Table Performance Considerations > Managing Sbspaces >

Estimating Pages That Smart Large Objects Occupy

In your estimate of the space required for a table, you should also consider the amount of sbspace storage for any smart large objects (such as CLOB, BLOB, or multirepresentative data types) that are part of the table. An sbspace contains user-data areas and metadata areas. CLOB and BLOB data is stored in sbpages that reside in the user-data area. The metadata area contains the smart-large-object attributes, such as average size and whether or not the smart large object is logged. For more information about sbspaces, see your IBM Informix: Dynamic Server Administrator's Guide.

Estimating the Size of the Sbspace and Metadata Area

The first chunk of an sbspace must have a metadata area. When you add smart large objects, the database server adds more control information to this metadata area.

If you add a chunk to the sbspace after the initial allocation, you can take one of the following actions for metadata space:

Allocate another metadata area on the new chunk by default.
This action provides the following advantages:
- It is easier because the database server automatically calculates and allocates a new metadata area on the added chunk based on the average smart large object size
- Distributes I/O operations on the metadata area across multiple disks
Use the existing metadata area
If you specify the onspaces -U option, the database server does not allocate metadata space in the new chunk. Instead it must use a metadata area in one of the other chunks.

In addition, the database server reserves 40 percent of the user area to be used in case the metadata area runs out of space. Therefore, if the allocated metadata becomes full, the database server starts using this reserved space in the user area for additional control information.

You can let the database server calculate the size of the metadata area for you on the initial chunk and on each added chunks. However, you might want to specify the size of the metadata area explicitly, to ensure that the sbspace does not run out of metadata space and the 40 percent reserve area. You can use one of the following methods to explicitly specify the amount of metadata space to allocate:

Specify the AVG_LO_SIZE tag on the onspaces -Df option.
The database server uses this value to calculate the size of the metadata area to allocate when the -Ms option is not specified. If you do not specify AVG_LO_SIZE, the database server uses the default value of 8 kilobytes to calculate the size of the metadata area.
Specify the metadata area size in the -Ms option of the onspaces utility.
Use the procedure that Sizing the Metadata Area Manually for a New Chunk describes to estimate a value to specify in the onspaces -Ms option.

For information on monitoring the space usage in an sbspace and allocating more space, see the IBM Informix: Dynamic Server Administrator's Guide.

Sizing the Metadata Area Manually for a New Chunk

This procedure assumes that you know the sbspace size and need to allocate more metadata space. Each chunk can contain metadata but the sum total must accommodate enough room for all LO headers (average length 570 bytes each) and the chunk free list (which lists all the free extents in the chunk), as the following procedure shows.

To size the metadata area manually for a new chunk

Use the onstat -d option to obtain the size of the current metadata area from the Metadata size field.
Estimate the number of smart large objects that you expect to reside in the sbspace and their average size.
Use the following formula to calculate the total size of the metadata area:
```
Total metadata kilobytes = (LOcount*570)/1024 +
                           (numchunks*800) + 100 
```
LOcount

is the number of smart large objects that you expect to have in all sbspace chunks, including the new one.

numchunks
is the total number of chunks in the sbspace.
1. To obtain the additional metadata area required, subtract the current metadata size that you obtained in step 1 from the value that you obtained in step 3.
2. When you add another chunk, specify in the -Ms option of the onspaces -a command the value that you obtained in step 3a.

Example of Calculating the Metadata Area for a New Chunk

This example estimates the metadata size required for two sbspace chunks, using the preceding procedure:

Suppose the Metadata size field in the onstat -d option shows that the current metadata area is 1000 pages.
If the system page size is 2048 bytes, the size of this metadata area is 2000 kilobytes, as the following calculation shows:
```
current metadata = (metadata_size * pagesize) / 1024
                 = (1000 * 2048) / 1024 
               = 2000 kilobytes
```
Suppose you expect 31,000 smart large objects in the two sbspace chunks

The following formula calculates the total size of metadata area required for both chunks, rounding up fractions:

Total metadata = (LOcount*570)/1024 + (numchunks*800) + 100 
               = (31,000 * 570)/1024 + (2*800) + 100 
               = 17256 + 1600 + 100
             = 18956 kilobytes

To obtain the additional metadata area required, subtract the current metadata size that you obtained in step 1 from the value that you obtained in step 3.
```
Additional metatdata = Total metatdata - current metadata 
                     = 18956 - 2000 
                   = 16956 kilobytes
```
When you add the chunk to the sbspace, use the -Ms option of the onspaces -a command to specify a metadata area of 16,956 kilobytes.
```
% onspaces -a sbchk2 -p /dev/raw_dev1 -o 200 -Ms 16956
```

For more information about onspaces and onstat -d, see the IBM Informix: Dynamic Server Administrator's Reference.