Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 38.1Mb
PDF (A4) - 38.1Mb
PDF (RPM) - 33.0Mb
HTML Download (TGZ) - 8.1Mb
HTML Download (Zip) - 8.1Mb
HTML Download (RPM) - 7.0Mb
Man Pages (TGZ) - 132.7Kb
Man Pages (Zip) - 188.9Kb
Info (Gzip) - 3.4Mb
Info (Zip) - 3.4Mb
Excerpts from this Manual

MySQL 8.0 Reference Manual  /  ...  /  Monitoring the Buffer Pool Using the InnoDB Standard Monitor

15.6.3.9 Monitoring the Buffer Pool Using the InnoDB Standard Monitor

InnoDB Standard Monitor output, which can be accessed using SHOW ENGINE INNODB STATUS, provides metrics that pertain to operation of the InnoDB buffer pool. Buffer pool metrics are located in the BUFFER POOL AND MEMORY section of InnoDB Standard Monitor output and appear similar to the following:

----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 2198863872
Dictionary memory allocated 776332
Buffer pool size   131072
Free buffers       124908
Database pages     5720
Old database pages 2071
Modified db pages  910
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 4, not young 0
0.10 youngs/s, 0.00 non-youngs/s
Pages read 197, created 5523, written 5060
0.00 reads/s, 190.89 creates/s, 244.94 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not
0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read
ahead 0.00/s
LRU len: 5720, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]

The following table describes InnoDB buffer pool metrics reported by the InnoDB Standard Monitor.

Note

Per second averages provided in InnoDB Standard Monitor output are based on the elapsed time since InnoDB Standard Monitor output was last printed.

Table 15.2 InnoDB Buffer Pool Metrics

Name Description
Total memory allocated The total memory allocated for the buffer pool in bytes.
Dictionary memory allocated The total memory allocated for the InnoDB data dictionary in bytes.
Buffer pool size The total size in pages allocated to the buffer pool.
Free buffers The total size in pages of the buffer pool free list.
Database pages The total size in pages of the buffer pool LRU list.
Old database pages The total size in pages of the buffer pool old LRU sublist.
Modified db pages The current number of pages modified in the buffer pool.
Pending reads The number of buffer pool pages waiting to be read in to the buffer pool.
Pending writes LRU The number of old dirty pages within the buffer pool to be written from the bottom of the LRU list.
Pending writes flush list The number of buffer pool pages to be flushed during checkpointing.
Pending writes single page The number of pending independent page writes within the buffer pool.
Pages made young The total number of pages made young in the buffer pool LRU list (moved to the head of sublist of new pages).
Pages made not young The total number of pages not made young in the buffer pool LRU list (pages that have remained in the old sublist without being made young).
youngs/s The per second average of accesses to old pages in the buffer pool LRU list that have resulted in making pages young. See the notes that follow this table for more information.
non-youngs/s The per second average of accesses to old pages in the buffer pool LRU list that have resulted in not making pages young. See the notes that follow this table for more information.
Pages read The total number of pages read from the buffer pool.
Pages created The total number of pages created within the buffer pool.
Pages written The total number of pages written from the buffer pool.
reads/s The per second average number of buffer pool page reads per second.
creates/s The per second average number of buffer pool pages created per second.
writes/s The per second average number of buffer pool page writes per second.
Buffer pool hit rate The buffer pool page hit rate for pages read from the buffer pool memory vs from disk storage.
young-making rate The average hit rate at which page accesses have resulted in making pages young. See the notes that follow this table for more information.
not (young-making rate) The average hit rate at which page accesses have not resulted in making pages young. See the notes that follow this table for more information.
Pages read ahead The per second average of read ahead operations.
Pages evicted without access The per second average of the pages evicted without being accessed from the buffer pool.
Random read ahead The per second average of random read ahead operations.
LRU len The total size in pages of the buffer pool LRU list.
unzip_LRU len The total size in pages of the buffer pool unzip_LRU list.
I/O sum The total number of buffer pool LRU list pages accessed, for the last 50 seconds.
I/O cur The total number of buffer pool LRU list pages accessed.
I/O unzip sum The total number of buffer pool unzip_LRU list pages accessed.
I/O unzip cur The total number of buffer pool unzip_LRU list pages accessed.

Notes:

  • The youngs/s metric only relates to old pages. It is based on the number of accesses to pages and not the number of pages. There can be multiple accesses to a given page, all of which are counted. If you see very low youngs/s values when there are no large scans occurring, you might need to reduce the delay time or increase the percentage of the buffer pool used for the old sublist. Increasing the percentage makes the old sublist larger, so pages in that sublist take longer to move to the tail and to be evicted. This increases the likelihood that the pages will be accessed again and be made young.

  • The non-youngs/s metric only relates to old pages. It is based on the number of accesses to pages and not the number of pages. There can be multiple accesses to a given page, all of which are counted. If you do not see a lot of non-youngs/s when you are doing large table scans (and lots of youngs/s), increase the delay value.

  • The young-making rate accounts for accesses to all buffer pool pages, not just accesses to pages in the old sublist. The young-making rate and not rate do not normally add up to the overall buffer pool hit rate. Page hits in the old sublist cause pages to move to the new sublist, but page hits in the new sublist cause pages to move to the head of the list only if they are a certain distance from the head.

  • not (young-making rate) is the average hit rate at which page accesses have not resulted in making pages young due to the delay defined by innodb_old_blocks_time not being met, or due to page hits in the new sublist that did not result in pages being moved to the head. This rate accounts for accesses to all buffer pool pages, not just accesses to pages in the old sublist.

InnoDB buffer pool server status variables and the INNODB_BUFFER_POOL_STATS table provide many of the same buffer pool metrics found in InnoDB Standard Monitor output. For more information about the INNODB_BUFFER_POOL_STATS table, see Example 15.10, “Querying the INNODB_BUFFER_POOL_STATS Table”.


User Comments
User comments in this section are, as the name implies, provided by MySQL users. The MySQL documentation team is not responsible for, nor do they endorse, any of the information provided here.
Sign Up Login You must be logged in to post a comment.