HP 3000 Manuals

ALLBASE/SQL Performance and Monitoring Guidelines

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, CID 3 has
issued the most page requests (BUFF ACCESS).


            

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, PIN 2 is responsible for
all of the log buffer I/O activity.


            

MPE/iX 5.0 Documentation