【《高性能 MySQL》笔记】性能优化

学习知识最快最好的方式就是问对问题。

本文将通过“问正确的问题”的方式循序渐进地深入总结性能优化相关知识。

---

性能优化基础

Q1：什么是“性能”？

即响应时间（RT，Response Time），完成某个任务所需要的时间度量。

Q2：什么是“性能优化”？

性能优化为在一定工作负载下尽可能地降低响应时间。注意，当继续提升的成本超过收益的时候，应当停止优化。

性能优化的第一原则是“性能即响应时间”，第二原则是“无法测量就无法有效优化”。

Q2.1：“性能”为什么要“优化”？

• 急需快速上线的（互联网）应用系统，在业务初始阶段实现“功能”为首要目标。业务上线后，终于有时间对之前的“莽撞”上线行为做优化补偿了。
• 优化的时间成本大于硬件采购成本，老板财大气粗，无脑堆硬件，暂时解决性能问题。
• 随着业绩发展，需要针对现有软硬件基础设施做优化以满足业务增长。老板没钱了或不想再出钱，想“性能优化”和“采购新硬件”两手抓，优先选择“性能优化”，需要员工测试“性能优化”是否可以满足需求。如不能，再购买新硬件。
• 硬件扩展达到瓶颈，或运维成本已经过高了，业务也趋于稳定进入瓶颈期，老板要求尽最大可能充分发挥现有系统性能，以提供高质量的服务。
• 随着业务的发展，业务逻辑、代码、组件、中间件、schema 等不断变化导致很多历史遗留问题，即便最初的设计是良好合理的，到这个阶段系统性能也会出现问题，亟需优化，甚至更新换代。

简言之，不好的系统设计、业务增长是性能需要优化的诱因。当硬件成本大于优化成本时，就需要性能优化了。

Q3：性能优化的目标是什么？

降低响应时间。此外，还可能产生降低 CPU 等资源的利用率、提升吞吐量、提高并发度的副作用。降低了单个请求的响应时间，即增加了单位时间内系统能处理的并发请求数，这很好理解。

Q3.1：什么是吞吐量、吞吐率RT、RPS、QPS、TPS？

吞吐量（Throughput [Capacity]）

特定时间间隔内系统处理的数据总量，以比特、字节等为单位。广义上，吞吐量等同于吞吐率，都可以用“ Throughput”表示，除非特定语境下为明确表示才需要严格区分。 有时，人们甚至会使用吞吐量来指代 RPS、QPS、TPS ，但不建议如此， 建议明确地使用这些术语。

吞吐率（Throughput [Rate]）

单位时间内系统处理数据的速率，即单位时间内的吞吐量。

每秒请求数（RPS）

对于诸如 Web 服务器的应用系统，其每秒可以处理的请求数量。

每秒查询数（QPS）

对于数据库系统来说，其每秒可处理的查询数量。

每秒事务数（TPS）

对于事务型应用系统来说，其每秒可处理的事务数。

Q3.2：QPS 和 RPS 有什么区别？

二者的对象不同，RPS 是针对 Web 服务器等应用系统的指标；QPS 是针对数据库系统的指标。有时，人们也将二者混淆不加以区分，甚至不是对一个数据库系统。

Q3.3：RPS/QPS 和 TPS 有什么区别？

一个事务可能包含不止一个请求/查询，因而 TPS 通常小于 RPS/QPS。当事务仅有一个请求/查询时，二者相等。

Q4：怎么做性能优化？

性能优化需要考虑的首要因素是成本利润率。成本利润率 = 利润 / 成本 * 100% 。成本主要包括时间、金钱（人力、物力）两方面成本。我们总是会先权衡目标与成本后，选择最符合当前形势的、最经济的方式（简言之，经济实惠）来进行优化。

Q4.1：为什么要做性能剖析？

性能优化第一步要做的是测量响应时间花在哪里，并且应将大部分（甚至 90%）时间花在这里。完成这一步的主要方法是 性能剖析。

性能剖析是对真实工作负载的测试。而我们在此之前往往更想要一个快速的、满足行业标准的测试，以大致了解系统的能力。这就是基准测试。

---

基准测试

Q5：什么是基准测试？

基准测试是指通过设计科学的测试方法、测试工具和测试系统，实现对一类测试对象的某项性能指标进行定量的和可对比的测试。可测量、可重复、可对比是基准测试的三大原则。

基准测试的其他特点包括：

• 基准测试的一个主要问题在于其不是真实压力的测试；
• 基准测试要尽量简单直接，结果之间容易相互比较，成本低且易于执行；
• 基准测试通常要求尽可能快地执行完成，所以经常给系统造成过大的压力。

Q6：怎样做基准测试？

需要使用一些策略来设计和规划基准测试。

1）如何选择基准测试的策略？

基准测试有两种主要策略：

• 集成式（full-stack），是指对整个系统的集成测试
• 单组件式（single-component），是指对 MySQL 这样的组件进行单独测试。

在项目初期通常不需要了解整个应用系统的情况，而只需要关注 MySQL 的性能。基于以下情况，可以选择只测试 MySQL ：

• 需要比较不同的 schema 或查询的性能。
• 针对应用中某个具体问题的测试。
• 为了避免漫长的基准测试，可以通过一个短期的基准测试，做快速的“周期循环”，来检测出某些调整后的效果。

有了策略后，就应该确定基准测试的目标，一般通过一些可量化的指标来表示。

2）应该测试哪些指标呢？

• 吞吐量、RPS、QPS、TPS

  它们其实本质上是一类东西，只是针对的测试对象不同。

• 响应时间或者延迟

  这个指标用于测试任务所需的整体时间。根据具体的应用，测试的时间单位可能是微秒、毫秒、秒或者分钟。根据不同的时间单位可以计算出平均响应时间、最小响应时间、最大响应时间和所占百分比。最大响应时间通常意义不大，因为测试时间越长，最大响应时间也可能越大。而且其结果通常不可重复，每次测试都可能得到不同的最大响应时间。因此，通常可以使用百分比响应时间（percentile response time）来替代最大响应时间。例如，如果95％的响应时间都是5毫秒，则表示任务在 95％ 的时间段内都可以在 5 毫秒之内完成。

  使用图表有助于理解测试结果。可以将测试结果绘制成折线图（比如平均值折线或者95％百分比折线）或者散点图，直观地表现数据结果集的分布情况。通过这些图可以发现长时间测试的趋势。

• 并发性

  Web 服务器的并发性更准确的度量指标，应该是在任意时间有多少同时发生的并发请求。注意不要将创建数据库连接和并发性搞混淆。一个设计良好的应用，同时可以打开成百上千个 MySQL 数据库服务器连接，但可能同时只有少数连接在执行查询。

  并发性基准测试需要关注的是正在工作中的并发操作，或者是同时工作中的线程数或者连接数。当并发性增加时，需要测量吞吐量是否下降，响应时间是否变长，如果是这样，应用可能就无法处理峰值压力。

  并发性的测量完全不同于响应时间和吞吐量。它不像是一个结果，而更像是设置基准测试的一种属性。并发性测试通常不是为了测试应用能达到的并发度，而是为了测试应用在不同并发下的性能。

• 可扩展性

  在系统的业务压力可能发生变化的情况下，测试可扩展性对于容量规划非常有用。可扩展性是指在对性能的影响在保持可接受范围内的情况下，系统的吞吐量随着资源的增加而变化的能力。理想情况下，吞吐量随资源的增加线性增长。

知道测试哪些指标，就可以指导如何设计和规划基准测试了。

3）如何设计和规划基准测试？

规划基准测试的第一步是提出问题并明确目标。然后决定是采用标准的基准测试，还是设计专用的测试。

• 如果采用标准的基准测试，应该确认选择了合适的测试方案。

• 设计专用的基准测试是很复杂的，往往需要一个迭代的过程。首先需要获得生产数据集的快照，并且该快照很容易还原，以便进行后续的测试。

  然后，针对数据运行查询。可以建立一个单元测试集作为初步的测试，并运行多遍。但是这和真实的数据库环境还是有差别的。更好的办法是选择一个有代表性的时间段，比如高峰期的一个小时，或者一整天，记录生产系统上的所有查询。如果时间段选得比较小，则可以选择多个时间段。这样有助于覆盖整个系统的活动状态，例如每周报表的查询、或者非峰值时间运行的批处理作业。

因为基准测试很可能是周期性的，所以应该建立将参数和结果文档化的规范。每一轮测试都必须进行详细记录测试数据、系统配置的步骤、如何测量和分析结果，以及预热的方案等。

4）基准测试应该运行多长时间？

应该在系统处于稳定状态时运行足够长的时间基准测试。如果无法确定测试需要运行多长时间，可以让测试一直运行，持续观察直到确认系统已经稳定。等待系统看起来稳定的时间至少等于系统预热的时间。监控系统的一些性能指标并绘制图形，可以迅速找到系统的稳定状态。

5）需要获取哪些系统性能和状态？

在执行基准测试时，需要尽可能多地收集被测试系统的信息。最好为基准测试建立一个目录，并且每执行一轮测试都创建单独的子目录，将测试结果、配置文件、测试指标、脚本和其他相关说明都保存在其中。即使有些结果不是目前需要的，也应该先保存下来。多余一些数据总比缺乏重要的数据要好，而且多余的数据以后也许会用得着。需要记录的数据包括系统状态和性能指标，诸如 CPU 使用率、磁盘 I/O、网络流量统计、SHOW GLOBAL STATUS计数器等。

6）如何选择基准测试工具？

集成式测试工具

• ab

  ab 是一个 Apache HTTP 服务器基准测试工具。它可以测试 HTTP 服务器每秒最多可以处理多少请求

• http_load

  这个工具概念上和 ab 类似，也被设计为对 Web 服务器进行测试，但比 ab 要更加灵活。

• JMeter

  JMeter 是一个Java 应用程序，可以加载其他应用并测试其性能。它虽然是设计用来测试Web应用的，但也可以用于测试其他诸如 FTP 服务器，或者通过 JDBC 进行数据库查询测试。

单组件式测试工具

• sysbench （推荐）

  sysbench（https://launchpad.net/sysbench）是一款多线程系统压测工具。它可以根据影响数据库服务器性能的各种因素来评估系统的性能。例如，可以用来测试文件 I/O、操作系统调度器、内存分配和传输速度、POSIX 线程，以及数据库服务器等。sysbench 支持 Lua 脚本语言（http://www.lua.org），Lua 对于各种测试场景的设置可以非常灵活。sysbench 是我们非常喜欢的一种全能测试工具，支持 MySQL 、操作系统和硬件的硬件测试。

  更多 2.5.3 sysbench 。后续会整理一篇使用 sysbench 对 MySQL 做基准测试的文章。

• MySQL Benchmark Suite（sql-bench）

  在 MySQL 的发行包中也提供了一款自己的基准测试套件，可以用于在不同数据库服务器上进行比较测试。

• Benchmark SQL （笔者加）

• Super Smack

• Database Test Suite

• Percona’s TPCC-MySQL Tool

7）如何获得准确的测试结果？

1. 获得准确测试结果的最好办法，是回答一些关于基准测试的基本问题：是否选择了正确的基准测试？是否为问题收集了相关的数据？是否采用了错误的测试标准？
2. 确认测试结果是否可重复。每次重新测试之前要确保系统的状态、工作集是一致的。
3. 确保基准测试运行过程中所需要的资源是专用于测试的。
4. 每次测试中，修改的参数应该尽量少。变量控制的理想情况为一个变量。一般情况下，都是通过迭代逐步地修改基准测试的参数，而不是每次运行时都做大量的修改。。举个例子，如果要通过调整参数来创造一个特定行为，可以通过使用分治法（divide-and-conquer，每次运行时将参数对分减半）来找到正确的值。
5. 很多基准测试都是用来做预测系统迁移后的性能的，比如从 Oracle 迁移到 MySQL 。这种测试通常比较麻烦，通常需要重新设计 MySQL 的 schema 和查询。
6. 如果测试中出现异常结果，不要轻易当作坏数据点而丢弃。应该认真研究并找到产生这种结果的原因。

此外，基于 MySQL 的默认配置的测试没有什么意义，因为默认配置是基于消耗很少内存的极小应用的。有时候可以看到一些 MySQL 和其他商业数据库产品的对比测试，结果很让人尴尬，可能就是 MySQL 采用了默认配置的缘故。让人无语的是，这样明显有误的测试结果还容易变成头条新闻。同样，与其他“友商”跨越多年的测试结果比较，也挺让人无语的，其优异的测试结果主要归功于硬件的迭代。

8）运行基准测试并分析结果

通常来说，自动化基准测试、分析结果是个好主意。这样做可以获得更精确的测试结果。因为自动化的过程可以防止测试人员偶尔遗漏某些步骤，或者误操作。另外也有助于归档整个测试过程。

自动化的方式有很多，可以是一个 Makefile 文件或者一组脚本。脚本语言可以根据需要选择：shell、PHP、Perl 等都可以。要尽可能地使所有测试过程都自动化，包括装载数据、系统预热、执行测试、记录结果等。设计自动化基准测试脚本的输出作为 gnuplot 或者 R 等绘图工具的数据来源。

基准测试通常需要运行多次。具体需要运行多少次要看对结果的记分方式，以及测试的重要程度。

获得测试结果后，通常需要写一些脚本来分析数据，这不仅能减轻分析的工作量，而且和自动化基准测试一样可以重复运行，并易于文档化。推荐使用绘图工具将测试结果图形化，使分析结果更加直观、方便。 最简单有效的图形，就是将性能指标按照时间顺序绘制。

---

性能剖析

对系统做完有一些标准规范支撑的基准测试后，系统稳定运行一段时间后，就需要对做性能剖析了。

Q7：什么是性能剖析？

性能剖析是测量和分析时间花费在哪里的主要方法。

性能剖析一般分为两步：

• 测量任务所花费的时间；
• 然后按任务的重要程度（从高到低）对结果进行统计和排序。

完成一项任务所需要的时间可以分成两部分：

• 执行时间
• 等待时间。

如果要优化任务的执行时间，最好的办法是通过测量定位不同的子任务花费的时间，然后优化去掉一些子任务、降低子任务的执行频率或执行时间。而优化任务的等待时间则相对要复杂一些，因为等待有可能是由其他系统间接影响导致，任务之间也可能由于争用磁盘或者 CPU 资源而相互影响。根据时间花在执行还是等待上的不同，诊断也需要不同的工具和技术。性能剖析是确认哪些子任务需要优化的技术。

根据任务时间类型将性能剖析分为两类：基于执行时间的分析和基于等待的分析。如果可以确定时间大部分花在执行还是等待上，另一部分时间的影响相对很小，则应集中精力优先处理重要的部分，对不重要的部分的优化可以推迟甚至不做。

性能剖析报告（profile report） 会列出所有任务列表，每行记录一个任务，包括任务名、任务的执行时间、任务的消耗时间、任务的平均执行时间，以及该任务执行时间占全部时间的百分比，按任务的消耗时间进行降序排序。

在性能剖析报告中显示的某些“执行时间”实际上是在等待。例如，报告中显示某些 SELECT 查询花费了大量时间，深入分析则可能发现时间都花在了等待 I/O 完成上。

对系统剖析前，需要确保系统是可测量的。这需要系统提供一些计数器来形成测量点。如果系统内部无法做到可测量化，则可从外部测量系统。如果测量失败，可以根据对系统的了解做出一些靠谱的猜测。但，无论是外部测量还是猜测，数据都不是百分百准确的，这是系统不透明所带来的风险。

性能剖析报告缺失的信息：

• 值得优化的查询。首先，一些只占总响应时间比重很小的查询是不值得优化的。根据阿姆达尔定律（Amadahl’s Law），对一个占总响应时间不超过 5% 的查询进行优化，无论如何努力，收益也不会超过 5% 。其次，如果优化的成本大于收益，就应当停止优化。
• 异常情况。某些任务即使没有出现在性能剖析输出的前面也需要优化。例如，某些任务执行频率很低，但每次执行都很慢，严重影响用户体验。
• “丢失的时间”。一款好的性能剖析工具会显示可能存在的“丢失的时间”。“丢失的时间”指的是任务的总时间和实际测量到的时间的差值。例如，如果处理器的 CPU 时间是 10 秒，而剖析到的任务总时间是 9.7 秒，那么就有 300 毫秒的丢失时间。这可能是有些任务没有测量到，也可能是由于测量的误差和精度问题的缘故。
• 被掩藏的细节。性能剖析无法显示所有响应时间的分布。平均值无法表达全部情况。例如，测量医院所有病人的平均体温没有任何价值。应追加更多响应时间的信息，比如直方图、百分比、标准差、方差、偏差指数等。

推荐使用 pt-query-digest ，它在剖析的结果里包含了很多这类细节信息，并输出在剖析报告中。

Q8：如何进行性能剖析？

对整个应用系统进行性能剖析时建议采自顶向下地进行，这样可以追踪自用户发起到服务器响应的整个流程。

Q8.1：如何对应用程序进行性能剖析？