在MySQL5.6的Information_Schema引入新的监控表,此表用来监控InnoDB运行是否正常,并且该表包括很多计数器,本人当前的MySQL版本为5.6.15,那么我们就来一下INNODB_METRICS.
此表的结构
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.00 sec)
包括的总计数器
mysql> select count(*) from INNODB_METRICS;
| count(*) |
+----------+
| 214 |
+----------+
1 row in set (0.01 sec)
默认开启的计数器
mysql> select count(*) from innodb_metrics where status = 'enabled';
+----------+
| count(*) |
+----------+
| 62 |
+----------+
1 row in set (0.01 sec)
开启、关闭、重置计数器的参数,主要有以下几个
mysql> show variables like '%monitor%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| innodb_monitor_disable | |
| innodb_monitor_enable | |
| innodb_monitor_reset | |
| innodb_monitor_reset_all | |
+--------------------------+-------+
我们以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.00 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.00 sec)
关闭
mysql> set global innodb_monitor_disable = 'metadata_%';
Query OK, 0 rows affected (0.01 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.00 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> 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.01 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: 2014-05-29 14:33:50
TIME_DISABLED: NULL
TIME_ELAPSED: 37
TIME_RESET: NULL
STATUS: enabled
TYPE: status_counter
COMMENT: Number of rows inserted
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> create table test (id int);
Query OK, 0 rows affected (0.23 sec)
mysql> insert into test values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
Query OK, 10 rows affected (0.04 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> insert into test values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
Query OK, 10 rows affected (0.00 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: 80
MAX_COUNT: 80
MIN_COUNT: NULL
AVG_COUNT: 0.9876543209876543
COUNT_RESET: 80
MAX_COUNT_RESET: 80
MIN_COUNT_RESET: NULL
AVG_COUNT_RESET: NULL
TIME_ENABLED: 2014-05-29 14:33:50
TIME_DISABLED: NULL
TIME_ELAPSED: 81
TIME_RESET: NULL
STATUS: enabled
TYPE: status_counter
COMMENT: Number of rows inserted
1 row in set (0.01 sec)
ERROR:
No query specified
这时我们重置计数器
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: 80
MAX_COUNT: 80
MIN_COUNT: NULL
AVG_COUNT: 0.5095541401273885
COUNT_RESET: 0
MAX_COUNT_RESET: 0
MIN_COUNT_RESET: NULL
AVG_COUNT_RESET: 0
TIME_ENABLED: 2014-05-29 14:33:50
TIME_DISABLED: NULL
TIME_ELAPSED: 157
TIME_RESET: 2014-05-29 14:36:10
STATUS: enabled
TYPE: status_counter
COMMENT: Number of rows inserted
1 row in set (0.01 sec)
ERROR:
No query specified
当我们重置计数器的时候,只会影响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
今天先到此.^_^
转载请注明出处.谢谢
阅读(8286) | 评论(0) | 转发(0) |
