转 [MySQL 5.6] Innodb 新的监控表 INNODB_METRICS

https://www.136.la/mysql/show-29241.html

简介这篇文章主要介绍了[MySQL 5.6] Innodb 新的监控表 INNODB_METRICS以及相关的经验技巧，文章约16394字，浏览量252，点赞数2，值得参考！

除了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’;

+———-+

| count(*) |

+———-+

| 148 |

+———-+

1 row in set (0.00 sec)

mysql> select count(*) from innodb_metrics where status = ‘enabled’;

+———-+

| count(*) |

+———-+

| 62 |

+———-+

1 row in set (0.00 sec)

默认打开62个计数器

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	Timestamp of last stop.
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%’;

+———-+———————-+——-+———–+

+———-+———————-+——-+———–+

| 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_%’;