MySQL 5.6升级至5.7后应用整体性能骤降的排查调试方法

我完全明白你的困扰——升级后不是个别查询变慢，而是整个数据库的性能都拉胯了，光看慢查询日志确实抓不到全局问题。下面是我在处理这类升级性能问题时常用的几个方向，比逐个调参数更高效：

• 先对比新旧版本的核心配置差异
  MySQL 5.7在很多关键参数上改了默认值，这往往是整体性能下滑的元凶：

  • 比如sql_mode默认开启了ONLY_FULL_GROUP_BY等严格模式，某些旧代码可能触发隐式转换导致性能损耗；
  • innodb_buffer_pool_size的默认计算逻辑变了，如果你的服务器内存没调整，可能缓冲池实际大小比5.6时小了；
  • optimizer_switch里的derived_merge默认开启，部分复杂子查询的执行计划会变差；
  • 还有query_cache在5.7里默认是关闭的，如果之前5.6依赖这个缓存，升级后直接没了自然会慢。
    你可以用mysqld --help --verbose分别导出5.6和5.7的默认配置，用diff工具对比，重点看InnoDB、优化器、缓存相关的参数。

• 排查系统层面的资源瓶颈
  数据库性能从来不是孤立的，升级后系统资源的使用可能悄悄变了：

  • 用top/htop看CPU，是不是MySQL进程占了满核，或者有其他进程抢资源？
  • 用free/vmstat看内存，有没有出现大量swap交换（如果缓冲池设太大，系统内存不够就会频繁换页）？
  • 用iostat/iotop看磁盘IO，5.7默认的InnoDB日志刷新策略、自动增量锁模式（innodb_autoinc_lock_mode=2）可能让IO模式变了，要是磁盘IO跑满了，性能肯定上不去。

• 用MySQL自带的性能工具做全局诊断
  5.7新增的Performance Schema和Sys Schema就是为这类全局问题设计的，比慢查询日志好用多了：

  • 先确认performance_schema=ON（默认应该开了），然后查sys.io_global_by_file_by_bytes看哪些文件IO量最大，sys.memory_global_by_current_bytes看内存都花在哪了，schema_unused_indexes找冗余索引拖慢写入；
  • 执行SHOW ENGINE INNODB STATUS，重点看缓冲池命中率（低于99%就说明缓冲池不够）、日志刷新状态、锁等待统计，要是buffer_pool_wait_free数值很高，就是缓冲池不足导致的等待。

• 验证升级后的数据与索引状态
  升级过程中MySQL会做一些表结构转换，要是没处理好也会影响整体性能：

  • 跑CHECK TABLE对所有表做完整性检查，看有没有损坏或需要修复的表；
  • 执行ANALYZE TABLE更新表的统计信息，5.7的优化器依赖更精准的统计数据，旧统计信息会让执行计划集体跑偏；
  • 检查有没有用5.7默认禁用的特性，比如MyISAM存储引擎（虽然5.6也不推荐，但5.7对它的支持更弱），或者过时的分区语法。

• 做受控的负载对比测试
  如果条件允许，把5.6的数据库镜像到一个和生产配置一致的5.7实例上，用sysbench或者回放生产的查询日志（用pt-query-digest导出后回放），对比两个版本的QPS、响应时间、资源占用。这样能排除生产环境其他变量的干扰，精准定位是版本配置问题还是其他因素。

内容的提问来源于stack exchange，提问作者Itay Moav -Malimovka