onstat -g buf command: Print buffer pool profile information

Use the onstat -g buf command to show profile information for each buffer pool.

Figure 1: Syntax:

Example output

Figure 2: onstat -g buf command output


Profile

Buffer pool page size: 2048
dskreads   pagreads   bufreads   %cached dskwrits   pagwrits   bufwrits   %cached
53056      118251     982617385  99.99   10640768   10915965   15968877    33.37 

bufwrits_sinceckpt  bufwaits   ovbuff     flushes   
17477               863        0          874       

Fg Writes     LRU Writes    Avg. LRU Time   Chunk Writes 
0             0             NaN             110767       

Fast Cache Stats
gets       hits       %hits   puts      
48314472   47220455   97.74   45757549

The output of the onstat -g buf command varies slightly depending on whether the BUFFERPOOL configuration parameter setting contains the memory field or the buffers field. The output for the memory setting is shown. The output for the buffers setting contains the max extends and next buffers fields instead of the max memory and next memory fields.

Figure 3: onstat -g buf output for the memory setting

Profile

Buffer pool page size: 2048
dskreads   pagreads   bufreads   %cached dskwrits   pagwrits   bufwrits   %cached
1190       1773       661359     99.82   16863      83049      185805     90.92  
bufwrits_sinceckpt  bufwaits   ovbuff     flushes   
11243               115        0          42        

Fg Writes     LRU Writes    Avg. LRU Time Chunk Writes  Total Mem
0             0             nan           10883         32Mb    

                                    cache                
# extends  max memory  next memory  hit ratio  last      
0          128Mb       32Mb         90         11:31:17  

Bufferpool Segments
id segment      size     # buffs  
0  0x449f0000   32Mb     13025    

----------------------------------

Buffer pool page size: 8192
dskreads   pagreads   bufreads   %cached dskwrits   pagwrits   bufwrits   %cached
0          0          11         100.00  4          16         4          0.00   
bufwrits_sinceckpt  bufwaits   ovbuff     flushes   
0                   0          0          1         

Fg Writes     LRU Writes    Avg. LRU Time Chunk Writes  Total Mem
0             0             nan           4             128Mb   

                                    cache                
# extends  max memory  next memory  hit ratio  last      
0          1280Mb      128Mb        90         11:31:41  

Bufferpool Segments
id segment      size     # buffs  
0  0x4928e000   128Mb    14988    

----------------------------------

Fast Cache Stats
gets       hits       %hits   puts      
246854     244407     99.01   111147

Output description

Buffer pool page size: The number of bytes in a page in the buffer pool
dskreads: The number of disk read operations that are performed to bring pages into this buffer pool. Each read operation reads one or more pages.
pagreads: The number of pages that are read from disk to this buffer pool.
bufreads: The number of times a memory image for a page was read from this buffer pool.
%cached: The percentage of page reads for this buffer pool that were satisfied by a cached page image (rather than having to perform a disk read). Computed as (bufreads - dskreads) / bufreads x 100. Higher percentages indicate better caching performance.
dskwrits: The number of disk write operations that are performed to write changed pages from this buffer pool back to disk. Each write operation writes one or more pages.
pagwrits: The number of pages that are written to disk from this buffer pool.
bufwrits: The number of times a memory image of a page was written to in this buffer pool.
%cached: The percentage of page writes for this buffer pool that were satisfied by a cached page image (rather than having to perform a disk write). Computed as (bufwrits - dskwrits) / bufwrits x 100.
bufwrits_sinceckpt: The number of times a memory image of a page was written to in this buffer pool since the last checkpoint.
bufwaits: The number of times a thread had to wait for a lock on a buffer in this buffer pool. Higher numbers indicate more contention among multiple threads for mutually incompatible locks on the same pages.
ovbuff: The number of times a changed buffer from this buffer pool was written to disk specifically to create a free buffer to read another requested page. If the ovbuff value is high, the buffer pool might not be large enough to hold the working set of pages that are needed by applications. An insufficient buffer pool can lead to performance degradation.
flushes: The number of times the server flushed all dirty buffers at once in the buffer pool. Mass flushing can occur for various reasons, including as part of checkpoint processing or if the buffer pool is running out of clean buffers despite normal LRU cleaning activity.
Fg Writes: Number of changed buffers from this buffer pool that were written to disk by a non-I/O flusher thread that was accessing the buffer. This number is a superset of the value of the ovbuff field. In addition to the writes to service page faults that are counted in the ovbuff field, this value also includes foreground writes to maintain the consistency of database logs and reserved pages to ensure a correct recovery.
LRU Writes: The number of changed buffers from this buffer pool that were written to disk by an LRU cleaner thread. LRU cleaners are activated if the buffer pool exceeds the value that is specified in the lru_max_dirty field of the BUFFERPOOL configuration parameter or if foreground writes occur due to buffer pool overflows.
Avg. LRU Time: The average amount of time that is taken by an LRU cleaner thread to clean a single LRU chain.
Chunk Writes: The number of changed buffers that were written to disk by a chunk cleaning operation. Chunk cleaning writes out all changed buffers of a chunk that are in the buffer pool. Chunk cleaning is done to clean many buffers quickly, such as during checkpoint processing and fast recovery.
Total Mem: The size of the buffer pool.
# extends: The number of times that the buffer pool was extended.
max memory (memory setting): The target maximum size of the buffer pool. The actual size of the buffer pool can exceed this value, but not more than the size of one segment.
max extends (buffers setting): The maximum number of times that the buffer pool can be extended. (This field is not shown in the example output.)
next memory (memory setting): The size of the next extension of the buffer pool.
next buffers (buffers setting): The number of buffers for the next extension of the buffer pool. (This field is not shown in the example output.)
cache hit ratio: The read cache hit ratio below which the buffer pool is extended.
last: The time of the last extension of the buffer pool.
id: The ID of the buffer pool segment.
segment: The internal address of the buffer pool segment.
size: The size of the buffer pool segment.
# buffs: The number of buffers in the buffer pool segment.
Fast Cache Stats: Statistics for the fast cache, which is a type of cache that reduces the time that is needed for accessing the buffer pool.
gets: The number of times the server looked for a buffer in the fast cache.
hits: The number of times that the server found the buffer it was searching for in the fast cache.
%hits: The percentage of hits, which is hits*100/gets.
puts: The number of times that the server inserted buffers inserted into the fast cache.