WL#5614: Information schema table for InnoDB buffer pool information

Status: Complete   —   Priority: Medium

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