RDS for MySQL handler 相关指标说明

在当前 RDS MySQL 控制台上，选择监控告警，引擎监控，handler，我们可以看如下指标：

涉及到 Handler 的指标共有三个，分别为：

1. 读下一行请求数
2. 内部回滚数
3. 内部提交数

注：这三个指标取值均为 "Count/Second"。

下面会介绍下这几个指标在RDS 中是如何取值的。

读下一行请求数取自如下指标Handler_read_rnd_next，即在数据文件中读下一行的请求数。handler::rnd_next() 类似一个游标操作，当被调用时，Handler_read_rnd_next 的值将会增加。

从MySQL 官方文档描述如下：The number of requests to read the next row in the data file[1]。

mysql> show global status like '%Handler_read_rnd_next%';
+-----------------------+----------+
| Variable_name         | Value    |
+-----------------------+----------+
| Handler_read_rnd_next | 42264759 |
+-----------------------+----------+
1 row in set (0.00 sec)

注：如果正进行大量的表扫描，该值较高。通常说明表索引不正确或写入的查询没有利用索引。

内部回滚数取自 Handler_rollback 指标，意味着innodb 存储引擎回滚的次数[2]。如下是一个简单的步骤，当我们调用rollback时，Handler_rollback 指标会增加。

mysql> select * from t1;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> show global status like '%Handler_rollback%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| Handler_rollback | 2     |
+------------------+-------+
1 row in set (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values(1);
Query OK, 1 row affected (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> show global status like '%Handler_rollback%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| Handler_rollback | 4     |
+------------------+-------+
1 row in set (0.00 sec)

mysql> select * from t1;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

这个指标取自 Handler_commit，意味着存储引擎内部提交的次数

mysql> show global status like '%Handler_commit%';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| Handler_commit | 11481 |
+----------------+-------+
1 row in set (0.00 sec)

从实际的测试发现，执行一次 insert 语句，会使Handler_commit 加 2：

mysql> show status like '%Handler_commit%';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| Handler_commit | 2     |
+----------------+-------+
1 row in set (0.01 sec)

mysql> insert into t1 values(1);
Query OK, 1 row affected (0.00 sec)

mysql> show status like '%Handler_commit%';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| Handler_commit | 4     |
+----------------+-------+
1 row in set (0.00 sec)

每次增加 2 是与binlog 有关，测试如下：关掉binlog记录之后，Handler_commit 在 insert 后只加1：

mysql> show status like '%Handler_commit%';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| Handler_commit | 0     |
+----------------+-------+
1 row in set (0.01 sec)

mysql> insert into t1 values(1,'rudong');
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> show status like '%Handler_commit%';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| Handler_commit | 2     |
+----------------+-------+
1 row in set (0.01 sec)

mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)

mysql> flush status;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values(1,'xinrudong');
Query OK, 1 row affected (0.00 sec)

mysql> show status like '%Handler_commit%';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| Handler_commit | 1     |
+----------------+-------+
1 row in set (0.00 sec)

注 select 语句也会使 Handler_commit 的值加 1。

[1] https://dev.mysql.com/doc/refman/5.7/en/server-status-variables.html#statvar_Handler_read_rnd_next

[2] https://dev.mysql.com/doc/refman/5.7/en/server-status-variables.html#statvar_Handler_rollback

如果您有其他问题，欢迎您联系火山引擎技术支持服务