技术分享 MySQL 数据库巡检需要做些什么？

最新推荐文章于 2024-08-02 09:31:54 发布

程序员DCS阿里

最新推荐文章于 2024-08-02 09:31:54 发布

阅读量579

点赞数 5

分类专栏： 2024年程序员学习文章标签：数据库 mysql php

本文链接：https://blog.csdn.net/m0_57473261/article/details/137682018

版权

2024年程序员学习专栏收录该内容

239 篇文章 1 订阅

订阅专栏

磁盘空间，这个是节前巡检中的重点，平时磁盘使用率的告警线是 80%，而巡检时我们应该去获取大于 70% 磁盘使用率的实例，提前扩容，避免放个春节(7天) 磁盘有告警的风险，这个时候回家过年还加班大家都不好受。

实例状态，一般来说就是检查 mysqld 的存活，有条件的话可以分析其是否健康。(至于怎么判断其是否健康，这里不扩展了)

高可用状态，通过巡检证明数据库是"可切换"状态。例如 MHA 架构的话，要检查这三个脚本执行的结果。

masterha_check_ssh --conf=/etc/masterha/app.cnf
masterha_check_repl --conf=/etc/masterha/app.cnf
masterha_check_status --conf=/etc/masterha/app.cnf

复制状态，实际上上述高可用状态检查，一般也会检查复制状态。但有一些复制不在高可用范畴，所以这里要多嘴提一下。异步复制、半同步复制、延迟复制、双向复制、级联复制状态的检查，看是否正常，容灾的 DTS 复制是否正常，DTS 的高可用是否正常等等。

VIP 状态，我们有一些实例是有双网络冗余链路的，这些实例会有双 VIP 。如果这时掉了那个冗余网络链路的 VIP 对业务是没有感知的。所以我们应该有一个巡检机制，这个可以开发一个探活任务去定时探测和告警。

按巡检程度来划分

按巡检程度来划分，分为普通巡检和深度巡检。上述的节前巡检虽然比日常巡检要深度，但也只是普通巡检，不能算是深度巡检。那什么样的巡检才算是深度巡检呢？

节前巡检，我们的关注点在于运维，在于数据库可用性，那么深度巡检必然关注点要向外扩展，要关注用户体验，要关注性能，这样的巡检就是深度巡检。

所以深度巡检是解决什么问题呢？深度巡检的目的是对日常巡检和节前巡检的补充，让数据库在未来日子里不单只可用，并且可靠和跑得更快。

我认为，深度巡检 = 可用性巡检 + 可靠性巡检 + 性能巡检 + 分析和建议

可用性巡检

在前面提及的节前巡检，已经大量检查了数据库的可用性，但那些都是从运维角度、从服务、从实例级别来衡量的，从应用角度、从业务角度，其实这个可用性检查是可以扩展的，例如在深度巡检里，我们会检查租户每张表的自增键使用情况，租户常见的自增键类型是 int unsigned 和 int signed，前者是无符号 int 类型，范围是(-2147483648,2147483647)，后者是有符号 int 类型 (0,4294967295)，开发人员在建表的时候更常见是直接定义 int，没有规定是 unsigned 还是 signed，那么默认就是 unsigned 了，而没有使用更理想的 int signed 值，这样自增键可用的范围就会少了一倍。再加上，自增键在插入时并不是连续的，和你插入的方式，参数设置(innodb_autoinc_lock_mode、auto_increment_increment)有关，自增键在插入前就会分配，所以一旦插入失败事务回滚，这个自增 id 也会自然浪费掉。所以 int unsigned 的上限虽然看起来很高，有 21 亿之多，但由于刚才说的原因，很有可能你的 table 里只有 10 亿行数据时，自增键就满了。自增键满了，你这张表就不可写入了，这就是业务层面的不可用。

我们在生产实践中就遇到过这样的情况，某业务是负责某商城交易账单数据分析的，其账单的日志表，每天入库入表的记录数一般为 500 万条，高峰时可以达到 900 万条以上，当时这张表采用的是 int unsigned 自增 id 作为主键，在业务上线不到 9 个月自增主键就用完了。解决办法就是修改自增利类型，从 int unsigned 修改为 bigint signed，我们知道 MySQL 修改主键列类型是锁表的，只能读不能写，所以当时这个业务受损了，DDL 花了 6 个小时。

所以深度巡检，需要对这些情况，做可用性巡检的扩展，更多的可用性巡检，读者可以自行补充。

可靠性巡检

在说性能巡检之前，我想补充一下，可靠性巡检，前面提到的节前巡检有大量的检查可用性了，但可用性是否等于可靠性呢，这里有很多人会混淆，他们并不相等。可用性指的是 Availability，一般是高可用要解决的问题，而可靠性指的是 Reliability，在数据库里一般指的是数据不错、不丢和数据副本的一致性。

节前巡检，已经包含了不少数据库可靠性检查，例如高可用检查中的"可切换"检查，复制状态检查。但这里并不是万无一失的，在这里我提出深度巡检需要做 “核心参数检查”。这里的"核心参数检查"包括三方面

检查数据库里的参数是否满足我们的交维规范要求的核心参数列表
检查主备数据库参数是否一致
检查数据库运行参数和配置文件(my.cnf)参数是否一致

检查数据库里的参数是否满足我们的交维规范要求的核心参数列表，这个其实是历史遗留问题，因为我们大多数数据库本身不来自于我们的部署交付，而是各业务部门交接给我们的，对于这些新交接的实例，务必检查核心参数，才能保证数据不错不丢和主从数据一致性，相关参数包含并不仅限于以下这些:

binlog_format = row
binlog_row_image = full
gtid_mode = on
enforce-gtid-consistency = on
innodb_doublewrite = on
innodb_flush_log_at_trx_commit = 1
log_bin = mysql-bin
master_info_repository = table
sync_binlog = 1
...

实际上我们检查的参数高达 80 个。

检查主备数据库参数是否一致，这个主要是避免主备切换后，有使用上的不一致。这里也会检查一些务必设置不一致的参数，例如 server_id ，反正目的只有一个，就是检查主备的参数，保证他们正常。

检查数据库运行参数和配置文件(my.cnf)参数是否一致。很多人以为持久化的配置文件一定会和运行参数一致，这个没必要检查，这就错了，在 MySQL 5.7 或之前，没有办法修改参数同时持久化配置文件，所以修改参数通常都是分两步，先在数据库里 set global 参数=值，然后登陆服务器修改 my.cnf 配置文件，因不是原子操作，那么运维人员就有犯错的可能，千万不要相信人，人总是会犯错的。之前我们就发生过好几次运行参数和持久化配置文件不一致产生的故障。例如，动态修改 MySQL 的 innodb_buffer_pool_size = 128G，然后忘记持久化到配置文件了。当时数据库发生了crash，之后被高可用组件拉回 mysqld 实例，发现性能很差，这个排查了半天，居然是 innodb_buffer_pool_size 被还原了默认值 128M ！

还有一个案例，在 mysql 5.6 年代，当时硬件性能不行加上没有好的并行复制技术，从库容易因为 io 瓶颈而复制延迟，临时解决方法是从库设置 sync_binlog=0、innodb_flush_log_at_trx_commit = 0 来追延迟，待延迟追平后，修改回 “双1”。这个时候 DBA 很容易忘记去执行修改回 "双1"操作。如果这个时候有个数据库实例级故障，造成主从切换，那么这个时候就有丢失数据的风险了。

另外，某些租户是持有 super 权限，可以修改数据库的参数，但他们是没有服务器权限，如果这些租户修改的参数涉及了我们认为的核心参数，造成这个核心参数的运行参数和配置文件(my.cnf)参数不一致，那就有可能埋了雷，后续引发数据库可靠性甚至是可用性问题了。

“核心参数检查” 是可靠性巡检的一个例子，更多的可靠性巡检，读者可以自行补充。

性能巡检

性能巡检上，就有很多细小的项目了，我这里介绍一些常见的。

1、是否存在没有主键的表。

MySQL 的玩法就是需要有主键，最好是业务无关的 int signed 自增主键，具体为什么请出门右拐看 “开发规范”，他是如何影响性能的，网上有大量的文章，这里我就没必要过多赘述了。

2、SQL 性能优化

首先，巡检报告里，可以列出 top 10 慢查询，让租户去优化 SQL。其次，在报告里可以抓取提供一些执行全表扫描次数 TOP 30 的 SQL 给租户，因为有些SQL 他的执行计划本身其实就有问题的，这些 SQL可能当前跑得很快，但没有评估过数据量增长，当表变得越来越大时，达到一个阀值时，线上可能就会爆发 CPU 100% 的性能问题，成为爆发性杀手级慢查询。

再次，索引方面，可以关注冗余索引、无效索引、索引区分度等信息。冗余索引意思是数据库里有重复的索引，对于a、b、c列的联合索引 idx_a_b_c，他其实同时等于拥有了(a、b、c)、(a、b)、(a) 三个索引的能力，如果这时候你再创建一个 idx_a，idx_a_b 索引，那他们就是冗余索引了，这些索引应该删掉，因为索引是占存储空间的，并且索引不是越多越好，维护索引是有开销的，他影响了 DML 语句的性能，所以用不到的索引就应该删除。同样的，无效索引，就是从未使用过的索引，巡检报告应该把这些索引列出来，开发去评估一下这些索引是否未来都不会用到，用不到就应该删除他们。索引区分度用于评估列的值是否足够分散，值越多越适合建立索引，如果是性别列，只有男女两个值，是不适用创建索引的。区分度越接近1，表示区分度越高；低于0.1，则说明区分度较差，开发者应该重新评估SQL语句涉及的字段，选择区分度高的多个字段创建索引。

3、是否有 MyISAM 存储引擎表

MyISAM 基本没有好处，我之前写过一篇文章《交维规范讲解系列——为什么我们禁止使用 MyISAM 存储引擎》来说过这个问题。实际上我们不应该还要检查是否存在 MyISAM 表，99% 的场景应该直接禁用他，请与我一样通过参数防止 MyISAM 表的创建，当然还有很多存储引擎我都建议不要用了，innodb 才是永远的王者，参考的参数如下:disabled_storage_engines=ARCHIVE,BLACKHOLE,EXAMPLE,FEDERATED,MEMORY,MERGE,NDB,MyISAM

因为 MySQL 5.7 版本仍然有 10 张元数据表使用 MyISAM 存储引擎，可能会影响数据库的升级，如果 5.7 版本使用上了这个参数，运维人员要注意在做数据库升级变更前先禁用此参数，升级完后加回来，参考 https://mp.weixin.qq.com/s/O9UtGskB3IydkEEMscEo1Q。8.0 版本则无这个问题。

我们的目标是，检查到 MyISAM 表后，尽量进行整改，加上上述这个参数，哈。

4、TOP 10 大表

大表在做全表扫描时非常耗费性能，在 DDL 方面的话更是灾难，大于 100G 的表都应该评估一下，为什么会有那么大的表？为什么会放在 MySQL 上，是否可以放到 TiDB 上？是否可以拆分为小表，水平拆还是纵向拆？归档，冷热分离？

建议：MySQL 单实例的存储空间大小应控制在 500G，单表行数控制在 1000 万行以内、大小在 30G 以内，单表字段 50 个以内，单表索引5个以内。

这是以前的建议，仅供参考。随着硬件的提升，我最新的观点是 MySQL 实例 2T 以内，单表体积 100G 以内我都可以接受。当然了，我是从运维角度考虑，性能角度的话主要是看业务是否能接受。

分析和建议

暂时列那么多，也就是抛砖引玉，性能巡检的目的是出具尽量多的数据给租户自行做性能分析，这里有 SQL 相关的，有非 SQL 相关的，至于对这些数据的加工和分析方面，我们的报告主要是对非 SQL 相关的加以文字说明，给出建议，SQL 相关的，我们不是这方面的专家，里面有很多门道有很多小技巧，这个交给租户级别高的开发人员去分析优化。

以上就是我个人对 MySQL 数据库巡检需要做什么的总结，欢迎指正。

文章推荐：

技术分享 | MySQL 修改可信 IP 白名单的方法

技术分享 | 如何计算 MySQL 的 QPS/TPS

新特性解读 | MySQL 8.0 通用表达式（WITH）深入用法