Ubuntu MySQL如何排查性能瓶颈

Ubuntu MySQL 性能瓶颈排查路线图

一快速定位瓶颈类型

系统层面先看资源：用top/htop观察 CPU、内存、负载；用vmstat 1看上下文切换与系统负载；用iostat -x 1查看磁盘 I/O（关注await、svctm、util）。若 CPU 高且 I/O 等待高，多为磁盘/索引/SQL问题；若 CPU 不高但负载高，可能是并发/锁导致。
MySQL 层面抓“正在发生”的异常：执行SHOW FULL PROCESSLIST或查询information_schema.processlist找出长时间运行或状态异常的 SQL；用SHOW GLOBAL STATUS LIKE 'Threads_running’观察当前运行线程数是否长期偏高；用SHOW ENGINE INNODB STATUS查看锁等待/死锁与缓冲池、日志等 InnoDB 细节。
判断瓶颈归属：
- 大量线程排队或 Threads_running 持续接近 max_connections → 多为连接/并发瓶颈；
- Innodb_buffer_pool_reads 高、磁盘 util 高 → 缓冲池不足/IO 瓶颈；
- 执行计划中type=ALL、Rows_examined 远大于 Rows_sent → 缺失索引/SQL 写法问题；
- 大量锁等待或死锁 → 事务/隔离级别/锁竞争问题。

二启用与分析慢查询

开启与动态调整（无需重启）：
- SET GLOBAL slow_query_log = ‘ON’;
- SET GLOBAL long_query_time = 1;（按业务先设1 秒再逐步收紧）
- SET GLOBAL log_queries_not_using_indexes = ‘ON’;
日志分析：
- 内置工具：mysqldumpslow -s t /var/log/mysql/mysql-slow.log（按时间排序）；
- 推荐：pt-query-digest /var/log/mysql/mysql-slow.log，可聚合相似 SQL、定位 Top N 慢 SQL。
执行计划与诊断：
- EXPLAIN查看是否走索引、扫描行数；
- EXPLAIN ANALYZE（MySQL 8.0+）可看到实际执行成本；
- 结合 sys 库快速筛查：sys.statements_with_full_table_scans、sys.schema_unused_indexes。

三连接与锁争用排查

连接与线程：
- 观察Threads_connected/Threads_running与max_connections；
- 若连接数长期接近上限，检查应用是否连接泄漏、连接池是否过小或超时设置不当；必要时优化连接池（如ProxySQL）或适度提升 max_connections。
锁与事务：
- 用SHOW ENGINE INNODB STATUS查看LATEST DETECTED DEADLOCK、TRANSACTIONS段，定位行锁/间隙锁争用；
- 检查事务是否过长、是否频繁范围更新/删除导致锁放大；
- 结合**SHOW GLOBAL STATUS LIKE ‘Innodb_row_lock%’**观察锁等待与冲突趋势。

四内存与 InnoDB 关键指标

缓冲池命中率：
- 计算：缓冲池命中率 ≈ 1 − Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests；
- 若命中率偏低，说明innodb_buffer_pool_size偏小，热点数据频繁落盘。
配置要点：
- 将innodb_buffer_pool_size设为物理内存的50%–70%（视实例角色与内存压力而定）；
- 关注Innodb_buffer_pool_wait_free，若持续大于 0，说明缓冲池空间不足或刷脏跟不上；
- 结合SHOW ENGINE INNODB STATUS中的BUFFER POOL AND MEMORY、LOG段落，检查脏页比例、检查点与日志刷新压力。

五配置与工具链建议

基线配置模板（/etc/mysql/my.cnf 的 [mysqld] 段，按需调整）：
- slow_query_log = 1
- slow_query_log_file = /var/log/mysql/mysql-slow.log
- long_query_time = 1
- log_queries_not_using_indexes = 1
- innodb_buffer_pool_size = 物理内存的 50%–70%（如 4G/8G）
- max_connections = 依据连接峰值与实例规格设置（避免过大导致上下文切换与内存压力）
- 修改后用**SHOW VARIABLES LIKE ‘%参数名%’;**验证生效。
常用工具：
- 实时监控：mytop（类 top 的 MySQL 监控）、innotop（聚焦 InnoDB I/O 与锁）；
- 监控平台：Prometheus + Grafana（抓取 MySQL 指标并可视化）、Percona PMM（全链路监控与诊断）；
- 巡检建议：MySQLTuner定期给出参数与配置建议。
应急与复盘：
- 对影响业务的慢 SQL 先KILL止血，再EXPLAIN/ANALYZE优化；
- 优化后做压测与 A/B 对比，观察平均响应时间、TPS、P95/P99与错误率变化，固化到基线。

最新问答