HP 3000 Manuals

ALLBASE/SQL Performance and Monitoring Guidelines

SampleIO Subsystem 

The SampleIO subsystem is useful for balancing the I/O load of a
DBEnvironment. 
The SampleIO screens display the amount of data buffer swapping activity
for DBEFiles, tables, indexes, and referential constraints.  For more
information, refer to the section "Load Balancing" in the chapter
"Guidelines on System Administration."

You should use the IO subsystem to tune the size of the data buffer pool
before you use the SampleIO subsystem to balance load.


NOTE  Use the SampleIO subsystem sparingly, because it significantly
      increases CPU usage.


Using the SET SAMPLING Command 

The SET SAMPLING command enables or disables sampling of the data buffer
pool.  Sampling only occurs if SAMPLING is ON and you access a SampleIO
screen.  The counters on the SampleIO screens are set to 0 when you issue
SET DBENVIRONMENT, and are then incremented as sampling occurs.  The
counters only reflect activity that was "seen" during sampling.  The
counters are cumulative; they represent the total activity observed over
all samples taken since you issued SET DBENVIRONMENT.

By default, SQLMON does not display SampleIO screens during sampling.
Instead, it displays a scale that allows you to determine the amount of
sampling that has occurred.  For example, if you issue the SET REFRESH 10
and SET CYCLE 5 commands and then invoke a SampleIO screen, SQLMON
displays the following:

     SQLMONITOR SAMPLEIO => /sa

     SAMPLING = ON
     REFRESH = 10   (One set of samples will be taken every 10 seconds).
     CYCLE = 5      (A total of 5 sets of samples will be taken, then you
             will automatically return to the SQLMONITOR prompt).

     --------10--------20--------30--------40--------50
     12345678901234567890123456789012345678901234567890

The command SET REFRESH 10 means that SQLMON will take one set of samples
every 10 seconds, and SET CYCLE 5 means that SQLMON will take 5 sets of
samples and then return to the prompt.

The scale includes the following elements:

   1.  The current value of the SAMPLING variable.  When you see a
       refresh scale, SAMPLING is ON, because this scale is never printed
       if SAMPLING is OFF. To view the results of previous sampling,
       issue the command SET SAMPLING OFF and then invoke the SampleIO
       screen you want to see.

   2.  The current value of the REFRESH variable, which determines the
       number of seconds that SQLMON pauses between each refresh cycle.

   3.  The current value of the CYCLE variable, which determines the
       number of refresh cycles that occur before you return to the
       SQLMON prompt.  If CYCLE is OFF, you must press Return to exit
       sampling and return to the prompt.

   4.  SQLMON displays a period each time a refresh cycle completes.

In the example above, SQLMON displays a period every 10 seconds, because
REFRESH is set to 10.  It displays 5 periods, because CYCLE is 5.  SQLMON
takes a total of 125 samples, which is 5 refresh cycles X 25 samples
each.

After SQLMON has completed the sampling, issue the command SET SAMPLING
OFF. If you are using SQLMON interactively, and if CYCLE has a value
other than OFF, you should also issue SET CYCLE OFF. Then, you can invoke
the SampleIO screens in which you are interested.

For example, the following screen lists the amount of I/O for each
DBEFile. 


            

PartsDBE0, which exists in the SYSTEM DBEFileSet and contains the system
catalog, has the most read I/O activity.  The only DBEFile with write I/O
activity is PurchDataF1.

DBEFiles provide storage for objects that exist within a DBEFileSet.  I/O
occurs for a DBEFile whenever I/O occurs for the tables, indexes, or
referential constraints within the DBEFileSet.  You can use the SampleIO
screen to determine which DBEFiles (and therefore which DBEFileSets) have
the most I/O.

In the SampleIO screen above, we see that objects in the PurchFS
DBEFileSet are showing slightly more I/O activity than objects in the
WarehFS DBEFileSet.  You can use the SampleIO Tables, SampleIO Indexes,
and SampleIO TabIndex screens to obtain I/O information about the
individual objects in these DBEFileSets.

In the following screen, we see that PurchDB.SupplyPrice is the table
with the most activity in the PurchFS DBEFileSet, and is responsible for
the write I/O activity that we saw on the SampleIO screen.


            

PurchDB.Parts shows the most activity in the WarehFS DBEFileSet.  The
next example shows that an index on PurchDB.Parts is also undergoing I/O.


            

Using the SET DISPLAYSAMPLES Command 

If you want to display screens in the SampleIO subsystem during sampling,
instead of seeing the refresh scale shown above, you can do so by issuing
a SET DISPLAYSAMPLES ON command.  When DISPLAYSAMPLES is ON, the screen
is refreshed after each set of samples is obtained, that is, after each
refresh cycle.

When DISPLAYSAMPLES is OFF, you see a refresh scale instead of the
screen, and a period is displayed each time a refresh cycle completes.
Because SQLMON does not display screen images, a smaller amount of output
is created when sampling occurs, which might be especially desirable for
batch jobs.  In addition, SQLMON needs less CPU time, because some
processing to sort and format the information displayed on the screen is
avoided.

When SAMPLING is OFF, the SET DISPLAYSAMPLES command has no effect.

A Sample Batch Job 

For example, you can use the following script within an SQLMON batch job
to obtain SampleIO statistics for the day on a particular DBEnvironment.

     /set dbenv MyDBE
     /set menu off
     #
     ###############################################################################
     # SAMPLING is ON by default, so the following command is not really
     # necessary.
     ###############################################################################
     /set sampling on
     #
     ###############################################################################
     # Do not generate screens while sampling. This reduces the output
     # generated by this job, and also reduces the amount of CPU SQLMON
     # consumes when it gathers statistics. DISPLAYSAMPLES is OFF by default,
     # so the following command is not really necessary.
     ###############################################################################
     /set displaysamples off
     #

     ###############################################################################
     # Take samples every 10 minutes for 8 hours:                                  #
     ###############################################################################
     # Take 1 set of 25 samples every 10 minutes (10 min x 60 sec/min = 600 sec)
     /set refresh 600
     #
     # Take 48 sets of samples (8 hours = 480 min x (1 cyc/10 min) = 48 cyc)
     /set cycle 48
     #
     ###############################################################################
     # Now perform the sampling. It does not matter which SampleIO screen is
     # used. The following command causes the SampleIO screen to be
     # visited 48 times (a 10-minute pause occurs between each visit).
     ###############################################################################
     /sampleio
     #
     ###############################################################################
     # Now print a report. When SAMPLING is OFF, each screen is painted
     # without taking additional samples of the data buffer pool. We simply
     # view the statistics that were obtained as a result of all of the
     # previous sampling.
     #
     # If you set the REFRESH variable to a large value for sampling, it's
     # good practice to reset it to a lower (normal) value when turning
     # sampling off. It's also good practice to reset the CYCLE variable.
     ###############################################################################
     /set sampling off
     /set refresh 10
     /set cycle 1
     ###############################################################################
     # The following commands cause tables and indexes from all DBEFileSets
     # to be included in the report. They will be printed in descending order
     # according to the total amount of I/O that was observed during sampling.
     ###############################################################################
     /set dbefileset off
     /set sortsamp 3
     /set output myfile
     /set
     /sa
     /sa tables
     /sa indexes
     /sa tabi owner.table1
     /sa tabi owner.table2
     /exit

Once you enter this script, you can use the MPE/iX stream command to
execute the job at a particular time.

Understanding the Internals of Sampling 

SampleIO statistics are generated entirely by SQLMON. In other words, the
data is not obtained by simply reading from some existing table where
these statistics are maintained.  The more often you perform sampling,
the more complete the I/O statistics become.  However, SQLMON uses CPU
time whenever it takes samples.  The larger the number of samples SQLMON
takes, the larger the amount of CPU time it consumes.  Furthermore,
SQLMON needs more CPU time to examine large data buffer pools than small
data buffer pools.

When SAMPLING is ON, SQLMON takes 25 "snapshots" of the data buffer pool
during each refresh cycle.  SQLMON pauses between successive snapshots.
The length of the pause is the refresh rate divided by 25.

As SQLMON takes each snapshot, it keeps track of the pages that are
currently in the data buffer pool and the pages that were in the data
buffer pool during the last snapshot.  The SWAPIN, SWAPOUT, and TOTALIO
counters are then incremented to reflect the changes.

The SWAPIN value represents read I/O. The SWAPIN value will be
incremented by 1 if

   *   A page is in the pool now, but it was not in the pool during the
       last snapshot.

   *   A page was in the pool last time, but it was dirty then and it is
       clean now.  The page was swapped out to disk and then swapped back
       in again.

   *   A page was in the pool last time, but it was occupying a different
       buffer page location than it is now.

The SWAPOUT value represents write I/O. The SWAPOUT value will be
incremented by 1 if

   *   A page is in the pool now, but it was not in the pool during the
       last snapshot.  The page is also dirty (if it is not dirty, the
       SWAPOUT value is not modified).

   *   A page was in the pool last time, but it was clean then and is
       dirty now.

If the page was dirty during the last snapshot, and it is still dirty,
the SWAPOUT column is not modified.  When a dirty page stays in the
buffer pool for a long time without being swapped out, ALLBASE/SQL is
using the information well without paying a high I/O price.

The TOTAL value represents total I/O, and is obtained by adding the
SWAPIN and SWAPOUT values.

MPE/iX 5.0 Documentation