onspaces Options That Affect Sbspace I/O

Enterprise Edition Home | Express Edition Home | Previous Page | Next Page Effect of Configuration on I/O Activity > Parameters That Affect I/O for Smart Large Objects (IDS) >

onspaces Options That Affect Sbspace I/O

When you create an sbspace, you can specify the following onspaces options that affect I/O performance:

Extent sizes
Buffering mode (use lightweight I/O or not)
Logging

Sbspace Extent Sizes

As you add smart large objects to a table, the database server allocates disk space to the sbspace in units called extents. Each extent is a block of physically contiguous pages from the sbspace. Even when the sbspace includes more than one chunk, each extent is allocated entirely within a single chunk so that it remains contiguous.

Contiguity is important to I/O performance. When the pages of data are contiguous, disk-arm motion is minimized when the database server reads the rows sequentially. The mechanism of extents is a compromise between the following competing requirements:

The size of some smart large objects is not known in advance.
The number of smart large objects in different tables can grow at different times and different rates.
All the pages of a single smart large object should ideally be adjacent for best performance when you retrieve the entire object.

Because you might not be able to predict the number and size of smart large objects, you cannot specify the extent length of smart large objects. Therefore, the database server adds extents only as they are needed, but all the pages in any one extent are contiguous for better performance. In addition, when the database server creates a new extent that is adjacent to the previous extent, it treats both extents as a single extent.

The number of pages in an sbspace extent is determined by one of the following methods:

The database server calculates the extent size for a smart large object from a set of heuristics, such as the number of bytes in a write operation. For example, if an operation asks to write 30 kilobytes, the database server tries to allocate an extent the size of 30 kilobytes.
The final size of the smart large object as indicated by one of the following functions when you open the sbspace in an application program:
- The DataBlade API mi_lo_specset_estbytes function
  For more information on the DataBlade API functions to open a smart large object and set the estimated number of bytes, see the IBM Informix: DataBlade API Programmer's Guide.
- The ESQL/C ifx_lo_specset_estbytes function
  For more information on the ESQL/C functions to open a smart large object and set the estimated number of bytes, see the IBM Informix: ESQL/C Programmer's Manual.

These functions are the best way to set the extent size because they reduce the number of extents in a smart large object. The database server tries to allocate the entire smart large object as one extent (if an extent of that size is available in the chunk).

The EXTENT_SIZE flag in the -Df option of the onspaces command when you create or alter the sbspace
Most administrators do not use the onspaces EXTENT_SIZE flag because the database server calculates the extent size from heuristics. However, you might consider using the onspaces EXTENT_SIZE flag in the following situations:
- Many one-page extents are scattered throughout the sbspace.
- Almost all smart large objects are the same length.
The EXTENT SIZE keyword of the CREATE TABLE statement when you define the CLOB or BLOB column
Most administrators do not use the EXTENT SIZE keyword when they create or alter a table because the database server calculates the extent size from heuristics. However, you might consider using this EXTENT SIZE keyword if almost all smart large objects are the same length.

Important:

For most applications, it is recommended that you use the values that the database server calculates for the extent size. Do not use the DataBlade API mi_lo_specset_extsz function or the ESQL/C ifx_lo_specset_extsz function to set the extent size of the smart large object.

If you know the size of the smart large object, it is recommended that you specify the size in the DataBlade API mi_lo_specset_estbytes() function or ESQL/C ifx_lo_specset_estbytes() function instead of in the onspaces utility or the CREATE TABLE or the ALTER TABLE statement. These functions are the best way to set the extent size because the database server allocates the entire smart large object as one extent (if it has contiguous storage in the chunk).

Extent sizes over one megabyte do not provide much I/O benefit because the database server performs read and write operations in multiples of 60 kilobytes at the most. However, the database server registers each extent for a smart large object in the metadata area; therefore, especially large smart large objects might have many extent entries. Performance of the database server might degrade when it accesses these extent entries. In this case, you can reduce the number of extent entries in the metadata area if you specify the eventual size of the smart large object in the mi_lo_specset_estbytes() function or ifx_lo_specset_estbytes() function.

For more information, see Improving Metadata I/O for Smart Large Objects.

Lightweight I/O for Smart Large Objects

By default, smart large objects pass through the buffer pool in the resident portion of shared memory. Although smart large objects have lower priority than other data, the buffer pool can become full when an application accesses many smart large objects. A single application can fill the buffer pool with smart large objects and leave little room for data that other applications might need. In addition, when the database server performs scans of many pages into the buffer pool, the overhead and contention associated with checking individual pages in and out might become a bottleneck.

Instead of using the buffer pool, the administrator and programmer have the option to use lightweight I/O. Lightweight I/O operations use private buffers in the session pool of the virtual portion of shared memory.

Important:

Use private buffers only when you read or write smart large objects in read or write operations greater than 8080 bytes and you seldom access them. That is, if you have infrequent read or write function calls that read large amounts of data in a single function invocation, lightweight I/O can improve I/O performance.

Advantages of Lightweight I/O for Smart Large Objects

Lightweight I/O provides the following advantages:

Transfers larger blocks of data in one I/O operation
These I/O blocks can be as large as 60 kilobytes. But the bytes must be adjacent for the database server to transfer them in a single I/O operation.
Bypasses the overhead of the buffer pool when many pages are read
Prevents frequently accessed pages from being forced out of the buffer pool when many sequential pages are read for smart large objects

When you use lightweight I/O buffers for smart large objects, the database server might read several pages with one I/O operation. A single I/O operation reads in several smart-large-object pages, up to the size of an extent. For information on when to specify extent size, see Sbspace Extent Sizes.

Specifying Lightweight I/O for Smart Large Objects

To specify the use of lightweight I/O when creating the sbspace, the administrator can use the BUFFERING tag of the -Df option in the onspaces -c -S command. The default value for BUFFERING is ON, which means to use the buffer pool. The buffering mode that you specify (or the default, if you do not specify) in the onspaces command is the default buffering mode for all smart large objects stored within the sbspace.

Important:

In general, if read and write operations to the smart large objects are less than 8080 bytes, do not specify a buffering mode when you create the sbspace. If you are reading or writing short blocks of data, such as 2 kilobytes or 4 kilobytes, leave the default of "buffering=ON" to obtain better performance.

Programmers can override the default buffering mode when they create, open, or alter a smart-large-object instance with DataBlade API and the ESQL/C functions. The DataBlade API and the ESQL/C application programming interface provide the LO_NOBUFFER flag to allow lightweight I/O for smart large objects.

Important:

Use the LO_NOBUFFER flag only when you read or write smart large objects in operations greater than 8080 bytes and you seldom access them. That is, if you have infrequent read or write function calls that read large amounts of data in a single function invocation, lightweight I/O can improve I/O performance.

For more information on these flags and functions, see the IBM Informix: DataBlade API Programmer's Guide and the IBM Informix: ESQL/C Programmer's Manual.

Logging

If you decide to log all write operations on data stored in sbspaces, logical-log I/O activity and memory utilization increases. For more information, see LOGBUFF.