高性能MySQL 笔记 第三章 服务器性能剖析

服务器性能剖析

性能优化简介

数据库服务器的性能用查询的响应时间来度量，单位是每个查询花费的时间。

优化：在一定的工作负载下尽可能地降低响应时间。

注意：性能优化不是降低CPU利用率！资源就是用来消耗并用来工作的，如果消耗了更多的CPU，但缩短了响应时间，这种情况是有利的。

完成一项任务需要的时间分为：执行时间和等待时间。

要优化执行时间：定位和测量不同子任务花费的时间。然后优化去掉一些子任务、降低子任务的执行频率或者提升子任务的效率。一些运行不频繁或者很短的子任务对整体响应时间的影响很小，通常可以忽略不计。

要优化等待时间：要复杂一些，因为有多种因素影响。

通过性能剖析进行优化

性能剖析一般有两个步骤：

1. 测量任务所花费的时间
2. 对结果进行统计和排序，将重要的任务排到前面

两种类型的性能剖析：

1. 基于执行时间的分析：研究的是什么任务执行时间最长
2. 基于等待的分析：判断任务在什么地方被阻塞的时间最长

理解性能剖析

性能剖析缺失了许多需要的信息：

1. 值得优化的查询：一些只占总响应时间比重很小的查询是不值得优化的；如果优化的成本大于收益，就应当停止优化。
2. 异常情况：某些任务没有出现在性能剖析输出的前面也需要优化。比如某些任务执行的次数很少，但每次执行都非常慢，严重影响用户体验。
3. 未知的未知：考虑丢失时间。丢失时间是指任务的总时间与实际测量到的时间之间的差。
4. 被掩藏的细节：性能剖析无法显示所有响应时间的分布。平均值会隐藏很多信息，而且无法表达全部情况。

对应用程序进行性能剖析

应用程序的性能瓶颈可能有很多影响因素：

• 外部资源，比如调用了外部的Web服务或者搜索引擎
• 应用需要处理大量的数据，比如分析一个超大的XML文件
• 在循环中执行昂贵的操作，比如滥用正则表达式
• 使用了低效的算法

剖析MySQL查询

对查询进行性能剖析有两种方式：

1. 剖析整个数据库服务器，分析出哪些查询是主要的压力来源
2. 定位到需要优化的查询后，可以对这些查询进行单独的剖析，分析哪些子任务是响应时间的主要消耗者

剖析服务器负载

捕获MySQL的查询到日志文件中

通过设置慢查询来捕获查询信息，通过设置long_query_time = 0的参数来捕获所有的查询。

慢查询日志是开销最低，精度最高的测量查询时间的工具，并且不需担心慢查询日志带来额外的I/O开销。更需要担心的是日志可能消耗大量的磁盘空间。如果长期开启慢查询日志，注意部署日志轮转工具。或者只在需要收集负载样本的期间开启即可。

分析查询日志

不要直接打开整个慢查询日志进行分析，首先应该生成一个剖析报告，如果需要，则可以再查看日志中需要特别关注的部分

诊断间歇性问题

比如系统偶尔停顿或者慢查询。

尽量不要使用试错的方式来解决问题。

单条查询问题还是服务器问题

首先确认这是单条查询的问题，还是服务器的问题。

如果服务器上所有程序都突然变慢，又突然变好，每一条查询也都变慢了，那么可能是服务器问题。反之，则可能为单条查询问题。

方法：

1. 使用SHOW GLOBAL STATUS
2. 使用SHOW PROCESSLIST
3. 使用查询日志
4. 使用可视化工具对上面产生的数据进行可视化分析

捕获诊断数据

开始之前，搞清楚两件事：

1. 一个可靠且实时的“触发器”，也就是能区分是什么时候问题出现的方法
2. 一个手机诊断数据的工具

诊断触发器

关键是找到一些能和正常时的阈值进行比较的指标，通常情况下是一个计数。

选择一个合适的阈值。注意阈值不能设置过高，问题持续上升的趋势一般会导致更多的问题发生。

需要收集什么样的数据

只在需要的时间段内尽可能收集所有能收集的数据。

解释结果数据

收集到大量的数据后，从哪里开始呢？

1. 检查问题是否真的发生了
2. 检查是否有非常明显的跳跃性变化