Monitoring Buffer-Pool Activity

You can obtain statistics that relate to buffer availability as well as information on the buffers in each LRU queue.

The statistical information includes the number of times that the database server attempted to exceed the maximum number of buffers and the number of writes to disk (categorized by the event that caused the buffers to flush). These statistics help you determine if the number of buffers is appropriate. For information on tuning database server buffers, see your IBM Informix: Performance Guide.

Information on the buffers in each LRU queue consists of the length of the queue and the percentage of the buffers in the queue that have been modified.

Using Command-Line Utilities

You can use the onstat command-line utility to obtain information on buffer-pool activity. For more information about the onstat options, refer to the utilities chapter of the IBM Informix: Administrator's Reference.

onstat -p

The onstat -p output contains a statistic (ovbuff) that indicates the number of times the database server attempted to exceed the maximum number of shared buffers specified by the BUFFERS parameter in the ONCONFIG file. Figure 48 shows onstat -p output, including the ovbuff field.

...

ovtbls   ovlock   ovuserthread ovbuff   usercpu  syscpu   numckpts flushes
0        0        0            0        13.55    13.02    5        18
...

onstat -F

Execute onstat -F to obtain a count by write type of the writes performed. (For an explanation of the different write types, see Describing Flushing Activity.) Figure 49 shows an example of the output. This information tells you when and how the buffers are flushed.

The onstat -F command displays totals for the following write types:

• Foreground write
• LRU write
• Chunk write

The onstat -F command also lists the following information about the page cleaners:

• Page-cleaner number
• Page-cleaner shared-memory address
• Current state of the page cleaner
• LRU queue to which the page cleaner was assigned

Figure 49 shows an example of the onstat -F output.

...

Fg Writes     LRU Writes    Chunk Writes
0             146           140

address  flusher  state    data
8067c4   0        I        0        = 0X0
      states: Exit Idle Chunk Lru

onstat -R

Execute onstat -R to obtain information about the number of buffers in each LRU queue and the number and percentage of the buffers that are modified or free. Figure 50 shows an example of onstat -R output.

8 buffer LRU queue pairs
# f/m   length   % of   pair total
 0 f         3    37.5%       8
 1 m         5    55.6%
 2 f         5    45.5%      11
 3 m         6    54.5%
 4 f         2    18.2%      11
 5 m         9    81.8%
 6 f         5    50.0%      10
 7 m         5    55.6%
 8 F         5    50.0%      10
 9 m         5    45.5%
10 f         0     0.0%      10
11 m        10   100.0%
12 f         1    11.1%       9
13 m         8    88.9%
14 f         2    28.6%       7
15 m         5    71.4%
53 dirty, 76 queued, 80 total, 128 hash buckets, 2048 buffer size
start clean at 60% (of pair total) dirty, or 6 buffs dirty, stop at 50%

Using SMI Tables

Query the sysprofile table to obtain the statistics on write types that are held in the following rows.

Row

Description

fgwrites

Number of foreground writes

lruwrites

Number of LRU writes

chunkwrites

Number of chunk writes