mysql|读书-高性能MySQL 第三章读书|读书|高性能MySQL|高性能

服务器性能剖析最经常碰到的三个性能相关的服务请求是：如何确认服务器是否达到了性能最佳的状态，找出某条语句为什么执行不够快，以及诊断被用户描述成“停顿”、“堆积”或者“卡死”的某些间歇性阴暗故障。核心方法是专注于服务器的时间花费在哪里，使用的技术则是性能剖析工具（profiling）。本章，将展示如何测量系统并生成剖析报告，以及如何分析系统的整个对渣（stack），包括从应用程序到数据库服务器到单个查询。
首先我们要保持空诶精神，抛弃掉一些关于性能的常见误解。
3.1 性能优化简介我们将性能定义为完成某件任务所需要的时间度量，性能即响应时间，这是一个非常重要的原则。我们通过任务和时间而不是资源来衡量性能。数据库服务器的目的是执行SQL语句，所以他关注的任务是查询语句。数据库服务器的性能用查询的相应时间来度量，单位是每个查询花费的时间。
很多人对此很迷恋。假如你认为性能优化是降低CPU利用率，那么可以减少对资源的使用，但是这是一个陷阱，资源是用来消耗并用来工作的，所以有时候消耗更多的资源能够加快查询速度。很多时候使用老版本InnoDB引擎的MySQL升级到新版本后，CPU利用率上升的厉害，这并不代表性能出现了问题，反而说明了新版本的InnoDB对资源的利用率上升了。查询的相应时间则更能体现升级后的性能是不是变得更好。版本升级有时候会带来一些bug，比如不能利用某些索引从而导致CPU利用率上升，CPU利用率只是一种现象，而不是很好的可度量目标。
同样，如果把性能优化仅仅看成提高每秒查询量，这其实只是吞吐量优化。吞吐量的提升可以看做性能优化的副产品、对查询的优化可以让服务器每秒执行更多的查询，因为每条查询执行的时间更短了。
所以如果目标是减低相应时间，那么就要理解为什么服务器执行查询需要这么多时间，然后减去或者消除那些获得查询结果来说不必要的工作。也就是说，先要搞清楚时间花在哪里。这就引申出优化的第二个原则：无法测量就无法有效地优化。所以第一步应该测量时间花在什么地方。
我们观察到，很多人在优化时，都将精力放在修改一些东西上，却很少去进行精确的测量。我们的做法完全相反，将花费非常多，甚至90%的时间来测量响应时间花在哪里。如果通过测量没有找到答案，那要么是测量的方式错了，要么是测量的不够完整。如果测量了系统中完整而且正确的数据性能问题一般都能暴露出来，对症下药的解决方案也就比较明了。测量是一项很有挑战性的工作，并且分析结果也同样具有挑战性，测出时间花费在哪里，和知道为什么花在那里，是两码事。
前面提到需要合适的测量范围，这是什么意思能？合适的测量范围是说还有测量需要优化的活动。有两种比较常见的情况会导致不合适的测量：

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

例如，一个常见的错误是先查看慢查询，然后又去排查整改服务器情况来判断问题在哪里。如果确认有慢查询，那么就应该测量慢查询，而不是测量整改服务器。测量的应该是从慢查询的开始时间到结束时间，而不是查询之前或者查询之后的时间。
完成一项任务所需要的时间可以分为两部分：执行时间和等待时间。如果要优化任务的执行时间，最好的办法是通过测量定位不同的子任务花费的时间，然后优化去掉一下子任务、降低子任务自信频率或者提升子任务的效率。而优化任务的等待时间相对要复杂一些，以为等待有可能是由其他系统间接影响导致，任务之间也可能由于争用次哦按或者CPU资源而相互影响。根据时间是花在执行还是等待上的不同，诊断也需要不同技术和工具。
刚擦索道需要定位和优化子任务，但只是一笔带过。一些运行不频繁或者很短的子任务对整体相应时间的影响很小，通常可以忽略不计。那么如何亿哪些子任务是优化的目标呢？这个时候性能剖析就可以派山用场了。
3.1.1 通过性能剖析进行优化
一旦掌握并时间面向响应时间的优化方法，就会发现需要不断地对系统进行性能剖析（profiling）。
性能剖析是测量和分析时间花费在哪里的主要方法。性能剖析一般有两个步骤：测量任务所花费的时间；然后对结果进行统计和排序，将重要的任务排到前面。
性能剖析工具的工作方式基本相同。在任务开始时启动计时器，在任务技术时停止计时器，然后用结束时间减去启动时间得到响应时间。也有些工具会记录任务的父任务。这些数据结果可以用来绘制调用关系图，但对于我们的目标来说更重要的是，可以将相似的任务分组并进行汇总。对相似的任务分组并进行汇总可以帮助最哪些分发哦一组的任务做更复杂的统计分必须，但至少需要知道没一组有多少任务，并计算出总的响应时间。通过性能剖析报告（profile report）可以获得需要的结果。性能剖析报告会列出所有任务列表。每行记录一个任务，包括任务名、任务执行时间、任务笑傲时间、任务平均执行时间，以及该任务执行时间占全部时间的百分比。性能剖析报告会安装任务的消耗时间进行降序排序。
下面举例说明，这是从整体的角度分析相应时间，后面会演示其他角度的分析结果。下面输出的是用Percona ToolKit中的pt-query-digest（实际上是Maatkit工具中的mk-query-digest）分析得到的记过。为了显示方便，对结果做了一些微调，并且只截取了前几行的结果

文章图片

上面只是性能剖析结果的前几行，根据总相应时间进行排名，只包括剖析所需要的最小列组合。没一行都包括了查询的相应时间和占总时间的百分比、查询的执行次数、单纯执行的平均时间，以及该查询的摘要。通过这个性能剖析可以清楚第看到每个查询之间的成本比较，以及每个查询占总成本的比较。在这个例子中，任务指的就是查询，实际上在分析MySQL的时候经常都指的是查询。
将实际讨论的两种类型的性能剖析：基于执行时间的分析和基于等待的分析。基于执行时间的分析研究的是什么任务的执行时间最长，而基于等待的分析是判断任务在上面地方被阻塞时间最长。
如果任务执行时间长是因为消耗了太多的资源并且大部分时间花费在执行上，等待的时间不多，这种情况基于等待的分析作用就不大。反之亦然，如果任务一直在等待，没有消耗什么资源，去分析执行时间就不会有什么结果。如果不能确认问题是出在执行还是等待上，那么这两种方式都要试试。
事实上，当基于执行时间的分析发现一个任务需要花费太多时间的时候，应该深入去分析一下，可能会发现某些“执行时间”实际上是在等待。例如上面简单的性能剖析显示表InvitesNew上的SELECT查询花费了大量时间，如果深入研究，则可能发现时间都花费在等待I/O完成上。
在对系统进行性能剖析前，陛下先要能够进行测量，这需要系统可测量化的支持。刻测量的系统一般会有多个测量点可以捕获并搜集数据，但实际系统很少可以做到可测量化。大部分系统没有多少克测量点，即使有也只是提供一些活动的计数，而没有活动花费的时间统计。MySQL就是一个典型的例子，知道版本5.5才第一次提供了Performance Schema，其中有一些基于某种活动发生的次数。这也是我们最终决定穿件Percons Server的主要原因，Percona Sever从版本5.0开始提供很多更详细的查询级别的测量点。
虽然理想的性能优化急速依赖于更多的测量点，但幸运的是，即使系统没有提供测量点，也还有其他办法可以开展优化工作。因为可以从外部去测量系统，如果测量失败，也可以根据对系统的了解做出一些靠谱的猜测。但这么做的时候一定要记住，不管是外部测量还是猜测，数据都不是百分之百准确的，这是系统不透明所带来的风险。
举个例子，在Percona Server 5.0中，慢查询日志揭露了一些性能地下的原因，如磁盘I/O等待或者行级锁等待。如果日志显示一条查询花费了10秒，其中9.6秒在的办法带磁盘I/O,那么追究其他的4%的事故华北花费在哪里就没有意义，磁盘I/O才是最重要的原因。
3.1.2理解性能剖析
MySQL的性能剖析（profile）将最重要的任务展示在前面，但有时候没显示出来的信息也很重要。不幸的是，尽管性能剖析输出了排名，总计和平均值，但还是有很多需要的信息缺失，如下所示。
值得优化的查询（worthwhile query）性能剖析不会自动给出哪些查询值得花时间去优化。这把我们带回到优化的本意。强调以下两点：第一，一些只占总响应时间比重很小的查询是不值得优化的。根据阿姆达尔定律，对于一个占总响应时间不超过5%的查询进行优化，无论如何努力，收益也不会超过5%。第二，如花费了1000美元去优化一个任务，但业务的收入没有任何增加，那么可以锁反而导致业务被逆优化了1000美元。如果优化的成本大于收益，就应当停止优化。
异常情况某些任务即使没有出现在性能剖析输出的前面也需要优化。比如某些任务执行此时很少，但每次执行都非常慢，严重影响用户体验。因为其执行频率低，所以总的响应时间占比并不突出。
未知的未知一款好的性能剖析工具会显示可能的“丢失的时间”。丢失的时间是指任务的总时间和实际测量到的时间之间的差。例如，如果处理器的CPU时间是秒，而剖析到任务的总时间是9.7秒，那么就有300毫秒的丢失时间。这可能是有些任务没有测量到，也有肯能是由于测量的误差和精度问题的缘故。如果工具发现了这类问题，则要引起重视，因为有肯能错过了某些重要的事情。即使性能剖析没有发现丢失时间，也需要考虑这类问题存在的可能性，这样才不会错过重要的信息。
被掩藏的细节性能剖析无法显示所有响应时间的分布。只相信平均值是非常危险的，他会隐藏很多信息，而且无法表达全部情况。Peter经常举例说明医院所有病人的平均体温没有任何意义。假如在前面的性能剖析的例子的第一项中，如果有两次查询的响应时间是1秒，而另外的12771此查询响应时间是几十微秒，结果会怎样？从平均值里面无法发现两次1秒的查询。要做出最好的决策，需要为性能剖析里输入的这一行中包含的12773次查询提供更多信息，尤其是更多响应时间的信息，比如直方图、百分比、标准差、偏差指数等。
好的工具可以自动地获取这些信息。实际上，pt-query-digest就在剖析的结果里包含了很多这类细节信息，并且输出在剖析报告中。对此我们做了简化，可以将经集中在重要而基础的例子上:通过排序将最昂贵的任务排在前面。本章后面会展示更多丰富而有用的性能剖析的例子。
前面的例子中，有一个重要的缺失，就是无法再更高层次的堆栈中进行交互式的分析。当我们仅仅着眼于服务器中的单个查询时，就无法将相关的查询联系起来，也无法理解这些查询是否是在同一个用户交互的一部分。性能剖析只能管中窥豹，而无法将剖析从任务扩展事务或者页面查看（page view）的级别。也有一些版本可以解决这个问题，比如给查询加上特殊注释作为标签，可以标明其来源并据此做聚合，也可以在应用层面增加更多的测量点，这是下一节的主题。
3.2 对应用程序进行性能剖析对任何需要消耗时间的任务都可以做性能剖析，当然也包括应用程序。实际上，剖析应用程序一般比剖析数据库服务器容易，而且汇报更多。虽然前面颜色的例子都是针对MySQL服务器的剖析，但对系统性能剖析还是建议自上而下地进行，这样可以追踪用户发起到服务器相应的整个流程。虽然性能问题大多数情况下都和数据库有关，但应用导致性能问题也不少。性能瓶颈可能有很多影响因素：

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

幸运的是，确定MySQL的问题没有这么复杂，只需要一款应用程序的剖析工具即可。建议在所有的新项目中都考虑包含性能剖析代码。往已有的项目中假如性能剖析代码也许很难，新项目就简单一些。
一款工具New Relic的软件即服务（software-as-service）产品，可以测量很多用户体验相关的点，涵盖从Web浏览器到应用代码，再到数据库及其他外部调用。
像New Relic这类工具的好处是可以全天候地测量生产环境的代码-既不限于测试环境，也不限于某个时间段。、
3.2.1 测量PHP应用程序
如果不使用New Relic，也有其他选择。尤其是对PHP，有好几款工具可以帮助进行性能剖析。其中一款xhprof（http://www.php.net/xhprof），xhprof有很多高级特性，易于安装使用，轻量级，可扩展性好，可以在生产环境大量部署全天候使用，还能针对函数调用进行剖析，并根据耗费时间排序。相比xhprof，还有一些更底层的工具，比如xdebug，Valgrind和cachegrind，可以从多个角度对代码进行检测。有些工具会产生大量输出，并且开销很大，并不适合在生产环境运行，但开发环境却可以发挥很大优势。

MySQL企业监控器的查询和分析功能
MySQL的企业监控器（Enterprise Monitor）也是值得考虑的工具之一。这是Oracle提供的MySQL商业服务支持的一部分。他可以捕获发送给服务器的查询。有良好的用户界面，可以直观显示查询的剖析结果，并且根据时间段进行缩放，例如可以选择某个异常的性能尖峰时间来查看状态图。也可以查看EXplAIN出来的执行计划，在故障诊断的时候非常有用。

3.3 剖析MySQL查询对查询进行性能剖析有两种方式。可以剖析整个数据库服务器，这样可以分析出主要的压力来源。定位到具体需要优化的查询后，也可以钻取下去对这些查询进行单独的剖析，分析哪些子任务是相应时间的主要消耗者。
3.3.1 剖析服务器负载
服务器端的剖析很有价值，因为在服务器可以有效地审计效率低下的查询。定位和优化“坏”查询能够显著地提升应用的性能，也可以解决某些特定的难题。还可以减低服务器的整体压力，这样所有的查询都将因为减少了对共享资源争用而受益。降低服务器的负载也可以推迟或者避免升级更昂贵的硬件的需求，还可以发现和定位糟糕的用户体验，比如一些极端的情况。
MySQL的没一个新版本都会增加更多的可测量点。如果当前的趋势可靠的话，那么在性能方面比较重要的测量需求很快能够子啊全球范围内得到支持。但如果只是需要剖析并找出代价高的查询，就不用如此复杂。可以使用慢查询日志。
捕获MySQL的查询到日志文件中在MySQL中，慢查询日志最初只是捕获比较慢的查询，而性能剖析却需要针对所有的查询。可以通过log_query_time为0来捕获所有传销，而且查询的响应时间单位已经可以做到微秒级。如果使用的是Percons Server，提供了对日志内容和查询捕获更多的控制能力。
在MySQL的当前版本中，慢查询日志是开销最低、精度最高的测量查询时间的工具。在I/O密集型场景做过基准测试，慢查询日志带来的开销可以忽略不计（实际上CPU密集型的影响稍微大一些）。更需要担心的是日志可能消耗大量的磁盘空间。如果长期开启慢查询日志，注意要部署日志轮转工具（log rotation）。或者不要长时间启用慢查询日志，只在需要搜集负载样本期间开启即可。
MySQL还有另外一种查询日志，被称为“通用日志”。但很少用于分析和剖析服务器的性能。通用日志在查询请求到服务器时进行记录，所以不包含相应时间和执行计划等重要信息。
Percona Server的慢查询日志比MySQL官方版本记录了更多细节且有价值的信息，如查询执行计划、锁、I/O活动等。这些特性都是随着处理各种不同的优化场景需求而慢慢加进来的。另外在可管理性上也进行了增强。比如全局修改针对每个连接的log_query_time的阈值，这样当应用连接池或者持久连接的时候，可以不用重置会话级别的变量而启动或者停止连接的查询日志。总的来说，慢查询日志是一种轻量而且功能全面的性能剖析工具，是优化服务器查询的利器。
有时候因为某些原因如权限不足等，无法再服务器上记录查询，这样的限制也会经常遇见。因此可以使用Percona Toolkit中的pt-query-digest中。第一种是通过–processlist选项不断查看SHOW FULL PROCESSLIST的输出，记录查询第一次出现和小时的时间。某些情况下这样的精度也足够发现问题，但却无法捕获所有的查询。一些执行比较快的查询可能在量子执行时间的间隙执行完成了，就无法捕获到。
第二种技术是通过抓取TCP网络包，然后根据MySQL的客户端/服务端通信协议进行机械。可以先通过tcpdump将网络包数据保存到磁盘，然后使用pt-query-digest的–type=tcpdump选项来进行解析并进行分析查询。此方法精度比较高，并且可以捕获所有查询。还可以机械更高级的协议特性，比如可以解析二进制协议，从而创建并执行服务端预解析的语句（prepared statement）及压缩协议。另外还有一种方法，就是通过MySQL Proxy代理层的脚本来记录所有查询，但实际中很少这样做。
分析查询日志建议从现在开始利用慢查询日志捕获服务器上所有查询，并进行分析。可以在一些典型的是啊见窗口如业务高峰期的一个小记录查询。如果业务趋势比较均衡，那么在一分钟甚至更短的时间内捕获需要优化的低效查询也是可行的。
不要直接打开整个慢查询日志进行分析，这样只会浪费时间和金钱。首先应该生成一个剖析报告，如果需要，则可以在查看日志中需要特别关注的部分。自顶向下是比较好的方式，否则可能像前面提到的，反而导致业务的逆优化。
从慢查询日志生成剖析报告可以使用pt-query-digest，功能强大，可以将报告保存到数据库中，以及追踪工作负载岁时间的变化。
一般情况下，只需要将慢查询日志文件作为参数传递给pt-query-digest，就可以正确地工作了。下面是一分pt-query-digest输出报告的例子，作为性能剖析的开始，这是前面提到过的一个未修改的剖析报告：

文章图片

ID，对查询语句计算出的Hash指纹，计算式去掉了查询条件中的文本值和所有空格，并且全部转换为小写字母（注意，第三条和第四条的摘要看起来是一样，但是Hash指纹是不一样的）。表名InvitesNew后面的问号意味着这是一个分片（shard）的表，表名后面分片的标识被问号替代，这样就可以将同一组分片表作为一个整体做汇总统计。
报告中V/M列提供了方差均值比（variance-to-mean ratio）的详细数据，方差均值比也就是常说的离差指数（index of dispersion）。离差指数搞的查询对应的执行时间变化加到，而这类查询通常都值得去优化，如果指定了–explain选项，输出结果中会正价一列简要描述查询的执行计划。通过联合观察执行计划列和V/M列，可以更容易识别出性能低下需要优化的查询。
最后，在尾部也增加了一行输出，显示其他17个占用比价低，而不值得单独显示的查询的统计信息。可以通过–limit和–outliers选项指定工具显示更多查询的详细信息，我不是讲义写不重要的查询汇总在最后一行，默认只会打印时间消耗前10位的查询，汇总执行时间超过1秒阈值很多倍的查询，这两个限制都是可以配置的。
剖析报告的后面包含了每种查询的详细报告。可以通过和查询ID或者排名来匹配前面的剖析统计和查询的详细报告。下面排名第一也就是“最慢”的查询的详细报告：

文章图片

查询报告的顶部包含一些元数据，包含查询执行的频率，平均并发度，以及该查询性能最长的一次执行在日志文件中的字节偏移值，接下来还有一个表格格式的元数据，包括诸如诸如标准差一类的统计信息。
接下里的部分就是响应时间的直方图。可以看到上面这个查询在Query_timedistribution部分的直方图上有两个明显的高峰，大部分情况下执行都需要几百毫秒，但在快三个数据级的部分也有一个明显的尖峰，大部分情况下执行都需要几百毫秒，但在快三个数量级的部分也有一个明显的尖峰，几百微秒就能执行完成。如果这是Percona Server的记录，那么在查询日志中还会有更多丰富的属性，可以对查询进行切片分析到底发生了什么。比如可能是因为查询条件传递了不同的值，而这些值的分布很不均衡，导致服务器选择了不同的索引；或者是由于查询缓存命中等。在实际系统中，这种有两个尖峰的直方图情况很少见，尤其的对于简单的查询，查询越简单执行计划也越稳定。
在细节报告的最后部分是方便复制，粘贴到终端去检查表的模式和状态的语句，以及完整的可以用explain分析执行计划的语句。explain分析的语句要求所有的条件是文本值而不是“指纹”替代符，所以是真正可直接执行的语句。在本利中执行时间最长的一条实际的查询。
确定需要优化的查询后，可以利用这个报告迅速检查查询的执行情况。这个工具我们经常使用，并且会根据根据的情况不断进行修正以帮助提升工具的可用性和效率，强烈建议大家都能熟练使用它。MySQL本身在未来或许会有更复杂的测量点和剖析工具，通过慢查询日志或者使用pt-query-digest分析tcpdump的结果，是可以找到最好的两种方式。
3.3.2 剖析单条查询
在丁外到需要优化的单条查询后，可以针对此查询“钻取”更多的信息，确认为什么会花费这么长时间执行，以及需要如何去优化。关于如何优化查询的技术将在后面章节介绍。本章节的主要目的介绍如何方便地测量查询执行的各部分花费多少时间，有了这些数据才能决定采用何种优化的技术。
在实际应用中，除了SHOW STATUS、SHOW PROFILE、检查查询日志的条目，下面逐条介绍。
使用SHOW PROFILE SHOW PROFILE命令是在MySQL5.1版本引进的。这也是在本书中唯一一个GA版本包含真正的查询剖析工具。默认是禁用，但可以通过服务器变量在会话（连接）级别动态的修改。

mysql> SET profiling=1；

然后，在服务器上执行所有的语句，都会测量其消耗的时间和其他一些查询执行状态变更相关的数据。这个功能有一定作用，但是在未来版本会被Performance Schema所取代。尽管如此，这个工具最有用的最用还是在语句执行期间剖析服务器的具体工作。
当一条查询提交给服务器时，此工具会聚类剖析信息到一张临时表，并且给查询赋予一个从1开始的整数标识符。下面是对Sakila样本数据库的一个视图的剖析结果：

文章图片

该查询返回了997行记录，花费大概1/6秒。下面看一下SHOW PROFILES有什么结果：

文章图片

首先可以看到的是以很高的精度显示了查询的相应时间，这很好。MySQL客户端显示的时间只有两位小数，对于一些执行的很快的查询这样的精度是不够的，下面继续看接下来的输出：

文章图片

文章图片

剖析报告给出了每个步骤及其花费的时间，看结果很那迅速地确定那个步骤花费的时间最多。因为输出是按照习性顺序排序的，而不是按花费的时间排序的–而实际上我们更关心花费了多少时间，这样才能知道那些开销比较大。到哪不行的是不能通过ORDER BY之类的命令进行重新排序。假如不使用SHOW PROFILE命令而是直接查询INFORMATION_SCHEMA对应的表，则可以按照需要格式输出：

mysql>set @query_id = 1; mysql>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/Call" FROM information_schema.PROFILING WHERE QUERY_ID=@query_id GROUP BY STATE ORDER BY Total_R DESC

文章图片

通过这个结果可以很容易查询时间太长主要是因为花了一大半的时间在将数据复制到临时表这一步。那么优化就要考虑如何改写查询避免使用临时表，或者提升临时表的使用效率。第二个消耗时间最多的是“发送数据（Sending data）”，这个状态代表的原因非常多，可能是各种不同的服务器活动，包括在关联时搜索匹配的行记录等，这部分很难说优化节省多少消耗的时间。另外也要注意“结果排序（Sortingresult）”花费的时间占比非常低，所以这部分是不值得去优化的。这是一个比较典型的问题，所以一般我们都不建议用户在“优化排序缓存区（tuning sort buffer）”或者类似的活动上花时间。
尽管剖析报告能帮助我们丁外到哪些活动花费了最多的时间，但并不会告诉我们为什么会这样。要弄清楚为什么复制数据到临时表需要花费这么多的时间，就需要深入下去，进一步剖析这一步的子任务。
SHOW STATUS MySQL的SHOW STATUS命令返回来一些计数器。既有服务器级别的全局计数器，也有基于某个连接的会话级别的计数器。例如其中Queries在会话开始时为0，每次提交一条查询就会增加1。如果执行SHOW GLOBAL STATUS，则可以查看服务器级别的从服务器启动时开始计算查询次数统计。不同的计数器的课件范围不一样，不过全局的计数器也会出现在SHOW STATUS的结果中，容易被误认为是会话级别的，千万不要搞错。在使用这个命令的时候需要注意几点，搜集合适级别的数据是很关键的。如果打算优化某些特定连接观察到的东西，测量的取舍全局级别的数据就会导致混乱。MySQL官方手册中对所有变量是会话级还是全局级做了详细的说明.
SHOW STATUS是一个有用的工具，但并不算一款剖析工具。SHOW STATUS的大部分结果都只是一个计数器，可以显示某些活动如读索引的频繁程度，但无法给出消耗多少时间。SHOW STATUS的结构中只有一条指的是操作的时间（Innodb_row_lock_time），而且只能是全局级的，所以还是无法测量会话级别的工作。
尽管SHOW STATUS无法提供基于时间的统计但对于执行完查询后观察某些计数器的值还是有帮助的。最有用的计数器包括句柄计数器（handler counter）、临时文件和表计数器等。在附录B中有更加详细的解释。下面的例子演示了讲会话级别的计数器重置为0，然后查询前面提到的视图，在检查计数器的结果：

mysql> FLUSH STATUS; mysql> SELECT * FROM user ;

文章图片

从结果可以看到该查询使用了三个临时表，其中两个是磁盘临时表，并且很多的没有用到索引的读操作（Handler_read_rnd_next）。假设我们不知道这个视图的具体定义，仅从结果来推测，这个查询可能做了多表关联（join）查询，并且没有合适的索引，可能其中一个子查询简历了临时表，然后和其他表做了联合查询。而用于保存子查询结果的临时表没有索引，如此大致可以解释这一的结果。
注意：使用SHOW STATUS本身也会创建一个临时表，而且也会通过句柄操作访问此临时表，这会影响SHOW STATUS结果中对应的数字，而且不同的版本可能行为也不尽相同。比较签名通过SHOW PROFILES获得到的查询的执行计划的结果来看，临时表的计数器多加了2。
EXPLAIN查看查询的执行计划也可以获得大部分相同的信息，但是EXPLAIN是通过估计得到的结果，而通过计数器则是实际的测量结果。例如，EXPLAIN无法告诉你临时表是否是磁盘表，这和内存临时表的性能差别是很大的。
使用慢查询日志下面是“使用SHOW PROFILE”一节演示过的相同的查询后抓取到的结果：

文章图片

从这里可以看到查询确实一共创建了三个临时表，其中两个是磁盘临时表。而SHOW PROFILE看起来则隐藏了信息（可能是由于服务器的执行查询方式有不一样的地方造成的）。淡最后对该查询执行SHOW PROFILE的数据也会写入到日志中，所以在Percona Server中甚至可以记录SHOW PROFILE的细节信息。
慢查询日志中记录的条目包含了SHOW PROFILE和SHOW STATUS所有的输出，并且还有更多的信息。所以通过pt-query-digest发现“坏”查询后，在慢查询日志中可以获得足够有用的信息。查看pt-query-digest的报告中，其标题部分一般会有如下输出：

文章图片

这样就可以直接跳转到细节部分了。另外，pt-query-digest能够出息Procona Server在慢查询日志中正价的所有键值对，并且会自动在报告中打印更多的细节信息。
使用Performance Schema

mysql> SELECT EVENT_NAME,COUNT_STAR,SUM_TIMER_WAIT FROM events_waits_summary_global_by_event_name ORDER BY SUM_TIMER_WAIT DESC LIMIT 5;

文章图片

对于大多数用户来讲，直接通过Performance Schema的裸数据获得有用的结果相对过于复杂和底层。主要是为了测量当为提升服务器性能而修改MySQL源代码是使用，包括等待和互斥锁。MySQL5.5中的特性对于高级用户也很有价值，而不仅仅为开发者使用，但还是需要开发一些前端工具以方便用户使用和分析结果。
3.3.3 使用性能剖析
当获得服务器或者查询的剖析报告后，怎么使用？好的剖析工具将潜在的问题显示出来，淡最终的解决方案还需要用户来决定。优化查询时，用户需要对服务器如何执行查询有比较深的了解。剖析报告尽可能多地收集需要的信息、给出诊断问题的正确方向，以及为其他诸如EXPLAIN等公交提供基础信息。后续章节继续讨论。
尽管一个拥有完整测量向欧盟向的剖析报告可以让事情变得简单，但现有系统通常都没有完美的测量支持。从前面的例子来说，虽然推断出是临时表和没有索引的读导致查询响应时间过长，但却没有明确的证据。因为无法测量所有需要的信息，或者测量的范围不明确，有些问题就很难解决。例如，可能没有集中在需要优化的地方测量，而是测量的服务器层面的活动；或者测量的是查询开始之前的计数器，而不是查询开始后的数据。
也有其他的可能性。设想一下正在分析慢查询，发现一个很简单的查询正常的情况下非常快，却有几次不合理地执行了很长时间。手工执行一遍，发现也非常快，然后使用EXPLAIN查询其执行计划，也正确的使用了索引。然后尝试修改WHERE条件中使用不同的值，以排除缓存命中的可能，也没发现什么问题，这可能是什么原因呢？
如果使用官方版本的MySQL，慢查询日志中没有执行计划或者详细细心，对于偶尔记录到的这几次查询异常慢的问，很难知道其原因在哪，因为心虚有限。可能是系统中其他东西消耗了资源，比如正在备份，也可能是某种类型的锁或者争用阻塞了查询的进度。这种间歇性的问题将在下一章讨论。
3.4 诊断间歇性问题间歇性的问题比如系统偶尔停顿或者慢查询，很难诊断。有些幻影问题只在没有注意到的时候才发生，而且无法确认如何重现，诊断这样的问题往往要花费很多时间，有时候甚至需要几个月。在这个过程中，有些人会尝试不断试错的方式来诊断，有时候甚至会想要通过随机地改变一些服务器的设置来侥幸找到问题。
尽量不要使用试错的方式来解决问题，低效，并且有可能导致结果变得更坏。如果一时无法定位问题，可能是测量的方式不正确,或者是车上的点选择有误，或者使用的工具不合适。
为了演示为什么要避免试错的诊断方式，下面列举了我们认为已经解决的一些间歇性数据库性能问题的实际案例：

应用通过curl从一个运行很慢的外部服务来获取汇率报价的数据。
memcached缓存中的一些重要条目过期，导致大量请求落到MySQL以重新生成缓存条目。
DNS查询偶尔有超时的现象
可能由于互斥锁争用，或者内部删除查询缓存的算法效率太低的缘故，MySQL的查询缓存有时候会导致服务有短暂的停顿。
当并发度超过某个阈值时，InnoDB的扩展性限制导致查询计划的优化需要很长时间。
从上面可以看到有些问题确实是数据库的原因，也有些不是。只有在问题发生的地方通过观察资源的使用情况，并尽可能滴测量出数据，才能避免在没有问题的耗费精力。

3.4.1 单条查询问题还是服务器问题
首先需要确认这是单条查询的问题，还是服务器的问题。这将为解决问题指出正确的方向。如果服务器上所有的程序都突然变慢，有突然编号，每一条查询都变慢了，那么慢查询就可能不一定是原因，而是由于其他问题导致的结果。反过来说，如果服务器整体运行没有问题，只有某条查询偶尔变慢，就需要将注意力放在这条特定的查询上面。
那么如何判定是服务器问题还是单条查询的问题能？如果问题不停地周期性出现，那么可以通过三种技术来解决，下面将意义描述。
使用SHOW GLOBAL STATUS 这个方法实际上就是比较高的频率比如一秒执行一次SHOW GLOBAL STATUS命令捕捉数据，问题出现是，则可以通过某些计数器（比如Threads_running、Threads_conndected、Questions和Queries）的“尖刺”或者“凹陷”来发现。这个方法比较简单，对服务器也很小，所以是一个花费时间不多却能很火地了解问题的好方法。下面是示例命令及其输出;

文章图片

这个明明每秒就捕获一次SHOW GLOBAL STATUS的数据，输出给awk计算并输出每秒的查询数、Threads_connected和Threads_running（表示当前正在执行查询的线程数）。这三个数据的趋势对于服务器级别偶尔停顿的敏感性很高。一般发生此类问题时，根据原因的不同和应用链接数据库方式的不同，每秒的查询数一般会下跌，而其他两个则至少有一个会出现尖刺。在这个例子中，应用使用了连接池，所以Threads_connected没有变化。淡正在执行查询的线程数明显上升，同时每秒的查询数相比正常的数据有严重的下跌。
如何解决这个现象呢？凭猜测有一定的风险。但在实际中有两个原因的可能性比较大。其中之一是服务器内部碰到了某种瓶颈，导致新查询在开始执行前因为需要获取老查询正在等待的锁而造成堆积。另外一个常见的原因是服务区突然遇到大量查询请求的冲击，比如前端的memcached突然失效导致的查询风暴。
这个命令每秒输出一行数据，可以运行几个小时或者几天，然后将结果绘制成图形，这样就可以方便地发现是否有趋势的突变。如果问题确实是间歇性的，发送频率又较低，也可以根据需要尽可能长时间的运行此命令，知道发现问题再回头看输出结果。大多数情况下，通过输出结果都可以明确地定位问题。
使用SHOW PROCESSLIST 这个方法是通过不停捕获SHOW PROCESSLIST的输出，来观察是否有大量线程处于不正常的状态获取有其他不正常的特征。例如查询很少会长时间处于“statistics”的状态，这个状态一般是指服务器在查询优化阶段如何确定表关联的顺序–通常是非常快的。
另外，也会很少见到大量线程报告是当前用户是“未经验证的用户（Unauthenticated user）”，这只是在链接握手的中间过程的状态，当客户端等待输入用于登录的用户信息的时候在会出现。

文章图片

如果要查看不同的列，只需要修改grep的格式即可，。在大多数情况下，State列都非常有用。例子中可以看到，有很多线程处于查询执行的记过部分的状态，包括“freeing items”、“end”、"cleaning up"和“Logging slow query”。事实上，在案例中的这台服务器上，同样模式或类似的输出采样出现了很多次。大量的线程处于“freeing items”状态是出现大量问题查询很明显的特征和指示。
用这种技术查找问题，上面的命令行不是唯一的方法。如果MySQL服务器的版本较新，也可以直接查询INFOFMATION_SCHEMA中的PROCESSLIST表；或者使用innotop工具以比较高的频率刷新，以观察屏幕上出现的不正常查询堆积。上面演示的这个例子是由于InnoDB内部的争用和脏块刷新所致，但有时候原因可能比这个要简单的多。一个景点的例子是很多处查询处于“Locked”的状态，这是MyISAM的一个典型问题，他的表级别锁定，在写请求较多时，可能迅速导致服务器级别的线程堆积。
使用查询日志如果要通过查询日志发现问题，需要开启慢查询日志并在全局级别设置long_query_time为0，并且要确认所有的链接都采用新的设置。这可能需要重置所有链接以使新的全局设置生效；或者使用Percona Server的一个特性，可以在不断开现有链接的情况下动态地设置强制生效。
如果因为某些原因，不能设置慢查询日志记录所有的查询，也可以通过tcpdump和pt-query-digest工具来模拟替代。要注意找吞吐量突然下降的时间段日志。查询是在完成阶段才写入到慢查询日志的，所以堆积会造成大量查询处于完成阶段，知道阻塞其他查询的资源占用者才能执行完成。这种行为特征的一个好处是，当遇到听听量突然下降时，可以归咎于吞吐量下降后完成的第一个查询（有时候也不一定是第一个查询，其他查询可以不受影响继续运行。所以不能完全依赖这个经验）。
好的工具可以帮助诊断这类问题。下面的例子只有一行代码，却可以根据MySQL每秒将当前时间写入日志中的模式统计每秒的查询数量：

文章图片

从上面的输出可以看到有吞吐量突然下降的情况发生，而且在下降之前还有一个突然的高峰，仅从这个输出而不去查询当时的详细情况很难确定发生了什么，但应该可以说这个突然的高峰和随后的下降有一定的关联。这种现象很奇怪，值得去日志中挖掘该时间段的详细信息（实际上通过日志的详细信息，可以发现图突然地高峰时间段有很多链接被断开的现象，可能是有一台应用服务器重启导致。所以并不是所有的问题的都是MySQL的问题）。
理解发现的问题（Making sense of the findings）可视化的数据最具有说服力。上面只演示了很少的几个例子，但在实际情况中，利用上面的工具诊断时可能产生大量的输出结果。可以选择用gnuplot或者R，或者其他绘图工具将结果绘制成图形。这些绘图工具速度很快，比电子表格要快得多，而且可以对图上的一些异常的地方进行缩放，这比在终端中通过滚轮翻看文字要好用的多。
建议诊断问题时优先使用前两种方法：SHOW STATUS和SHOW PROCESSLIST。这两种方法的开销很低，而且可以通过简单的shell搅拌或者反复执行的查询来交互式地收集数据。分析慢查询日志则相对他要困难一些，经常会发现一些蛛丝马迹，淡仔细去研究的时候可能又消失了。这样我们很容易会任务其实没有问题。
3.4.2 捕获诊断数据
当出现间歇性问题时，需要尽可能地多收集所有数据，而不只是问题出现时的数据。虽然这样会收集大量的诊断数据，但总比真正能够诊断问题的数据没有被收集到的情况要好。
1.一个可靠且实时的“触发器”，也就是是能够区分什么时候问题出现的方法。
2.一个收集诊断数据的工具。
诊断触发器触发器很重要。这是在问题出现时能够捕获数据的基础。有两个常见的问题可能导致无法达到预期的结果：误报（false positive）或者漏检（false negative）。误报是指收集了很多诊断数据，但期间没有发生问题。而漏检则指在问题时没有捕获到数据，错失机会。所以在开始收集数据之前多花一些时间来确认触发器能够真正的识别问题是划算的。
那摩好的触发器的标准是什么呢？比如前面的Threads_running的趋势在出现问题时会比较敏感，而没有问题的时候比较平稳。另外SHOW PROCESSLIST中线程中的异常状态尖峰也是一个不错的指标。当然还有很多其他的方法，包括SHOW INNODB STATUS的特定输出、服务器的平均负载尖峰等。关键是找到一些能和正常时的阈值进行比较的指标。通常情况下是一个技术，比如正在运行的线程的数量、处于“freeing items”状态的线程的数量等。当要计算线程某个状态的数量时，grep的-c选项非常有用：

文章图片

选择一个合适的阈值很重要，既要足够高，以确保正常时不会触发；又不能太高，要确保问题发生时不会错过。另外要注意，要在问题开始时就捕获数据，及更不能将阈值设置得太高。问题持续上升的趋势一般会导致更多问题的发生，如果在问题导致系统快要崩溃时才开始捕获数据，就很难诊断到最初的根本原因。如果可能，在问题还是涓涓细流的时候就要开始收集数据，而不要等到波涛汹涌的时候才开始。举个例子，Threads_connected偶尔出现非常高的尖峰值，在几分钟时间内会从100冲到5000或者更高，所以阈值设置到4999也可以捕获到问题，但为什么要等到那么高的时候才收集数据呢？如果正常该值一般不超过150，将阈值设置为200或者300会更好些。
回到前面关于Threads_running的例子，正常情况下的并发度不超过10.但是阈值设置为10并不是一个好主意，很可能会导致很多误报。即使设置为15也不够，还是会有很多正常的波动会到这个范围。当并发运行线程到15的时候可能也会有少量的堆积的情况，但可能还没有到问题的引爆点。淡也应该在糟糕到一眼就能看出问题前就清晰地识别出来，对于这个例子，我们建议的阈值设置为20.
我们当然希望在问题确实发生的时候能够捕获到数据，到哪有时候也需要稍微等待一下以确保不是误报或者短暂的尖峰。所以，最后的触发条件可以这样设置：每秒监控状态值，如果Threads_running连续5秒超过20，就开始搜集诊断数据。
所以我们需要利用一种工具来监控服务器，当达到触发条件时能搜集数据。当然可以自己编写脚本实现，不过不用那么麻烦，Percona Tookit中的pt-stalk就是为这种情况设计的，这个工具有很多有用的特性，只要碰到过类似的问题就能明白这些特性的必要性。例如，监控磁盘的可用空间，所以不会因为搜集太多数据将空间耗尽而导致服务器崩溃。如果之前碰到过这种情形，就会理解这一点。
pt-stalk是用法很简单。可以配置需要监控的变量、阈值、检查的频率等。还支持一些比较时间需要更多花哨特性，但在这个例子中有这些已经足够了。在使用之前建议先阅读说明文档。
需要收集什么样的数据现在已经确定了诊断触发器，可以开始启动一些进程来搜集数据了。但需要搜集什么样的数据呢？就像前面说的，但是是尽可能搜集所有能搜集的数据，淡只在需要的时间段内搜集。包括系统的状态、CPU利用率、磁盘使用率和可用空间、ps的输出采样、内存利用率，以及可以从MySQL获得的信息，如SHOW STATUS、SHOW PROCESSLIST和SHOW INNODB STATUS。这些在诊断问题时都需要用到。
执行时间包括用于工作时间和等待时间。当一个未知问题发生时，一般来说有两种可能：服务器需要做大量工作，从而导致大量消耗CPU；或者在等待某些资源被释放。所以需要用不同的方法搜集诊断数据，来确认是何种原因；剖析报告用于确认是否有太多的工作，而等待分析则用于确认是否存在大量等待。如果是未知的问题，怎么知道将精力集中在那个方面呢？只能将两种数据都尽量收集。
在GNU/Linux平台，可用于服务器内部诊断的一个重要工具oprofile。后面会展示一些例子。也可以使用strace剖析服务器的系统调用，但在生产环境中使用它有一定的风险。后面还会继续讨论。如果要剖析查询，可以使用tcpdump。大多数MySQL版本无法方便地打开和关闭慢查询日志，此时可以通过监听TCP流量来模拟。此外，网络流量在其他一些分析中也非常有用。
对于等待分析，常用的方法是GDB跟踪。MySQL内的.MySQL内的线程如果卡在一个特定的地方很长时间，往往都有相同的堆栈跟踪信息。跟踪的过程是先启动gdb，然后附加（attach）到mysql进程，将所有的线程的堆栈都转储出来，然后可利用一些简短的脚本，将类似时的堆栈跟踪信息做汇总，再利用sort|uniq|sort的方式排序出总计最多的堆栈信息。稍后将演示如何用pt-pmp工具来完成。
也可以使用SHOW PROCESSLIST和SHOW INNODBSTATUS的快照信息观察线程的事务的状态来进行等待分析。
搜集所有的数据听起来工作量很大。但是pt-collect会提供一些帮助，也是Percona Toolkit中的一员。pt-collect一般使用pt-stalk来调用。因为涉及很多重要数据的收集，所以需要用root权限来运行。默认情况下，启动后会收集30秒的数据，然后退出。对于大多数问题的诊断来说这已经足够，但是如果有误报(false positive)的问题出现，则可能收集的信息就不够。工具不需要任何配置，配置都是通过pt-stalk进行的。系统中最好安装gdb和oprofile，然后在pt-stalk中配置使用。另外mysqld也需要有调试符号信息。当触发条件满足是，pt-collect会很好地收集完整的数据。它会在目录中创建时间戳文件。
解释结果数据如果已经正确地设置好触发条件，并且长时间运行pt-stalk，则只需要等待猪狗长的时间捕获几次问题，就能够得到大量的数据来进行筛选。可以先根据两个目的来查看一些东西。第一，检查问题是否真的发生了，因为很多的样本数据需要检查。如果是误报就会浪费大量的时间。第二，是否有非常明显的跳跃性变化。
查看异常的查询或事务的行为，以及异常的服务器内部行为通常是最有收获的。查询或事务的行为可以显示否由于使用服务器的方式导致的问题：性能低下的SQL查询、使用不当的索引、设计糟糕的数据库逻辑架构等。通过抓取TCP流量或者SHOW PROCESSLIST输出，可以获得查询的事务出现的地方，从而知道用户对数据库进行了什么操作。通过对服务器内部行为则可以清楚服务器是否有bug，或者内部的性能和扩展性是否有问题。这些信息在类似的地方都可以看到，包括在oprofile或者gdb的输出中，但要理解则需要更多的经验。
如果遇到无法解释的错误，则最好将收集到的所有数据大把，提交给技术支持人员进行分析。详细的数据对于支持人员非常重要。另外也可以将Percona Toolkit中另外两款pt-mysql-summary和pt-summary的输出结果打包，这两个工具会输出MySQL的状态和配置信息，以及操作系统和硬件的信息。
Percona Toolkit还提供了一款快速检查收集到数据样本的工具：pt-sift。这个工具会轮流导航到所有的样本数据，得到每个样本的汇总信息。如果需要也可以钻取到详细信息。可以少打很多字。
前面演示了状态计数器和线程状态的例子。在本章结束之前，将再给出一些operfile和gdb的输出例子。下面是一个问题服务器上的operfile输出，你能找到问题吗？

文章图片

如果你大答案是“查询缓存”，那么恭喜你答对了。在这里查询缓存导致了大量的工作，并拖慢了整个服务器。这个问题是一夜之间突然发生的，系统变慢了50倍，淡这期间系统并没有做过任何其他变更。关闭查询缓存后系统性能恢复了正常。这个例子比较简单地解释了服务器内部行为对性能的影响。
另外一个重要的关于等待分析的性能瓶颈分析工具便是gdb的堆栈跟踪。下面是Udine一个线程的堆栈跟踪的输出结果，为了便于印刷做了一些格式化:

文章图片

堆栈信息需要自下而上来看。也就是说，线程当前正在执行的是pthread_cond_wait函数，这是由os_event_wait_low来调用的。继续往下，看起来线程试图进入到InnoDB内核（srv_conc_enter_innodb0），但被放入了一个内部队列中（os_event_wait_low），原因应该是内核中线程数已经操作innodb_thread_concurrency的限制。当然，要真正发挥堆栈跟踪的价值需要将很多信息聚合在一起看。这种技术是有Domas Mituzas推广的，他以前的MySQL的支持工程师，开发了情人剖析器“poor man’s profiler”，和其他人开发了更多的收集和分析堆栈跟踪的工具，可以查看https://poormansprofiler.org/。
在Percona Toolkit中我们也开了了有、一个类似的启穷人剖析器，叫做pt-pmp。这是一个用shell和awk脚本编写的工具，可以将类似的堆栈跟踪输出合并到一起，然后通过sort|uniq|sort将常见的条目在最前面输出。下面是一个堆栈跟踪的完整例子，用过此工具将重要的信息展示出来。使用-l 5的选项指定了堆栈跟踪不超过5层，以免因太多前面部分想用而后面部分不同的跟踪信息而导致无法聚合在一起的情况，这样才能更好地显示到底在哪里产生了等待：

文章图片

第一行是MySQL中非常典型的空闲线程的一种特征，所以可以忽略。第二行才是最有意思的地方，看起来大量线程正准备进入到InnoDB的内核中，淡都被阻塞了。从第三行可以看出许多线程都带等待某些互斥锁，但具体是什么锁，不清楚，因为更深城池的被截断了，如果需要确切知道什么是互斥锁，则需要更大的-l选项重跑一次。一般来说这个堆栈显示很多线程都在等待进入InnoDB，这是为什么呢？这个工具并不清楚，需要从其他的地方来入手。
从前面的堆栈跟踪和oprofile报表来看，如果不是MySQL和InnoDB源码方面的专家，这种类型的分析很难进行。如果用户在进行此类分析遇见问题，通常需要请教这样的专家。
3.4.3 一个诊断案例
本章节中逐步演示一个客户时间碰到的间歇性性能问题的诊断过程。这个案例是诊断需要具备MySQL、InnoDB和GUN/Linux的相关知识。要尝试从疯狂中找到条理：阅读本章节并保持对之前的假设和猜测的关注，保持对之前基于合理性和基于可度量的方式的关注。

首先，问题是什么？一定要清晰地描述出来，费力去解决一个错误的问题是常有的事。在这个案例中，用户抱怨说每隔一两天，服务器就会拒绝链接，报max_connections错误。这种情况一般会持续几秒到几分钟，发生的会就爱你非常随机。
其次，为解决问题已经做过什么操作？在这个案例中，用户没有为这个问题做个任何操作。这个信息非常有帮助，因为很少有其他事情会像另外一个人来描述一件事情发生的确切顺序和曾经做过的改变及其后果一样难以理解。如果一台服务器遭受过未知的变更，产生了未知的结果，问题就更难解决了，尤其是时间有非常有限的时候。

搞清楚这个两个问题后，就可以开始了。不仅需要了解服务器的行为，也需要花点时间去梳理一下服务器的状态、参数配置，以及软硬件环境。使用pt-summary和pt-mysql-summary工具可以获得这些信息。这个例子的服务器有16个CPU核心，12GB内存，数据量有900MB，且全部采用InnoDB存储引擎，存储在一块SSD固态硬盘上。服务器操作系统GBU/Linux，MySQL版本5.1.37，存储引擎版本InnoDB plugin 1.0.4。过去的数据库没有出现问题，大多数问题都是由应用程序的不良行为导致的。初步建成服务器也没有发现明显的问题。查询一些优化的空间，但大多数相应时间都不到10毫秒。所以认为正常情况下数据库服务器运行良好（这一点比较重要，因为很多问题一开始只是零星出现，慢慢地积累成大问题。比如RAID阵列中坏了一块硬盘案例情况）。
我们安装好诊断工具，在Thread_connected上设置触发条件，正常情况下Threads_connected的值一般都少于15，但在发生问题时该值Kenneth飙升到几百。下面我们会先给出一个样本数据的收集结果，后续再来评论。首先试试看，能否从大量输出中找出问题的重点在哪里：

查询活动从1000到10000的QPS，其中有很多是“垃圾”命令，比如ping一下看是否存活。其余大部分是SELECT命令，大约每秒300~2000次，只很少的update命令（大约每秒5次）：
在SHOW PROESSLIST中主要有两种类型的查询，只是在WHERE条件中的值不一样。下面是查询状态的汇总数据：

文章图片
大部分查询都是索引扫描或者范围扫描，很少有全表扫描或者关联扫描的情况。
每秒大约有20-100次排序，需要排序的行大约有1000-12000行。
每秒大约创建12-90个临时表，其中有3-5个磁盘临时表。
没有锁或者查询缓存的问题
在SHOW INNODB STATUS中可以观察到主要线程的状态是“flushing buffer pool pages”，但只有很少的脏页需要刷新（Innodb_buffer_pool_pages_dirty），innodb_biffer_pool_pages_flushed也没有太大变化，日志顺序号（log sequence number）和最后检查点（last checkpoint）之间的差距也很少。InnoDB缓存池还远么有用满；缓存池比数据集还要大很多。大多数现场在等待InnoDB队列；“12 queries inside InnoDB，495 queries in queue”（12个查询在InnoDB内部执行，495个在队列中）。
每秒捕获一次iostat输出，持续30秒。从输出可以发现没有磁盘读，而写操作则接近了“天花板”，所以I/O平均等待时间和队列长度都非常高。下面是部分输出结果，为便于打印输出，这里截取了部分字段：

文章图片
vmstat的输出也验证了iostat的结果，并且CPU的大部分时间是空闲的，只是偶尔在写尖峰时有一些I/O等待时间（最高约占9%的CPU）。
是不是感觉脑袋里塞满了东西？当你深入一个系统的细节并且没有任何先入为主的观念时，很容易碰到这种情况，最终只能检查所有的情况。很多倍检查的地方最终要吗是完全正常的，妖魔发现是问题导致的结果而不是问题产生的原因。尽管此时我们会有很多关于问题原因的猜测，但还是需要继续检查下面给出的oprofile报表，并且在给出更多数据的时候添加一些评论和解释：

文章图片

这里大多数符号（symbol）代表的意义并不是那么明显，而大部分的时间都消耗在内核符号（no-vmlinux）和一个通用的mysqld符号中，这两个符号无法告诉我们更多的细节。不要被多个ha_innodb.so符号分散了注意力，看一下他们占用的百分比就知道了，不管它们在做什么，其占用时间都很少，所以应该不是问题所在。这个例子说明，仅仅从剖析报表触发是无法得到解决问题的结果的。我们追踪的数据是错误的。如果遇到上述例子这样的情况，需要继续检查其他的数据，寻找问题根源更为明显的证据。
到这里，如果希望从gdb的堆栈跟踪进行等待分析，可以参考3.4.2节最后的部分内容。那个案例就是我们当前正在诊断的这个问题。回想一下，当时堆栈跟踪分析的记过是等待进入到INNODB内核，所以SHOW INNODB STATUS的输出解雇哟中有“12 queries inside InnoDB, 495 queries in queue”.
从上面的分析发现问题的关键点了吗？没有。我们看到了许多不同问题可能的症状，根据经验和直觉可以推测至少有两个可能的原因。淡也有一些没有意义的地方。如果再次检查一下iostat输出，可以发现wsec/s列显示了至少在6秒内，服务器写入几百MB的数据到磁盘。每个磁盘扇区是512B,所以这里采样的结果显示每秒最多写入了150MB的数据。然而整个数据库也只有900MB大小，系统的压力有主要是SELECT查询，怎么会出现这种情况呢？
对一个系统进行检查的时候，应该先问一下自己，是否也碰到过上面这种明显不合理的问题，如果有就需要深入调查。应该尽量跟进每一个可能的问题直到发现结果，而不要被离题太多的各种情况分散了注意力，以至于最后忘记了最初要调查的问题。可以把问题先写到小纸条上，检查一个划掉一个，最后再确认问题都已将完成了调查。
在这一点上，我们可以直接得到一个结论，但却有可能是错误的。可以看到主线程的状态是InnoDB正在刷新的脏页。在状态输出中出现这样的状况，一般都意味着刷新已经延迟了。我们知道这部版本的InnoDB存在“疯狂刷新”的问题（检查点停顿）。发生这样的情况是因为InnoDB没有按照时间均匀分布刷新请求，而是隔一段时间突然请求一次强制检查点导致大量刷新的操作。这种机制可能会导致InnoDB内部发生严重的阻塞,导致所有的操作需要排队等待进入内核，从而引发InnoDB上一层的服务器产生堆积。在第二章中演示的例子就是一个因为“疯狂刷新”而导致性能周期性下跌的问题。很多类似的问题都是由于强制检查点导致的，但这个案例中却不是这个问题。有很多方法可以证明，最简单的方法就是查询SHOW STATUS的计数器，最终一下Innod_buffer_pool_pages_flushed的变化，之前已经提到了，这个值并没有怎么正价。另外，注意到InnoDB缓冲池中也没有大量的脏页需要刷新，肯定不到几百MB。这并不值得惊讶，因为这个服务器的工作压力几乎都是SELECT查询。所以可以得到一个初步的结论，我们要关注的不是InnoDB刷新的问题，而应该是刷新延迟的问题，但这只是一个现象，而不是原因。根本的原因是磁盘的I/O已经包含，InnoDB无法完成其I/O操作。至此我们消除了一个可能的原因，可以从基于直觉的原因列表中将其划掉了。
从结果中将原因区别出来有时候会很困难。当一个问题看起来很眼熟的时候，可以跳过检查阶段直接诊断。当然最好不要走这样的捷径，但有时候依赖直接也非常重要。如果有什么地方看起来眼熟，明智之举还是需要花费一点时间去测量一下充分必要条件，以证明是否就是问题所在。这样可以节省大量时间，避免查看大量其他的系统和性能数据。不过也不要过于相信直觉而直接下结论.应该去收集相关的证据，尤其是能证明直觉的证据。
下一步是尝试找出什么导致服务器的I/O利用率异常的高。首先应该注意到前面已经提到的“服务器有连续几秒内每秒写入了几百MB的数据到磁盘，而数据库一共只有900MB的大小，怎么会发送这样的情况？”，注意到这里已经隐式地假设是数据库导致了磁盘的写入。那么有什么证据表明是数据库导致的呢？当你有未证实的相符、或者觉得不可思议时，如果可能的话就应该进行测量，然后排除掉一些怀疑。
我们看到了两种的可能性；要么是数据库导致了I/O(如果能找到源头，那摩就可以找到问题的原因)；要么不是数据库导致了所有的I/O而是其他什么导致的，而系统因为缺少了I/O资源影响了数据库的性能。我们也很消息地尽力避免引入另外一个隐式的假设：磁盘忙并不一定意味着MySQL存在问题。要记住，这个服务器的主要压力是内核的读取，所以也很可能出现磁盘长时间无法响应但没造成严重问题的现象。
如果你一直跟随我们的推理逻辑，就可以发现还需要回头检查一下另外一个假设。我们已经知道了磁盘设备很忙，因为其等待时间很高。对于固态硬盘来说，其I/O平均等待时间一般不会超过1/.4秒。实际上，从iostat的输出结果也可以发现磁盘本身的响应还是很快的，但请求在块设备队列中等待了很长时间才能进入到磁盘设备。但要记住的试试，这是iostat的输出结果，也可能是错误的信息。

究竟是什么导致了性能低下？
1.资源被过度使用，余量已经不足以正常工作。
2.资源没有被正确的配置。
3.资源已经损坏或者失灵。
在检查过所有的诊断数据之后，接下来的任务就很明显了：测量出什么导致了I/O的消耗。不幸的是，客户当前使用到GNU/Linux版本对此支持不力。通过一些工作我们可以做一些相对准确的猜测，但首先还是需要探索一下其他的可能性。我们可以测量有多少I/O来自于MySQL，但客户使用的MySQL版本较低以至于缺乏一些诊断功能，所以也无法提供确切和有利的支持。
作为替代，基于我们已经知道MySQL如何使用磁盘，我们来观察MySQL的I/.O情况。通常来说，MySQL只会写数据、日志、排序文件和临时表到磁盘。从前面的状态计数器和其他信息来看，首先可以排除数据和日志的写入问题。那么,只能假设MySQL突然写入大量数据到临时表或者排序文件，如何来观察这种情况能？有两个简单的方法；一是观察磁盘的可用空间，二是通过lsof命令观察服务器打开的文件句柄。这两个方法我们都采用了，结果也足以满足我们的需求。下面是问题期间每秒运行df -h的结果：

文章图片

下面则是lsof的数据，因为某些原因每5秒才收集一次。我们简单地将mysqld在/tmp中打开的文件大小做了加总，并且把总大下和采样的时间戳一起输出到结果文件中：

文章图片

从这个结果可以看出，在问题之初MySQL大约写了1.5G的数据到临时表，这和之前在SHOW PROCESSLIST中有大量的“Copying to tmp table”相吻合。这个数据表明可能是某些效率低下的查询风暴耗尽了磁盘资源。根据我们的工作直觉，出现这种情况比较普遍的原因是缓存失效。当memcached中所有缓存的条目同时失效，而又有很多应用需要同时访问的时候就会出现这种情况。我们给开发人员出示了部分采样的查询，并讨论了这些查询的作用。实际情况是，缓存同时失效就是罪魁祸首。一方面开发人员在应用层面解决缓存失效的问题，另一方面我们也修改了查询，避免使用磁盘李世彪。这两个方法的任何一个都可以解决问题，当然最后是两个都是实施。
如果读者一直顺着前面的思路，可能还会有一些疑问。在这里我们可以稍微解释一下：
Q:为什么我么不一开始就优化慢查询？
A:因为问题不在于慢查询，而是“太多连接”的错误。当然，因为慢查询，太多查询的时间过长而导致堆积在逻辑上也是成立的。淡也有可能是其他原因导致的链接过多。如果没有找到问题的真正原因，那么回头查看慢查询或其他可能的原因，看是否能够改善是很自然的事情。淡这样做很多情况下会让问题变得更糟。
Q:但是查询由于糟糕的执行计划而执行缓慢不是一种警告吗？
A:在事故中确实如此。但慢查询到底是原因还是结果？在深入调查之前是无法知晓的。在正常的时候这个查询也是正常运行的。一个查询需要执行filesort和创建临时表并不一定意味着就是有问题的。尽管消除filesort和临时白哦通常来说是“最佳实践”。
通常的“最佳实践”自然有他的道理，淡不一定是解决某些特殊问题的“灵丹妙药”。比如说问题可能是很简单的配置错误。我们碰到很多这样的案例，问题本来是由于错误的配置导致的，却去优化查询，这不但浪费了时间，也使得真正的问题被解决的时间被拖延了。
Q:如果缓存项被从先生成了很多次，是不是会导致很多同样的查询呢？
A:这个问题确实没有调查到。如果是多线程重新生成同样的缓存项，那么确实有可能导致产生很多同样的查询（这和很多同类型的查询不同，比如WHERE字句中的参数可能不一样）。注意要这样会刺激我们的直觉，并更快地带我们找到问题的解决方案。
Q:每秒有几百次SELECT查询，但只有五次UPDATE。怎么能确定这五次UPDATE的压力不会导致问题呢？
A:这些UPDATE有可能对服务器造成很大的压力。我们没有将真正的查询展示出来，因为这样可能会将事情搞得更混乱。淡有一点可以明确，某种查询的绝对数量不一定有意义。
Q：I/O风暴最初的证据看起来不是很充分？
A:是的，确实是这样。有很多种解释可以说明为什么一个这么小的数据库可以产生这么大量的写入磁盘，或者说为什么磁盘的可用空间下降的这么快。这个问题中使用MySQL和GNU/Linux版本都很难对一些问题进行测量。
尽管在很多时候我们可能扮演“魔鬼代言人”的角色，淡我们还是尽量平衡成本和潜在的利益为第一优先级。越是难以测量的时候，成本/收益比越攀升，我们也更原因接收不确定性。
Q:之前说“数据库过去从来没有出现问题”是一种偏见吗？？
A:是的，这是一种偏见。如果抓住问题很好；如果没有，也可以证明我们都有偏见很好的例子。
至此我么要结束这个案例的学习了。需要指出的是，如果使用了诸如New Relic这样的剖析工具，即使没有我们的参与，也可能解决这个问题。
3.5 其他剖析工具 3.5.1 使用USER_STATISTICS表
Percona Server和MeriaDB都引入了一些额外的对象级别使用统计的INFOMATION_SCHEMA表，这些最初是由Google开发的。这些表对于查找服务器各部分的实际使用情况非常有帮助。下面就是这些表：

文章图片

这里我么不详细演示对这些表的所有有用的查询，下面几个要点说明一下：

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

3.5.2 使用strace
strace工具可以调查系统调用的情况。有好几种可以使用的方法，其中一种是计算系统调用的时间并打印出来：

文章图片

这种用法和oprofile有点像。但是oprofile还可以剖析程序的内部符号，而不仅仅是系统调用。另外，strace拦截系统调用使用的是不同于oprofile的技术，这会有一些不可预期性，开销也更大些。strace度量是使用的是实际时间，而oprofile使用的是花费CPU的周期。举个例子，当I/O等待出现问题的时候，strace能将他们显示出来，因为它从诸如read或者pread64这样的系统调用开始计时知道调用结束。但oprofile不会这样，因为I/O系统调用并不会真正消耗CPU周期，而只是等待I/O完成而已。
我们会在需要的时候使用oprofile，因为strace对像mysqld这样有大量线程的场景会产生一些副作用。当strace附加上去后，mysqld的运行会变得很慢，因此不适合在产品环境中使用。但在某些场景中strace还是相当有用的，Percona Toolkit中有一个叫pt-ioprofile的工具就是使用strace来生成I/O活动的剖析报告的。这个工具很有帮助，可以证明或者驳斥某些难以测量情况下的一些观点，此时其他方法很难达到目的（MySQL的Performance Schema也可以）。
3.6 总结 【mysql|读书-高性能MySQL 第三章】本章给出了一些进本的思路和技术，有利于你成功的进行性能优化。正确的事务方式是开启系统的全部潜力和应用本书其他章节提供的知识的关键。西面是一些基本的知识点：

我们认为定义性能最有效的方法是相应时间。
如果无法测量就无法有效地优化，所以性能优化工作需要机遇搞质量、全方位及完整的相应时间测量。
测量的最佳开始时间是应用程序我，而不是数据库，即使问题出现在底层的数据库，借助良好的测试也可以很容易地发现问题。
大多数系统无法完整地测量，测量有时候也会有错误的结果。但也可以想办法绕过一些限制，并的得到好的结果（大梅沙要能意思到锁使用的方法的缺陷和不确定性在哪里）。
完成的测量会产生大量需要分析的数据，所以需要用到剖析器。这是最佳的工具，可以帮助将重要的问题冒泡到前面，这样就可以决定从哪里开始分析比较好。
剖析报告是一种汇总信息，掩盖和丢弃了太多细节。而且它不会告诉你缺少了什么，所以完- 全依赖剖析报告也是不明智的。
有两种消耗时间的操作：工作或等待。大多数剖析器只能测量因为工作而消耗的时间，所以等待分析有时候是很有用的补充，尤其是当CPU利用率很低但工作却一直无法完成的时候。
优化和提升是两回事。当继续提升的成本高于收益的时候，应当停止优化。
注意你的直觉，但应该只根据直觉来指导解决问题的思路，而不是用于确定系统的问题。决策应当尽量基于数据而不是感觉。
总体来说，解决性能问题的方法，首先是要澄清问题，然后选择合适的技术来解答这些问题。如果想尝试提示服务器的总体性能，那么一个比较好的起点就是将所有查询记录到日志中，然后利用pt-query-digest工具生成系统基本的剖析报告。如果是要追查某些性能低下的查询，记录和剖析的方法也会有帮助。可以把经理放在寻找那些消耗时间最多的、导致了糟糕的用户体验的，或者那些高度变化的，亦或有奇怪响应时间直方图的查询。当找到了这些“坏“查询时，要钻取pt-query-digest报告中包含该查询的详细信息，或者使用SHOW PROFILE及其他诸如EXPLAIN这样的工具。
如果找不到这些查询性能低下的原因，那么也可能遇到了服务器级别的性能问题。这时，可以较高精度测量绘制服务器状态计数器的细节信息。如果通过这样的分析重现了问题，则应该通过同样的数据制定一个可靠的触发条件，来搜集跟多诊断数据。多花费一点时间来确定可靠的触发条件，尽量避免漏检或者误报。如果已经可以捕获到故障活动期间的数据，还是无法找到根本原因，则要吗尝试捕获更多的数据，要吗尝试寻求帮助。
我们无法完整地测量工作系统，淡说到底他们都是某种状态机，所以只要足够西西，逻辑清晰并且坚持下去，通常来说都能得到想要的结果。要注意的是不要把原因和结果搞混了，而且在确认问题之前不要随便针对系统做变动。
理论上纯粹的自顶向下的方法分析和详尽的测量只是理想的情况，而我们常常需要处理的是真实系统。真实系统是复杂且无法充分测量的，所以我们只能根据情况尽力而为。使用诸如pt-query-digest和MySQL企业监控器的查询分析器这样的工具并不完美，通常都不会给出问题根源的直接证据。但真正掌握了以后，已经足以完成大部分优化诊断的工作了。