mysql性能排查

mysql性能瓶颈排查 top/free/vmstat/sar/mpstat

• 查看mysqld进程的cpu消耗占比
• 确认mysql进程的cpu消耗是%user, 还是sys%高
• 确认是否是物理内存不够用了
• 确认是否有swap产生

top (%cpu  load  %MMEM)
free -gt 
vmstat -S m 1 (procs  io  cpu)
sar -u 1 (%user)
sar -d 1

如何优化

• 硬件优化

• 查看mysql线程状态 show [full] processlist

  • 长时间的Sending data

    从引擎层读取数据返回给server端
    
    长时间存在的原因:
      1 没有合适的索引 查询效率低下
      2 读取大量数据 读取缓慢
      3 系统负载高 读取缓慢
      
    如何做：
      1 加上合适的索引
      2 改写sql
      3 增加LIMIT限制每次读取量
      4 检查&升级IO设备性能

  • 长时间等待MDL锁 (waiting for table metadata lock)

    原因:
      DDL被阻塞 进而阻塞其他后续sql
      DDL之前的sql长时间未结束
      
    如何做:
      1 提高每条sql的效率
      2 kill掉长时间运行的sql
      3 把DDL放在夜间低谷时段
      4 采用pt-osc执行DDL

  • 长时间的sleep

  1. 占用连接数
  2. 消耗内存未释放

  3. 可能有行锁(甚至是表锁未释放)

     如何做:
     1 适当调低timeout
     2 主动kill超时不活跃连接
     3 定期检查锁、锁等待
     4 可以利用pt-kill工具

  • 其他状态

    Copy to tmp table [on disk]
      执行alter table修改表结构，需要生成临时表
      建议放在夜间低谷进行， 或者用pt-osc
      
    Creating tmp table
      常见于group by没有索引的情况
      需要拷贝数据到临时表[内存/磁盘上]
      执行计划中会出现Using temporary关键字
      建议创建合适的索引，消除临时表
      
    Creating sort index
      常见于order by没有索引的情况
      需要进行filesort排序
      执行计划中会出现Using filesort关键字
      建议创建排序索引

其他排除方法

• use information_schema; SELECT * from innodb_lock_waits;
• show engine innodb status;
• 测试环境调低long_query_time的值 开启log_queries_not_using_indexes 分析慢日志