《高性能MySQL》学习笔记三服务器性能剖析

性能定义为，完成某件任务所需要的时间度量，换句话说，性能即响应时间。数据库服务器的性能用查询的响应时间来度量，单位是每个查询花费的时间。

性能优化并不等于减少资源消耗，有时候消耗更多的资源能够加快查询速度，CPU利用率会上升。当然CPU上升也可能是由于一些bug所造成的，因此不是一个很好的可度量目标。

性能优化会产生一些副产品比如吞吐量优化，毕竟每秒查询数增加意味着每条查询执行时间更短了。

性能剖析

性能剖析是测量和分析时间花费在哪里的方法，一般讨论两种类型的剖析：基于执行时间的分析和基于等待的分析。基于时间的分析研究的是什么任务的执行时间最长，而基于等待的分析是判断任务在什么地方呗阻塞的时间最长。

进行性能剖析之前，必须要先进行测量，这需要系统能提供测量点来捕获并收集数据，但大多数系统是没有测量点的，即使有也只是提供一些活动的计数，而没有活动花费时间的统计，MySQL直到5.5版本后CIA提供了一些基于时间的测量点。当然也不一定非要系统提供测量点，可以采用一些外部工具测量。

以Percona Toolkit中的pt-query-digest为例，性能剖析会将最重要的任务展示在前面，进行排行、总计和平均值等，但是还是会存在一些需要的信息缺失。比如：

值得优化的查询：不能自动给出哪些查询值得花时间优化。强调两点：一、一些占总响应时间比重小的查询是不值得优化的；二、如果优化成本大于收益也就不值得优化。

异常情况：一些由于执行次数少而低占比的查询，单次查询的响应时间很大，这些属于异常的查询，也需要考虑优化。

丢失的时间：任务的总时间和实际测量的时间之间存在差异，这类问题很难发现，一旦发现要引起重视，因为有可能错过某些重要的事情。即使性能剖析没有发现丢失时间，也要考虑这类问题存在的可能性。

被掩藏的细节：性能剖析无法显示所有响应时间的分布，只相信平均值是很危险的，这会隐藏很多信息。需要提供更多的信息，比如直方图、百分比、标准差、偏差等。

对应用程序进行性能剖析也很有必要，对系统的剖析建议都是自顶向下进行，这样可以追踪自用户发起到服务器响应的整个流程。

幸运的是，我们只需要嵌入一个类似New Relic的软件服务产品到系统中，就可以很好的定位分析到我们要处理的性能问题。

剖析MySQL查询

剖析服务器负载可以很有效地审计效率低下的查询。使用慢查询日志是开销最低，精度最高的测量查询时间的工具。

书中建议使用慢查询日志记录查询或者使用pt-query-digest分析tcpdump的结果，是两种比较好的方式来查询要优化的语句的细节。

当定位到需要查询优化的单条查询后，可以针对此查询”钻取“更多的信息，确认为什么花费这么长的时间执行，以及需要如何去优化。在官方MySQL版本下，一般使用以下几种方法来剖析查询：（推荐一个实验的例子：https://blog.csdn.net/zuozewei/article/details/86681249）

使用Explain执行计划：该命令使用十分简单，只需要“EXPLAIN+SQL语句”即可，该命令可以对SELECT语句进行分析，并输出SELECT执行的详细信息，以供开发、测试人员针对性的优化。

使用SHOW PROFILE：该命令是在 MySQL5.1 以后引入的，来源于开源社区中的 Jeremy Cole 的贡献。在 MySQL 数据库中默认是禁用的，可以通过服务器变量在会话（连接）级别动态地修改。然后，在服务器上执行的所有语句，都会测量其耗费的时间和其它一些查询执行状态变更相关数据。但是，尽管报告可以帮助我们定位到哪些活动花费了最多的时间，但并不会告诉我们为什么会这样，要弄清楚为什么会发费这么多时间，就需要深入下去，继续剖析其子任务。

使用SHOW STATUS：该命令返回一些计数器，既有服务器级别的全局计时器，也有基于某个连接的会话级别的计数器。该命令是一个很有用的工具，但并不是一款剖析工具。虽然无法提供基于时间的统计，但是执行查询完后观察某些计数器的值还是很有帮助的。我们可能注意到通过 Explain 执行计划也可以获得大部分相同的信息，但是 Explain 是通过估计得到的结果，而通过计数器则是实际的测量结果。

慢查询日志：该功能需要修改配置文件来生效，慢查询的语句可以在设置的slow.log中查询到。

诊断间歇性问题

间歇性问题比如系统偶尔停顿或者慢查询，很难诊断。这些幻影问题只在没有注意到时才发生，而且无法确认如何重现，诊断这样的问题往往要花费很多时间。

尽量不要使用试错的方式来解决问题，这种方式风险很大，可能导致更坏的结果，而且这种方式也很低效，好的做法是使用正确的测量方式来定位错误。

要解决这类问题，首先要定位是单条查询问题还是服务器问题。如果是服务器问题，那么需要对MySQL内部机制更加了解才能诊断出来问题，当然很多问题也可以通过升级到MySQL新版本来解决（如果没有足够的理由不要尝试升级）。

如何判断单条还是服务器问题，使用下面几种技术：

1. 使用SHOW GLOBAL STATUS：该方法就是以较高的频率比如一秒执行一次SHOW GLOBAL STATUS命令捕获数据，问题出现时，可以通过某些计数器（比如Threads_running、Threads_connected、Questions和Queries）的“尖刺”或者“凹陷”来发现，这个方法比较简单，而且对服务器影响也很小。

2. 使用SHOW PROCESSLIST：该方法不停捕获SHOW PROCESSLIST的输出，来观察是否有大量线程处于不正常的状态或者有其他不正常的特征。也可以直接查询INFORMATION_SCHEMA中的PROCESSLIST表，或者使用innotop工具以较高的频率刷新，以观察屏幕上出现的不正常查询堆积。

3. 使用查询日志：开启慢查询日志并设置long_query_time为0，就可以捕获所有的查询语句。

4. 理解发现的问题：可视化数据。

当出现间歇性问题时，需要尽可能多地收集所有数据，而不只是问题出现时的数据。虽然这样会收集大量的诊断数据，但总比真正能够诊断问题的数据没有被收集到的情况要好。收集前，需要一个可靠且实时的‘触发器“，也就是区分什么时候问题出现的方法以及一个收集诊断数据的工具。

可收集的数据包括系统的状态、CPU利用率、磁盘使用率和可用空间、ps的输出采样、内存利用率，以及可以从MySQL获得的信息，如SHOW STATUS、SHOW PROCEESLIST和SHOW INNODB STATUS。

总结

解决性能问题的方法，首先是澄清问题，然后选择合适的技术来解答这些问题。如果想提升服务器总体性能，好的起点是将所有查询记录到日志中，然后利用工具（pt_query_digest）生成系统级别的剖析报告。如果是要追查某些性能低下的查询，记录和剖析额方法也会有帮助。可以把精力放在寻找那些消耗时间最多的，找到这些查询钻取报告中包含的该查询的详细信息，或者使用SHOW PROFILE及其他诸如EXPLAIN这样的工具。

如果找不到查询性能低下的原因，那么可能遇到了服务器级别的性能问题，这时可以较高精度测量和绘制服务器状态计数器的细节信息。如果通过这样的分析重现了问题，则应该通过同样的数据制定一个可靠的触发条件来收集更多的诊断数据，多花时间来确定可靠的触发条件尽量避免漏检误报。

理论上纯粹的自顶向下的方法分析和详尽的测量只是理想的情况，而我们常常需要处理的是真实系统，需要按实际的情况来处理。