InnoDB INFORMATION_SCHEMA缓冲池表
InnoDB INFORMATION_SCHEMA缓冲池表提供了缓冲池状态信息和InnoDB缓冲池中页的元数据。这些表是在MySQL 5.6.2中引入的，后来移植到MySQL 5.5 (MySQL 5.5.28)和MySQL 5.1 (MySQL 5.1.66)。
InnoDB INFORMATION_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 | +-----------------------------------------------+ 3 rows in set (0.00 sec)
.INNODB_BUFFER_PAGE:保存InnoDB缓冲池中每一页的信息。
.INNODB_BUFFER_PAGE_LRU:保存了关于InnoDB缓冲池中页的信息，特别是它们在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_PAGE表或INNODB_BUFFER_PAGE_LRU表会引入显著的性能开销。不要在生产系统上查询这些表，除非您意识到您的查询可能产生的性能影响，并确定它是可接受的。为了避免影响性能，在测试实例上重现您想要调查的问题，并在测试实例上运行您的查询。
查询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(*) |
+----------+
| 451428 |
+----------+
1 row in set (1.80 sec)
该查询返回包含系统数据的大约页数、缓冲池页数和包含系统数据的大约百分比的页数。
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 |
+--------------+-------------+------------------------+
| 451428 | 524224 | 86 |
+--------------+-------------+------------------------+
1 row in set (3.35 sec)
通过查询PAGE_TYPE的值，可以确定缓冲池中系统数据的类型。例如，下面的查询返回包含系统数据的页的10个不同的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 |
| INODE |
| IBUF_INDEX |
| INDEX |
| IBUF_BITMAP |
| TRX_SYSTEM |
| FILE_SPACE_HEADER |
| UNKNOWN |
| UNDO_LOG |
| EXTENT_DESCRIPTOR |
+-------------------+
10 rows in set (2.02 sec)
查询INNODB_BUFFER_PAGE表中的用户数据
这个查询通过统计TABLE_NAME值不为NULL且不像’%INNODB_SYS_TABLES%’的页面，提供了包含用户数据的页面的近似计数。
mysql> select count(*) from information_schema.innodb_buffer_page
-> where table_name is not null and table_name not like '%innodb_sys_tables%';
+----------+
| count(*) |
+----------+
| 91067 |
+----------+
1 row in set (1.67 sec)
该查询返回包含用户数据的页面的大致数量、缓冲池页面的总数以及包含用户数据的页面的大致百分比。
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 |
+------------+-------------+----------------------+
| 72796 | 524224 | 14 |
+------------+-------------+----------------------+
1 row in set (3.31 sec)
这个查询将使用缓冲池中的页的用户定义的表标识出来:
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 |
+--------------------------------------------------------+
| `undo`.`opening_lines` |
| `undo`.`FTS_00000000000001d9_00000000000002da_INDEX_3` |
| `mysql`.`help_category` |
| `mysql`.`help_topic` |
| `undo`.`big_table` |
| `undo`.`key_block_size_4` |
| `sys`.`sys_config` |
| `test`.`FTS_000000000000020c_DELETED_CACHE` |
| `undo`.`FTS_00000000000001d9_BEING_DELETED` |
| `undo`.`FTS_00000000000001d9_00000000000002da_INDEX_4` |
| `mysql`.`help_relation` |
| `undo`.`t8` |
| `test`.`FTS_000000000000020c_0000000000000310_INDEX_1` |
| `mysql`.`slave_relay_log_info` |
| `undo`.`FTS_00000000000001d9_BEING_DELETED_CACHE` |
| `undo`.`FTS_00000000000001d9_00000000000002da_INDEX_5` |
| `mysql`.`help_keyword` |
| `undo`.`employees` |
| `undo`.`t13` |
| `test`.`FTS_000000000000020c_0000000000000310_INDEX_2` |
| `mysql`.`time_zone_name` |
| `mysql`.`slave_master_info` |
| `test`.`t7` |
| `test`.`user` |
| `undo`.`FTS_00000000000001d9_CONFIG` |
| `undo`.`FTS_00000000000001d9_00000000000002da_INDEX_6` |
| `undo`.`t9` |
| `undo`.`t14` |
| `test`.`articles` |
| `test`.`FTS_000000000000020c_0000000000000310_INDEX_3` |
| `mysql`.`time_zone` |
| `mysql`.`slave_worker_info` |
| `test`.`t8` |
| `undo`.`FTS_00000000000001d9_DELETED` |
| `undo`.`t1` |
| `undo`.`t10` |
| `test`.`t1` |
| `test`.`FTS_000000000000020c_BEING_DELETED` |
| `test`.`FTS_000000000000020c_0000000000000310_INDEX_4` |
| `mysql`.`plugin` |
| `mysql`.`gtid_executed` |
| `mysql`.`time_zone_transition` |
| `undo`.`FTS_00000000000001d9_DELETED_CACHE` |
| `undo`.`t2` |
| `undo`.`t11` |
| `test`.`parent` |
| `test`.`FTS_000000000000020c_BEING_DELETED_CACHE` |
| `test`.`FTS_000000000000020c_0000000000000310_INDEX_5` |
| `mysql`.`server_cost` |
| `mysql`.`time_zone_transition_type` |
| `mysql`.`servers` |
| `undo`.`FTS_00000000000001d9_00000000000002da_INDEX_1` |
| `undo`.`t7` |
| `test`.`child` |
| `test`.`FTS_000000000000020c_CONFIG` |
| `test`.`FTS_000000000000020c_0000000000000310_INDEX_6` |
| `mysql`.`engine_cost` |
| `mysql`.`time_zone_leap_second` |
| `undo`.`FTS_00000000000001d9_00000000000002da_INDEX_2` |
| `undo`.`t12` |
| `test`.`abc` |
| `test`.`FTS_000000000000020c_DELETED` |
+--------------------------------------------------------+
62 rows in set (1.81 sec)
查询INNODB_BUFFER_PAGE表中的索引数据
有关索引页的信息，请使用索引的名称查询INDEX_NAME列。例如，下面的查询返回在employees.salaries表上定义的emp_no索引的页数和总数据大小：
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 | 44 | 1 |
+------------+-------+-----------------+
1 row in set (1.75 sec)
该查询返回在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 | 44 | 1 |
| idx_salary | 65 | 1 |
| PRIMARY | 6086 | 95 |
+------------+-------+-----------------+
3 rows in set (1.73 sec)
查询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 < 6570;
+---------------------+
| count(lru_position) |
+---------------------+
| 13 |
+---------------------+
1 row in set (0.58 sec)
查询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: 65528
FREE_BUFFERS: 46471
DATABASE_PAGES: 17936
OLD_DATABASE_PAGES: 6640
MODIFIED_DATABASE_PAGES: 0
PENDING_DECOMPRESS: 4480
PENDING_READS: 0
PENDING_FLUSH_LRU: 0
PENDING_FLUSH_LIST: 0
PAGES_MADE_YOUNG: 16823
PAGES_NOT_MADE_YOUNG: 19769
PAGES_MADE_YOUNG_RATE: 0
PAGES_MADE_NOT_YOUNG_RATE: 0
NUMBER_PAGES_READ: 857
NUMBER_PAGES_CREATED: 17087
NUMBER_PAGES_WRITTEN: 24505
PAGES_READ_RATE: 0
PAGES_CREATE_RATE: 0
PAGES_WRITTEN_RATE: 0
NUMBER_PAGES_GET: 3448729
HIT_RATE: 0
YOUNG_MAKE_PER_THOUSAND_GETS: 0
NOT_YOUNG_MAKE_PER_THOUSAND_GETS: 0
NUMBER_PAGES_READ_AHEAD: 768
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
*************************** 2. row ***************************
POOL_ID: 1
POOL_SIZE: 65528
FREE_BUFFERS: 47000
DATABASE_PAGES: 17515
OLD_DATABASE_PAGES: 6485
MODIFIED_DATABASE_PAGES: 0
PENDING_DECOMPRESS: 4051
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: 864
NUMBER_PAGES_CREATED: 16651
NUMBER_PAGES_WRITTEN: 23771
PAGES_READ_RATE: 0
PAGES_CREATE_RATE: 0
PAGES_WRITTEN_RATE: 0
NUMBER_PAGES_GET: 5410983
HIT_RATE: 0
YOUNG_MAKE_PER_THOUSAND_GETS: 0
NOT_YOUNG_MAKE_PER_THOUSAND_GETS: 0
NUMBER_PAGES_READ_AHEAD: 832
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
*************************** 3. row ***************************
POOL_ID: 2
POOL_SIZE: 65528
FREE_BUFFERS: 46659
DATABASE_PAGES: 17733
OLD_DATABASE_PAGES: 6565
MODIFIED_DATABASE_PAGES: 0
PENDING_DECOMPRESS: 4544
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: 755
NUMBER_PAGES_CREATED: 16978
NUMBER_PAGES_WRITTEN: 23908
PAGES_READ_RATE: 0
PAGES_CREATE_RATE: 0
PAGES_WRITTEN_RATE: 0
NUMBER_PAGES_GET: 3462987
HIT_RATE: 0
YOUNG_MAKE_PER_THOUSAND_GETS: 0
NOT_YOUNG_MAKE_PER_THOUSAND_GETS: 0
NUMBER_PAGES_READ_AHEAD: 640
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
*************************** 4. row ***************************
POOL_ID: 3
POOL_SIZE: 65528
FREE_BUFFERS: 46644
DATABASE_PAGES: 17751
OLD_DATABASE_PAGES: 6572
MODIFIED_DATABASE_PAGES: 0
PENDING_DECOMPRESS: 4528
PENDING_READS: 0
PENDING_FLUSH_LRU: 0
PENDING_FLUSH_LIST: 0
PAGES_MADE_YOUNG: 2251
PAGES_NOT_MADE_YOUNG: 17746
PAGES_MADE_YOUNG_RATE: 0
PAGES_MADE_NOT_YOUNG_RATE: 0
NUMBER_PAGES_READ: 802
NUMBER_PAGES_CREATED: 16961
NUMBER_PAGES_WRITTEN: 23848
PAGES_READ_RATE: 0
PAGES_CREATE_RATE: 0
PAGES_WRITTEN_RATE: 0
NUMBER_PAGES_GET: 4188354
HIT_RATE: 0
YOUNG_MAKE_PER_THOUSAND_GETS: 0
NOT_YOUNG_MAKE_PER_THOUSAND_GETS: 0
NUMBER_PAGES_READ_AHEAD: 768
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
*************************** 5. row ***************************
POOL_ID: 4
POOL_SIZE: 65528
FREE_BUFFERS: 46660
DATABASE_PAGES: 17729
OLD_DATABASE_PAGES: 6564
MODIFIED_DATABASE_PAGES: 0
PENDING_DECOMPRESS: 4550
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: 792
NUMBER_PAGES_CREATED: 16937
NUMBER_PAGES_WRITTEN: 24222
PAGES_READ_RATE: 0
PAGES_CREATE_RATE: 0
PAGES_WRITTEN_RATE: 0
NUMBER_PAGES_GET: 4314265
HIT_RATE: 0
YOUNG_MAKE_PER_THOUSAND_GETS: 0
NOT_YOUNG_MAKE_PER_THOUSAND_GETS: 0
NUMBER_PAGES_READ_AHEAD: 768
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
*************************** 6. row ***************************
POOL_ID: 5
POOL_SIZE: 65528
FREE_BUFFERS: 46748
DATABASE_PAGES: 17642
OLD_DATABASE_PAGES: 6532
MODIFIED_DATABASE_PAGES: 0
PENDING_DECOMPRESS: 4548
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: 790
NUMBER_PAGES_CREATED: 16852
NUMBER_PAGES_WRITTEN: 23741
PAGES_READ_RATE: 0
PAGES_CREATE_RATE: 0
PAGES_WRITTEN_RATE: 0
NUMBER_PAGES_GET: 3449085
HIT_RATE: 0
YOUNG_MAKE_PER_THOUSAND_GETS: 0
NOT_YOUNG_MAKE_PER_THOUSAND_GETS: 0
NUMBER_PAGES_READ_AHEAD: 768
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
*************************** 7. row ***************************
POOL_ID: 6
POOL_SIZE: 65528
FREE_BUFFERS: 46821
DATABASE_PAGES: 17571
OLD_DATABASE_PAGES: 6506
MODIFIED_DATABASE_PAGES: 0
PENDING_DECOMPRESS: 4544
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: 727
NUMBER_PAGES_CREATED: 16844
NUMBER_PAGES_WRITTEN: 24005
PAGES_READ_RATE: 0
PAGES_CREATE_RATE: 0
PAGES_WRITTEN_RATE: 0
NUMBER_PAGES_GET: 3852166
HIT_RATE: 0
YOUNG_MAKE_PER_THOUSAND_GETS: 0
NOT_YOUNG_MAKE_PER_THOUSAND_GETS: 0
NUMBER_PAGES_READ_AHEAD: 704
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
*************************** 8. row ***************************
POOL_ID: 7
POOL_SIZE: 65528
FREE_BUFFERS: 46651
DATABASE_PAGES: 17749
OLD_DATABASE_PAGES: 6571
MODIFIED_DATABASE_PAGES: 0
PENDING_DECOMPRESS: 4512
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: 804
NUMBER_PAGES_CREATED: 16945
NUMBER_PAGES_WRITTEN: 23958
PAGES_READ_RATE: 0
PAGES_CREATE_RATE: 0
PAGES_WRITTEN_RATE: 0
NUMBER_PAGES_GET: 3606901
HIT_RATE: 0
YOUNG_MAKE_PER_THOUSAND_GETS: 0
NOT_YOUNG_MAKE_PER_THOUSAND_GETS: 0
NUMBER_PAGES_READ_AHEAD: 768
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
8 rows in set (0.00 sec)
为了比较，SHOW ENGINE INNODB STATUS输出和INNODB缓冲池状态变量输出如下所示，基于相同的数据集。
mysql> show engine innodb status \G ...... ---------------------- BUFFER POOL AND MEMORY ---------------------- Total large memory allocated 8795455488 Dictionary memory allocated 519969 Buffer pool size 524224 Free buffers 373654 Database pages 141626 Old database pages 52435 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 19074, not young 37515 0.00 youngs/s, 0.00 non-youngs/s Pages read 6391, created 135255, written 191958 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: 141626, unzip_LRU len: 35757 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---------------------- INDIVIDUAL BUFFER POOL INFO ---------------------- ---BUFFER POOL 0 Buffer pool size 65528 Free buffers 46471 Database pages 17936 Old database pages 6640 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 16823, not young 19769 0.00 youngs/s, 0.00 non-youngs/s Pages read 857, created 17087, written 24505 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: 17936, unzip_LRU len: 4480 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 1 Buffer pool size 65528 Free buffers 47000 Database pages 17515 Old database pages 6485 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 864, created 16651, written 23771 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: 17515, unzip_LRU len: 4051 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 2 Buffer pool size 65528 Free buffers 46659 Database pages 17733 Old database pages 6565 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 755, created 16978, written 23908 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: 17733, unzip_LRU len: 4544 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 3 Buffer pool size 65528 Free buffers 46644 Database pages 17751 Old database pages 6572 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 2251, not young 17746 0.00 youngs/s, 0.00 non-youngs/s Pages read 802, created 16961, written 23848 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: 17751, unzip_LRU len: 4528 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 4 Buffer pool size 65528 Free buffers 46660 Database pages 17729 Old database pages 6564 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 792, created 16937, written 24222 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: 17729, unzip_LRU len: 4550 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 5 Buffer pool size 65528 Free buffers 46748 Database pages 17642 Old database pages 6532 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 790, created 16852, written 23741 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: 17642, unzip_LRU len: 4548 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 6 Buffer pool size 65528 Free buffers 46821 Database pages 17571 Old database pages 6506 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 727, created 16844, written 24005 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: 17571, unzip_LRU len: 4544 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 7 Buffer pool size 65528 Free buffers 46651 Database pages 17749 Old database pages 6571 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 804, created 16945, written 23958 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: 17749, unzip_LRU len: 4512 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 | Dumping of buffer pool not started | | Innodb_buffer_pool_load_status | Buffer pool(s) load completed at 230510 9:02:54 | | Innodb_buffer_pool_resize_status | | | Innodb_buffer_pool_pages_data | 141626 | | Innodb_buffer_pool_bytes_data | 2466910208 | | Innodb_buffer_pool_pages_dirty | 0 | | Innodb_buffer_pool_bytes_dirty | 0 | | Innodb_buffer_pool_pages_flushed | 154983 | | Innodb_buffer_pool_pages_free | 373654 | | Innodb_buffer_pool_pages_misc | 8944 | | Innodb_buffer_pool_pages_total | 524224 | | Innodb_buffer_pool_read_ahead_rnd | 0 | | Innodb_buffer_pool_read_ahead | 6016 | | Innodb_buffer_pool_read_ahead_evicted | 0 | | Innodb_buffer_pool_read_requests | 31733470 | | Innodb_buffer_pool_reads | 376 | | Innodb_buffer_pool_wait_free | 0 | | Innodb_buffer_pool_write_requests | 15535407 | +---------------------------------------+--------------------------------------------------+ 18 rows in set (0.01 sec)