除了performance schema外,在mysql 5.6中还提供了一个新的information_schema表来监控innodb的内部运行状态——innodb_metrics;该表维护了一组计数器,用户可以通过这些计数器,来监控innodb内部运行是否健康。当前的mysql5.6.12版本中,共有210个计数器:
mysql> select count(*) from innodb_metrics;
+———-+
| count(*) |
| 210 |
1 row in set (0.00 sec)
mysql> select count(*) from innodb_metrics where status = ‘disabled';
| 148 |
mysql> select count(*) from innodb_metrics where status = ‘enabled';
| 62 |
默认打开62个计数器
这里我们不讨论相关的代码实现,因为其实现很简单,所有的监控项,包括跟这些计数器相关的操作都在文件srv/srv0mon.cc中,独立成模块,外部我们经常看到类似如下代码的,就是插入的计数器:
buf_lru_get_free_block:
monitor_inc(monitor_lru_get_free_search);
如果我们自己要添加新的counter,可以往srv/srv0mon.cc中自行添加。
以下只讨论如何使用该表,以及其包含的内容。
innodb_metrics表包括如下列(摘自官方文档)
column name
description
name
unique name for the counter.
subsystem
the aspect of innodb that the metric applies to. see the list following the table for the corresponding module names to use with the set global syntax.
count
value since the counter is enabled.
max_count
maximum value since the counter is enabled.
min_count
minimum value since the counter is enabled.
avg_count
average value since the counter is enabled.
count_reset
counter value since it was last reset. (the _reset fields act like the lap counter on a stopwatch: you can measure the activity during some time interval, while the cumulative figures are still available in thecount, max_count, and so on fields.)
max_count_reset
maximum counter value since it was last reset.
min_count_reset
minimum counter value since it was last reset.
avg_count_reset
average counter value since it was last reset.
time_enabled
timestamp of last start.
time_disabled
timestamp of last stop.
time_elapsed
elapsed time in seconds since the counter started.
time_reset
status
whether the counter is still running () or stopped ().
type
whether the item is a cumulative counter, or measures the current value of some resource.
comment
additional description.
例如,我们要查询dml的执行量:
mysql> select status, name, count, subsystem from innodb_metrics where name like ‘%dml%';
+———-+———————-+——-+———–+
| status | name | count | subsystem |
| disabled | purge_dml_delay_usec | 0 | purge |
| enabled | dml_reads | 942 | dml |
| enabled | dml_inserts | 0 | dml |
| enabled | dml_deletes | 0 | dml |
| enabled | dml_updates | 913 | dml |
5 rows in set (0.00 sec)
我们可以通过以下几个变量来控制计数器的设置:
mysql> show variables like ‘%monitor%';
+————————–+——-+
| variable_name | value |
| innodb_monitor_disable | |
| innodb_monitor_enable | |
| innodb_monitor_reset | |
| innodb_monitor_reset_all | |
4 rows in set (0.00 sec)
我们以ahi相关的计数器为例,默认情况下他们是关闭的
mysql> select status, name, subsystem from innodb_metrics where status = ‘disabled’ and subsystem like ‘%adaptive_hash_index%';
+———-+——————————————+———————+
| status | name | subsystem |
| disabled | adaptive_hash_searches_btree | adaptive_hash_index |
| disabled | adaptive_hash_pages_added | adaptive_hash_index |
| disabled | adaptive_hash_pages_removed | adaptive_hash_index |
| disabled | adaptive_hash_rows_added | adaptive_hash_index |
| disabled | adaptive_hash_rows_removed | adaptive_hash_index |
| disabled | adaptive_hash_rows_deleted_no_hash_entry | adaptive_hash_index |
| disabled | adaptive_hash_rows_updated | adaptive_hash_index |
7 rows in set (0.00 sec)
打开计数器:
mysql> set global innodb_monitor_enable = ‘adaptive_hash_%';
query ok, 0 rows affected (0.00 sec)
关闭计数器:
mysql> set global innodb_monitor_disable = ‘adaptive_hash_%';
重置ahi所有列的值:
mysql> set global innodb_monitor_reset_all = “adaptive_hash_%”;
只重置counter的值:
mysql> set global innodb_monitor_reset = “adaptive_hash_%”;
根据模块名打开:
mysql> set global innodb_monitor_enable = module_adaptive_hash;
打开所有计数器:
mysql> set global innodb_monitor_enable = all;
关闭所有计数器:
mysql> set global innodb_monitor_disable = all;
我们既可以设置具体的某一个counter,或者通过通配符,或者通过模块名,或者all,来设置counter,配置还是相当灵活的。
模块名与subsystem的对应关系:
模块名
对应subsystem
描述
module_metadata
metadata
表级别的打开、关闭、引用次数等
module_lock
lock
锁系统相关信息,例如死锁次数, 创建/移除/请求的记录锁,包括表锁等统计信息,锁等待/持有时间等等。。
module_buffer
buffer
跟buffer pool相关的操作,
module_buf_page
buffer_page_io
buffer pool做写操作的计数
module_os
os
os层的数据读写等信息
module_trx
transaction
事务量统计,例如只读事务,写事务,回滚事务,活跃事务,事务undo信息等。
module_purge
purge
purge操作统计,例如purge 标记删除的记录树,purge undo日志的page数等
module_compress
compression
压缩表相关统计信息,例如压缩,解压,增加/减少padding的次数等。
module_file
file_system
只有一个counter:file_num_open_files 表示打开的文件数
module_index
index
索引分裂和合并的次数
module_adaptive_hash
adaptive_hash_index
自适应hash相关操作
module_ibuf_system
change_buffer
change buffer相关操作统计
module_srv
server
实例内部运行状态,例如bp size , page size ,master线程信息,spin 统计,读写锁信息,写double write buffer的计数
module_ddl
ddl
ddl统计
module_dml
dml
读/插入/删除/更新的次数
module_log
recovery
跟redo log相关的信息,例如reodo checkpoinr信息,flush 信息,同步/异步刷日志点,日志写入量,pending的日志请求等。。
module_icp
icp
在innodb层的index condition pushdown的相关信息