Managing Parameters for Table and Index I/O

Home | Previous Page | Next Page Tuning Configuration for Resource Usage > Tuning I/O-Management Parameters for Queries and Transactions >

Managing Parameters for Table and Index I/O

This section describe the performance considerations associated with the following read-ahead and data skipping behavior of the database server:

The maximum number of locked rows permitted for a single scan
The number of data and index pages that are read in a single I/O operation and the number of pages that are read in before the database server fetches the next group of rows
The behavior of the database server when a dbspace is not available and when I/O errors occur

Specifying the Maximum Number of Locked Rows for Each Scan

The ISOLATION_LOCKS configuration parameter specifies the maximum number of rows that can be locked on a single scan when the Cursor Stability isolation level is in effect. The default value is 1.

In Cursor Stability isolation level. the current row is locked until the row is no longer current. You use Cursor Stability when Repeatable Read is too strong because it locks the entire table, but Committed Read is too weak because it does not lock any rows. Cursor Stability allows for one or more rows to be locked but does not lock the entire table.

If most of your applications work in Cursor Stability isolation level, for more efficient row buffering, you might increase the setting of ISOLATION_LOCKS. At some point, however, performance improvements level off, and concurrency conflicts approach those of the Repeatable Read isolation level or whole-table locking.

Important:

If you set ISOLATION_LOCKS to a value greater than 1, review the setting for the LOCKS configuration parameter. For information on how to determine the value for the LOCKS configuration parameter, refer to Setting the Initial Number of Concurrent Locks.

Tuning Read-Ahead Behavior

Because most I/O wait time is used to seek the correct starting point on disk, you increase the efficiency of sequential scans and index builds if you increase the number of contiguous pages that are brought in with each transfer.

The following four configuration parameters control how many pages are read in each I/O operation and at what point the next group of pages is read.

The RA_PAGES parameter specifies the number of data pages that the database server brings into memory in a single I/O operation during sequential table scans, index builds, and UPDATE STATISTICS processing.
The RA_THRESHOLD parameter specifies the point at which the database server issues an I/O request to bring in the next set of data pages from disk.
In general, set RA_THRESHOLD close to the value of RA_PAGES so that the database server does not have to wait for read-ahead actions to be complete before it can use the pages.
The IDX_RA_PAGES and IDX_RA_THRESHOLD configuration parameters specify the equivalent read-ahead behavior for index pages. If your queries scan indexes for a large range of values, adjust the index read-ahead parameters to accommodate these scans. The default value for IDX_RA_PAGES is 4 for single-processor nodes and 8 for multiprocessor nodes. The default value for IDX_RA_THRESHOLD is IDX_RA_PAGES / 2.

To verify the efficiency of read-ahead parameter settings, compare the sum of pages that are read ahead with the number of pages actually used. If significantly more pages are read into the buffers than are used, reduce the read-ahead parameters. You might also notice a decrease in the read-cache rate if read-ahead is set too high. Adjust the IDX_RA_PAGES setting as appropriate to balance read-ahead for index and data pages.

Use onstat -P output, as shown in the following example, to monitor read-ahead efficiency:

Profile
dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached
1468     845      2565859  99.94   80581    67220    593352   86.42  
isamtot  open     start    read    write    rewrite  delete   commit rollbk
3329106  582147   539920   478921   116870   31109    28704    2240     18
ovlock   ovuserthread ovbuff   usercpu  syscpu   numckpts flushes 
0        0            0        1364.45  90.76    15       2114    
bufwaits lokwaits lockreqs deadlks  dltouts  ckpwaits compress seqscans
2322     4        20002682 0        0        7        1218     12130   
ixda-RA  idx-RA   da-RA    RA-pgsused lchwaits
0        0        13       13         107663

Add the values in the ixda-RA, idx_RA, and da-RA fields in the last row of the output. Compare the total to the value in the RA-pgused field. If the sum of the read-ahead field values is not approximately equal to the number of read-ahead pages used, reduce the setting of RA_PAGES to adjust the number of read-ahead pages.

Use the following formulas to calculate initial values for RA_PAGES and RA_THRESHOLD:

RA_PAGES = (BUFFERS * bp_fract) / (2 * large_queries) + 2
RA_THRESHOLD = (BUFFERS * bp_fract) / (2 * large_queries) - 2

bp_fract: is the portion of data buffers to use for large scans that require read-ahead. For example, to allow large scans to take up to 75 percent of buffers, set bp_fract to 0.75.
large_queries: is the number of concurrent queries that require a read-ahead. If you set DS_MAX_QUERIES, as described in Limiting the Number of Concurrent Queries, use this value. If you do not set DS_MAX_QUERIES, estimate the average number of large queries that might run concurrently.

To monitor read-ahead activity, use onstat -P. If the sum of the read-ahead columns is higher than RA-pgused, read-ahead is set too high.

Specifying Spaces that Can Be Skipped if Not Available

The DATASKIP configuration parameter allows you to specify which dbspaces, if any, queries can skip when those dbspaces are not available as the result of a disk failure. You can list specific dbspaces or turn data skipping on or off for all dbspaces.

To specify dbspaces to be skipped and turn DATASKIP off and on without restarting the database server, you can use the SQL statement SET DATASKIP. For more information, see the IBM Informix: Guide to SQL Syntax.

The database server sets the sixth character in the SQLWARN array to W when data skipping is enabled. For more information about the SQLWARN array, refer to the IBM Informix: Guide to SQL Tutorial.

Warning:

The database server cannot determine whether the results of queries are consistent when dbspaces can be skipped. If the dbspace contains a table fragment, the user who executes the query must be sure that the rows in that fragment are not needed for an accurate query result. If DATASKIP is on, queries with incomplete data might return results that are inconsistent with the actual state of the database or with similar queries run earlier or later, which might result in confusing or misleading query results.

Tip:

The RA_PAGES, RA_THRESHOLD, IDX_RA_PAGES, IDX_RA_THRESHOLD, and DATASKIP configuration parameters can be tuned with the onutil SET command while the server is running.