InnoDB INFORMATION_SCHEMA缓冲池表

该InnoDBINFORMATION_SCHEMA缓冲池表提供缓冲关于内页池状态信息和元数据InnoDB缓冲池。

该InnoDBINFORMATION_SCHEMA缓冲池表包括那些列举如下：

mysql> SHOW TABLES FROM INFORMATION_SCHEMA LIKE 'INNODB_BUFFER%';
+-----------------------------------------------	+
| Tables_in_INFORMATION_SCHEMA (INNODB_BUFFER%) 	|
+-----------------------------------------------	+
| INNODB_BUFFER_PAGE_LRU                        	|
| INNODB_BUFFER_PAGE                            	|
| INNODB_BUFFER_POOL_STATS                      	|
+-----------------------------------------------	+

表格总览

• INNODB_BUFFER_PAGE：保存有关InnoDB缓冲池中每个页面的信息。
• INNODB_BUFFER_PAGE_LRU：保存有关InnoDB缓冲池中页面的信息，尤其是LRU列表中的页面顺序，LRU列表确定在缓冲池已满时应从缓冲池中退出哪些页面。该INNODB_BUFFER_PAGE_LRU表与该表具有相同的列INNODB_BUFFER_PAGE，不同之处在于该INNODB_BUFFER_PAGE_LRU表具有一LRU_POSITION列而不是一BLOCK_ID列。
• INNODB_BUFFER_POOL_STATS：提供缓冲池状态信息。许多相同的信息由SHOW ENGINE INNODB STATUS输出提供，或者可以使用InnoDB缓冲池服务器状态变量获得。

警告

查询INNODB_BUFFER_PAGEor INNODB_BUFFER_PAGE_LRU表可能会影响性能。除非您了解性能影响并确定可接受，否则不要在生产系统上查询这些表。为避免影响生产系统的性能，请重现要调查的问题并在测试实例上查询缓冲池统计信息。

示例15.6在INNODB_BUFFER_PAGE表中查询系统数据

该查询通过排除表名称中TABLE_NAME值为NULL或包含斜杠/或句点.（指示用户定义的表）的页面来提供包含系统数据的页面的近似计数。

mysql> SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
       WHERE TABLE_NAME IS NULL OR (INSTR(TABLE_NAME, '/') = 0 AND INSTR(TABLE_NAME, '.') = 0);
+----------	+
| COUNT(*) 	|
+----------	+
|     1516 	|
+----------	+

该查询返回包含系统数据的页面的大约数量，缓冲池页面的总数以及包含系统数据的页面的大约百分比。

mysql> SELECT
       (SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
       WHERE TABLE_NAME IS NULL OR (INSTR(TABLE_NAME, '/') = 0 AND INSTR(TABLE_NAME, '.') = 0)
       ) AS system_pages,
       (
       SELECT COUNT(*)
       FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
       ) AS total_pages,
       (
       SELECT ROUND((system_pages/total_pages) * 100)
       ) AS system_page_percentage;
+--------------	+-------------	+------------------------	+
| system_pages 	| total_pages 	| system_page_percentage 	|
+--------------	+-------------	+------------------------	+
|          295 	|        8192 	|                      4 	|
+--------------	+-------------	+------------------------	+

可以通过查询该PAGE_TYPE值来确定缓冲池中系统数据的类型。例如，以下查询PAGE_TYPE在包含系统数据的页面中返回八个不同的值：

mysql> SELECT DISTINCT PAGE_TYPE FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
       WHERE TABLE_NAME IS NULL OR (INSTR(TABLE_NAME, '/') = 0 AND INSTR(TABLE_NAME, '.') = 0);
+-------------------	+
| PAGE_TYPE         	|
+-------------------	+
| SYSTEM            	|
| IBUF_BITMAP       	|
| UNKNOWN           	|
| FILE_SPACE_HEADER 	|
| INODE             	|
| UNDO_LOG          	|
| ALLOCATED         	|
+-------------------	+

示例15.7在INNODB_BUFFER_PAGE表中查询用户数据

该查询通过对TABLE_NAME值为NOT NULL和的页面进行计数来提供包含用户数据的页面的近似计数NOT LIKE '%INNODB_TABLES%'。

mysql> SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
       WHERE TABLE_NAME IS NOT NULL AND TABLE_NAME NOT LIKE '%INNODB_TABLES%';
+----------	+
| COUNT(*) 	|
+----------	+
|     7897 	|
+----------	+

该查询返回包含用户数据的页面的大约数量，缓冲池页面的总数以及包含用户数据的页面的大约百分比。

mysql> SELECT
       (SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
       WHERE TABLE_NAME IS NOT NULL AND (INSTR(TABLE_NAME, '/') > 0 OR INSTR(TABLE_NAME, '.') > 0)
       ) AS user_pages,
       (
       SELECT COUNT(*)
       FROM information_schema.INNODB_BUFFER_PAGE
       ) AS total_pages,
       (
       SELECT ROUND((user_pages/total_pages) * 100)
       ) AS user_page_percentage;
+------------	+-------------	+----------------------	+
| user_pages 	| total_pages 	| user_page_percentage 	|
+------------	+-------------	+----------------------	+
|       7897 	|        8192 	|                   96 	|
+------------	+-------------	+----------------------	+

此查询使用缓冲池中的页面标识用户定义的表：

mysql> SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
       WHERE TABLE_NAME IS NOT NULL AND (INSTR(TABLE_NAME, '/') > 0 OR INSTR(TABLE_NAME, '.') > 0)
       AND TABLE_NAME NOT LIKE '`mysql`.`innodb_%';
+-------------------------	+
| TABLE_NAME              	|
+-------------------------	+
| `employees`.`salaries`  	|
| `employees`.`employees` 	|
+-------------------------	+

示例15.8在INNODB_BUFFER_PAGE表中查询索引数据

有关索引页的信息，请INDEX_NAME使用索引名称查询列。例如，以下查询返回表emp_no上定义的索引的页面数和页面的总数据大小employees.salaries：

mysql> SELECT INDEX_NAME, COUNT(*) AS Pages,
ROUND(SUM(IF(COMPRESSED_SIZE = 0, @@GLOBAL.innodb_page_size, COMPRESSED_SIZE))/1024/1024)
AS 'Total Data (MB)'
FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
WHERE INDEX_NAME='emp_no' AND TABLE_NAME = '`employees`.`salaries`';
+------------	+-------	+-----------------	+
| INDEX_NAME 	| Pages 	| Total Data (MB) 	|
+------------	+-------	+-----------------	+
| emp_no     	|  1609 	|              25 	|
+------------	+-------	+-----------------	+

该查询返回employees.salaries表上定义的所有索引的页面数和页面的总数据大小：

mysql> SELECT INDEX_NAME, COUNT(*) AS Pages,
       ROUND(SUM(IF(COMPRESSED_SIZE = 0, @@GLOBAL.innodb_page_size, COMPRESSED_SIZE))/1024/1024)
       AS 'Total Data (MB)'
       FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
       WHERE TABLE_NAME = '`employees`.`salaries`'
       GROUP BY INDEX_NAME;
+------------	+-------	+-----------------	+
| INDEX_NAME 	| Pages 	| Total Data (MB) 	|
+------------	+-------	+-----------------	+
| emp_no     	|  1608 	|              25 	|
| PRIMARY    	|  6086 	|              95 	|
+------------	+-------	+-----------------	+

示例15.9在INNODB_BUFFER_PAGE_LRU表中查询LRU_POSITION数据

该INNODB_BUFFER_PAGE_LRU表保存有关InnoDB缓冲池中页面的信息，尤其是它们的排序方式，以确定在缓冲池已满时要从缓冲池中退出哪些页面。该页面的定义与的定义相同INNODB_BUFFER_PAGE，不同之处在于此表具有一LRU_POSITION列而不是一BLOCK_ID列。

此查询计算LRU列表中特定位置在employees.employees表页面所占据的位置数量。

mysql> SELECT COUNT(LRU_POSITION) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE_LRU
       WHERE TABLE_NAME='`employees`.`employees`' AND LRU_POSITION < 3072;
+---------------------	+
| COUNT(LRU_POSITION) 	|
+---------------------	+
|                 548 	|
+---------------------	+

示例15.10查询INNODB_BUFFER_POOL_STATS表

该INNODB_BUFFER_POOL_STATS表提供了SHOW ENGINE INNODB STATUS与InnoDB缓冲池状态变量相似的信息。

mysql> SELECT * FROM information_schema.INNODB_BUFFER_POOL_STATS \G
*************************** 1. row 	***************************
                         POOL_ID	: 0
                       POOL_SIZE	: 8192
                    FREE_BUFFERS	: 1
                  DATABASE_PAGES	: 8173
              OLD_DATABASE_PAGES	: 3014
         MODIFIED_DATABASE_PAGES	: 0
              PENDING_DECOMPRESS	: 0
                   PENDING_READS	: 0
               PENDING_FLUSH_LRU	: 0
              PENDING_FLUSH_LIST	: 0
                PAGES_MADE_YOUNG	: 15907
            PAGES_NOT_MADE_YOUNG	: 3803101
           PAGES_MADE_YOUNG_RATE	: 0
       PAGES_MADE_NOT_YOUNG_RATE	: 0
               NUMBER_PAGES_READ	: 3270
            NUMBER_PAGES_CREATED	: 13176
            NUMBER_PAGES_WRITTEN	: 15109
                 PAGES_READ_RATE	: 0
               PAGES_CREATE_RATE	: 0
              PAGES_WRITTEN_RATE	: 0
                NUMBER_PAGES_GET	: 33069332
                        HIT_RATE	: 0
    YOUNG_MAKE_PER_THOUSAND_GETS	: 0
NOT_YOUNG_MAKE_PER_THOUSAND_GETS	: 0
         NUMBER_PAGES_READ_AHEAD	: 2713
       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

为了进行比较，下面基于相同的数据集显示SHOW ENGINE INNODB STATUS输出和InnoDB缓冲池状态变量输出。

有关SHOW ENGINE INNODB STATUS输出的更多信息，请参见“ InnoDB标准监视器和锁定监视器输出”。

mysql> SHOW ENGINE INNODB STATUS \G
...
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 137428992
Dictionary memory allocated 579084
Buffer pool size   8192
Free buffers       1
Database pages     8173
Old database pages 3014
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 15907, not young 3803101
0.00 youngs/s, 0.00 non-youngs/s
Pages read 3270, created 13176, written 15109
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 8173, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
...

有关状态变量的说明，请参见“服务器状态变量”。

mysql> SHOW STATUS LIKE 'Innodb_buffer%';
+---------------------------------------	+-------------	+
| Variable_name                         	| Value       	|
+---------------------------------------	+-------------	+
| Innodb_buffer_pool_dump_status        	| not started 	|
| Innodb_buffer_pool_load_status        	| not started 	|
| Innodb_buffer_pool_resize_status      	| not started 	|
| Innodb_buffer_pool_pages_data         	| 8173        	|
| Innodb_buffer_pool_bytes_data         	| 133906432   	|
| Innodb_buffer_pool_pages_dirty        	| 0           	|
| Innodb_buffer_pool_bytes_dirty        	| 0           	|
| Innodb_buffer_pool_pages_flushed      	| 15109       	|
| Innodb_buffer_pool_pages_free         	| 1           	|
| Innodb_buffer_pool_pages_misc         	| 18          	|
| Innodb_buffer_pool_pages_total        	| 8192        	|
| Innodb_buffer_pool_read_ahead_rnd     	| 0           	|
| Innodb_buffer_pool_read_ahead         	| 2713        	|
| Innodb_buffer_pool_read_ahead_evicted 	| 0           	|
| Innodb_buffer_pool_read_requests      	| 33069332    	|
| Innodb_buffer_pool_reads              	| 558         	|
| Innodb_buffer_pool_wait_free          	| 0           	|
| Innodb_buffer_pool_write_requests     	| 11985961    	|
+---------------------------------------	+-------------	+