《高性能MySQL》 第三章 服务器性能剖析 读书笔记

性能优化

基于响应时间

性能剖析

测量任务所花费的时间

对结果进行统计和排序，将重要的任务排到前面

时间分类

执行时间

等待时间

理解性能剖析

值得优化的查询

• 一些只占总响应时间比重很小的査询是不值得优化的
• 如果优化的成本大于优化的收益，则应停止优化

异常情况

• 对于某些执行时间过长影响用户体验

被隐藏的细节

• 如平均值会掩盖部分异常的情况

  • 参考工具：pt-query-digest

性能测量工具

New Relic、instrumentation-for-php

剖析MySQL查询

剖析服务器负载

• 借助慢查询日志定位到需要优化的SQL

  • 分析工具：pt-query-digest

剖析单条查询

• 打开profile(SET profiling =1)，再使用show profiles指令，可以测量出耗费的时间和其他一些査询执行状态变更相关的数据(仅在当前会话有效)

  • show profiles

    • show profile

• show profile无法按查询时间对show profile中的条目进行排序，定位真正耗费时间长的项比较困难，可以利用INFORMATION_SCHEMA中的表进行查询并排序

  • SET @query_id =1;
    SELECT STATE, SUM(DURATION) AS Total_R, -> ROUND( -
    -> 100 * SUM(DURATION) /
    -> (SELECT SUM(DURATION)
    -> FROM INFORMATION SCHEMA.PROFILING
    -> WHERE QUERY_ID =~@query id
    -> ),2) AS Pct R,~ ~
    -> COUNT() AS Calls,
    -> SUM(DURATION) / COUNT() AS “R/CalT’ -> FROM INFORMATION SCHEMA.PROFILING -> WHERE QUERY_ID =~@query_id -> GROUP BY STATE ~
    -> ORDER BY Total_R DESC;

• 通过SHOW STATUS/SHOW GLOBAL STATUS查看会话/全局计数信息

• 使用Performance Schema

诊断间歇性问题

• 判断是单条语句问题还是服务器问题

  • SHOW GLOBAL STATUS打印出当前线程序数以及当前正在执行查询的线程数，（借助mysqladmin工具可以每1秒打印一次数据）并绘制成曲线，通过尖峰或低谷分析

    • 通常有两种原因：第一种是新查询等待老查询的锁导致。第二种是有大量的SQL涌进数据库

  • 不停捕获SHOW PROCESSLIST的结果，观察是否有大量线程处于不正常的状态，高版本还可通过INFORMATION_SCHEMA的processlist表

    • 书中给出了两个例子：第一种是INNODB内部争用脏块与频繁刷新导致；第二种是MYSIAM锁表导致

  • 使用查询日志，开启慢查询日志并设置long_query_time=0

• 捕获诊断数据

  • 诊断阈值的设置。需要保证不会误报，又不会漏检，可借助pt-stalk工具

  • 收集数据

    • 如果是执行时间较长，则进行剖析分析。可使用tcpdump
    • 如果是等待时间较长，则进行等待分析.可使用GDB堆栈跟踪
    • 如果是未知问题，则两种分析的数据均要收休
    • 可借助pt-collect工具

  • 解释结果数据

    • 有目的地查看报告

      • 检査问题是否真的发生
      • 是否有非常明显的跳跃性变化

    • 问题分类

      • 查询异常的事件或行为
      • 对于无法解释的问题，打包交由技术支持人员

• 一个诊断案例