Blobpage Size Considerations

Blobspaces are divided into units called blobpages. The database server retrieves simple large objects from a blobspace in blobpage-sized units.You specify the size of a blobpage in multiples of a disk page when you create the blobspace. The optimal blobpage size for your configuration depends on the following factors:

• The size distribution of the simple large objects
• The trade-off between retrieval speed for your largest simple large object and the amount of disk space that is wasted by storing simple large objects in large blobpages

To retrieve simple large objects as quickly as possible, use the size of your largest simple large object rounded up to the nearest disk-page-sized increment. This scheme guarantees that the database server can retrieve even the largest simple large object in a single I/O request. Although this scheme guarantees the fastest retrieval, it has the potential to waste disk space. Because simple large objects are stored in their own blobpage (or set of blobpages), the database server reserves the same amount of disk space for every blobpage even if the simple large object takes up a fraction of that page. Using a smaller blobpage allows you to make better use of your disk, especially when large differences exist in the sizes of your simple large objects.

To achieve the greatest theoretical utilization of space on your disk, you could make your blobpage the same size as a standard disk page. Then many, if not most, simple large objects would require several blobpages. Because the database server acquires a lock and issues a separate I/O request for each blobpage, this scheme performs poorly.

In practice, a balanced scheme for sizing uses the most frequently occurring simple-large-object size as the size of a blobpage. For example, suppose that you have 160 simple-large-object values in a table with the following size distribution:

• Of these values, 120 are 12 kilobytes each.
• The other 40 values are 16 kilobytes each.

You can choose one of the following blobpage sizes:

• The 12-kilobyte blobpage size provides greater storage efficiency than a 16-kilobyte blobpage size, as the following two calculations show:
  • 12 kilobytes

    This configuration allows the majority of simple-large-object values to require a single blobpage and the other 40 values to require two blobpages. In this configuration, 8 kilobytes is wasted in the second blobpage for each of the larger values. The total wasted space is as follows:

    wasted-space = 8 kilobtyes * 40 
                 = 329 kilobytes

  • 16 kilobytes

    In this configuration, 4 kilobytes is wasted in the extents of 120 simple large objects. The total wasted space is as follows:

    wasted-space = 4 kilobtyes * 120 
                 = 640 kilobytes

• If your applications access the 16-kilobyte simple-large-object values more frequently, the database server must perform a separate I/O operation for each blobpage. In this case, the 16-kilobyte blobpage size provides better retrieval speed than a 12-kilobyte blobpage size.

Optimizing Blobspace Blobpage Size

When you are evaluating blobspace storage strategy, you can measure efficiency by two criteria:

• Blobpage fullness
• Blobpages required per simple large object

Blobpage fullness refers to the amount of data within each blobpage. TEXT and BYTE data stored in a blobspace cannot share blobpages. Therefore, if a single simple large object requires only 20 percent of a blobpage, the remaining 80 percent of the page is unavailable for use. However, avoid making the blobpages too small. When several blobpages are needed to store each simple large object, you increase the overhead cost of storage. For example, more locks are required for updates, because a lock must be acquired for each blobpage.

Obtaining Blobspace Storage Statistics

To help you determine the optimal blobpage size for each blobspace, use the oncheck -pB command. The oncheck -pB command lists the following statistics for each table (or database):

• The number of blobpages used by the table (or database) in each blobspace
• The average fullness of the blobpages used by each simple large object stored as part of the table (or database)

Determining Blobpage Fullness with oncheck -pB

The oncheck -pB command displays statistics that describe the average fullness of blobpages. These statistics provide a measure of storage efficiency for individual simple large objects in a database or table. If you find that the statistics for a significant number of simple large objects show a low percentage of fullness, the database server might benefit from changing the size of the blobpage in the blobspace.

Both the oncheck -pB and onstat -d update outputs display the same information about the number of free blobpages. For information about onstat -d update, see managing disk space in the IBM Informix: Administrator's Guide.

Execute oncheck -pB with either a database name or a table name as a parameter. The following example retrieves storage information for all simple large objects stored in the table sriram.catalog in the stores_demo database:

oncheck -pB stores_demo:sriram.catalog 

Figure 19 shows the output of this command.

              BLOBSpace Report for stores_demo:sriram.catalog

Total pages used by table            7

BLOBSpace usage:
Space   Page             Percent Full
Name    Number    Pages  0-25%  26-50%  51-75  76-100%
-------------------------------------------------------------
blobPIC 0x300080  1      x
   blobPIC 0x300082  2      x
         ------
Page Size is 6144      3

bspc1   0x2000b2  2             x
bspc1   0x2000b6  2                     x
              ------
Page Size is 2048      4

Space Name is the name of the blobspace that contains one or more simple large objects stored as part of the table (or database).

Page Number is the starting address in the blobspace of a specific simple large object.

Pages is the number of the database server pages required to store this simple large object.

Percent Full is a measure of the average blobpage fullness, by blobspace, for each blobspace in this table or database.

Page Size is the size in bytes of the blobpage for this blobspace. Blobpage size is always a multiple of the database server page size.

The example output indicates that four simple large objects are stored as part of the table sriram.catalog. Two objects are stored in the blobspace blobPIC in 6144-byte blobpages. Two more objects are stored in the blobspace bspc1 in 2048-byte blobpages.

The summary information that appears at the top of the display, Total pages used by table is a simple total of the blobpages needed to store simple large objects. The total says nothing about the size of the blobpages used, the number of simple large objects stored, or the total number of bytes stored.

The efficiency information displayed under the Percent Full heading is imprecise, but it can alert an administrator to trends in the storage of TEXT and BYTE data.

Interpreting Blobpage Average Fullness

This section demonstrates the idea of average fullness. The first simple large object listed in Figure 19 is stored in the blobspace blobPIC and requires one 6144-byte blobpage. The blobpage is 51 to 75 percent full, meaning that the size is between 0.51 * 6144 = 3133 bytes and 0.75 * 6144 = 4608. The maximum size of this simple large object must be less than or equal to 75 percent of 6144 bytes, or 4608 bytes.

The second object listed under blobspace blobPIC requires two 6144-byte blobpages for storage, or a total of 12,288 bytes. The average fullness of all allocated blobpages is 51 to 75 percent. Therefore, the minimum size of the object must be greater than 50 percent of 12,288 bytes, or 6144 bytes. The maximum size of the simple large object must be less than or equal to 75 percent of 12,288 bytes, or 9216 bytes. The average fullness does not mean that each page is 51 to 75 percent full. A calculation would yield 51 to 75 percent average fullness for two blobpages where the first blobpage is 100 percent full and the second blobpage is 2 to 50 percent full.

Now consider the two simple large objects in blobspace bspc1. These two objects appear to be nearly the same size. Both objects require two 2048-byte blobpages, and the average fullness for each is 76 to 100 percent. The minimum size for these simple large objects must be greater than 75 percent of the allocated blobpages, or 3072 bytes. The maximum size for each object is slightly less than 4096 bytes (allowing for overhead).

Applying Efficiency Criteria to Output

Looking at the efficiency information for blobspace bspc1, a database server administrator might decide that a better storage strategy for TEXT and BYTE data would be to double the blobpage size from 2048 bytes to 4096 bytes. (Blobpage size is always a multiple of the database server page size.) If the database server administrator made this change, the measure of page fullness would remain the same, but the number of locks needed during an update of a simple large object would be reduced by half.

The efficiency information for blobspace blobPIC reveals no obvious suggestion for improvement. The two simple large objects in blobPIC differ considerably in size, and there is no optimal storage strategy. In general, simple large objects of similar size can be stored more efficiently than simple large objects of different sizes.