MySQL 监控指标

2015-07-14 Tuesday

为了排查问题，对数据库的监控是必不可少的，在此介绍下 MySQL 中的常用监控指标。

简介

MySQL 有多个分支版本，常见的有 MySQL、Percona、MariaDB，各个版本所对应的监控项也会有些区别，在此仅介绍一些通用的监控项。

通常，监控项的源码是在 mysql/mysqld.cc 文件中定义，其内容如下所示。

SHOW_VAR status_vars[]= {
    {"Aborted_clients",          (char*) &aborted_threads,        SHOW_LONG},
    {"Aborted_connects",         (char*) &aborted_connects,       SHOW_LONG},
    {"Acl",                      (char*) acl_statistics,          SHOW_ARRAY},
    // ... ...
};

监控

对于数据库，通常可以主动监控以下四个与性能及资源利用率相关的指标：

查询吞吐量
查询执行性能
连接情况
缓冲池使用情况

吞吐量

在 MySQL 中有各种针对不同命令的统计，其监控项指标以 Com_XXX 方式命名，其中比较常用的统计项包括了 TPS/QPS。

而 MySQL 与 QPS 相关的包括了三个监控项，分别为 Queries、Questions、Com_select，一般会采用 Com_select 作为采集项；对于 TPS，一般认为是 Com_insert + Com_update + Com_delete 三个统计项的和。

Queries 和 Questioins 区别

如下是 Server Status Variables 中对这两个值的介绍。

Queries : The number of statements executed by the server. This variable
includes statements executed within stored programs, unlike the Questions
variable. It does not count COM_PING or COM_STATISTICS commands.

Questions : The number of statements executed by the server. This includes
only statements sent to the server by clients and not statements executed
within stored programs, unlike the Queries variable. This variable does
not count COM_PING, COM_STATISTICS, COM_STMT_PREPARE, COM_STMT_CLOSE, or
COM_STMT_RESET commands.

也就是说，如果不使用 prepared statements ，那么两者的区别是 Questions 会将存储过程作为一个语句；而 Queries 会统计存储过程中的各个执行的语句。

mysql> SHOW STATUS LIKE "questions";        ← 查看本连接执行的查询SQL数
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Questions     | 2     |
+---------------+-------+
1 row in set (0.00 sec)

mysql> SHOW STATUS LIKE "queries";
+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| Queries       | 21241470 |
+---------------+----------+
1 row in set (0.00 sec)

上述的 questions 是会话级别的，当然可以通过 global 查看全局的变量。

另外，MySQL 会在执行 SQL 之前开始增加上述的统计计数；而当前正在执行的 SQL 数量则可以通过 threads_running 查看。

总结

Questions        已执行的由客户端发出的语句
Com_select       执行SELECT语句的数量，通常也就是QPS
Writes           Com_insert+Com_update+Com_delete，也就是TPS

执行性能

目前，有几种方式可以用来查看 MySQL 的执行性能问题，可以参考如下。

performance_schema

在 events_statements_summary_by_digest 表中保存了许多关键指标，均以微秒为单位，该表会忽略数值、规范化空格与大小写。

如果要以微秒为单位查看各个 database 的平均运行时间，或者出现的错误语句总数，可以通过如下方式查看：

mysql> SELECT schema_name, SUM(count_star) count, \
       ROUND((SUM(sum_timer_wait)/SUM(count_star))/1000000) AS avg_microsec \
       FROM performance_schema.events_statements_summary_by_digest
       WHERE schema_name IS NOT NULL GROUP BY schema_name;

mysql> SELECT schema_name, SUM(sum_errors) err_count \
       FROM performance_schema.events_statements_summary_by_digest \
       WHERE schema_name IS NOT NULL GROUP BY schema_name;

sys

sys 存储引擎默认在 5.7.7 中添加，对于 5.6 可以手动安装，详细可以参考 github - sys schema 。

慢查询

MySQL 提供了一个 Slow_queries 计数器，当查询的执行时间超过 long_query_time 参数指定的值之后，该计数器就会增加，默认设置为 10 秒。

mysql> SHOW VARIABLES LIKE 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)

mysql> SET GLOBAL long_query_time = 5;
Query OK, 0 rows affected (0.00 sec)

需要注意的是，需要关闭会话然后重新连接之后，才能使该参数生效。

总结

Slow_queries         慢查询的数量

其它的一些比较适合发现性能问题后用于排查。

连接情况

监控客户端连接情况相当重要，因为一旦可用连接耗尽，新的客户端连接就会遭到拒绝，MySQL 默认的连接数限制为 151，可通过下面的方法在配置文件中进行设置。

[mysqld]
max_connections = 200

通过如下方法查询和临时设置。

mysql> SHOW VARIABLES LIKE 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+
1 row in set (0.00 sec)

mysql> SET GLOBAL max_connections = 200;
Query OK, 0 rows affected (0.00 sec)

通常 Linux 可以处理 500~1000 个连接，如果 RAM 资源足够，可处理 1W+ 个连接，而 Windows 由于采用 Posix 兼容层，能处理的连接数一般不超过 2048 个。

监控连接使用率

如果采用每个连接一个线程的方式，可以通过 Threads_connected 查看，监控该指标与先前设置的连接限制，可以确保服务器拥有足够的容量处理新的连接。

另外，通过 Threads_running 指标，可以查看正在处理请求的线程，可以用来判断那些连接被占用但是却没有处理任何请求。

如果达到 max_connections 就会拒绝新的连接请求，Connection_errors_max_connections 指标就会开始增加，同时，追踪所有失败连接尝试的 Aborted_connects 指标也会开始增加。

另外，通过 Connection_errors_internal 这个指标，可以用来监控来自服务器本身导致的错误，例如内存不足。

总结

Threads_connected                   已经建立的连接
Threads_running                     正在运行的连接
Connections_errors_internal         由于服务器内部本身导致的错误
Aborted_connects                    尝试与服务器建立连接但是失败的次数
Connections_errors_max_connections  由于到达最大连接数导致的错误

缓冲池使用情况

InnoDB 使用一片内存区域作为缓冲区，用来缓存数据表与索引数据，缓冲区太小可能会导致数据库性能下滑，磁盘 I/O 攀升。

默认值一般是 128MiB，建议将其设置为物理内存的 80%；不过需要注意的是，InnoDB 可能会使用超过缓冲池 10%，如果耗尽内存，则会使用分页，从而使数据库性能受损。

SHOW GLOBAL VARIABLES LIKE "innodb_buffer_pool_chunk_size";
SHOW GLOBAL VARIABLES LIKE "innodb_buffer_pool_instances";

如果 innodb_buffer_pool_chunk_size 查询没有返回结果，则表示在你使用的 MySQL 版本中此参数无法更改，其值为 128 MiB，实际参数为 innodb_buffer_pool_size 。

在服务器启动时，你可以这样设置缓冲池的大小以及实例的数量：

$ mysqld --innodb_buffer_pool_size=8G --innodb_buffer_pool_instances=16

监控指标

Innodb_buffer_pool_read_requests 记录了读取请求的数量，而 Innodb_buffer_pool_reads 记录了缓冲池无法满足，因而只能从磁盘读取的请求数量，也就是说，如果 Innodb_buffer_pool_reads 的值开始增加，意味着数据库性能大有问题。

缓存的使用率和命中率可以通过如下方法计算：

(Innodb_buffer_pool_pages_total - Innodb_buffer_pool_pages_free) /
    Innodb_buffer_pool_pages_total * 100%

(Innodb_buffer_pool_read_requests - Innodb_buffer_pool_reads) /
    Innodb_buffer_pool_read_requests * 100%

如果数据库从磁盘进行大量读取，而缓冲池还有许多闲置空间，这可能是因为缓存最近才清理过，还处于预热阶段。

总结

Innodb_buffer_pool_pages_total          BP中总页面数
Buffer pool utilization                 BP中页面的使用率
Innodb_buffer_pool_read_requests        BP的读请求
Innodb_buffer_pool_reads                需要读取磁盘的请求数

响应时间

“响应时间” (Response Time, RT) 在数据库应用中，尤其是 OLTP 的场景，非常重要，但官方版本中一直没有加上这个统计功能。开始使用的是 tcpdump+mk-query-digest，再后来 tcprstat，很快 Percona 提供了响应时间统计插件。

对于 MariaDB 也存在类似的方式，可以通过如下的方式安装、测试。

----- 1. 查看是否存在插件
$ ls /opt/mariadb/lib/plugin | grep response
query_response_time.so

----- 2. 安装插件
mysql> INSTALL PLUGIN query_response_time_audit SONAME 'query_response_time.so';
Query OK, 0 rows affected (0.00 sec)
mysql> INSTALL PLUGIN query_response_time SONAME 'query_response_time.so';
Query OK, 0 rows affected (0.00 sec)

----- 3. 打开统计功能
mysql> SET GLOBAL query_response_time_stats = 'ON';
Query OK, 0 rows affected (0.00 sec)
mysql> SET GLOBAL query_response_time_flush = 'ON';
Query OK, 0 rows affected (0.00 sec)

----- 4. 查看统计值
mysql> SHOW QUERY_RESPONSE_TIME;
mysql> SELECT * FROM information_schema.query_response_time;
+----------------+-------+----------------+
| Time           | Count | Total          |
+----------------+-------+----------------+
|       0.000001 |     0 |       0.000000 |
|       0.000010 |     0 |       0.000000 |
|       0.000100 |     1 |       0.000089 |
|       0.001000 |    14 |       0.010173 |
|       0.010000 |     0 |       0.000000 |
|       0.100000 |     0 |       0.000000 |
|       1.000000 |     0 |       0.000000 |
|      10.000000 |     0 |       0.000000 |
|     100.000000 |     0 |       0.000000 |
|    1000.000000 |     0 |       0.000000 |
|   10000.000000 |     0 |       0.000000 |
|  100000.000000 |     0 |       0.000000 |
| 1000000.000000 |     0 |       0.000000 |
| TOO LONG       |     0 | TOO LONG       |
+----------------+-------+----------------+
14 rows in set (0.00 sec)

默认的时间统计区间是按照基数 10 增长的，也就是说默认的区间如下：

(0;10^-6], (10^-6;10^-5], (10^-5;10^-4], ..., (10^-1;10^1], (10^1; 10^2], ...

可以通过修改参数 query_response_time_range_base 来缩小时间区间，默认该是 10，实际的统计时间区间如上，可以修改为 2，则区间如下：

(0;2^-19], (2^-19;2^-18], (2^-18;2^-17], ..., (2^-1; 2^1], (2^1; 2^2], ...

第一个区间总是最接近 0.000001 的区间开始；最后区间是到最接近且小于 10000000 处结束。

当然，有些比较从网上摘录的不错 SQL，可以根据自己需求修改。

SELECT
    case TRIM(time)
        when '0.000001' then '<  1us'
        when '0.000010' then '< 10us'
        when '0.000100' then '<100us'
        when '0.001000' then '<  1ms'
        when '0.010000' then '< 10ms'
        when '0.100000' then '<100ms'
        when '1.000000' then '<   1s'
        when '10.000000' then '<  10s'
        when '100.000000' then '<100s'
        else '>100s'
    END as `RT area`,
    CONCAT(ROUND(100*count/query_count,2),"%") as percent,
    count
FROM(
    SELECT
        c.count,
        c.time,
        (
            SELECT SUM(a.count)
            FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME as a
            WHERE a.count != 0
        ) as query_count
    FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME as c
    WHERE c.count > 0
) d;

其它监控项

除了上述的监控项，常见的还有如下常用的方法。

1. 是否可用

可以使用如下几条命令来查看当前 MySQL 服务是否处于运行状态。

mysqladmin -h 127.1 -u root -pnew-password ping
mysqladmin -h 127.1 -u root -pnew-password status

2. 用户管理

严禁对用户的 “host” 部分采用 “%”，除非你想从世界任何一个地方登陆；默认不要使用 GRANT ALL ON . 给用户过度赋权，

mysql> CREATE USER 'user' IDENTIFIED BY 'password';
mysql> GRANT privileges TO 'user'@'host' [WITH GRANT OPTION];
mysql> FLUSH PRIVILEGS;

mysql> GRANT privileges  TO 'user'@'host' IDENTIFIED BY 'password' [WITH GRANT OPTION];
mysql> FLUSH PRIVILEGS;

3. 连接数是否正常

主要查看客户是否有由于没正确关闭连接而死掉的连接，有多少失败的连接，是否有恶意连接等。

----- 查看连接数，root会看到所有，其它用户只能看到自己的连接
mysql> SHOW FULL PROCESSLIST;
$ mysqladmin -h host -u user -p processlist

----- 当前失败连接数
mysql> SHOW GLOBAL STATUS LIKE 'aborted_connects';

----- 由于客户没有正确关闭连接而死掉的连接数
mysql> SHOW GLOBAL STATUS LIKE 'aborted_clients';

----- 最大连接数
mysql> SHOW CLOBAL VARIABLES LIKE 'max_connections';
mysql> SHOW GLOBAL STATUS LIKE 'max_connections';

4. 慢查询日志

慢查询日志对 SQL 调优来说是非常重要的，它记录了超过指定时间 long_query_time 的查询语句；一般只在需要时开启。

MyISAM

在对 MyISAM 存储引擎调优时，很多文章推荐使用 Key_read_requests 和 Key_reads 的比例作为调优的参考，来设置 key_buffer_size 参数的值，而实际上这是错误的，详细可以参考 Why you should ignore MySQL’s key cache hit ratio 这篇文章。

简单介绍如下。

rate VS. ratio

首先需要注意的是，这里有两个重要的概念：”miss rate” 一般是每秒 miss 的数目；”miss ratio” 表示从磁盘读取和从 cache 读取的比例，该参数没有单位。

如下的两个参数可以通过 SHOW GLOBAL STATUS 命令查看，官方文档的解释如下。

Key_read_requests
The number of requests to read a key block from the cache.

Key_reads
The number of physical reads of a key block from disk.

也就是说，两者分别对应了：A) 从缓存读取索引的请求次数；B) 从磁盘读取索引的请求次数。

NOTE: 实际上，Key_reads 统计的并非严格意义上的读磁盘，严格来说应该是发送系统请求的次数。如果文件系统中有缓存的话，实际耗时就是系统调用，并没有磁盘读取的耗时。

很多人认为 Key_reads/Key_read_requests 越小越好，否则就应该增大 key_buffer_size 的设置，但通过计数器的比例来调优有两个问题：

比例并不显示数量的绝对值大小，并不知道总共的请求有多少；
计数器并没有考虑时间因素。

假设有两台机器，其 miss ratio 分别为 23% 和 0.1% ，因为没有读的请求量，很难判断那台机器需要进行调优。比如，前者是 23/100，后者则是 10K/10M 。

参数指标

虽说 Key_read_requests 大比小好，但是对于系统调优而言，更有意义的应该是单位时间内的 Key_reads，通常可以通过 Key_reads / Uptime 计算；该参数可以通过如下命令得到：

$ mysqladmin ext -ri10 | grep Key_reads

其中第一行表示的是系统启动后的总请求，在此可以忽略，下面的每行数值都表示 10 秒内的数据变化，这样就可以大致评估每秒有多少的磁盘请求，而且可以根据你的磁盘性能进行评估是否合理。

NOTE: 命令里的 mysqladmin ext 其实就是 mysqladmin extended-status，你甚至可以简写成 mysqladmin e 。

结论

通过 Key_reads / Uptime 替换 Key_reads / Key_read_requests 。

xtools

----- 标准数据统计，主要是命令次数的统计，transaction
    ins  Com_insert                               (diff)
    upd  Com_update                               (diff)
    del  Com_delete                               (diff)
    sel  Com_select                               (diff)
    tps  Com_insert + Com_update + Com_delete     (diff)

----- 线程处理，threads
    run  Threads_running
    con  Threads_connected
    cre  Threads_created                          (diff)
    cac  Threads_cached

----- 网络字节数，bytes
   recv  Bytes_received                           (diff)
   sent  Bytes_sent                               (diff)

----- buffer pool的缓存命中率
         Innodb_buffer_pool_read_requests         逻辑读总次数
         Innodb_buffer_pool_reads                 物理读总次数
   read  Innodb_buffer_pool_read_requests         每秒读请求(diff)
   hits  (Innodb_buffer_pool_read_requests-Innodb_buffer_pool_reads)/Innodb_buffer_pool_read_requests

----- buffer pool页的状态，innodb bp pages status
   data  Innodb_buffer_pool_pages_data            已经使用缓存页数
   free  Innodb_buffer_pool_pages_free            空闲缓存页数
  dirty  Innodb_buffer_pool_pages_dirty           脏页数目
  flush  Innodb_buffer_pool_pages_flushed         每秒刷新页数(diff)

----- innoDB相关的操作，innodb rows status
    ins  Innodb_rows_inserted                     (diff)
    upd  Innodb_rows_updated                      (diff)
    del  Innodb_rows_deleted                      (diff)
   read  Innodb_rows_read                         (diff)

----- 数据读写请求数，innodb data status
  reads  Innodb_data_reads                        数据读总次数(diff)
 writes  Innodb_data_writes                       数据写的总次数(diff)
   read  Innodb_data_read                         至此已经读的数据量(diff)
written  Innodb_data_written                      至此已经写的数据量(diff)

----- 日志写入磁盘请求，innodb log
 fsyncs  Innodb_os_log_fsyncs                     向日志文件写的总次数(diff)
written  Innodb_os_log_written                    写入日志文件的字节数(diff)

参考

可以参考官方文档 Reference Manual - Server Status Variables，主要介绍各个监控项的含义。

Monitoring MySQL performance metrics，一篇很不错的介绍 MySQL 监控项文章，包括上述的吞吐量、执行性能、链接情况、缓冲池使用情况等。

Why you should ignore MySQL’s key cache hit ratio 这篇文章介绍了 MyISAM 缓存的调优，其中的思想其它参数也可以考虑，也可以参考本地文档。

KISS

Keep It Simple and Stupid.

Seize The Day

And Get Busy Living

Stay hungry

Stay foolish