在上一篇《InnoDB 层系统字典表|全方位认识 information_schema》中,我们详细介绍了InnoDB层的系统字典表,本期我们将为大家带来系列第六篇《InnoDB 层锁、事务、统计信息字典表|全方位认识 information_schema》
| INNODB_LOCKS
该表提供查询innodb引擎事务中正在请求的且并未获得的且同时阻塞了其他事务的锁信息(即没有发生不同事务之间的锁等待的锁信息,在这里是查看不到的,例如,只有一个事务时,该事务所加的锁信息无法查看到)
下面是该表中存储的信息内容(即将废弃,8.0中使用performance_schema.data_locks视图代替)
root@localhost : information_schema 12:33:45> select * from innodb_locks\G;
*************************** 1. row ***************************
lock_id: 2476293:115:5:2
lock_trx_id: 2476293
lock_mode: X
lock_type: RECORD
lock_table: `luoxiaobo`.`t_luoxiaobo`
lock_index: PRIMARY
lock_space: 115
lock_page: 5
lock_rec: 2
lock_data: 5
*************************** 2. row ***************************
lock_id: 2476292:115:5:2
lock_trx_id: 2476292
lock_mode: X
lock_type: RECORD
lock_table: `luoxiaobo`.`t_luoxiaobo`
lock_index: PRIMARY
lock_space: 115
lock_page: 5
lock_rec: 2
lock_data: 5
2 rows in set, 1 warning (0.00 sec)
字段含义如下:
| INNODB_TRX
该表提供查询当前在InnoDB引擎中执行的每个事务(不包括只读事务)的信息,包括事务是否正在等待锁、事务什么时间点开始、以及事务正在执行的SQL语句文本信息等(如果有SQL的话)
下面是该表中存储的信息内容
admin@localhost : information_schema 06:26:08> select * from INNODB_TRX limit 1\G;
*************************** 1. row ***************************
trx_id: 8075544
trx_state: RUNNING
trx_started: 2017-09-25 18:24:20
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 4
trx_mysql_thread_id: 9
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 2
trx_lock_structs: 4
trx_lock_memory_bytes: 1136
trx_rows_locked: 2
trx_rows_modified: 0
trx_concurrency_tickets: 0
trx_isolation_level: READ COMMITTED
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 0
trx_is_read_only: 0
trx_autocommit_non_locking: 0
1 row in set (0.00 sec)
字段含义如下:
| INNODB_LOCK_WAITS
该表提供查询关于每个被阻塞的InnoDB事务的锁等待记录,包括发生锁等带事务所请求的锁和阻止该锁请求被授予的锁
下面是该表中存储的信息内容
root@localhost : information_schema 12:38:55> select * from innodb_lock_waits\G;
*************************** 1. row ***************************
requesting_trx_id: 2476294
requested_lock_id: 2476294:115:5:2
blocking_trx_id: 2476292
blocking_lock_id: 2476292:115:5:2
1 row in set, 1 warning (0.00 sec)
字段含义如下:
| INNODB_BUFFER_PAGE
该表提供查询关于buffer pool中的页相关的信息
下面是该表中存储的信息内容
root@localhost : information_schema 11:50:15> 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: 25646701632
OLDEST_MODIFICATION: 0
ACCESS_TIME: 1508663684
TABLE_NAME: NULL
INDEX_NAME: NULL
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 (0.07 sec)
字段含义如下:
PS:该表中记录的信息与show engine innodb status语句和show status like '%buffer_pool%';语句的输出信息中一些页的统计信息类似,他们是基于相同数据集输出的,如下
# show engine语句
root@localhost : (none) 11:15:59> show engine innodb
status\G;
............
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 1099431936
Dictionary memory allocated 106991
Buffer pool size 65528
Free buffers 65165
Database pages 363
Old database pages 0
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 328, created 35, written 39
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: 363, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
............
# show status语句
root@localhost : (none) 11:18:06> show status like '%buffer_pool%';
+---------------------------------------+--------------------------------------------------+
| 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 180204 14:41:11 |
| Innodb_buffer_pool_resize_status | |
| Innodb_buffer_pool_pages_data | 363 |
| Innodb_buffer_pool_bytes_data | 5947392 |
| Innodb_buffer_pool_pages_dirty | 0 |
| Innodb_buffer_pool_bytes_dirty | 0 |
| Innodb_buffer_pool_pages_flushed | 39 |
| Innodb_buffer_pool_pages_free | 65165 |
| Innodb_buffer_pool_pages_misc | 0 |
| Innodb_buffer_pool_pages_total | 65528 |
| Innodb_buffer_pool_read_ahead_rnd | 0 |
| Innodb_buffer_pool_read_ahead | 0 |
| Innodb_buffer_pool_read_ahead_evicted | 0 |
| Innodb_buffer_pool_read_requests | 1718 |
| Innodb_buffer_pool_reads | 329 |
| Innodb_buffer_pool_wait_free | 0 |
| Innodb_buffer_pool_write_requests | 515 |
+---------------------------------------+--------------------------------------------------+
18 rows in set (0.01 sec)
| INNODB_BUFFER_PAGE_LRU
该表提供查询缓冲池中的页面信息,与INNODB_BUFFER_PAGE表不同,INNODB_BUFFER_PAGE_LRU表保存有关innodb buffer pool中的页如何进入LRU链表以及在buffer pool不够用时确定需要从缓冲池中逐出哪些页
下面是该表中存储的信息内容
admin@localhost : information_schema 06:35:36> select * from INNODB_BUFFER_PAGE_LRU where TABLE_NAME='`sbtest`.`sbtest1`' limit 1\G;
*************************** 1. row ***************************
POOL_ID: 0
LRU_POSITION: 192
SPACE: 32
PAGE_NUMBER: 108304
PAGE_TYPE: INDEX
FLUSH_TYPE: 0
FIX_COUNT: 0
IS_HASHED: NO
NEWEST_MODIFICATION: 0
OLDEST_MODIFICATION: 0
ACCESS_TIME: 0
TABLE_NAME: `sbtest`.`sbtest1`
INDEX_NAME: i_c
NUMBER_RECORDS: 124
DATA_SIZE: 16120
COMPRESSED_SIZE: 0
COMPRESSED: NO
IO_FIX: IO_NONE
IS_OLD: NO
FREE_PAGE_CLOCK: 0
1 row in set (0.00 sec)
字段含义如下:
| INNODB_TEMP_TABLE_INFO
该表提供查询有关在InnoDB实例中当前处于活动状态的用户(已建立连接的用户,断开的用户连接对应的临时表会被自动删除)创建的InnoDB临时表的信息。 它不提供查询优化器使用的内部InnoDB临时表的信息查询。INNODB_TEMP_TABLE_INFO表在首次查询时创建,且数据仅存在于内存中。 不会持久化到磁盘
下面是该表中存储的信息内容
admin@localhost : information_schema 06:37:36> select * from INNODB_TEMP_TABLE_INFO;
+----------+-------------+--------+-------+----------------------+---------------+
| TABLE_ID | NAME | N_COLS | SPACE | PER_TABLE_TABLESPACE | IS_COMPRESSED |
+----------+-------------+--------+-------+----------------------+---------------+
| 130 | #sqldd5_b_0 | 4 | 165 | FALSE | FALSE |
+----------+-------------+--------+-------+----------------------+---------------+
1 row in set (0.00 sec)
字段含义如下:
| INNODB_BUFFER_POOL_STATS
该表提供查询一些Innodb buffer pool中的状态信息,该表中记录的信息与SHOW ENGINE INNODB STATUS输出的信息类似相同,另外,innodb buffer pool的一些状态变量也提供了部分相同的值
下面是该表中存储的信息内容
admin@localhost : information_schema 06:50:38> select * from INNODB_BUFFER_POOL_STATS limit 1\G;
*************************** 1. row ***************************
POOL_ID: 0
POOL_SIZE: 32764
FREE_BUFFERS: 32074
DATABASE_PAGES: 690
OLD_DATABASE_PAGES: 235
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: 294
NUMBER_PAGES_CREATED: 396
NUMBER_PAGES_WRITTEN: 1785
PAGES_READ_RATE: 0
PAGES_CREATE_RATE: 0
PAGES_WRITTEN_RATE: 0
NUMBER_PAGES_GET: 10822
HIT_RATE: 0
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)
字段含义如下:
| INNODB_METRICS
该表提供查询InnoDB更为详细细致的性能信息,是对InnoDB的PERFORMANCE_SCHEMA的补充。通过对该表的查询,可用于检查innodb的整体健康状况。也可用于诊断性能瓶颈、资源短缺和应用程序的问题等。
下面是该表中存储的信息内容
admin@localhost : information_schema 06:48:59> select * from INNODB_METRICS where COUNT_RESET > 0 limit 1\G;
*************************** 1. row ***************************
NAME: lock_timeouts
SUBSYSTEM: lock
COUNT: 2
MAX_COUNT: 2
MIN_COUNT: NULL
AVG_COUNT: 0.00005471806516921562
COUNT_RESET: 2
MAX_COUNT_RESET: 2
MIN_COUNT_RESET: NULL
AVG_COUNT_RESET: NULL
TIME_ENABLED: 2017-09-25 08:40:07
TIME_DISABLED: NULL
TIME_ELAPSED: 36551
TIME_RESET: NULL
STATUS: enabled
TYPE: counter
COMMENT: Number of lock timeouts
1 row in set (0.00 sec)
字段含义如下:
本期内容就介绍到这里,本期内容参考链接如下:
| 作者简介
《千金良方——MySQL性能优化金字塔法则》、《数据生态:MySQL复制技术与生产实践》作者之一。熟悉MySQL体系结构,擅长数据库的整体调优,喜好专研开源技术,并热衷于开源技术的推广,在线上线下做过多次公开的数据库专题分享,发表过近100篇数据库相关的研究文章。