Monitoring and tuning buffer pools by using online commands

The DISPLAY BUFFERPOOL and ALTER BUFFERPOOL commands enable you to monitor and tune buffer pools on line, while Db2 is running, without the overhead of running traces.

To monitor and tune your buffer pools with online commands:

• Use the DISPLAY BUFFERPOOL command to display the current status of one or more active or inactive buffer pools.

  DISPLAY BUFFERPOOL(BP0) DETAIL

  

  +DISPLAY BPOOL(BP2) DETAIL
  DSNB401I + BUFFERPOOL NAME BP2, BUFFERPOOL ID 2, USE COUNT 47
  DSNB402I + BUFFER POOL SIZE             = 4000  
             AUTOSIZE                     = NO
             VPSIZE MINIMUM = 3000   VPSIZE MAXIMUM = 5000
             ALLOCATED      = 4000        TO BE DELETED    = 0
  DSNB404I + THRESHOLDS - 
               VP SEQUENTIAL         = 80   
               DEFERRED WRITE        = 85   VERTICAL DEFERRED WRT = 80,  0
               PARALLEL SEQUENTIAL   = 50   ASSISTING PARALLEL SEQ = 0
  DSNB406I + PGFIX ATTRIBUTE -
              CURRENT  =  NO
              PENDING  =  NO
              PAGE STEALING METHOD -
              CURRENT                   = LRU
              PENDING                   = LRU
  DSNB409I + INCREMENTAL STATISTICS SINCE 14:57:55 JAN 22, yyyy    
  DSNB411I + RANDOM GETPAGE    =   491222 SYNC READ I/O (R) =    18193
             SEQ.   GETPAGE    =  1378500 SYNC READ I/O (S) =        0
             DMTH HIT          =        0 PAGE-INS REQUIRED =   460400
             SEQUENTIAL        =      200 VPSEQT HIT        =        0 
             RECLASSIFY        =        0
  DSNB412I + SEQUENTIAL PREFETCH  
               REQUESTS        =    41800   PREFETCH I/O   =    14473
               PAGES READ      =   444030
  DSNB413I + LIST PREFETCH - 
               REQUESTS        =     9046   PREFETCH I/O    =     2263
               PAGES READ      =     3046
  DSNB414I + DYNAMIC PREFETCH -  
               REQUESTS        =     6680   PREFETCH I/O    =      142
               PAGES READ      =     1333
  DSNB415I + PREFETCH DISABLED -   
               NO BUFFER       =        0   NO READ ENGINE  =        0
  DSNB416I + OVERFLOW RANDOM GETPAGE    = 0
             OVERFLOW SYNC READ I/O (R) = 0
             OVERFLOW SEQ.   GETPAGE    = 0
             OVERFLOW SYNC READ I/O (S) = 0
  
  DSNB420I + SYS PAGE UPDATES  =   220425 SYS PAGES WRITTEN =    35169
             ASYNC WRITE I/O   =     5084 SYNC WRITE I/O    =        3
             PAGE-INS REQUIRED =       45
  DSNB421I + DWT HIT           =        2 VERTICAL DWT HIT  =        0
  DSNB440I + PARALLEL ACTIVITY -
             PARALLEL REQUEST  =        0 DEGRADED PARALLEL   =      0
  DSNB441I + LPL ACTIVITY -
             PAGES ADDED       =        0  
  DSN9022I + DSNB1CMD '+DISPLAY BPOOL' NORMAL COMPLETION

  

  +DISPLAY BPOOL(BP2) DETAIL
  DSNB401I + BUFFERPOOL NAME BP2, BUFFERPOOL ID 2, USE COUNT 47
  DSNB402I + BUFFER POOL SIZE             = 4000  
             AUTOSIZE                     = NO
             VPSIZE MINIMUM = 3000   VPSIZE MAXIMUM = 5000
             ALLOCATED      = 4000        TO BE DELETED    = 0
  DSNB404I + THRESHOLDS - 
               VP SEQUENTIAL         = 80   
               DEFERRED WRITE        = 85   VERTICAL DEFERRED WRT = 80,  0
               PARALLEL SEQUENTIAL   = 50   ASSISTING PARALLEL SEQ = 0
  DSNB406I + PGFIX ATTRIBUTE -
              CURRENT  =  NO
              PENDING  =  NO
              PAGE STEALING METHOD = LRU
  DSNB409I + INCREMENTAL STATISTICS SINCE 14:57:55 JAN 22, yyyy    
  DSNB411I + RANDOM GETPAGE    =   491222 SYNC READ I/O (R) =    18193
             SEQ.   GETPAGE    =  1378500 SYNC READ I/O (S) =        0
             DMTH HIT          =        0 PAGE-INS REQUIRED =   460400
             SEQUENTIAL        =      200 VPSEQT HIT        =        0 
             RECLASSIFY        =        0
  DSNB412I + SEQUENTIAL PREFETCH  
               REQUESTS        =    41800   PREFETCH I/O   =    14473
               PAGES READ      =   444030
  DSNB413I + LIST PREFETCH - 
               REQUESTS        =     9046   PREFETCH I/O    =     2263
               PAGES READ      =     3046
  DSNB414I + DYNAMIC PREFETCH -  
               REQUESTS        =     6680   PREFETCH I/O    =      142
               PAGES READ      =     1333
  DSNB415I + PREFETCH DISABLED -   
               NO BUFFER       =        0   NO READ ENGINE  =        0
  DSNB420I + SYS PAGE UPDATES  =   220425 SYS PAGES WRITTEN =    35169
             ASYNC WRITE I/O   =     5084 SYNC WRITE I/O    =        3
             PAGE-INS REQUIRED =       45
  DSNB421I + DWT HIT           =        2 VERTICAL DWT HIT  =        0
  DSNB440I + PARALLEL ACTIVITY -
             PARALLEL REQUEST  =        0 DEGRADED PARALLEL   =      0
  DSNB441I + LPL ACTIVITY -
             PAGES ADDED       =        0  
  DSN9022I + DSNB1CMD '+DISPLAY BPOOL' NORMAL COMPLETION

  In figure above, find the following fields:

  • SYNC READ I/O (R) shows the number of random synchronous read I/O operations. SYNC READ I/O (S) shows the number of sequential synchronous read I/O operations. Sequential synchronous read I/Os occur when prefetch is disabled.

    To determine the total number of synchronous read I/Os, add SYNC READ I/O (S) and SYNC READ I/O (R).

  • In message DSNB412I, REQUESTS shows the number of times that sequential prefetch was triggered, and PREFETCH I/O shows the number of times that sequential prefetch occurred. PAGES READ shows the number of pages read using sequential prefetch.
  • SYS PAGE UPDATES corresponds to the number of buffer updates.
  • SYS PAGES WRITTEN is the number of pages written to disk.
  • DWT HIT is the number of times the deferred write threshold (DWQT) was reached. This number is workload dependent.
  • VERTICAL DWT HIT is the number of times the vertical deferred write threshold (VDWQT) was reached. This value is per data set, and it is related to the number of asynchronous writes.
• Use the LSTATS option of the DISPLAY BUFFERPOOL command to obtain buffer pool information on a specific data set.
  For example, you can use the LSTATS option to:

  • Provide page count statistics for a certain index. With this information, you could determine whether a query used the index in question, and perhaps drop the index if it was not used.
  • Monitor the response times on a particular data set. If you determine that I/O contention is occurring, you could redistribute the data sets across your available disks.

  This same information is available with IFCID 0199 (statistics class 8).

• Use the ALTER BUFFERPOOL command to change the following attributes:
  • Buffer pool size: VPSIZE
  • Minimum automatic size: VPSIZEMIN
  • Maximum size for automatic size: VPSIZEMAX
  • Frame size: FRAMESIZE
  • Thresholds:
    • VPSEQT
    • VPPSEQT
    • VPXPSEQT
    • DWQT
    • VDWQT
  • Page-stealing algorithm: PGSTEAL
  • Page fix attribute: PGFIX
  • Automatic adjustment attribute: AUTOSIZE

Example

Because the number of synchronous read I/O is relatively high, you might tune the buffer pools by changing the buffer pool specifications. For example, you might increase the buffer pool size to reduce the amount of unnecessary I/O, which would make buffer operations more efficient. To do that, you would enter the following command:

-ALTER BUFFERPOOL(BP0) VPSIZE(nnnn)