http://blog.chinaunix.net/uid-10661836-id-4278807.html
在MySQL5.6的Information_Schema引入新的INNODB_METRICS,此表用来监控InnoDB运行是否正常,并且该表包括很多计数器
mysql> select version(); +------------------+ | version() | +------------------+ | 5.6.28-debug-log | +------------------+ 1 row in set (0.00 sec)
此表的结构：
mysql> desc INNODB_METRICS; +-----------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------+--------------+------+-----+---------+-------+ | NAME | varchar(193) | NO | | | | | SUBSYSTEM | varchar(193) | NO | | | | | COUNT | bigint(21) | NO | | 0 | | | MAX_COUNT | bigint(21) | YES | | NULL | | | MIN_COUNT | bigint(21) | YES | | NULL | | | AVG_COUNT | double | YES | | NULL | | | COUNT_RESET | bigint(21) | NO | | 0 | | | MAX_COUNT_RESET | bigint(21) | YES | | NULL | | | MIN_COUNT_RESET | bigint(21) | YES | | NULL | | | AVG_COUNT_RESET | double | YES | | NULL | | | TIME_ENABLED | datetime | YES | | NULL | | | TIME_DISABLED | datetime | YES | | NULL | | | TIME_ELAPSED | bigint(21) | YES | | NULL | | | TIME_RESET | datetime | YES | | NULL | | | STATUS | varchar(193) | NO | | | | | TYPE | varchar(193) | NO | | | | | COMMENT | varchar(193) | NO | | | | +-----------------+--------------+------+-----+---------+-------+ 17 rows in set (0.19 sec)
总计数器:
mysql> select count(*) from INNODB_METRICS; +----------+ | count(*) | +----------+ | 214 | +----------+ 1 row in set (0.19 sec)
默认开启的计数器
mysql> select count(*) from innodb_metrics where status = 'enabled'; +----------+ | count(*) | +----------+ | 63 | +----------+ 1 row in set (0.18 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)
我们以metadata相关的计数器为例,演示开启、关闭、重置.默认此计数器是关闭状态.
mysql> select status, name, subsystem from INNODB_METRICS where status = 'disabled' and subsystem='metadata'; +----------+--------------------------------+-----------+ | status | name | subsystem | +----------+--------------------------------+-----------+ | disabled | metadata_table_handles_opened | metadata | | disabled | metadata_table_handles_closed | metadata | | disabled | metadata_table_reference_count | metadata | +----------+--------------------------------+-----------+ 3 rows in set (0.01 sec)
开启：
mysql> set global innodb_monitor_enable = 'metadata_%'; Query OK, 0 rows affected (0.01 sec)
mysql> select status, name, subsystem from INNODB_METRICS where subsystem='metadata'; +---------+--------------------------------+-----------+ | status | name | subsystem | +---------+--------------------------------+-----------+ | enabled | metadata_table_handles_opened | metadata | | enabled | metadata_table_handles_closed | metadata | | enabled | metadata_table_reference_count | metadata | | enabled | metadata_mem_pool_size | metadata | +---------+--------------------------------+-----------+ 4 rows in set (0.01 sec)
关闭:
mysql> set global innodb_monitor_disable = 'metadata_%'; Query OK, 0 rows affected (0.00 sec)
重置所有metadata的所有值：
mysql> set global innodb_monitor_reset_all = 'metadata_%'; Query OK, 0 rows affected (0.00 sec)
重置计数器值:
mysql> set global innodb_monitor_reset = 'metadata_%'; Query OK, 0 rows affected (0.00 sec)
打开与关闭所有计数器
mysql> set global innodb_monitor_disable = all; Query OK, 0 rows affected (0.18 sec) mysql> set global innodb_monitor_enable = all; Query OK, 0 rows affected (0.00 sec)
计数器既支持单个开启也支持模糊匹配,所以配置起来还是非常灵活多变的.
模块对应的子系统(此翻译为个人理解,如有错误请指出.)
module_metadata (subsystem = metadata) 表计数器开启、关闭
module_lock (subsystem = lock) 锁监控死锁、表锁,锁统计信息等等.
module_buffer (subsystem = buffer) buffer_pool
module_buf_page (subsystem = buffer_page_io) buffer的写操作
module_os (subsystem = os) 操作系统信息
module_trx (subsystem = transaction) 事务信息监控回滚、只读事务等等信息
module_purge (subsystem = purge) purge删除信息
module_compress (subsystem = compression) 表压缩相关信息
module_file (subsystem = file_system) 打开文件数
module_index (subsystem = index) 索引相关信息,统计索引信息、索引分裂等等
module_adaptive_hash (subsystem = adaptive_hash_index) 自适应哈希
module_ibuf_system (subsystem = change_buffer) '这个还不知道'囧
module_srv (subsystem = server) server运行状态信息
module_ddl (subsystem = ddl) ddl信息
module_dml (subsystem = dml) 读、写、更新、删除信息
module_log (subsystem = recovery) flush,同步异步刷日志等等信息
module_icp (subsystem = icp) icp通信? 囧,'这个不知道怎么理解'
这里举个例子以dml_insert为例
mysql> select name,subsystem,status,count from information_schema.innodb_metrics where name like "dml%"; +-------------+-----------+----------+-------+ | name | subsystem | status | count | +-------------+-----------+----------+-------+ | dml_reads | dml | disabled | 0 | | dml_inserts | dml | disabled | 0 | | dml_deletes | dml | disabled | 0 | | dml_updates | dml | disabled | 0 | +-------------+-----------+----------+-------+
mysql> set global innodb_monitor_enable = 'dml_inserts'; Query OK, 0 rows affected (0.00 sec)
mysql> select status, name, subsystem from INNODB_METRICS where status = 'enabled' and subsystem='dml';
+---------+-------------+-----------+
| status | name | subsystem |
+---------+-------------+-----------+
| enabled | dml_inserts | dml |
+---------+-------------+-----------+
1 row in set (0.00 sec)
mysql> select * from information_schema.innodb_metrics where name='dml_inserts'\G; *************************** 1. row *************************** NAME: dml_inserts SUBSYSTEM: dml COUNT: 0 MAX_COUNT: 0 MIN_COUNT: NULL AVG_COUNT: 0 COUNT_RESET: 0 MAX_COUNT_RESET: 0 MIN_COUNT_RESET: NULL AVG_COUNT_RESET: NULL TIME_ENABLED: 2016-06-27 21:30:56 TIME_DISABLED: NULL TIME_ELAPSED: 138 TIME_RESET: NULL STATUS: enabled TYPE: status_counter COMMENT: Number of rows inserted 1 row in set (0.21 sec)
测试：
mysql> use test; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> create table test (id int); Query OK, 0 rows affected (0.24 sec) mysql> insert into test values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10); Query OK, 10 rows affected (0.21 sec) Records: 10 Duplicates: 0 Warnings: 0 mysql> insert into test values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10); Query OK, 10 rows affected (0.18 sec) Records: 10 Duplicates: 0 Warnings: 0 mysql> insert into test values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10); Query OK, 10 rows affected (0.01 sec) Records: 10 Duplicates: 0 Warnings: 0 mysql> insert into test values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10); Query OK, 10 rows affected (0.01 sec) Records: 10 Duplicates: 0 Warnings: 0 mysql> insert into test values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10); Query OK, 10 rows affected (0.01 sec) Records: 10 Duplicates: 0 Warnings: 0 mysql> select * from information_schema.innodb_metrics where name='dml_inserts'\G; *************************** 1. row *************************** NAME: dml_inserts SUBSYSTEM: dml COUNT: 50 MAX_COUNT: 50 MIN_COUNT: NULL AVG_COUNT: 0.04950495049504951 COUNT_RESET: 50 MAX_COUNT_RESET: 50 MIN_COUNT_RESET: NULL AVG_COUNT_RESET: NULL TIME_ENABLED: 2016-06-27 21:30:56 TIME_DISABLED: NULL TIME_ELAPSED: 1010 TIME_RESET: NULL STATUS: enabled TYPE: status_counter COMMENT: Number of rows inserted 1 row in set (0.19 sec)
这时我们重置计数器
mysql> set global innodb_monitor_reset = module_dml; Query OK, 0 rows affected (0.00 sec) mysql> select * from information_schema.innodb_metrics where name='dml_inserts'\G; *************************** 1. row *************************** NAME: dml_inserts SUBSYSTEM: dml COUNT: 50 MAX_COUNT: 50 MIN_COUNT: NULL AVG_COUNT: 0.04428697962798937 COUNT_RESET: 0 MAX_COUNT_RESET: 0 MIN_COUNT_RESET: NULL AVG_COUNT_RESET: 0 TIME_ENABLED: 2016-06-27 21:30:56 TIME_DISABLED: NULL TIME_ELAPSED: 1129 TIME_RESET: 2016-06-27 21:49:34 STATUS: enabled TYPE: status_counter COMMENT: Number of rows inserted 1 row in set (0.01 sec)
当我们重置计数器的时候,只会影响count_reset、max_count_reset.(不知道会不会影响avg_count_reset)
那么如果我们重置所有,那么它会把所有值全部重置
mysql> set global innodb_monitor_disable = module_dml; Query OK, 0 rows affected (0.00 sec) mysql> set global innodb_monitor_reset_all = module_dml; Query OK, 0 rows affected (0.00 sec) mysql> select * from information_schema.innodb_metrics where name='dml_inserts'\G *************************** 1. row *************************** NAME: dml_inserts SUBSYSTEM: dml COUNT: 0 MAX_COUNT: NULL MIN_COUNT: NULL AVG_COUNT: NULL COUNT_RESET: 0 MAX_COUNT_RESET: NULL MIN_COUNT_RESET: NULL AVG_COUNT_RESET: NULL TIME_ENABLED: NULL TIME_DISABLED: NULL TIME_ELAPSED: NULL TIME_RESET: NULL STATUS: disabled TYPE: status_counter COMMENT: Number of rows inserted 1 row in set (0.00 sec)
此文章部分内容参考MySQL官方文档和Oracle英文Blog,地址如下:
https://blogs.oracle.com/mysqlinnodb/entry/get_started_with_innodb_metrics
扫一扫
305
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
