罗罗编程学习记录
本页内容 展开查看

03.8 阶段 7:性能诊断、参数调优与运维

所属:全栈开发面试题库 / 学习路径 / 03 MySQL 与 PostgreSQL

本页目录

一、阶段定位

前六个阶段把"建库、写查询、加索引、做事务、搭复制、做分片"都讲了。本阶段解决线上日常:数据库突然慢了怎么定位、哪些参数该调、日常运维要做什么。这是 DBA 与后端工程师的分水岭。

判断自己是否卡在本阶段:

  • 线上慢了只会"重启数据库",不知道从慢查询日志、pg_stat_statements、锁等待哪里入手——这是没有诊断闭环。
  • 背了一堆参数名,但不知道 innodb_buffer_pool_size 该设多少、work_mem 设大了会出事——这是调优凭感觉。
  • 连接数设到 2000 以为能扛更高并发,结果吞吐反而下降——这是连接数与并发的关系没搞懂。
  • PG 用了一年表越来越大,不知道要 VACUUM;MySQL undo 暴涨不知道长事务——这是运维盲区。

衔接关系:本阶段把前六阶段的知识点串成诊断闭环——慢查询用阶段 2 的 EXPLAIN、锁等待用阶段 3 的锁机制、复制延迟用阶段 5 的指标、膨胀用阶段 3 的 MVCC。不掌握这些前置,看到指标也不知道怎么解读。

二、阶段目标与投入

能力目标(可验证的行为) 验证方式
能开启慢查询日志并从高频慢 SQL 定位问题 MySQL slow log / PG pg_stat_statements
能读懂 SHOW ENGINE INNODB STATUS 关键段 解释 TRANSACTIONS、LATEST DEADLOCK、BUFFER POOL
能找到锁等待链与长事务源头 data_lock_waits / pg_blocking_pids
能按内存规格估算 buffer pool / shared_buffers 给出 32GB 机器的经验配置
能说清连接数不是越大越好 解释连接切换成本与吞吐关系
能诊断 PG 表膨胀并触发 VACUUM pg_stat_user_tables.n_dead_tup 下降
能列出核心监控指标清单 连接数、QPS、慢查询、复制延迟、缓存命中率

建议学时:

  • 全职:1.5 周,约 35 小时。慢查询与统计视图 8 小时、锁/长事务 6 小时、内存参数 8 小时、连接池 4 小时、VACUUM/膨胀 5 小时、监控与在线 DDL 4 小时。
  • 在职:3 周,约 40 小时。必须在有压测的环境练,不要只看文档。

学时分配:慢查询定位 25%、锁等待与长事务 20%、内存参数 20%、连接池 10%、VACUUM/undo 膨胀 15%、监控指标 10%。

三、先修自检

先修项 合格标准 不合格时回到
索引与 EXPLAIN 能解读执行计划 03.3 阶段 2
事务与锁 能定位锁等待与死锁 03.4 阶段 3
日志与复制 知道 redo/binlog/WAL 03.5 阶段 4
复制延迟 会看 Seconds_Behind 03.6 阶段 5
操作系统 知道 CPU/内存/IO 查看 01.1 操作系统

经验判断:调优的铁律是"先量后调"。没有基线指标(QPS、RT、缓存命中率)就改参数,是玄学调参。

四、知识地图

模块 知识点 掌握程度 对应题库章节
慢查询 slow log、long_query_time、pg_stat_statements 会用 05.2 MySQL
活动视图 INNODB_TRX、pg_stat_activity 能讲清原理 05.2 MySQL
锁等待 data_lock_waits、pg_blocking_pids 会用 05.3 PostgreSQL
内存参数 buffer_pool、shared_buffers、work_mem 能讲清原理 05.2 MySQL
连接池 max_connections、HikariCP、PgBouncer 会用 05.1 关系型数据库通用能力
膨胀 VACUUM、autovacuum、undo 回收 能讲清原理 05.3 PostgreSQL
在线 DDL pt-online-schema-change、pg_repack 了解 05.2 MySQL
监控 指标清单、Prometheus exporter 会用 08.4 CI/CD 与可观测性

进度管理建议:每个指标都要知道"异常时去哪查、怎么修"。术语见 03.12 术语表。

五、分模块精讲

5.1 慢查询定位:slow log 与 pg_stat_statements

为什么需要它:数据库慢了,第一步不是猜,而是找到"哪条 SQL 慢、慢多少次、占多少时间"。MySQL 用慢查询日志,PG 用 pg_stat_statements 扩展(聚合所有慢 SQL)。

核心机制:

  • MySQL 慢查询日志:
    • slow_query_log=ON、long_query_time(默认 10 秒,生产设 1 秒)。
    • log_queries_not_using_indexes=ON 记录没走索引的查询。
    • 用 mysqldumpslow 或 pt-query-digest 聚合分析。
  • PG pg_stat_statements:
    • 编译时/安装扩展:CREATE EXTENSION pg_stat_statements;。
    • 记录每条 SQL 的调用次数、总时间、行读、行写,按 queryid 聚合(不绑定参数值)。
    • 按 total_time / mean_time 排序找高频慢 SQL。

最小可运行示例:

-- MySQL
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;
SHOW VARIABLES LIKE 'slow_query_log_file';

-- 分析(命令行)
-- mysqldumpslow -s t -t 10 /var/lib/mysql/slow.log

-- PG
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT queryid, query, calls,
       round(total_exec_time::numeric, 1) AS total_ms,
       round(mean_exec_time::numeric, 1) AS mean_ms,
       rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

常见坑:

现象 原因 修法
慢查询日志为空 long_query_time 设太大 设 1 秒;确认开启
pg_stat_statements 无数据 扩展未装 CREATE EXTENSION;配 shared_preload_libraries
日志文件暴涨 没聚合 pt-query-digest;定期轮转

面试考法:"怎么定位慢查询""慢查询日志关键参数"。要点:slow log + long_query_time;PG 用 pg_stat_statements 聚合。

5.2 活动会话与锁等待

为什么需要它:慢查询之外,"数据库突然卡住"往往是锁等待或长事务。本模块学会一眼找到阻塞源头。

核心机制:

  • MySQL:
    • information_schema.INNODB_TRX:当前所有事务。
    • performance_schema.data_lock_waits:谁等谁、等什么锁。
    • SHOW PROCESSLIST:所有连接。
  • PG:
    • pg_stat_activity:当前会话、状态、时长、SQL。
    • pg_locks WHERE NOT granted:未授予的锁。
    • pg_blocking_pids(pid):返回阻塞该会话的 pid 数组。

最小可运行示例:

-- MySQL:找最长事务
SELECT trx_id, trx_started,
       TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS dur,
       trx_query
FROM information_schema.INNODB_TRX
ORDER BY trx_started;

-- 锁等待链
SELECT * FROM performance_schema.data_lock_waits;

-- PG:找长事务
SELECT pid, now() - xact_start AS dur, state, query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY dur DESC;

-- 谁阻塞了谁
SELECT pid, pg_blocking_pids(pid) AS blocked_by
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

-- 杀会话(PG)
SELECT pg_terminate_backend(12345);
-- MySQL: KILL 1234;

常见坑:

现象 原因 修法
大量 idle in transaction 应用拿了连接没提交 杀会话;应用修事务边界
锁等待找不到源头 只看等待方 用 data_lock_waits / pg_blocking_pids 反查
杀错会话 没确认 SQL 先查 query 再 terminate

面试考法:"线上锁等待怎么排查""长事务为什么危险"。要点:活动视图找源头;长事务阻碍 MVCC 回收、持锁。

5.3 内存参数:buffer pool 与 shared_buffers

为什么需要它:数据库性能 80% 靠内存缓存。参数设错,要么 OOM,要么命中率低。

核心机制:

  • MySQL InnoDB:
    • innodb_buffer_pool_size:数据页与索引页缓存,经验值为物理内存的 50%–70%( dedicated 数据库机)。
    • 命中率:Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests,应 > 99%。
  • PostgreSQL:
    • shared_buffers:共享缓冲,经验值物理内存的 25%(不要设太大,PG 还依赖 OS page cache)。
    • work_mem:每个排序/哈希操作的内存,默认 4MB;设大了并发时 N 个操作 × work_mem 可能 OOM。
    • effective_cache_size:告诉优化器 OS 缓存多大,不实际分配,经验 50%–75%。
  • 32GB 机器参考:MySQL buffer_pool 设 16–20GB;PG shared_buffers 设 8GB、effective_cache_size 设 24GB。

最小可运行示例:

-- MySQL 命中率
SHOW STATUS LIKE 'Innodb_buffer_pool_read%';
-- 命中率 = 1 - reads / read_requests

-- PG
SHOW shared_buffers;
SHOW work_mem;
SHOW effective_cache_size;

常见坑:

现象 原因 修法
命中率 < 95% buffer pool 太小 调大;或查是否有大查询扫全表
PG OOM work_mem 设太大,并发排序 调小 work_mem(如 8–16MB)
shared_buffers 设 75% PG 不推荐 降到 25%,交给 OS cache

面试考法:"buffer pool 设多少""work_mem 为什么不能乱设"。要点:专用机 50–70%;work_mem 是每操作内存,并发放大。

5.4 连接数与连接池

为什么需要它:连接数不是越大越好。每个连接在数据库占内存,且操作系统线程切换有成本。设 2000 连接,实际吞吐反而比 200 低。

核心机制:

  • MySQL max_connections 默认 151;调过大会占内存(每连接约 1MB 栈)。
  • PG max_connections 默认 100;每连接是一个 OS 进程(不是线程),进程切换成本高,通常设到 500–1000,再大就用连接池。
  • 连接池:
    • JVM 用 HikariCP,池大小经验 ((核心数 * 2) + 有效磁盘数)(PostgreSQL wiki),通常 10–50,不是几百。
    • PG 用 PgBouncer 做事务级池化,几百应用连接复用几十个后端连接。
  • 池太小:请求排队;池太大:数据库被打爆。要压测找拐点。

最小可运行示例:

# HikariCP 参考
spring:
  datasource:
    hikari:
      maximum-pool-size: 20
      minimum-idle: 5
      connection-timeout: 3000
-- PG 连接占用
SELECT count(*), state FROM pg_stat_activity GROUP BY state;

常见坑:

现象 原因 修法
应用报 "too many connections" max_connections 不够 调大;或上连接池
连接数 1000 但 RT 升高 操作系统切换成本 降连接数;上 PgBouncer
池满请求排队 pool-size 太小 压测调大;查慢 SQL

面试考法:"连接池设多大""为什么不用几百连接"。要点:连接有内存与切换成本;压测找拐点;PG 用 PgBouncer。

5.5 表膨胀与 VACUUM(PostgreSQL)

为什么需要它:PG 的 MVCC(阶段 3)UPDATE 产生死元组,不回收会导致表与索引膨胀、查询变慢。这是 PG 日常运维的核心。

核心机制:

  • 死元组(dead tuple):UPDATE/DELETE 后旧版本,被 VACUUM 回收。
  • autovacuum 后台进程自动触发,但默认参数对大表偏保守。
  • n_dead_tup 与 n_live_tup 比例高说明膨胀。
  • VACUUM(不锁表,标记可回收);VACUUM FULL(锁表重建,彻底回收但停写);pg_repack 在线重建。
  • 长事务阻止 autovacuum 回收(阶段 3)。

最小可运行示例:

-- 查膨胀
SELECT relname, n_live_tup, n_dead_tup,
       round(n_dead_tup * 100.0 / NULLIF(n_live_tup,0), 1) AS dead_pct
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

-- 手动 VACUUM 某表
VACUUM (VERBOSE, ANALYZE) orders;

-- 紧急重建(停写窗口)
VACUUM FULL orders;

常见坑:

现象 原因 修法
表体积 100GB 但 n_live_tup 只占 1GB 膨胀严重 VACUUM FULL / pg_repack
autovacuum 跑不完 大表 + 默认参数 调 autovacuum_vacuum_scale_factor
长事务期间 VACUUM 不回收 快照持有 杀长事务

面试考法:"PG 为什么要 VACUUM""VACUUM 与 VACUUM FULL 区别"。要点:回收死元组;VACUUM 标记不锁表,FULL 重建停写。

5.6 在线 DDL 与 MDL 锁

为什么需要它:大表加索引、改列在高峰期直接锁表,会被 MDL(元数据锁)堵住,甚至把整个库堵死。本模块学会安全地做在线变更。

核心机制:

  • MySQL DDL 在 5.6+ 有 Online DDL,但部分操作仍会短暂锁 MDL。
  • MDL:DML 与 DDL 互斥。长事务持有 MDL,后续 DDL 排队,把所有新 DML 也堵死——这是"改个表把库搞挂"的经典事故。
  • 工具:pt-online-schema-change(gh-ost)通过影子表 + 触发器/binlog 同步在线变更。
  • 大表加索引:MySQL 8 支持 instant/inplace;仍建议低峰、用工具、先备份。
  • PG:CREATE INDEX CONCURRENTLY 不锁表(但慢)。

最小可运行示例:

-- MySQL:查看 MDL 等待
SELECT * FROM performance_schema.metadata_locks;

-- PG:在线建索引
CREATE INDEX CONCURRENTLY idx_orders_user ON orders(user_id);

-- MySQL:pt-online-schema-change(命令行)
-- pt-online-schema-change --alter "ADD INDEX idx_user(user_id)" \
--   D=shop,t=orders --execute

常见坑:

现象 原因 修法
改表后所有查询卡住 MDL 被长事务堵 杀长事务;低峰;用 gh-ost
建索引锁表 普通 CREATE INDEX PG 用 CONCURRENTLY;MySQL 用工具
大表 DDL 几小时 重建表 用 pt-osc/gh-ost;业务低峰

面试考法:"大表怎么加索引不锁表""MDL 事故怎么发生"。要点:长事务持 MDL 堵 DDL、堵 DML;用在线工具、低峰。

5.7 监控指标清单与版本升级

为什么需要它:没有监控就没有告警,故障只能靠用户投诉。本模块列一份生产必备指标清单。

核心机制:

  • 核心指标:
    • 容量:连接数、QPS、TPS、缓冲池命中率、磁盘使用率。
    • 慢查询:慢查询数、平均 RT、top SQL。
    • 复制:复制延迟(Seconds_Behind)。
    • 锁:锁等待数、最长事务时长、死锁数。
    • 膨胀(PG):n_dead_tup、autovacuum 运行。
  • 采集:mysqld_exporter / postgres_exporter + Prometheus + Grafana。
  • 版本升级:
    • 先在测试库跑全量回归;查 release notes 不兼容项;逻辑复制跨版本可做滚动升级。
    • PG 大版本升级(如 15→16)需 pg_dump 或 pg_upgrade;小版本滚动重启。

最小可运行示例:

监控告警建议阈值:
  连接数 > 80% max_connections
  慢查询数 > 10/分钟
  复制延迟 > 10s
  最长事务 > 30s
  缓冲池命中率 < 99%
  磁盘 > 80%

常见坑:

现象 原因 修法
故障才知道 没监控/告警 部署 exporter + Grafana
升级后功能异常 没测回归 测试库全量回归
磁盘满 没监控磁盘 告警 + 日志轮转

面试考法:"你管数据库监控哪些指标""版本升级注意什么"。要点:连接/QPS/慢查询/复制延迟/锁/膨胀;先测试、查不兼容、滚动。

六、分层练习

6.1 复现型

练习 要求 验收标准 预计耗时
开慢查询 MySQL 设 long_query_time=1,PG 装 pg_stat_statements 能查到 top 慢 SQL 1 小时
找长事务 造一个 idle in transaction,用视图找到并杀掉 抄下会话与 SQL 1 小时
锁等待复现 两会话互相锁,用 data_lock_waits / pg_blocking_pids 定位 找到阻塞链 1.5 小时
内存参数 对 32GB 机器写 MySQL/PG 配置 buffer_pool/shared_buffers 有依据 1 小时
膨胀观察 PG 批量 UPDATE,看 n_dead_tup 后 VACUUM 死元组清零 1.5 小时

6.2 改造型

练习 要求 验收标准 预计耗时
修慢 SQL 从 top 慢 SQL 选一条,加索引/改写 RT 下降对比 2 小时
连接池调优 压测不同 pool-size,找拐点 记录吞吐曲线 2 小时
在线加索引 用 CONCURRENTLY 或 gh-ost 不锁表 2 小时
杀长事务 写脚本定时查 >60s 事务并告警 能告警 1.5 小时

6.3 设计型

练习 要求 验收标准 预计耗时
监控大盘 列出 10 个核心指标与阈值 可交付给 Grafana 2 小时
应急手册 写"数据库慢/锁等待/磁盘满"三类应急步骤 可执行 2 小时
升级方案 对一次小版本升级写步骤与回滚 含测试与回归 1.5 小时

七、排障表

现象 可能原因 排查步骤 解决办法
数据库整体慢 慢 SQL、锁等待、资源打满 看 pg_stat_statements / 负载 定位 top SQL、杀锁、扩容
连接被打满 连接池过大/泄漏 pg_stat_activity 计数 调池;修泄漏;PgBouncer
改表后全库卡住 MDL 被长事务堵 metadata_locks 杀长事务;用在线工具
PG 表越来越慢 表膨胀 n_dead_tup 比例 VACUUM / pg_repack
命中率低 buffer 小/大查询扫表 Innodb_buffer_pool_reads 调大 buffer;优化 SQL
复制延迟升高 大事务/从库慢 Seconds_Behind 拆批、并行复制(阶段 5)
OOM work_mem/max_connections 过大 看内存日志 调小;压测
磁盘满 日志/binlog 未轮转 查磁盘 日志轮转;归档;清理

八、出口条件

全部勾选后再进入 阶段 8 两库差异、迁移与架构综合(可选)。

  • [ ] 能开启慢查询并从 slow log / pg_stat_statements 定位 top 慢 SQL
  • [ ] 能从活动视图找到长事务与锁等待源头并杀掉
  • [ ] 能为 32GB 机器写出 buffer_pool/shared_buffers 经验配置
  • [ ] 能解释连接数为什么不是越大越好
  • [ ] 能诊断 PG 膨胀并触发 VACUUM
  • [ ] 能说清 MDL 事故与在线 DDL 工具
  • [ ] 能列出 10 个核心监控指标与告警阈值

自测题:

  1. 线上数据库突然变慢,你的排查顺序?
    • 要点:看负载/资源 → 慢查询 top(pg_stat_statements)→ 锁等待/长事务 → 复制延迟 → 资源(磁盘/CPU)。
  2. MySQL buffer pool 与 PG shared_buffers 经验值各是多少?
    • 要点:MySQL 专用机 50–70%;PG 25%(其余交给 OS cache)。
  3. work_mem 为什么不能设太大?
    • 要点:每个排序/哈希操作独立分配,并发 N 个操作就放大 N 倍,可能 OOM。
  4. PG 的 VACUUM 与 VACUUM FULL 区别?
    • 要点:VACUUM 标记可回收、不锁表;FULL 重建表、彻底回收但停写。
  5. 大表加索引怎么避免锁表?
    • 要点:PG 用 CREATE INDEX CONCURRENTLY;MySQL 用 pt-osc/gh-ost;低峰执行。
  6. 连接池是不是越大越好?为什么?
    • 要点:不是;每个连接占内存、进程切换成本高,压测找拐点(通常 10–50)。
  7. 长事务为什么是性能与膨胀的共同源头?
    • 要点:持锁阻塞其他写;其 Read View 阻碍 undo/死元组回收,导致膨胀。