mysql性能排查

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 分析慢日志

相关推荐