第 24 章 性能监控与参数调优

📖 本篇简介：参数调优不是玄学，而是建立在监控数据上的持续改进过程。本篇系统讲解如何用 SHOW STATUS、SHOW VARIABLES 掌握数据库的运行状态，重点调连接数、Buffer Pool、日志、IO 等关键参数，并介绍 sysbench 等压测工具与标准的调优流程。学完本篇，你将能拿到一台新部署的 MySQL，从空配置开始逐步调到生产可用水平。本系列示例基于 MySQL 8.0 语法。

目录

• 1 性能监控整体思路
• 2 SHOW STATUS 状态变量
• 3 SHOW VARIABLES 系统变量
• 4 关键参数调优
• 5 压测工具与方法
• 6 调优流程与方法论
• 7 性能调优速查表
• 8 常见坑与注意事项
• 9 练习题与参考答案

1 性能监控整体思路

很多人调优的第一步是"网上抄一份 my.cnf"。这是错的。正确的思路是：先监控、再分析、后调参、再验证。

监控指标 → 发现瓶颈 → 假设原因 → 调整参数 → 压测验证 → 上线
   ↑                                                    │
   └──────────────── 持续迭代 ────────────────────────────┘

监控的核心指标分四类：

| 类别 | 指标 | 关注点 |

|---|---|---|

| 吞吐 | QPS、TPS | 每秒查询/事务数 |

| 延迟 | 慢查询数、平均响应时间 | 接口是否超时 |

| 资源 | CPU、IO、连接数 | 哪一层是瓶颈 |

| 错误 | 死锁数、连接拒绝、复制延迟 | 异常是否突增 |

生产环境一般用 Prometheus + Grafana 采集 MySQL 的 SHOW STATUS 指标，画成大盘。手工排查时，就直接在 MySQL 里跑下面这些 SQL。

2 SHOW STATUS 状态变量

SHOW STATUS 是 MySQL 运行以来的累计计数器。要看当前值、增长率，一般要隔一段时间取两次值相减。

2.1 查看连接与线程

-- 连接相关状态
SHOW GLOBAL STATUS LIKE 'Threads%';
-- Threads_connected: 当前打开的连接数
-- Threads_running:    正在并发执行的线程数（不含 Sleep）
-- Threads_created:    历史创建过的连接总数
-- Threads_cached:     连接池缓存的连接数

Threads_running 是最值得盯的指标：如果它长期超过 CPU 核数，说明 CPU 已经成为瓶颈；如果它瞬间飙升，一般是有大 SQL 或锁等待。

2.2 查看 QPS / TPS

-- 取两次值相减：Questions 是所有查询数，Com_commit/Com_rollback 统计事务
SHOW GLOBAL STATUS LIKE 'Questions';
SHOW GLOBAL STATUS LIKE 'Com_commit';
SHOW GLOBAL STATUS LIKE 'Com_rollback';

QPS ≈（第二次 Questions - 第一次 Questions）/ 间隔秒数。

TPS ≈（第二次 Com_commit + Com_rollback - 第一次）/ 间隔秒数。

2.3 查看 Buffer Pool 命中率

-- 缓存命中率
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';
-- Innodb_buffer_pool_read_requests: 总请求次数
-- Innodb_buffer_pool_reads:         其中从磁盘读的次数（未命中）

命中率 = 1 - reads / read_requests。前面第 21 章算过公式，健康值 > 99%。

2.4 查看临时表与排序

-- 临时表、排序相关
SHOW GLOBAL STATUS LIKE 'Created_tmp%';
-- Created_tmp_tables:       内存临时表创建数
-- Created_tmp_disk_tables:  落盘临时表数（这部分高说明 sort_buffer 不够）
SHOW GLOBAL STATUS LIKE 'Sort%';
-- Sort_merge_passes: 归并次数
-- Sort_rows:         排序的行数

Created_tmp_disk_tables 占 Created_tmp_tables 的比例高，说明 GROUP BY / DISTINCT 经常把数据挤到磁盘上，要调 tmp_table_size 和 max_heap_table_size。

2.5 查看 InnoDB 行操作与死锁

-- 行操作统计
SHOW GLOBAL STATUS LIKE 'Innodb_rows%';
-- Innodb_rows_inserted/updated/deleted/read：累计行操作数

-- 死锁数
SHOW GLOBAL STATUS LIKE 'Innodb_deadlocks';
-- 如果这个值持续增长，要查 SHOW ENGINE INNODB STATUS 找最近一次死锁

把两次 Innodb_rows_read 相减除以间隔秒数，就是每秒扫描的行数。这个值远高于 Innodb_rows_returned，说明有很多全表扫描。

3 SHOW VARIABLES 系统变量

SHOW VARIABLES 是当前配置值。调参就是改这些值。

-- 查看所有变量（太多，要按模糊匹配）
SHOW VARIABLES LIKE 'innodb_buffer%';
SHOW VARIABLES LIKE 'max_connections';
SHOW VARIABLES LIKE 'thread_cache%';

-- 动态修改（重启失效）
SET GLOBAL max_connections = 1000;
-- 永久修改要写 my.cnf

关键变量速览（下一节展开）：

| 变量 | 作用 |

|---|---|

| max_connections | 最大连接数 |

| innodb_buffer_pool_size | Buffer Pool 大小 |

| innodb_log_file_size | redo 单文件大小 |

| innodb_flush_log_at_trx_commit | redo 刷盘策略 |

| sort_buffer_size | 排序缓冲 |

| tmp_table_size | 内存临时表上限 |

| thread_cache_size | 线程缓存数 |

| open_files_limit | 最大打开文件数 |

4 关键参数调优

4.1 连接数

-- 查看当前最大连接数和已用连接数
SHOW VARIABLES LIKE 'max_connections';   -- 默认 151
SHOW GLOBAL STATUS LIKE 'Max_used_connections';  -- 历史峰值

如果 Max_used_connections 接近 max_connections，新连接会被拒绝（报错 Too many connections）。生产一般设 500~2000：

[mysqld]
max_connections = 1000
# 连接被拒绝时的告警日志

但连接数不是越大越好：每个连接要占内存（thread_stack、sort_buffer 等），1000 个连接每个 10MB 就是 10GB。应用层应该用连接池（HikariCP 等）复用连接。

4.2 Buffer Pool

[mysqld]
# 物理内存 50%~70%
innodb_buffer_pool_size = 12G
# 多核 CPU 开多个实例降锁竞争
innodb_buffer_pool_instances = 8

这是对性能影响最大的一个参数，没有之一。

4.3 日志参数（安全性与性能的权衡）

[mysqld]
# 两个关键参数，金融类业务必须都为 1
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1

• innodb_flush_log_at_trx_commit=1：每次事务提交 redo 落盘；
• sync_binlog=1：每次事务提交 binlog 落盘。

两个都为 1 是 MySQL 官方"双 1"配置，保证宕机不丢已提交事务。牺牲一些写入性能（大概 20%~30%），换数据安全。

4.4 排序与临时表

[mysqld]
# 单个排序缓冲（每个连接独占，别设太大）
sort_buffer_size = 4M
# 内存临时表上限
tmp_table_size = 64M
max_heap_table_size = 64M

注意：sort_buffer_size 是每个连接独占的，设成 1G，1000 个连接就是 1TB，会 OOM。一般 2M~8M 足够。

4.5 线程缓存

[mysqld]
# 复用断开的连接线程，避免频繁创建销毁
thread_cache_size = 100

短连接场景下，thread_cache 能显著降低 Threads_created 的增速。

4.6 IO 与日志文件位置

[mysqld]
#  redo 日志最好放在独立磁盘，避免和数据文件争抢 IO
innodb_log_group_home_dir = /disk1/mysql/redo
#  数据文件放另一块盘
innodb_data_home_dir = /disk2/mysql/data
#  临时表、binlog 也建议分盘
tmpdir = /disk3/mysql/tmp

如果redo、数据、binlog 都在一块磁盘上，高并发写入时磁盘 IO 会先成瓶颈。物理机或云盘上，至少把 redo 放到和数据不同的盘。

5 压测工具与方法

调参前后必须压测，不能凭感觉。

5.1 sysbench

sysbench 是最常用的 MySQL 压测工具（Lua 脚本驱动）：

# 1. 准备数据：16 张表，每张 1000 万行
sysbench oltp_common.lua \
  --mysql-host=127.0.0.1 --mysql-port=3306 \
  --mysql-user=root --mysql-password=xxx \
  --tables=16 --table-size=10000000 prepare

# 2. 跑压测：32 线程、跑 60 秒
sysbench oltp_read_write.lua \
  --mysql-host=127.0.0.1 --mysql-port=3306 \
  --threads=32 --time=60 run

# 3. 清理
sysbench oltp_read_write.lua --mysql-host=127.0.0.1 cleanup

典型输出：

SQL statistics:
    queries performed:
        read:  ...
        write: ...
    transactions:          12345  (205.78 per sec.)
    queries:               98765  (1645.67 per sec.)

关注 TPS 和 QPS 两个数。

5.2 其他工具

| 工具 | 特点 |

|---|---|

| sysbench | 通用、Lua 脚本 |

| mysqlslap | MySQL 自带 |

| JMeter | 图形化、适合业务接口 |

| 业务回放 | 用真实流量镜像压测，最准 |

5.3 压测注意事项

1. 压测机和 MySQL 不能同一台，否则抢资源；

2. 从低并发起步（1、4、16、32、64、128），找到拐点；

3. 每个档位跑到稳定（至少 5 分钟）；

4. 同时看 MySQL 的 Threads_running、CPU、IO，找瓶颈点。

5.4 读懂一份压测报告

sysbench 输出末尾会给一份 latency 百分位报告：

Latency (ms):
         min:                                    2.1
         avg:                                   18.6
         max:                                  312.4
         95th percentile:                       45.2
         sum:                                ...

重点看两个数：

• 95th percentile（P95）：95% 的请求都在这个延迟以内。这个数比 avg 更能反映用户体验；
• max：最大延迟。如果 max 远高于 P95，说明有零星的"长尾请求"，可能是锁等待、IO 抖动或网络问题。

调优目标不是让 avg 最低，而是让 P95 稳定在业务 SLA 以内（比如接口要求 100ms，P95 必须 < 80ms）。

6 调优流程与方法论

6.1 标准调优流程

1. 基线：用 sysbench 跑当前配置，记录 TPS、延迟、CPU、IO；

2. 找瓶颈：看是 CPU 打满、IO 打满、还是连接数打满；

3. 一次只改一个参数：改完再压，观察变化；

4. 记录对比：每次改动记 before/after；

5. 上线灰度：先在从库或预发环境跑一周；

6. 持续监控：上线后看大盘，回退机制要想好。

6.2 常见瓶颈定位

| 现象 | 可能瓶颈 | 调优方向 |

|---|---|---|

| CPU 高、IO 低 | 全表扫描、复杂计算 | 加索引、优化 SQL |

| IO 高、CPU 低 | Buffer Pool 太小 | 加 Buffer Pool |

| Threads_running 高 | 锁等待或慢 SQL | 看锁、杀慢查询 |

| 连接被拒绝 | max_connections 不够 | 加连接数 + 应用连接池 |

| 复制延迟大 | 大事务、从库弱 | 拆事务、升级从库 |

6.3 用 Python 模拟一次压测结果分析

下面用 Python 对一组压测数据做简单分析（本机实跑）：

# 假设压测在并发 1/8/32/128 下的 TPS 和平均延迟
data = [
    (1,    120,  5.0),
    (8,    850,  9.2),
    (32,  2400, 13.3),
    (64,  3100, 20.6),
    (128, 2800, 45.7),   # 拐点：并发再加，TPS 反而下降
]
print(f"{'并发':>6} {'TPS':>8} {'延迟ms':>10}")
for c, tps, lat in data:
    print(f"{c:>6} {tps:>8} {lat:>10}")

# 找 TPS 最高点
best = max(data, key=lambda x: x[1])
print(f"\n最佳并发 = {best[0]}, TPS = {best[1]}")

实跑输出：

  并发      TPS     延迟ms
     1      120        5.0
     8      850        9.2
    32     2400       13.3
    64     3100       20.6
   128     2800       45.7

最佳并发 = 64, TPS = 3100

这张表告诉你：并发 64 是拐点，再往上延迟飙升、TPS 反而下降。应用层线程池大小就该设成这个值附近。

7 性能调优速查表

| 参数 | 作用 | 生产推荐 |

|---|---|---|

| max_connections | 最大连接数 | 500~1000 |

| innodb_buffer_pool_size | Buffer Pool | 内存 50%~70% |

| innodb_buffer_pool_instances | Buffer Pool 实例 | 8 |

| innodb_log_file_size | redo 文件 | 1G~4G |

| innodb_flush_log_at_trx_commit | redo 刷盘 | 1（双 1） |

| sync_binlog | binlog 刷盘 | 1（双 1） |

| sort_buffer_size | 排序缓冲 | 2M~8M |

| tmp_table_size | 内存临时表 | 32M~128M |

| thread_cache_size | 线程缓存 | 100 |

| open_files_limit | 打开文件数 | 65535 |

| long_query_time | 慢查询阈值 | 1 |

| innodb_lock_wait_timeout | 锁等待超时 | 5~10 |

常用监控命令速记：

-- 一键看连接
SHOW GLOBAL STATUS LIKE 'Threads%';
-- 一键看 QPS
SHOW GLOBAL STATUS LIKE 'Questions';
-- 一键看 Buffer Pool 命中率
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';
-- 一键看临时表落盘
SHOW GLOBAL STATUS LIKE 'Created_tmp%';
-- 一键看当前配置
SHOW VARIABLES LIKE 'innodb%';

8 常见坑与注意事项

坑 1：网上抄一份 my.cnf 直接用。

别人机器的内存、CPU、业务负载和你不一样，抄过来可能 Buffer Pool 设 32G 但机器只有 16G，直接 swap。调参要基于自己的监控数据。

坑 2：sort_buffer_size 设成 1G。

这是每个连接独占的。1000 个连接并发排序，理论上要 1TB 内存，瞬间 OOM。一般 2M~8M。

坑 3：max_connections 设成 10000。

连接数过高每个连接都要分配栈和缓冲，内存吃不消。应用层要做连接池，MySQL 端 500~2000 就够。

坑 4：一次改一堆参数。

改完压测，发现变好了——但不知道是哪个参数起的作用。一次只改一个，记录 before/after。

坑 5：不压测就上线。

参数改完不压测，直接上线。半夜业务高峰才发现新参数让写入性能掉一半。

坑 6：只看 TPS 不看延迟。

TPS 上去了但 P99 延迟从 10ms 涨到 1s，用户体验反而差。压测要同时看吞吐和延迟。

坑 7：忽略 OS 层。

MySQL 调到最优，但操作系统 vm.swappiness=60（默认），MySQL 一紧张就 swap。要设 vm.swappiness=1，关闭 transparent_hugepage。

坑 8：Buffer Pool 调大后不预热。

刚重启的 MySQL，Buffer Pool 是空的，所有请求都要从磁盘读，性能暴跌。生产环境重启前要计划好低谷期，或者用 innodb_buffer_pool_dump_at_shutdown / innodb_buffer_pool_load_at_startup 在重启时自动预热。

坑 9：把 my.cnf 写满几百行参数。

新手喜欢把所有能找到的参数都配上，结果互相冲突、自己也看不懂。默认配置 + 少量关键参数（Buffer Pool、连接数、双 1、慢日志）就够 80% 场景。

9 练习题与参考答案

练习 1：SHOW GLOBAL STATUS 里 Threads_running 长期超过 CPU 核数，说明什么？

<details>

<summary>参考答案</summary>

说明 CPU 已经成为瓶颈，大量线程在争抢 CPU。可能原因：全表扫描、复杂计算、锁等待（线程卡在那没释放）。要先看是不是有慢 SQL，再看是 CPU 还是锁的问题。

</details>

练习 2：MySQL 官方说的"双 1"配置是哪两个参数？为什么？

<details>

<summary>参考答案</summary>

innodb_flush_log_at_trx_commit=1 和 sync_binlog=1。前者保证 redo log 每次提交落盘，后者保证 binlog 每次提交落盘。两个都为 1 时，宕机最多丢一个事务，不会丢已提交的数据。

</details>

练习 3：压测发现并发 64 时 TPS 最高 3100，并发 128 时 TPS 掉到 2800、延迟翻倍，应用线程池该设多少？

<details>

<summary>参考答案</summary>

设成 64 附近（不要超过 64 太多）。并发再加，CPU/锁/IO 已经饱和，再多线程只是排队，延迟飙升。一般取拐点值的 0.8~1.2 倍。

</details>

练习 4：怎么用 Python 算 Buffer Pool 命中率？

<details>

<summary>参考答案</summary>

reads（未命中，从磁盘读）除以 read_requests（总请求），1 减这个比值就是命中率。例：reads=500000, read_requests=100000000，命中率 = 1 - 500000/1e8 = 99.5%。

</details>

练习 5：一次调优你打算改三个参数，应该怎么做？

<details>

<summary>参考答案</summary>

一次只改一个。先压测基线，改参数 A，压测看效果，记录 before/after；如果变好，保留；再改参数 B，重复。一次改三个，出问题不知道是哪个参数导致的。

</details>

下一篇预告：第 25 章 电商数据库设计实战