如何判断一个数据库是不是出问题了

一、背景：主备切换与主库状态判断

主备切换场景：
- 主动切换： 有计划的维护、升级等。
- 被动切换： 通常因主库出现问题，由HA (High Availability) 系统自动发起。
核心问题： HA系统如何准确、及时地判断主库是否真的“出问题了”？

二、常用的数据库状态检测方法及其局限性

方法一：SELECT 1 判断
- 机制： 连接MySQL，执行 SELECT 1;，若成功返回则认为正常。
- 局限性： 只能说明MySQL进程存在且能响应简单查询，不能完全代表主库健康。
- 反例：InnoDB并发线程耗尽
  - 通过 SET GLOBAL innodb_thread_concurrency=N; (N较小，如3) 限制InnoDB并发线程数。
  - 当并发执行的长查询占满所有InnoDB线程时，新的InnoDB表查询会被阻塞。
  - 此时 SELECT 1; 仍能成功（因为它不一定访问InnoDB表，或者MySQL Server层能处理），但数据库对业务而言已不可用。
- innodb_thread_concurrency 理解：
  - 默认值0表示不限制，但通常建议设为64-128，避免CPU上下文切换成本过高。
  - 并发连接 vs 并发查询： SHOW PROCESSLIST 中的大量连接是并发连接（主要消耗内存）；真正消耗CPU的是“当前正在执行”的并发查询。
  - 锁等待与并发计数： 等待行锁（包括间隙锁）的线程不计入 innodb_thread_concurrency 的并发线程数。这是必要的设计，避免因锁等待耗尽并发线程数导致整个系统锁死。
  - 正在执行查询（如 SELECT SLEEP(100)）的线程，即使最终会等待，在执行期间仍计入并发线程数。
方法二：查询InnoDB表判断 (如 SELECT * FROM mysql.health_check;)
- 机制： 在系统库 mysql 中创建一张健康检查表（如 health_check，只含少量数据），定期查询该表。
- 改进： 能检测出因InnoDB并发线程过多导致的不可用情况。
- 新的局限性：磁盘空间满
  - 当binlog所在磁盘空间100%时，所有更新语句和事务提交 (COMMIT) 会被阻塞。
  - 但系统仍能正常读取数据，此时查询 mysql.health_check 可能成功返回，无法检测到写入问题。
方法三：更新InnoDB表判断 (如 UPDATE mysql.health_check SET t_modified=NOW();)
- 机制： 定期更新健康检查表中的时间戳字段。
- 改进： 能检测到因磁盘满导致的写入阻塞问题。
- 双M结构下的考虑：
  - 备库也需要做更新检测，其binlog会传回主库。
  - 若主备库使用相同更新命令更新同一行，可能导致行冲突，主备同步停止。
  - 解决方案： health_check 表使用 server_id 作为主键，主备库各自更新对应 server_id 的行，避免冲突。 sql CREATE TABLE mysql.health_check ( id INT NOT NULL, t_modified TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id) ) ENGINE=InnoDB; -- 检测命令 INSERT INTO mysql.health_check(id, t_modified) VALUES (@@server_id, NOW()) ON DUPLICATE KEY UPDATE t_modified=NOW();
- 新的局限性：判定慢 (False Negatives under High IO Load)
  - 超时机制： 所有检测逻辑都有超时时间N。更新语句若N秒内未返回，则认为系统不可用。
  - 场景： 日志盘IO利用率100%，系统响应极慢，已需切换。
  - 问题： 检测的 UPDATE 语句所需资源少，可能在IO繁忙时“幸运地”获得资源并快速完成，在N秒内返回成功。
  - 结果： 检测系统认为主库正常，但业务SQL已严重卡顿。HA系统可能延迟切换。
  - 根本原因：外部检测的随机性。轮询检测，可能在问题发生后一段时间才发现，甚至多次轮询未发现。
方法四：基于内部统计 (Performance Schema)
- 思路： 利用MySQL内部统计信息判断IO性能。
- Performance Schema： MySQL 5.6+ 提供 performance_schema 数据库。
- 关键表： file_summary_by_event_name
  - 统计各类文件IO事件的时间。
  - Redo Log: event_name = 'wait/io/file/innodb/innodb_log_file'
  - Binlog: event_name = 'wait/io/file/sql/binlog'
  - 字段： COUNT_STAR (总次数), SUM_TIMER_WAIT, MIN_TIMER_WAIT, AVG_TIMER_WAIT, MAX_TIMER_WAIT (总耗时、最小、平均、最大耗时，单位皮秒)。也分别统计读、写、其他操作。
- 启用统计：
  - 有性能损耗 (全开约10%)，建议按需开启。
  - UPDATE setup_instruments SET ENABLED='YES', TIMED='YES' WHERE name LIKE '%specific_event_name%';
- 检测逻辑：
  - 通过查询 MAX_TIMER_WAIT 字段，设定阈值 (如单次IO超过200ms为异常)。 sql SELECT event_name, MAX_TIMER_WAIT FROM performance_schema.file_summary_by_event_name WHERE event_name IN ('wait/io/file/innodb/innodb_log_file', 'wait/io/file/sql/binlog') AND MAX_TIMER_WAIT > 200 * 1000000000; -- 200ms in picoseconds
  - 发现异常后，可 TRUNCATE TABLE performance_schema.file_summary_by_event_name; 清空统计，以便下次准确累积。
- 优点：
  - 更接近数据库内部真实运行状况，能反映IO瓶颈。
  - 减少了外部检测的随机性。
- 缺点：
  - Performance Schema自身有性能开销。
  - 配置和解读相对复杂。

三、总结与权衡

没有完美方案： 每种检测方法都有其适用场景和局限性。
MHA的默认： 广泛使用的MHA默认采用 SELECT 1 (或仅连接成功) 作为检测方式。
成本与收益： 改进的方案通常会增加额外损耗，需根据业务实际情况权衡。
推荐组合：
- 优先考虑更新系统表 (方法三)。
- 配合检测 Performance Schema的IO统计信息 (方法四) 作为补充或更精细的判断依据。

四、延伸思考：文末问题预告

问题： 业务系统的高可用需求中，你是如何判断服务有没有出问题的？ * 思考方向： * 健康检查接口 (Health Check API)： 服务自身提供。 * 心跳机制 (Heartbeat)： 服务主动上报状态。 * 业务指标监控： QPS、响应时间、错误率等。 * 依赖服务检查： 检查其依赖的数据库、缓存、其他微服务是否正常。 * 日志分析： 实时分析错误日志。 * 综合判断： 多维度信息结合，避免单点误判。

五、上期问题回顾：GTID等位点方案下，大表DDL的影响及应对

影响：
- 大表DDL执行时间长 (如10分钟)，其GTID会延迟10分钟才在备库应用完成。
- 在此期间，依赖等待此DDL GTID或后续GTID的读请求，在从库上会因 wait_for_executed_gtid_set 超时而退化到主库。
- 可能导致读写分离机制在DDL期间失效，大量读请求涌向主库。
应对策略：
1. 业务低峰期操作：
  - 确保主库能承载所有业务查询。
  - 临时将所有读请求切换到主库。
  - 在主库执行DDL。
  - 等待备库追上延迟后，再将读请求切回备库。
2. 备库先做，再切主库： (评论区补充) 先在备库执行DDL，然后进行主备切换，再在新备库上执行DDL。
3. 使用在线DDL工具： 如 gh-ost 或 pt-online-schema-change，它们对主库影响较小，且不产生单一的巨大事务GTID。