Information Schema INNODB_BUFFER_POOL_STATS Table

The Information Schema INNODB_BUFFER_POOL_STATS table contains information about pages in the buffer pool, similar to what is returned with the SHOW ENGINE INNODB STATUS statement.

The PROCESS privilege is required to view the table.

It has the following columns:

ColumnDescription
POOL_IDID of the buffer pool instance.
POOL_SIZESize in pages of the buffer pool.
FREE_BUFFERSNumber of free pages in the buffer pool.
DATABASE_PAGESTotal number of pages in the buffer pool.
OLD_DATABASE_PAGESNumber of pages in the old sublist.
MODIFIED_DATABASE_PAGESNumber of dirty pages.
PENDING_DECOMPRESSNumber of pages pending decompression.
PENDING_READSPending buffer pool level reads.
PENDING_FLUSH_LRUNumber of pages in the LRU pending flush.
PENDING_FLUSH_LISTNumber of pages in the flush list pending flush.
PAGES_MADE_YOUNGPages moved from the old sublist to the new sublist.
PAGES_NOT_MADE_YOUNGPages that have remained in the old sublist without moving to the new sublist.
PAGES_MADE_YOUNG_RATEHits that cause blocks to move to the top of the new sublist.
PAGES_MADE_NOT_YOUNG_RATEHits that do not cause blocks to move to the top of the new sublist due to the innodb_old_blocks delay not being met.
NUMBER_PAGES_READNumber of pages read.
NUMBER_PAGES_CREATEDNumber of pages created.
NUMBER_PAGES_WRITTENNumber of pages written.
PAGES_READ_RATENumber of pages read since the last printout divided by the time elapsed, giving pages read per second.
PAGES_CREATE_RATENumber of pages created since the last printout divided by the time elapsed, giving pages created per second.
PAGES_WRITTEN_RATENumber of pages written since the last printout divided by the time elapsed, giving pages written per second.
NUMBER_PAGES_GETNumber of logical read requests.
HIT_RATEBuffer pool hit rate.
YOUNG_MAKE_PER_THOUSAND_GETSFor every 1000 gets, the number of pages made young.
NOT_YOUNG_MAKE_PER_THOUSAND_GETSFor every 1000 gets, the number of pages not made young.
NUMBER_PAGES_READ_AHEADNumber of pages read ahead.
NUMBER_READ_AHEAD_EVICTEDNumber of pages read ahead by the read-ahead thread that were later evicted without being accessed by any queries.
READ_AHEAD_RATEPages read ahead since the last printout divided by the time elapsed, giving read-ahead rate per second.
READ_AHEAD_EVICTED_RATERead-ahead pages not accessed since the last printout divided by time elapsed, giving the number of read-ahead pages evicted without access per second.
LRU_IO_TOTALTotal least-recently used I/O.
LRU_IO_CURRENTLeast-recently used I/O for the current interval.
UNCOMPRESS_TOTALTotal number of pages decompressed.
UNCOMPRESS_CURRENTNumber of pages decompressed in the current interval

Examples

DESC information_schema.innodb_buffer_pool_stats;
+----------------------------------+---------------------+------+-----+---------+-------+
| Field                            | Type                | Null | Key | Default | Extra |
+----------------------------------+---------------------+------+-----+---------+-------+
| POOL_ID                          | bigint(21) unsigned | NO   |     | 0       |       |
| POOL_SIZE                        | bigint(21) unsigned | NO   |     | 0       |       |
| FREE_BUFFERS                     | bigint(21) unsigned | NO   |     | 0       |       |
| DATABASE_PAGES                   | bigint(21) unsigned | NO   |     | 0       |       |
| OLD_DATABASE_PAGES               | bigint(21) unsigned | NO   |     | 0       |       |
| MODIFIED_DATABASE_PAGES          | bigint(21) unsigned | NO   |     | 0       |       |
| PENDING_DECOMPRESS               | bigint(21) unsigned | NO   |     | 0       |       |
| PENDING_READS                    | bigint(21) unsigned | NO   |     | 0       |       |
| PENDING_FLUSH_LRU                | bigint(21) unsigned | NO   |     | 0       |       |
| PENDING_FLUSH_LIST               | bigint(21) unsigned | NO   |     | 0       |       |
| PAGES_MADE_YOUNG                 | bigint(21) unsigned | NO   |     | 0       |       |
| PAGES_NOT_MADE_YOUNG             | bigint(21) unsigned | NO   |     | 0       |       |
| PAGES_MADE_YOUNG_RATE            | double              | NO   |     | 0       |       |
| PAGES_MADE_NOT_YOUNG_RATE        | double              | NO   |     | 0       |       |
| NUMBER_PAGES_READ                | bigint(21) unsigned | NO   |     | 0       |       |
| NUMBER_PAGES_CREATED             | bigint(21) unsigned | NO   |     | 0       |       |
| NUMBER_PAGES_WRITTEN             | bigint(21) unsigned | NO   |     | 0       |       |
| PAGES_READ_RATE                  | double              | NO   |     | 0       |       |
| PAGES_CREATE_RATE                | double              | NO   |     | 0       |       |
| PAGES_WRITTEN_RATE               | double              | NO   |     | 0       |       |
| NUMBER_PAGES_GET                 | bigint(21) unsigned | NO   |     | 0       |       |
| HIT_RATE                         | bigint(21) unsigned | NO   |     | 0       |       |
| YOUNG_MAKE_PER_THOUSAND_GETS     | bigint(21) unsigned | NO   |     | 0       |       |
| NOT_YOUNG_MAKE_PER_THOUSAND_GETS | bigint(21) unsigned | NO   |     | 0       |       |
| NUMBER_PAGES_READ_AHEAD          | bigint(21) unsigned | NO   |     | 0       |       |
| NUMBER_READ_AHEAD_EVICTED        | bigint(21) unsigned | NO   |     | 0       |       |
| READ_AHEAD_RATE                  | double              | NO   |     | 0       |       |
| READ_AHEAD_EVICTED_RATE          | double              | NO   |     | 0       |       |
| LRU_IO_TOTAL                     | bigint(21) unsigned | NO   |     | 0       |       |
| LRU_IO_CURRENT                   | bigint(21) unsigned | NO   |     | 0       |       |
| UNCOMPRESS_TOTAL                 | bigint(21) unsigned | NO   |     | 0       |       |
| UNCOMPRESS_CURRENT               | bigint(21) unsigned | NO   |     | 0       |       |
+----------------------------------+---------------------+------+-----+---------+-------+

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.