In the first post on db2top’s Bufferpools screen, we saw aggregates across all of the buffer pools in the database. In this second part, you will see what db2top reports for individual buffer pools. For example, db2top reports:
- Individual hit ratios
- The sizes of each buffer pool
- Asynchronous reads and writes due to prefetchers and page cleaners
- Reads and writes of XML storage objects
- Block versus vectored IOs
We will examine the table beneath the gauges section by looking at one screen’s worth of columns at a time:
| Column Name |
Definition |
| Bufferpool Name |
The name of the buffer pool (bp_name). Shown in bold text when active. |
- Delta mode:
- Delta l_reads/s
- Actual mode:
- Actual l_reads
|
- Delta mode:
- The number of requests for pages (data, index, temp data, or temp index) per second from this buffer pool during the last interval (pool_data_l_reads + pool_index_l_reads + pool_temp_data_l_reads + pool_temp_index_l_reads).
- Actual mode:
- The number of requests for pages (data, index, temp data, or temp index) from this buffer pool since the last snapshot reset or database activation (pool_data_l_reads + pool_index_l_reads + pool_temp_data_l_reads + pool_temp_index_l_reads).
|
- Delta mode:
- Delta p_reads/s
- Actual mode:
- Actual p_reads
|
- Delta mode:
- The number of pages per second (data, index, temp data, or temp index) requested from this buffer pool and read from table space containers during the last interval (pool_data_p_reads + pool_index_p_reads + pool_temp_data_p_reads + pool_temp_index_p_reads).
- Actual mode:
- The number of pages (data, index, temp data, or temp index) requested from this buffer pool and read from table space containers since the last snapshot reset or database activation (pool_data_p_reads + pool_index_p_reads + pool_temp_data_p_reads + pool_temp_index_p_reads).
|
| Hit Ratio % |
- Delta mode:
- The percentage of requests for pages (data, index, temp data, or temp index) from this buffer pool that were serviced during the last interval without requiring a physical read from a table space container.
- Actual mode:
- The percentage of requests for pages (data, index, temp data, or temp index) from this buffer pool that were serviced since the last snapshot reset or database activation without requiring a physical read from a table space container.
100% – (pool_data_p_reads + pool_index_p_reads + pool_temp_data_p_reads + pool_temp_index_p_reads) / (pool_data_l_reads + pool_index_l_reads + pool_temp_data_l_reads + pool_temp_index_l_reads)
|
| Async Read% |
- Delta mode:
- The percentage of those pages (data, index, temp data, or temp index) read in from containers into this buffer pool that were read in by asynchronous engine dispatchable units (EDUs) during the interval.
- Actual mode:
- The percentage of those pages (data, index, temp data, or temp index) read in from containers into this buffer pool that were read in by asynchronous engine dispatchable units (EDUs) since the last snapshot or database activation.
(pool_async_data_reads + pool_async_index_reads) / (pool_data_p_reads + pool_index_p_reads + pool_temp_data_p_reads + pool_temp_index_p_reads) × 100%
|
- Delta mode:
- Delta Writes/s
- Actual mode:
- Actual Writes
|
- Delta mode:
- The number of times per second during the interval that one of this buffer pool’s pages was physically written to disk or a write operation took place that did not use the buffer pool (pool_data_writes + pool_index_writes + direct_writes).
- Actual mode:
- The number of times since the last snapshot reset or database activation that one of this buffer pool’s pages was physically written to disk or a write operation took place that did not use the buffer pool (pool_data_writes + pool_index_writes + direct_writes).
|
- Delta mode:
- Delta a_reads/s
- Actual mode:
- Actual a_reads
|
- Delta mode:
- The number of pages (data or index) per second read from containers into this buffer pool by asynchronous engine dispatchable units (EDUs) during the interval (pool_async_data_reads + pool_async_index_reads)
- Actual mode:
- The number of pages (data or index) read from containers into this buffer pool by asynchronous engine dispatchable units (EDUs) since the last snapshot reset or database activation (pool_async_data_reads + pool_async_index_reads)
|
| Column Name |
Definition |
| Async ReadMs |
- Delta mode:
- The average amount of time per read spent reading in data and index pages from the table space containers (physical) by asynchronous engine dispatchable units (EDUs) for this buffer pool during the interval. This value is given in milliseconds.
- Actual mode:
- The average amount of time per read spent reading in data and index pages from the table space containers (physical) by asynchronous engine dispatchable units (EDUs) for this buffer pool since the last snapshot reset or database activation. This value is given in milliseconds.
pool_async_read_time / (pool_async_data_reads + pool_async_index_reads)
|
- Delta mode:
- Delta a_writes/s
- Actual mode:
- Actual a_writes
|
- Delta mode:
- The number of times per second during the interval that one of this buffer pool’s data pages was physically written to disk by either an asynchronous page cleaner or a prefetcher.
- Actual mode:
- The number of times since the last snapshot reset or database activation that one of this buffer pool’s data pages was physically written to disk by either an asynchronous page cleaner or a prefetcher.
pool_async_data_writes + pool_async_index_writes
|
| Async WrtMs |
- Delta mode:
- The average elapsed time per write spent writing data or index pages from the buffer pool to disk by database manager page cleaners during the interval. This value is given in milliseconds.
- Actual mode:
- The average elapsed time per write spent writing data or index pages from the buffer pool to disk by database manager page cleaners since the last snapshot or database activation. This value is given in milliseconds.
pool_async_write_time / (pool_async_data_writes + pool_async_index_writes) |
| Async wrt% |
- Delta mode:
- The percentage of write operations for this buffer pool and direct write operations in which the write was performed asynchronously by database manager page cleaners during the interval.
- Actual mode:
- The percentage of write operations for this buffer pool and direct write operations in which the write was performed asynchronously by database manager page cleaners since the last snapshot reset or database activation.
(pool_async_data_writes + pool_async_index_writes) / (pool_data_writes + pool_index_writes + direct_writes) × 100% |
| # of DBP |
For this bufferpool, the number of partitions for which buffer pool information is being reported. |
| BP Pages |
The current buffer pool size (bp_cur_buffsz). |
- Delta mode:
- File Closed/s
- Actual mode:
- File Closed
|
- Delta mode:
- The total number of database files closed per second for this buffer pool during the interval (files_closed).
- Actual mode:
- The total number of database files closed for this buffer pool since the last snapshot reset or database activation (files_closed).
|
| Column Name |
Definition |
- Delta mode:
- Block IOs/s
- Actual mode:
- Block IOs
|
- Delta mode:
- The number of block I/O requests per second during the interval. More specifically, the number of times per second that DB2® performed sequential prefetching of pages into the block area of the buffer pool during the interval (block_ios).
- Actual mode:
- The number of block I/O requests since the last snapshot reset or database activation. More specifically, the number of times DB2® performed sequential prefetching of pages into the block area of the buffer pool since the last snapshot reset of database activation (block_ios).
|
- Delta mode:
- Pages BlkIOs/s
- Actual mode:
- Pages BlkIOs
|
- Delta mode:
- The average number of pages per block IO request that were read by block I/O into the block area of the buffer pool during the interval (pages_from_block_ios / block_ios).
- Actual mode:
- The average number of pages per block IO request that were read by block I/O into the block area of the buffer pool since the last snapshot reset or database activation (pages_from_block_ios / block_ios).
|
- Delta mode:
- Vectored IOs/s
- Actual mode:
- Vectored IOs
|
- Delta mode:
- The number of vectored I/O requests per second during the interval. More specifically, the number of times per second that DB2® performed sequential prefetching of pages into the page area of the buffer pool during the interval (vectored_ios).
- Actual mode:
- The number of vectored I/O requests since the last snapshot reset or database activation. More specifically, the number of times DB2® performed sequential prefetching of pages into the page area of the buffer pool since the last snapshot reset or database activation (vectored_ios).
|
- Delta mode:
- Pages VctIOs/s
- Actual mode:
- Pages VctIOs
|
- Delta mode:
- The average number of pages per vectored IO request that were read by vectored I/O into the page area of the buffer pool during the interval (pages_from_vectored_ios / vectored_ios).
- Actual mode:
- The average number of pages per vectored IO request that were read by vectored I/O into the page area of the buffer pool since the last snapshot reset or database activationl (pages_from_vectored_ios / vectored_ios).
|
- Delta mode:
- Victim Pages/s
- Actual mode:
- Victim Pages
|
- Delta mode:
- Number of times per second that an agent did not have a preselected victim buffer available for this buffer pool during the interval (pool_no_victim_buffer).
- Actual mode:
- Number of times that an agent did not have a preselected victim buffer available for this buffer pool since the last snapshot reset or database activation (pool_no_victim_buffer).
|
- Delta mode:
- Unread PrefPages/s
- Actual mode:
- Unread PrefPages
|
- Delta mode:
- The number of pages per second that the prefetcher read in during the interval that were never used (unread_prefetch_pages).
- Actual mode:
- The number of pages that the prefetcher read in since the last snapshot reset or database activation that were never used (unread_prefetch_pages).
|
| %Unread PrefPages |
- Delta mode:
- The percentage of pages that the prefetcher read in during the interval that were never used.
- Actual mode:
- The percentage of pages that the prefetcher read in since the last snapshot reset or database activation that were never used.
(unread_prefetch_pages / (pool_async_data_writes + pool_async_index_writes)) × 100% |
| Column Name |
Definition |
- Delta mode:
- XDA l_reads/s
- Actual mode:
- XDA l_reads
|
- Delta mode:
- The number of data pages per second for XML storage objects (XDAs) that have been requested from this buffer pool during the interval (pool_xda_l_reads + pool_temp_xda_l_reads).
- Actual mode:
- The number of data pages for XML storage objects (XDAs) that have been requested from this buffer pool since the last snapshot reset or database activation (pool_xda_l_reads + pool_temp_xda_l_reads).
|
- Delta mode:
- XDA p_reads/s
- Actual mode:
- XDA p_reads
|
- Delta mode:
- The number of data pages read in per second from the table space containers for this buffer pool for XML storage objects (XDAs) during the interval (pool_xda_p_reads + pool_temp_xda_p_reads).
- Actual mode:
- The number of data pages read in from the table space containers for this buffer pool for XML storage objects (XDAs) since the last snapshot reset or database activation (pool_xda_p_reads + pool_temp_xda_p_reads).
|
- Delta mode:
- XDA writes/s
- Actual mode:
- XDA writes
|
- Delta mode:
- The number of times per second that one of this buffer pool’s data pages for an XML storage object (XDA) was physically written to disk during the interval (pool_xda_writes).
- Actual mode:
- The number of times that one of this buffer pool’s data pages for an XML storage object (XDA) was physically written to disk since the last snapshot reset of database activation (pool_xda_writes).
|
- Delta mode:
- Avg_Reads Dir_Reqs/s
- Actual mode:
- Avg_Reads Dir_Reqs
|
- Delta mode:
- The average number of direct read operations, that is, those reads that do not use the buffer pool, per direct read request during the interval (direct_reads / direct_read_reqs).
- Actual mode:
- The average number of direct read operations, that is, those reads that do not use the buffer pool, per direct read request since the last snapshot reset or database activation (direct_reads / direct_read_reqs).
|
| Recent IOs[1 to 30] |
The amount of logical reads to occur for this buffer pool over the specified window of time. A sliding window is used to measure only the last x samples, where x is a number between 1 and 30 that the user can control using the ‘+’ and ‘-’ keys.
pool_data_l_reads + pool_index_l_reads + pool_temp_data_l_reads + pool_temp_index_l_reads |
The bufferpools screen is affected by pressing the ‘k’ key to switch between deltas and actuals, the ‘i’ key to switch between showing only active bufferpools and showing all bufferpools, and the ‘G’ key to switch between global and local snapshots. The ‘X’ key to turn on and off extended mode has no effect.
