MySQL可用性指标

作者: mysia | 来源:发表于2019-04-03 17:40 被阅读53次

1. 高可用性

高可用性(High Availability简称HA)多被定义为IT系统的运营综合指标，其体现形式就是一个多个九的百分数，表征IT系统运营的稳定可靠程度，越靠近100%，就表明系统约稳定可靠。

最直接的解释就是表明1年时间内允许中断服务(运营)的时间，具体的算法如下：
T=365 * 24 * 60 * (1-HA) 单位：分钟

2. 监控指标

2.1 数据安全

数据安全是第一位的，DBA的首要职责必须保证不丢数据。

这有3方面的含义：

在人为误操作的时候（update,insert,delete,drop,alter)，能够恢复数据到正确的状态；
在机房，硬件故障或者操作系统，数据库软件故障的时候，能够恢复数据到正确的状态；
不丢事务，保证已经入库的数据能够被正确的查询到；

另外，还要注意到需要保证主从数据库的一致性，否则读写分离的情况下其实在用户看来仍然丢失了数据。
对于1，主要靠备份来保证，因为复制可以容灾，却不可以容错（延迟备份在一定程度可以）。
对于2，可能用备份来恢复，也可能直接进行主库或者从库的切换来恢复服务。
对于3，交易类系统的要求会非常高，采用最高安全级别的数据库软硬件设置以及冗余设备，目标是不丢任何1个事务，因为即使1个事务也可能造成大量金钱的损失，同时造成企业信誉的下降。

2.1.1 可测量指标

RPO（Recovery Point Object）：是指业务系统所允许的在灾难过程中的最大数据丢失量，用来衡量容灾系统的数据冗余备份能力。

RPO
RTO（Recovery Time Object）：是指信息系统从灾难状态恢复到可运行状态所需的时间，用来衡量容灾系统的业务恢复能力。

RTO

RPO说明了备份的可靠性和完整性，RTO说明了恢复的可靠性与速度。由于MySQL社区版并不提供热备工具，因此MySQL的备份还原平台是DBA的首要工作。

我国的国家标准《GB20988-2007-T 信息安全技术信息系统灾难恢复规范》对灾备数据中心根据RPO与RTO两项指标分成了6个相应的等级，如下所示：

国标

2.2 无故障（停机）时间

运维和开发不一样，开发最重要的是保证一定效率的情况下实现功能，同时程序Bug少。运维讲的是提供稳定服务的时间。用术语来说就是几个9，具体含义就是年度不可服务（不管是主动的还是被动的）时间除以全年时间，百分比越高越好。具体和时间的换算关系见下表：

描述	可用率	年度停机时间
基本可用	99%	87.6小时
较高可用	99.9%	8.8小时
有自动恢复能力的高可用	99.99%	53分钟
极高可用	99.999%	5分钟

根据墨菲定理（If anything can go wrong,it will）的推论，世界上没有 100% 可靠的 Web站点(除非不运行)。运维的最高境界当然就是5个9了，一年停机时间只有5分钟，这是相当难以达到的目标，往往一个大故障就会把全年的停机时间用完。

业界网站的可用性都是多少？ Twitter， 2018 年前四个月的可用性只有 98.72%，有 37小时 16分钟不能提供服务，连2个9 都达不到，甚至还没达到”基本可用”状态。电子商务巨头 eBay 2017 年的可用性是 99.94%，考虑到 eBay 站点的规模与应用的复杂程度，这是个很不错可用性指标了。

多数情况下，网站可用性会是 SLA （Service Level Agreement, 服务水平协议) 中的一个重要度量指标，也是运维团队向自己老板做出的正式承诺。但可用性是能够持续改进的东西，运维负责人不可希望一步登天。

另外，如果是做第三方托管，需要明确第三方的服务能力与责任。否则，IDC 经常断电或者断网，即使自身做的再好也无法保证服务时间了。

提高可用性的一些常规策略有消除单点，部署冗余设备等。如果要提供更高的可用性，比如4个9甚至5个9，就不是简单靠硬件就能做到的事情，还需要建立自动化的工具与平台，完善的流程制度与变更机制，7*24小时的专人值班等。

2.2.1 可测量指标

年度不可服务时间比例：年度不可服务（不管是主动的还是被动的）时间除以全年时间。

2.3 响应时间

响应时间是指一条查询或者更新语句从发出请求到接收完数据的时间。

因为最大响应时间的不确定性和不可重复性，所以一般使用X%的查询响应时间作为指标。如果值为95%为10ms，意味着95%的查询会在10ms内返回。对于OLTP查询来说，在50ms内返回是比较理想的结果。超过200ms的查询可以视为慢查询。

此指标较难收集，采用tcprstat虽然可以，但是tcprstat本身有一定的负载，另外也只收集最高到99%的响应时间，如果想知道比如99.999%的平均、最大响应时间就需要修改源码了。

目前有2个思路收集此数据：

采用tcpdump+pt-query-digest，将tcpdump抽样数据发送到中心机上利用pt-query-digest进行分析，然后入库后显示。此方法也需要修改pt源码，因为原版的pt支持的粒度太粗了；
将慢查询日志阀值打到10ms甚至更低，然后统计慢查询时间的分布，可以按时间和服务器维度进行分析（使用pt工具也可以得到不同语句的响应时间分布）；

2.3.1 可测量指标

X%的查询/写入响应时间（ms）

3. 性能监控

为了实现上述目标，可以主动监控以下四个与性能及资源利用率相关的指标：

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

3.1 吞吐量

MySQL 有一个名为 Questions 的内部计数器（根据 MySQL 用语，这是一个服务器状态变量），客户端每发送一个查询语句，其值就会加一。由 Questions 指标带来的以客户端为中心的视角常常比相关的 Queries 计数器更容易解释。作为存储程序的一部分，后者也会计算已执行语句的数量，以及诸如 PREPARE 和 DEALLOCATE PREPARE 指令运行的次数，作为服务器端预处理语句的一部分。

通过以下指令，查询诸如 Questions 或 Com_select 服务器状态变量的值：

SHOW GLOBAL STATUS LIKE "Questions";
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| Questions     | 254408 |
+---------------+--------+

也可以监控读、写指令的分解情况，从而更好地理解数据库的工作负载、找到可能的瓶颈。通常，读取查询会由 Com_select 指标抓取，而写入查询则可能增加三个状态变量中某一个的值，这取决于具体的指令：

Writes = Com_insert + Com_update + Com_delete

应该设置告警的指标：Questions
当前的查询速率通常会有起伏，因此，如果基于固定的临界值，查询速率常常不是一个可操作的指标。但是，对于查询数量的突变设置告警非常重要——尤其是查询量的骤降，可能暗示着某个严重的问题。

3.2 查询性能

MySQL 用户监控查询延迟的方式有很多，既可以通过 MySQL 内置的指标，也可以通过performance schema模式。从 MySQL 5.6.6版本开始默认启用，MySQL 的 performance_schema 数据库中的表格存储着服务器事件与查询执行的低水平统计数据。

3.2.1 performance_schema

performance_schema的events_statements_summary_by_digest中保存着许多关键指标，抓取了与每条标准化语句有关的延迟、错误和查询量信息。从该表截取的一行样例显示，某条语句被执行了两次，平均执行用时为 325 毫秒（所有计时器的测量值都以微微秒为单位）：

*************************** 1. row *************************** 
               SCHEMA_NAME: employees                     
                    DIGEST: 0c6318da9de53353a3a1bacea70b4fce                
               DIGEST_TEXT: SELECT * FROM `employees` WHERE `emp_no` > ? 
                COUNT_STAR: 2             
            SUM_TIMER_WAIT: 650358383000             
            MIN_TIMER_WAIT: 292045159000             
            AVG_TIMER_WAIT: 325179191000             
            MAX_TIMER_WAIT: 358313224000              
             SUM_LOCK_TIME: 520000000                 
                SUM_ERRORS: 0               
              SUM_WARNINGS: 0         
         SUM_ROWS_AFFECTED: 0              
             SUM_ROWS_SENT: 520048          
          SUM_ROWS_EXAMINED: 520048
          ...          
          
          SUM_NO_INDEX_USED: 0     
     SUM_NO_GOOD_INDEX_USED: 0                 
                 FIRST_SEEN: 2019-03-24 14:25:32                  
                  LAST_SEEN: 2019-03-24 14:25:55

该表会标准化所有语句（如上面的 DIGEST_TEXT 一栏所示），忽略数据值，规范化空格与大小写，因此，下面的两条查询会被认为是相同的：

select * from employees where emp_no >200;
SELECT * FROM employees WHERE emp_no > 80000;

想要按模式抽取出以微秒为单位的平均运行时间，可以这样查询性能模式：

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;
+--------------------+-------+--------------+
| schema_name        | count | avg_microsec |
+--------------------+-------+--------------+
| employees          |   223 |       171940 |
| performance_schema |    37 |        20761 |
| sys                |     4 |          748 |
+--------------------+-------+--------------+

相似地，按模式计算出现错误的语句总数，可以这么做：

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;
+--------------------+-----------+
| schema_name        | err_count |
+--------------------+-----------+
| employees          |         8 |
| performance_schema |         1 |
| sys                |         3 |
+--------------------+-----------+

3.2.2 sys schema

用上面的方式查询性能模式能有效地从数据库中检索出指标。然而，对于一些查询，使用 MySQL 的sys schema通常更为简单。sys 模式以人们更易读的格式提供了一个有条理的指标集合，使得对应的查询更加简单。例如，想要找出最慢的语句（运行时间在95名开外）：

SELECT * FROM sys.statements_with_runtimes_in_95th_percentile;

或者查看哪些标准化语句出现了错误：

SELECT * FROM sys.statements_with_errors_or_warnings;

在 sys 模式的文档中，详细介绍了许多有用的例子。sys 模式在 MySQL 5.7.7 版本中是默认包含的。不过，MySQL 5.6 用户通过简单的几个指令就能安装。

3.2.3 慢查询

除了性能模式与 sys schema中丰富的性能数据，MySQL 还提供了一个 Slow_queries 计数器，每当查询的执行时间超过 long_query_time 参数指定的值之后，该计数器就会增加。默认情况下，该临界值设置为10秒。

SHOW VARIABLES LIKE 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+

long_query_time 参数的值可通过一条指令进行调整。例如，将慢查询临界值设置为0.1秒：

SET GLOBAL long_query_time = 0.1;

3.3 连接

监控客户端连接情况相当重要，因为一旦可用连接耗尽，新的客户端连接就会遭到拒绝。

连接数限制可以在系统运行时进行调整：

SET GLOBAL max_connections = 200;

然而，此设置会在服务器重启时恢复为默认值。想要永久地改变连接数限制，可以在配置文件中添加如下配置：

max_connections = 200

3.3.1 监控连接使用率

MySQL 提供了 Threads_connected 指标以记录连接的线程数，每个连接对应一个线程。通过监控该指标与先前设置的连接限制，你可以确保服务器拥有足够的容量处理新的连接。MySQL 还提供了 Threads_running 指标，帮助你分隔在任意时间正在积极处理查询的线程与那些虽然可用但是闲置的连接。

如果服务器真的达到max_connections限制，它就会开始拒绝新的连接。在这种情况下，Connection_errors_max_connections指标就会开始增加，同时，追踪所有失败连接尝试的 Aborted_connects指标也会开始增加。

MySQL 提供了许多有关连接错误的指标，帮助你调查连接问题。Connection_errors_internal是个很值得关注的指标，因为该指标只会在错误源自服务器本身时增加。内部错误可能反映了内存不足状况，或者服务器无法开启新的线程。

3.3.2 应该设置告警的指标

Threads_connected：当所有可用连接都被占用时，如果一个客户端试图连接至 MySQL，后者会返回 Too many connections(连接数过多)错误，同时将Connection_errors_max_connections的值增加。为了防止出现此类情况，你应该监控可用连接的数量，并确保其值保持在max_connections限制以内。
Aborted_connects：如果该计数器在不断增长，意味着用户尝试连接到数据库的努力全都失败了。此时，应该借助Connection_errors_max_connections与Connection_errors_internal之类细粒度高的指标调查该问题的根源。

3.4 缓冲池使用情况

MySQL 默认的存储引擎 InnoDB 使用了一片称为缓冲池的内存区域，用于缓存数据表与索引的数据。缓冲池指标属于资源指标，而非工作指标，前者更多地用于检查（而非检测）性能问题。如果数据库性能开始下滑，而磁盘 I/O 在不断攀升，扩大缓冲池往往能带来性能回升。

3.4.1 检查缓冲池的大小

默认设置下，缓冲池的大小通常相对较小，为 128MiB。不过，MySQL 建议可将其扩大至专用数据库服务器物理内存的80%大小。然而，MySQL 也指出了一些注意事项：InnoDB 的内存开销可能提高超过缓冲池大小10%的内存占用。并且，如果你耗尽了物理内存，系统会求助于分页，导致数据库性能严重受损。

缓冲池也可以划分为不同的区域，称为实例。使用多个实例可以提高大容量缓冲池的并发性。缓冲池大小调整操作是分块进行的，缓冲池的大小必须为块的大小乘以实例的数目再乘以某个倍数。

innodb_buffer_pool_size = N * innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances

块的默认大小为 128 MiB，但是从 MySQL 5.7.5 开始可以自行配置。以上两个参数的值都可以通过如下方式进行检查：

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。

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

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

在 MySQL 5.7.5 版本，你可以通过 SET 指令在系统运行时修改缓冲池的大小，并精确到字节数。例如，假设有两个缓冲池实例，你可以将其总大小设置为 8 GB，这样每个实例的大小即为 4 GB。

SET GLOBAL innodb_buffer_pool_size=8589934592;

3.4.2 关键的 InnoDB 缓冲池指标

MySQL 提供了许多关于缓冲池及其利用率的指标。其中一些有用的指标能够追踪缓冲池的总大小，缓冲池的使用量，以及其处理读取操作的效率。

指标 Innodb_buffer_pool_read_requests 及 Innodb_buffer_pool_reads 对于理解缓冲池利用率都非常关键。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

如果数据库从磁盘进行大量读取，而缓冲池还有许多闲置空间，这可能是因为缓存最近才清理过，还处于热身阶段。如果你的缓冲池并未填满，但能有效处理读取请求，则说明你的数据工作集相当适应目前的内存配置。

然而，较高的缓冲池利用率并不一定意味着坏消息，因为旧数据或不常使用的数据会根据 LRU 算法自动从缓存中清理出去。但是，如果缓冲池无法有效满足读取工作量，这可能说明扩大缓存的时机已至。

3.4.3 将缓冲池指标转化为字节

大多数缓冲池指标都以内存页面为单位进行记录，但是这些指标也可以转化为字节，从而使其更容易与缓冲池的实际大小相关联。例如，你可以使用追踪缓冲池中内存页面总数的服务器状态变量找出缓冲池的总大小（以字节为单位）：

Innodb_buffer_pool_pages_total * innodb_page_size

InnoDB 页面大小是可调整的，但是默认设置为 16 KB，或 16,384 字节。可以使用 SHOW VARIABLES 查询了解其当前值：

SHOW VARIABLES LIKE "innodb_page_size";

4. 总结

监控MySQL的关键性能指标，有助于提前发现问题，留给DBA充足的时间来执行应急操作，将故障和隐患消灭在萌芽。从而间接提高MySQL可用性。