WL#5614: Information schema table for InnoDB buffer pool information
Status: Complete
This is a feature request being mentioned a few times by other developers. One of them is being documented in BUG#12810. There is a recent request, documented in BUG#58059 In summary, this is an implementation to display buffer pool page information for those pages being brought into buffer pool. So user/DBA/developer (with super user privilege) can query to find out detail information in the buffer pool.
Three tables are created for this feature:
1) INFORMATION_SCHEMA.INNODB_BUFFER_PAGE (describes what kind of pages in the
buffer pool)
2) INFORMATION_SCHEMA.INNODB_BUFFER_POOL_STATS (describes general buffer pool
information/statistics)
3)INFORMATION_SCHEMA.INNODB_BUFFER_LRU (describes what kind of pages in the
buffer pool's LRU list)
For INNODB_BUFFER_POOL_STATS, user requires to hold PROCESS privilege (to be
compatible with show engine innodb status) to access the table. For
INNODB_BUFFER_PAGE and INNODB_BUFFER_LRU, user require to hold SUPER user
privilege to access the table.
1) For INFORMATION_SCHEMA.INNODB_BUFFER_PAGE, there are total 19 columns in the
table, describing following information:
POOL_ID /*!< Buffer Pool ID */
BLOCK_ID /*!< Buffer Pool Block ID */
SPACE /*!< Tablespace ID */
PAGE_NUMBER /*!< Page number */
PAGE_TYPE /*!< Page type string */
FLUSH_TYPE /*!< Flush type */
FIX_COUNT /*!< Count of how manyfold this block is bufferfixed */
IS_HASHED /*!< Whether hash index has been built on this page */
NEWEST_MODIFICATION /*!< log sequence number of the youngest modification */
OLDEST_MODIFICATION /*!< log sequence number of the oldest modification */
ACCESS_TIME /*!< Time of first access */
TABLE_NAME /*!< Name of the table the page belongs to */
INDEX_NAME /*!< Name of the index the page belongs to */
NUMBER_RECORDS /*!< Number of records on Page */
DATA_SIZE /*!< Sum of the sizes of the records */
COMPRESSED_SIZE /*!< Compress Page size */
PAGE_STATE /*!< Page state */
IO_FIX /*!< io_fix */
IS_OLD /*!< bpage->old */
FREE_PAGE_CLOCK/*!< bpage->freed_page_clock */
Pool ID is special for 5.5 as we have multiple Buffer Pool now.
For Page types, we have following type strings with each page type:
{"allocated", FIL_PAGE_TYPE_ALLOCATED}, /*!< Freshly allocated page */
{"index", FIL_PAGE_INDEX}, /*!< B-tree node */
{"undo_log", FIL_PAGE_UNDO_LOG}, /*!< Undo log page */
{"inode", FIL_PAGE_INODE}, /*!< Index node */
{"ibuf_free_list", FIL_PAGE_IBUF_FREE_LIST}, /*!< Insert buffer free list */
{"ibuf_bitmap", FIL_PAGE_IBUF_BITMAP}, /*!< Insert buffer bitmap */
{"system", FIL_PAGE_TYPE_SYS}, /*!< System page */
{"trx_system", FIL_PAGE_TYPE_TRX_SYS}, /*!< Transaction system data */
{"file_space_header", FIL_PAGE_TYPE_FSP_HDR}, /*!< File space header */
{"extent_descriptor", FIL_PAGE_TYPE_XDES}, /*!< Extent descriptor page */
{"blob", FIL_PAGE_TYPE_BLOB}, /*!< Uncompressed BLOB page */
{"compressed_blob", FIL_PAGE_TYPE_ZBLOB}, /*!< First compressed BLOB page */
{"compressed_blob2", FIL_PAGE_TYPE_ZBLOB2}, /*!< Subsequent comp BLOB page */
{"unknown", I_S_PAGE_TYPE_UNKNOWN}
A page with valid data should have one of the following states:
* FILE_PAGE (BUF_BLOCK_FILE_PAGE)
* MEMORY (BUF_BLOCK_MEMORY)
* COMPRESSED (BUF_BLOCK_ZIP_PAGE or BUF_BLOCK_ZIP_DIRTY)
Other possible states:
* READY_FOR_USE
* NOT_USED
* REMOVE_HASH
A sample row look like following:
mysql> select * from INNODB_BUFFER_PAGE LIMIT 1 \G
*************************** 1. row ***************************
POOL_ID: 0
BLOCK_ID: 0
SPACE: 0
PAGE_NUMBER: 7
PAGE_TYPE: SYSTEM
FLUSH_TYPE: 1
FIX_COUNT: 0
IS_HASHED: NO
NEWEST_MODIFICATION: 1615278
OLDEST_MODIFICATION: 0
ACCESS_TIME: 1670350787
TABLE_NAME:
INDEX_NAME:
NUMBER_RECORDS: 0
DATA_SIZE: 0
COMPRESSED_SIZE: 0
PAGE_STATE: FILE_PAGE
IO_FIX: IO_NONE
IS_OLD: NO
FREE_PAGE_CLOCK: 0
1 row in set (3.34 sec)
The column for TABLE_NAME, INDEX_NAME and DATA_SIZE are limited to index page only.
2) For INFORMATION_SCHEMA.INNODB_BUFFER_POOL_STATS, there are 33 columns.
Column names are pretty much self explanatory. The result is fully compatible
with "show engine innodb status" output for the buffer pool information
1 POOL_ID
2 POOL_SIZE
3 FREE_BUFFERS
4 DATABASE_PAGES
5 OLD_DATABASE_PAGES
6 MODIFIED_DATABASE_PAGES
7 PENDING_DECOMPRESS
8 PENDING_READS
9 PENDING_FLUSH_LRU
10 PENDING_FLUSH_LIST
11 PAGES_MADE_YOUNG
12 PAGES_NOT_MADE_YOUNG
13 PAGES_MADE_YOUNG_RATE
14 PAGES_MADE_NOT_YOUNG_RATE
15 NUMBER_PAGE_READ
16 NUMBER_PAGE_CREATED
17 NUMBER_PAGE_WRITTEN
18 PAGES_READ_RATE
19 PAGES_CREATE_RATE
20 PAGES_WRITTEN_RATE
21 NUMBER_PAGE_GET
22 HIT_RATE
23 YOUNG_MAKE_PER_THOUSAND_GETS
24 NOT_YOUNG_MAKE_PER_THOUSAND_GETS
25 NUMBER_PAGE_READ_AHEAD
26 NUMBER_READ_AHEAD_EVICTED
28 READ_AHEAD_RATE
29 REAT_AHEAD_EVICTED_RATE
30 LRU_IO_TOTAL
31 LRU_IO_CURRENT
32 UNCOMPRESS_TOTAL
33 UNCOMPRESS_CURRENT
A sample output is shown here, there is only one buffer pool in this server:
mysql> select * from INNODB_BUFFER_POOL_STATS \G
*************************** 1. row ***************************
POOL_ID: 0
POOL_SIZE: 512
FREE_BUFFERS: 85
DATABASE_PAGES: 426
OLD_DATABASE_PAGES: 0
MODIFIED_DATABASE_PAGES: 0
PENDING_DECOMPRESS: 0
PENDING_READS: 0
PENDING_FLUSH_LRU: 0
PENDING_FLUSH_LIST: 0
PAGES_MADE_YOUNG: 0
PAGES_NOT_MADE_YOUNG: 0
PAGES_MADE_YOUNG_RATE: 0
PAGES_MADE_NOT_YOUNG_RATE: 0
NUMBER_PAGES_READ: 426
NUMBER_PAGES_CREATED: 0
NUMBER_PAGES_WRITTEN: 13
PAGES_READ_RATE: 0.999967742976033
PAGES_CREATE_RATE: 0
PAGES_WRITTEN_RATE: 0
NUMBER_PAGES_GET: 0
HIT_RATE: 1000
YOUNG_MAKE_PER_THOUSAND_GETS: 0
NOT_YOUNG_MAKE_PER_THOUSAND_GETS: 0
NUMBER_PAGES_READ_AHEAD: 0
NUMBER_READ_AHEAD_EVICTED: 0
READ_AHEAD_RATE: 0
READ_AHEAD_EVICTED_RATE: 0
LRU_IO_TOTAL: 0
LRU_IO_CURRENT: 0
UNCOMPRESS_TOTAL: 0
UNCOMPRESS_CURRENT: 0
1 row in set (0.00 sec)
There are one row for each buffer pool. And it represents the latest buffer pool
activity as shown.
3) INFORMATION_SCHEMA.INNODB_BUFFER_PAGE_LRU, the column and content is almost
identical to INNODB_BUFFER_PAGE, except the BLOCK_ID is changed to LRU_POS:
mysql> select * from INNODB_BUFFER_PAGE_LRU LIMIT 10 \G
....
*************************** 6. row ***************************
POOL_ID: 0
LRU_POSITION: 5
SPACE: 0
PAGE_NUMBER: 11
PAGE_TYPE: INDEX
FLUSH_TYPE: 0
FIX_COUNT: 0
IS_HASHED: no
NEWEST_MODIFICATION: 0
OLDEST_MODIFICATION: 0
ACCESS_TIME: 2533499412
TABLE_NAME: SYS_INDEXES
INDEX_NAME: CLUST_IND
NUMBER_RECORDS: 70
DATA_SIZE: 4749
COMPRESS_SIZE: 0
COMPRESSED: NO
IO_FIX: IO_NONE
IS_OLD: NO
FREE_PAGE_CLOCK: 0
To start, there is a buffer_page_info_t structure that holds information for
each page as we go through each buffer page in a chunk of buffer in a buffer
pool. The fill of IS table will be done after scanning of such a buffer chunk
with information collected in this buffer_page_info_t array, so we will not need
to hold buffer mutex while filling the IS table:
/** This structure defines information we will fetch from pages
currently cached in the buffer pool. It will be used to poplulate
table INFORMATION_SCHEMA.INNODB_BUFFER_PAGES */
struct buffer_page_info_struct{
ulint pool_id; /*!< Buffer Pool ID */
ulint space_id; /*!< Tablespace ID */
ulint page_num; /*!< Page number */
char* page_type; /*!< Page type string */
ulint flush_type; /*!< Flush type */
ulint fix_count; /*!< Count of how manyfold this block
is bufferfixed */
ulint hashed; /*!< Whether hash index has been
built on this page */
ib_uint64_t newest_mod; /*!< log sequence number of
the youngest modification */
ib_uint64_t oldest_mod; /*!< log sequence number of
the oldest modification */
ulint access_time; /*!< time of first access */
char* table_name; /*!< Name of the table the page
belongs to */
char* index_name; /*!< Name of the index the page
belongs to */
ulint num_recs; /*!< Number of records on Page */
ulint data_size; /*!< sum of the sizes of the records */
};
The scan of buffer pool will be done in following four functions in their
calling sequence:
/*******************************************************************//**
Fill page information for pages in InnoDB buffer pool to the
dynamic table INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
@return 0 on success, 1 on failure */
static
int
i_s_innodb_buffer_page_fill()
/*========================*/
/*******************************************************************//**
This is the function that loops through each buffer pool and fetch page
information to information schema table: I_S_INNODB_BUFFER_PAGE
@return 0 on success, 1 on failure */
static
int
i_s_innodb_buffer_fill_general()
/*******************************************************************//**
This is the function that goes through each block of the buffer pool
and fetch information to three information schema buffer pool page related
tables: I_S_INNODB_BUFFER_POOL_ALL_PAGES, I_S_INNODB_BUFFER_POOL_INDEX_PAGES,
I_S_INNODB_BUFFER_POOL_BLOB_PAGES.
@return 0 on success, 1 on failure */
static
int
i_s_innodb_fill_buffer_pool(
/*========================*/
/*******************************************************************//**
Scans pages in the buffer cache, and cache their general information
to the buffer_page_info_t array. */
static
void
i_s_innodb_fill_general_page_array()
/*===============================*/
The buffer pool mutex is held only in the duration when we go through a chunk of
buffer pool. So buffer content in other chunks could still change as we go
through the buffer pool.
After scanning a buffer chunk,i_s_innodb_fill_block_general() is called to fill
the IS table:
/*******************************************************************//**
Fill Information Schema table INNODB_BUFFER_POOL_ALL_PAGES with information
cached in the buffer_page_info_t array
@return 0 on success, 1 on failure */
static
int
i_s_innodb_fill_block_general()
/*==========================*/
Implementation:
* INFORMATION_SCHEMA.INNODB_BUFFER_PAGE and
INFORMATION_SCHEMA.INNODB_BUFFER_POOL_STATS are implemented in rb://465
* INFORMATION_SCHEMA.INNODB_BUFFER_LRU is implemented in rb://527
Copyright (c) 2000, 2024, Oracle Corporation and/or its affiliates. All rights reserved.