IO Subsystem

Slow DBEnvironment performance is often caused by I/O activity. Use the IO subsystem to determine if the DBEnvironment has insufficient data buffer space or insufficient log buffer space.




	NOTE: You should use the Static subsystem to remove indirect rows, eliminate overflow chains from hashed tables, and recluster appropriate indexes before you use the IO subsystem to tune I/O.

Most of the information displayed on the IO screens is for logical I/O, not physical I/O. Logical I/O means that ALLBASE/SQL requests that the operating system read or write a page.

If ALLBASE/SQL requests to read a page, and if the page is in the operating system's buffer pool, no physical I/O occurs. If ALLBASE/SQL requests to write to a page, the operating system may record the write in its buffer pool to avoid physical I/O, unless ALLBASE/SQL forces it to write the page to disk. ALLBASE/SQL forces the operating system to write the page to disk when log disk writes occur. This ensures the integrity of the database in case of a system crash.

To minimize log disk writes, you can use the IO screens to tune the size of the ALLBASE/SQL buffer pools. Specifically, you would use the IO screen, the IO Log Program screen, or the IO Log Session screen and then adjust the the LOG DISK WR field, as described in the chapter "SQLMON Screen Reference."

To further improve I/O performance, you should also use performance tuning tools on the operating system.

Insufficient Data Buffer Space

A high data buffer miss rate leads to increased I/O and slower performance. If ALLBASE/SQL requests a page that is not in the data buffer pool, then the operating system must fetch the page, either from the operating system buffer pool or by a physical disk read.

If the data buffer miss rate of your DBEnvironment seems high, try increasing the number of data buffer pages. For more information, see "Choosing a Number of Data Buffer Pages" in the chapter "Guidelines on System Administration." On the IO screen that follows, the average data buffer miss rate is 33% and the maximum is 60%.

The BUFF ACCESS, DATA DISK RD, and DATA DISK WR fields of the IO screen provide more information on data buffer I/O activity. The following screen shows that on average BUFF ACCESS is 3, which means that 3 page requests are made to the data buffer pool every 10.0 seconds.

You can also see that on average, DATA DISK RD is 1, which means that 1 of the 3 pages does not reside in the pool. That page may need to be read from disk, resulting in physical I/O.

To determine which sessions are engaged in data buffer I/O, access the IO Data Session screen. In the example screen that follows, PID 27344 has issued the highest number of page requests (BUFF ACCESS), but PID 27332 has the highest data buffer miss rate.

Insufficient Log Buffer Space

Log files are required to recover data in the event of a system crash, but the I/O incurred by logging can reduce the performance of your DBEnvironment. You can use the IO screen to monitor log buffer I/O. If the log buffer I/O seems excessive, try increasing the number of log buffer pages. For more information, see "Choosing a Number of Data Buffer Pages" in the chapter, "Guidelines on System Administration." Limiting the number of checkpoints issued can also reduce logging I/O.

To monitor the amount of log buffer I/O activity, check the fields shown in the following IO screen.

To determine which sessions are performing log buffer I/O, check the IO Log Session screen. In the following example, PID 6167 is responsible for all of the log buffer I/O activity.

IO Subsystem

Technical documentation

» Table of Contents

» Index

Insufficient Data Buffer Space

Insufficient Log Buffer Space