高性能mysql：服务器性能剖析

2019独角兽企业重金招聘Python工程师标准>>>

性能优化简介

MySQL性能定义为完成某件任务所需要的时间量度，换句话说，性能即响应时间，这是一个非常重要的原则。我们通过任务和时间而不是资源来测量性能。数据库服务器的目的是执行SQL语句，所以它关注的任务是查询或者语句。数据库服务器的性能用查询的响应时间来度量，单位是每个查询话费的时间。

如果把性能优化仅仅看成提升每秒查询量，这其实是吞吐量优化。吞吐量的提升可以看作性能优化的副产品。对查询的优化可以让服务器每秒执行更多的查询，因为每条查询执行的时间更短了(吞吐量的定义是单位时间内的查询数量，这正好是我们对性能的定义的倒数)。 如果目标是降低响应时间，那么就需要理解为什么服务器执行查询需要这么多时间，然后减少或者消除那些对获得查询结果来说不必要的工作。也就是说，先要搞清楚时间花在哪里。这就引申出优化的第二个原则：无法测量就无法有效优化。所以第一步应该测量时间花在什么地方。 合适的测量范围是说只测量需要优化的活动。有两种比较常见的情况会导致不合适的测量：

• 在错误的时间启动和停止测量
• 测量的是聚合后的信息，而不是目标活动本身。

一个常见的错误是先查看慢查询，然后又去排查整个服务器的情况来判断问题在哪里。如果确认是有慢查询，那么就应该测量慢查询，而不是测量整个服务器。测量的应该是从慢查询的开始到结束的时间，而不是查询之前或查询之后的时间。 完成一项任务所包含的时间包括等待时间和执行时间。如果要优化任务的执行时间，最好的办法就是通过测量定位不同的子任务话费的时间，然后优化去掉一些子任务、降低子任务的执行频率或者提升子任务的效率。根据时间是花在执行还是等待上的不同，诊断也需要不同的技术。

通过性能剖析进行优化

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

性能剖析工具(推荐percona-toolkit)的工作方式基本相同。在任务开始时启动计时器，在任务结束时停止计时，然后用结束时间减去启动时间得到响应时间。性能剖析报告会列出所有任务列表。每行记录一个任务，包括任务名、任务的执行时间、任务的消耗时间、任务的平均执行时间，以及该任务执行时间占全部时间的百分比。性能剖析报告会按照任务的消耗时间进行降需排序。

我们将实际地讨论两种类型的性能剖析：基于执行时间的分析和基于等待时间的分析。基于执行时间的分析研究的是什么任务的执行时间最长，而基于等待的分析则是判断任务在什么地方被阻塞的时间长。

理解性能剖析

MQSQL的性能剖析将最重要的任务展示在前面，但有时候没显示出来的信息也很重要。

• 值得优化的查询 性能剖析不会自动给出哪些查询值得花时间去优化。一些只占总响应时间比重小的查询是不值得优化的。如果优化的成本大于收益，就应当停止优化。
• 异常情况 某些任务即使没有出现在性能剖析输出的前面也需要优化。比如某些任务执行次数很少，但每次执行都非常慢，严重影响用户体验。因为其执行频率低，所以总的响应时间占比并不突出。
  • 未知的未知 一款好的性能剖析工具会显示可能的“丢失的时间”。丢失的时间指的是任务的总时间和实际测量到的时间之间的差。
  • 被掩藏的细节 性能剖析无法显示所有响应时间的分布。只相信平均值是非常危险的，他会隐藏很多信息。 性能剖析只能管中窥豹，而无法将剖析从任务扩展至事务或者页面查看的级别。

对应用程序进行性能剖析

虽然性能问题大多数情况下都和数据库有关，但应用导致的性能问题也不少。性能瓶颈可能有很多影响因素：

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

剖析MySQL查询

剖析服务器负载

服务器端的剖析很有价值，因为在服务器端可以有效的审计效率地下的查询。定位和优化“坏”查询能够显著地提升应用的性能，也能解决某些特定的难题。还可以降低服务器的整体压力，这样所有的查询都将因为减少了对共享资源的争用而受益。降低服务器的负载也可以推迟或者避免升级更昂贵硬件的需求，还可以发现和定位糟糕的用户体验，比如某些极端情况。

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

在MySQL5.1及更新版本中，慢日志的功能已经被加强了，可以通过设置long_query_time为0来捕获所有的查询，而且查询的响应时间单位已经可以做到微秒级。在MySQL的当前版本中，慢查询日志是开销最低、精度最高的测量查询时间的工具。慢查询日志带来的开销可以忽略不计。更需要担心的是日志可能消耗大量的磁盘空间。如果长期开启慢查询日志，注意要部署日志轮转工具。或者不要长期启用慢查询日志，只在需要收集负载样本的期间开启即可。

总的来说，慢查询日志是一种轻量而且功能全面的性能剖析工具，是优化服务器查询的利器。 有时候因为某些原因如权限不足等，无法在服务器上记录查询。

• 通过--processlist选项不断查看show full processlist的输出，记录查询第一次出现的时间和消失的时间。
• 通过抓取TCP网络包，然后根据MySQL的客户端/服务端通信协议进行剖析。

分析查询日志

不要直接打开整个慢查询日志进行分析，这样做只会浪费时间和金钱。首先应该生产一个剖析报告，如果需要，则可以在查看日志中需要特别关注的部分。自顶向下是比较好的方式，否则容易导致业务的逆优化。

剖析单条查询

SHOW STATUS、SHOW PROFILE、检查慢查询日志的条目。 Performance Schema

使用性能剖析

好的剖析报告能够将潜在的问题显示出来，但最终的解决方案还需要用户来决定。优化查询时，用户需要对服务器如何执行查询有较深的了解。剖析报告能够尽可能多收集需要的信息、给出诊断问题的正确方向，以及为其他诸如EXPLAIN等工具提供基础信息。

诊断间歇性问题

间歇性的问题比如系统偶尔停顿或者慢查询，很难诊断。 尽量不要使用试错的方式来解决问题。这种方式有很大的风险，因为结果可能变得很坏。这也是一种令人沮丧且低效的方式。 实际案例：

• 应用通过curl从一个运行很慢的外部服务来获取汇率报价的数据
• memcached缓存中的一些重要条目过期，导致大量请求落到MySQL以重新生产缓存条目
• DNS查询偶尔会有超时现象
• 可能是由于互斥锁争用，或者内部删除查询缓存的算法效率太低的缘故，MySQL的查询缓存有时候会导致服务有短暂的停顿。
• 当并发度超过某个阀值时，InnoDB的扩展性限制导致查询计划的优化需要很长的时间。

其他剖析工具

使用USER_STATISTICS表

Percona Server和MariaDB都引入了一些额外的对象级别使用统计的INFORMATION_SCHEMA表，这些表对于查找服务器个部分的实际使用情况非常有帮助。

• 可以查找使用得最多或者使用得最少的表和索引，通过读取次数或者更新次数，或者两者一起排序。
• 可以查找出从未使用的索引，可以考虑删除
• 可以看看复制用户的CONNECTED_TIME和BUSY_TIME，已确认复制是否会很难跟上主库的进度。

使用strace

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

总结

• 定义性能最有效的方法是响应时间。
• 如果无法测量就无法有效的优化，所以性能优化工作需要基于高质量、全方位及完整的响应时间测量。
• 测量的最佳开始点是应用程序，而不是数据库。即使问题出现在底层的数据库，借助良好的测量也可以很容易地发现问题。
• 大多数系统无法完整的测量，测量有时候也会有错误的结果。但也可以想办法绕过一些限制，并得到好的结果。
• 完整的测量会产生大量需要分析的数据，所以需要用到剖析器。
• 剖析报告是一种汇总信息，掩盖和丢失了太多细节。而且它不会告诉你缺少什么，所以完全依赖剖析报告也是不明智的。
• 有两种消耗时间的操作：工作或者等待。大多数剖析器只能测量因为工作而消耗的时间，所以等待分析有时候是很有用的补充，尤其是当CPU利用率很低但工作却一直无法完成的时候。
• 优化和提升是两回事。当继续提升的成本超过收益的时候，应当停止优化。
• 注意你的直觉，但应该只根据直觉来指导解决问题的思路，而不是用于确定系统的问题。决策应该尽量基于数据而不是感觉。

总体来说，我们认为解决性能问题的方法，首先是要澄清问题，然后选择合适的技术来解答这些问题。

转载于:https://my.oschina.net/u/4008390/blog/2253883