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与错误率变化，固化到基线。