高性能Mysql笔记（三）

服务器性能剖析

3.1 性能优化简介

我们将性能定义为完成某件任务所需要的时间度量，换句话说，性能即响应时间。数据库服务器的性能用查询的响应时间来度量，单位是每个查询花费的时间。
3.1.1 通过性能剖析进行优化

性能剖析是测量和分析时间花费在哪里的主要方法。性能剖析一般有两个步骤：测量任务所花费的时间，然后对结果进行统计和排序，将重要的任务排到前面。

性能剖析工具的工作方式基本相同。在任务开始时启动计时器，在任务结束时停止计时器，然后用结束时间减去启动时间得到响应时间。
3.1.2 理解性能剖析

Mysql的性能剖析将最重要的任务展示在前面，但

• 值得优化的查询
  性能剖析不会自动给出哪些查询值得花时间去优化。第一，一些只占总响应时间比重很小的查询时不值得优化的。第二，如果优化的成本大于收益，就应当停止优化。

• 异常情况
  某些任务即使没有出现在性能剖析输出的前面也需要优化。比如某些任务执行次数很少，但每次执行都非常慢，严重影响用户体验。因为其执行效率低，所以总的响应时间并不突出。

• 未知的未知
  一款好的性能剖析工具会显示可能的“丢失的时间”。例如，如果处理器的cpu时间是10秒，而剖析到的任务总时间是9.7秒，那么就有300毫秒的丢失时间。这可能是有些任务没有测量到，也可能是由于测量的误差和精度问题的缘故。如果工具发现有这类问题，则要引起重视。

• 被掩藏的细节
  性能剖析无法显示所有响应时间的分布。

3.2 对应用程序进行性能剖析

对任何需要消耗时间的任务都可以做性能剖析，当然也包括应用程序。
New Relic的软件即服务产品，New Relic会插入到应用程序中进行性能剖析，将收集到的数据发送到一个基于Web的仪表盘，使用仪表盘可以更容易利用面向响应时间的方法分析应用性能。
3.3 剖析MySQL查询
对查询进行性能剖析有两种方式。可以剖析整个数据库服务器，这样可以分析哪些查询是主要的压力来源（如果已经在最上面的应用层做过剖析，则可能已经知道哪些查询需要特别留意）。定位到具体需要优化的查询后，也可以对这些查询进行单独的剖析，分析哪些子任务是响应时间的主要消耗者。
3.3.1 剖析服务器负载
服务器端的剖析很有价值，因为在服务器端可以有效地审计效率低下的查询。定位和优化“坏”查询能够显著地提升应用的性能，也能解决某些特定的难题。还可以降低服务器的整体压力，这样所以的查询都将因为减少了对共享资源的争用而受益。

• 捕获MySQL的查询到日志文件中
  慢查询日志是开销最低，精度最高的测量查询时间的工具。如果长期开启慢查询日志，要部署日志轮换工具。或者不要长期启用慢查询日志，只在需要收集负载样本的期间开启即可。
  MySQL还有另外一种查询日志，被称为“通用日志”。通用日志在查询请求到服务器时进行记录，所以不包括响应时间和执行计划等重要信息。

• 分析查询日志
  利用慢查询日志捕获服务器上的所有查询，并且进行分析。首先应该生成一个剖析报告，需要一款好工具，推荐使用pt-query-digest。只需要将慢查询日志文件作为参数传递给pt-query-digest，它会将查询的剖析报告打印出来，并且能够选择将“重要”的查询逐条打印出更详细的信息。

  3.3.2 剖析单条查询

• 使用 SHOW PROFILE
  默认禁用，可以通过服务器变量在会话（连接）级别动态地修改。

mysql> SET profiling = 1;

然后，在服务器上执行得所以语句，都会测量其消耗的时间和其他一些查询执行状态变更相关的数据。
当一条查询提交给服务器时，此工具会记录剖析信息到一张临时表，并且给查询赋予一个从1开始的整数标识符。
使用SHOW STATUS

MySQL的SHOW STATUS 命令返回了一些计数器。既有服务器级别的全局计数器，也有基于某个连接的会话级别的计数器。例如其中的Queries在会话开始为0，每提交一条查询增加1。如果执行SHOW GLOBAL STATUS，则可以查看服务器级别的从服务器启动时开始计算的查询次数统计。

SHOW STATUS 的大部分结果都只是一个计数器，可以显示某些活动如读索引的频繁程度，但无法给出消耗了多少时间。SHOW STATUS 的结果中只有一条指的是操作时间，而且只能是全局级的，所以无法测量会话级别的工作。

• 使用慢查询日志
  慢查询日志中详细记录的条目包含了SHOW PROFILE和SHOW STATUS所有的输出。
• 使用Performance Schema
  对大多数用户来说，直接通过Preformance Schema的裸数据获得有用的结果相对来说过于复杂和底层。到目前为止实现的这个特性，主要是为了测量当为提升服务器性能而修改MySQL源代码使用，包括等待和互斥锁。

3.3.3 使用性能剖析
好的剖析报告能够将潜在的问题显示出来，但最终的解决方案和需要用户来决定。优化查询时，用户需要对服务器如何执行查询有较深的了解。剖析报告能够尽可能地多收集需要的信息，给出诊断问题的正确方向。

3.4 诊断间歇性问题
间歇性的问题比如系统偶尔停顿或者慢查询，很难诊断。
3.4.1 单条查询问题还是服务器问题

• 使用SHOW GLOBAL STATUS
  这个方法实际上就是以较高的频率比如一秒执行一次SHOW GLOBAL STATUS命令捕获数据，问题出现时，则可以通过某些计数器（比如Threas_running,Threads_connected,Questions和Queries）的“尖刺”或者“凹陷”来发现。
  这个命令每秒输出一行数据，可以运行几个小时或者几天，然后将结果绘制成图形，如果问题确实是间歇性的，发生的频率又较低，也可以根据需要尽可能长时间地运行此命令，直到发现问题再回头来看输出结果。

• 使用SHOW PROCESSLIST
  这个方法是通过不停地捕获SHOW PROCESSLIST的输出，来观察是否有大量线程处于不正常的状态或者其他不正常的特征。

• 使用查询日志
  如果要通过查询日志发现问题，需要开启慢查询日志并在全局级别设置long_query_time为0，并且要确认所有的连接都采用了新的设置。要注意找到吞吐量突然下降时间段的日志。查询是在完成阶段才写入到慢查询日志的，所以堆积会造成大量查询处于完成阶段，直到阻塞其他查询的资源占用者释放资源后，其他的查询才能执行完成。

3.4.2 捕获诊断数据
当出现间歇性问题时，需要尽可能地收集所以数据。

• 诊断触发器
  触发器在问题出现时能够捕获数据，有亮哥哥常见的问题可能导致无法达到预期的结果：误报或者漏检。误报是指收集了很多诊断数据，但期间其实没有发生问题。漏检则指在问题出现时没有捕获到数据。

选择一个合适的阈值很重要，既要足够高，以确保在正常时不会被触发；又不能太高，要确保问题发生时不会错过。

• 需要收集什么样的数据
  尽可能收集所有能收集的数据，但只在需要的时间段内收集。包括系统的状态，cpu利用率，磁盘使用率和可用空间等等。
  pt-collect这个工具可以通过pt-stalk来调用。默认情况下，启动后会收集30秒的数据，然后退出。当触发条件满足时，pt-collect会很好地收集完整的数据。它也会在目录中创建时间戳文件。

• 解释结果数据
  正确设置好触发条件，并且长时间运行pt-stalk,则等待足够长的时间来捕获几次问题，就能得到大量的数据来进行筛选。第一，检查问题是否真的发生了。第二，是否有非常明显的跳跃性变化。查看异常的查询或事务的行为，以及异常的服务器内部行为通常都是最有收获的。

  查询或事务的行为可以显示是否是由于使用服务器的方式导致的问题：性能低下的SQL查询，使用不当的索引，设计糟糕的数据库逻辑架构等。通过抓取TCP流量或者SHOW PROCESSLIST输出，可以获得查询和事务出现的地方，从而知道用户对数据库进行了什么操作。通过服务器的内部行为则可以清楚服务器是否有bug,或者内部的性能和扩展性是否有问题。

3.5 其他剖析工具

3.5.1 使用USER_STATISTICS表
Percona Server和MariaDB 都引入了一些额外的对象级别使用统计的INFORMATION_SCHEMA表。

Percona Server为 MySQL 数据库服务器进行了改进，在功能和性能上较 MySQL 有着很显著的提升。该版本提升了在高负载情况下的 InnoDB 的性能、为 DBA 提供一些非常有用的性能诊断工具；另外有更多的参数和命令来控制服务器行为。

MariaDB数据库管理系统是MySQL的一个分支，主要由开源社区在维护，采用GPL授权许可 MariaDB的目的是完全兼容MySQL，包括API和命令行，使之能轻松成为MySQL的代替品。在存储引擎方面，使用XtraDB（英语：XtraDB）来代替MySQL的InnoDB。

3.5.2 使用strace
strace工具可以调查系统调用的情况。

strace度量时使用的是实际时间，而oprofile使用的是花费的CPU周期。举例：当I/O等待出现问题的时候，strace能将它们显示出来，因为它从诸如read或者pread64这样的系统调用开始计时，直到调用结束。但oprofile不会，因为I/O系统调用并不会真正的消耗CPU周期，而只是等待I/O完成而已。

3.6 总结

• 定义性能最有效的方法是响应时间。
• 性能优化工作需要基于高质量，全方位及完整的响应时间测量。
• 测量的最佳开始点是应用程序，而不是数据库。
• 完整的测量会产生大量需要分析的数据，所以需要用到剖析器。
• 有两种消耗时间的操作：工作或者等待。大多数数剖析器只能测量因为工作而消耗的时间，所以等待分析有时候是很有用的补充，尤其是当CPU利用率很低但工作却一直无法完成的时候。
• 优化和提升是两回事。当继续提升的成本超过收益的时候，应当停止优化。