二、MySQL基准测试

2.1 为什么需要基准测试

基准测试是唯一方便有效的、可以学习系统在给定的工作负载下会发生什么的方法。

基准测试可以观察系统在不同压力下的行为，评估系统的容量，掌握哪些是重要的变化，或者观察系统如何处理不同的数据。

基准测试可以在系统实际负载之外创造一些虚构场景进行测试。

2.2 基准测试的策略

基准测试有两种主要的策略:

• 一是针对整个系统的整体测试，集成式(full-stack)
• 二是单独测试MySQL，单组件式(single-component)

针对整个系统做集成式测试，而不是单独测试MySQL的原因主要有以下几点:

• 测试整个应用系统，包括Web服务器、应用代码、网络和数据库是非常有用的，因为用户关注的并不仅仅是MySQL本身的性能，而是应用整体的性能。

• MySQL 并非总是应用的瓶颈，通过整体的测试可以揭示这一点。

• 只有对应用做整体测试，才能发现各部分之间的缓存带来的影响。

• 整体应用的集 成式测试更能揭示应用的真实表现，而单独组件的测试很难做到这一点。

---

另外一方面，应用的整体基准测试很难建立，甚至很难正确设置。如果基准测试的设计有问题，那么结果就无法反映真实的情况，从而基于此做的决策也就可能是错误的。

不过，有时候不需要了解整个应用的情况，而只需要关注MySQL的性能，至少在项目初期可以这样做。

基于以下情况，可以选择只测试MySQL :

• 需要比较不同的 schema或查询的性能。

• 针对应用中某个具体问题的测试。

• 为了避免漫长的基准测试，可以通过一个短期的基准测试，做快速的“周期循环”,来检测出某些调整后的效果。

另外，如果能够在真实的数据集上执行重复的查询，那么针对MySQL的基准测试也是有用的，但是数据本身和数据集的大小都应该是真实的。如果可能，可以采用生产环境的数据快照。

不幸的是，设置一个基于真实数据的基准测试复杂而且耗时。如果能得到一份生产数据集的拷贝，当然很幸运，但这通常不太可能。比如要测试的是一个刚开发的新应用，它只有很少的用户和数据。如果想测试该应用在规模扩张到很大以后的性能表现，就只能通过模拟大量的数据和压力来进行。

2.2.1 测试何种指标

有时候需要用不同的方法测试不同的指标。比如，针对 延迟(latency) 和 吞吐量(throughput) 就需要采用不同的测试方法。

请考虑以下指标，看看如何满足测试的需求。

• 吞吐量

吞吐量指的是单位时间内的事务处理数。

这类基准测试主要针对 在线事务处理(OLTP) 的吞吐量，非常适用于多用户的交互式应用。

常用的测试单位是 每秒事务数(TPS) , 有些也采用 每分钟事务数(TPM) 。

• 响应时间或者延迟

这个指标用于测试任务所需的整体时间。

根据具体的应用，测试的时间单位可能是微秒、亳秒、秒或者分钟。根据不同的时间单位可以计算出平均响应时间、最小响应时间、最大响应时间和所占百分比。

最大响应时间通常意义不大，因为测试时间越长，最大响应时间也可能越大。而且其结果通常不可重复,每次测试都可能得到不同的最大响应时间。

因此，通常可以使用 百分比响应时间(percentile response time) 来替代最大响应时间。

例如，如果95%的响应时间都是5毫秒，则表示任务在95%的时间段内都可以在5毫秒之内完成。

• 并发性

并发性是一个非常重要又经常被误解和误用的指标。

例如，它经常被表示成多少用户在同一时间浏览一个Web站点，经常使用的指标是有多少个会话。

然而，HTTP协议是无状态的，大多数用户只是简单地读取浏览器上显示的信息，这并不等同于Web服务器的并发性。而且，Web服务器的并发性也不等同于数据库的并发性，而仅仅只表示会话存储机制可以处理多少数据的能力。

Web服务器的并发性更准确的度量指标，应该是在任意时间有多少同时发生的并发请求。

在应用的不同环节都可以测量相应的并发性。Web服务器的高并发，一般也会导致数据库的高并发，但服务器采用的语言和工具集对此都会有影响。注意不要将创建数据库连接和并发性搞混淆。

一个设计良好的应用，同时可以打开成百上千个MySQL数据库服务器连接，但可能同时只有少数连接在执行查询。所以说，一个Web站点“同时有50000个用户”访问，却可能只有10~15个并发请求到MySQL数据库。

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

---

并发性的测量完全不同于响应时间和吞吐量。它不像是一个结果，而更像是设置基准测试的一种属性。

并发性测试通常不是为了测试应用能达到的并发度，而是为了测试应用在不同并发下的性能。当然，数据库的并发性还是需要测量的。

可以通过sysbench指定32、64或者128个线程的测试，然后在测试期间记录MySQL数据库的Threads_ running 状态值。

• 可扩展性

在系统的业务压力可能发生变化的情况下，测试可扩展性就非常必要了。

简单地说，可扩展性指的是，给系统增加一倍的工作，在理想情况下就能获得两倍的结果(即吞吐量增加一倍)。

或者说，给系统增加一倍的资源(比如两倍的CPU数),就可以获得两倍的吞吐量。

当然，同时性能(响应时间)也必须在可以接受的范围内。大多数系统是无法做到如此理想的线性扩展的。随着压力的变化，吞吐量和性能都可能越来越差。

可扩展性指标对于容量规范非常有用，它可以提供其他测试无法提供的信息，来帮助发现应用的瓶颈。比如，如果系统是基于单个用户的响应时间测试(这是一个很糟糕的测试策略)设计的，虽然测试的结果很好，但当并发度增加时，系统的性能有可能变得非常糟糕。而一个基于不断增加用户连接的情况下的响应时间测试则可以发现这个问题。

一些任务，比如从细粒度数据创建汇总表的批量工作，需要的是周期性的快速响应时间。当然也可以测试这些任务纯粹的响应时间，但要注意考虑这些任务之间的相互影响。批量工作可能导致相互之间有影响的查询性能变差，反之亦然。

测试对用户来说最重要的指标。

2.3 基准测试方法

首先要避免一些常见的错误:

• 使用真实数据的子集而不是全集。例如应用需要处理几百GB的数据，但测试只有1GB数据;或者只使用当前数据进行测试，却希望模拟未来业务大幅度增长后的情况。

• 使用错误的数据分布。例如使用均匀分布的数据测试，而系统的真实数据有很多热点区域(随机生成的测试数据通常无法模拟真实的数据分布)。

• 使用不真实的分布参数，例如假定所有用户的 个人信息(profile) 都会被平均地读取些。

• 在多用户场景中，只做单用户的测试。

• 在单服务器上测试分布式应用。

• 与真实用户行为不匹配。例如Web页面中的“思考时间”。真实用户在请求到一个页面后会阅读一段时间，而不是不停顿地一个接一个点击相关链接。

• 反复执行同一个查询。真实的查询是不尽相同的，这可能会导致缓存命中率降低。而反复执行同一个查询在某种程度上，会全部或者部分缓存结果。

• 没有检查错误。如果测试的结果无法得到合理的解释，比如一个本应该很慢的查询突然变快了，就应该检查是否有错误产生。否则可能只是测试了MySQL检测语法错误的速度了。基准测试完成后，一定要检查一下错误日志， 这应当是基本的要求。

• 忽略了系统 预热(warm up) 的过程。例如系统重启后马上进行测试。有时候需要了解系统重启后需要多长时间才能达到正常的性能容量，要特别留意预热的时长。反过来说，如果要想分析正常的性能，需要注意，若基准测试在重启以后马上启动,则缓存是冷的、还没有数据，这时即使测试的压力相同，得到的结果也和缓存已经装满数据时是不同的。

• 使用默认的服务器配置。

• 测试时间太短。基准测试需要持续一定的时间。

2.3.1设计和规划基准测试

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

如果采用标准的基准测试，应该确认选择了合适的测试方案。例如，不要使用TPC-H测试电子商务系统。在TPC的定义中,“TPC-H是即席查询和决策支持型应用的基准测试"，因此不适合用来测试OLTP系统。

TPC-H测试超详细介绍

---

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

然后，针对数据运行查询。可以建立一个单元测试集作为初步的测试，并运行多遍。但是这和真实的数据库环境还是有差别的。

更好的办法是选择一个有 代表性的时间段，比如高峰期的一个小时，或者一整天，记录生产系统上的所有查询。

如果时间段选得比较小，则可以选择多个时间段。这样有助于覆盖整个系统的活动状态，例如每周报表的查询、或者非峰值时间运行的批处理作业。

可以在不同级别记录查询。例如，如果是 集成式(full-stack) 基准测试，可以记录Web服务器上的HTTP请求，也可以打开MySQL的 查询日志(Query Log) 。

---

倘若要重演这些查询，就要确保创建多线程来并行执行，而不是单个线程线性地执行。对日志中的每个连接都应该创建独立的线程，而不是将所有的查询随机地分配到一些线程中。查询日志中记录了每个查询是在哪个连接中执行的。

即使不需要创建专用的基准测试，详细地写下测试规划也是必需的。测试可能要多次反复运行，因此需要精确地重现测试过程。而且也应该考虑到未来，执行下一轮测试时可能已经不是同一个人了。即使还是同一个人，也有可能不会确切地记得初次运行时的情况。测试规划应该记录测试数据、系统配置的步骤，如何测量和分析结果，以及预热的方案等。

应该建立将参数和结果文档化的规范，每一轮测试都必 须进行详细记录。文档规范可以很简单，比如采用 电子表格(spreadsheet) 或者记事本形式，也可以是复杂的自定义的数据库。

需要记住的是，经常要写一些脚本来分析测试结果，因此如果能够不用打开电子表格或者文本文件等额外操作，当然是更好的。

2.3.2 基准测试应该运行多长时间

基准测试应该运行足够长的时间，这一点很重要。

如果需要测试系统在稳定状态时的性能，那么当然需要在稳定状态下测试并观察。而如果系统有大量的数据和内存，要达到稳定状态可能需要非常长的时间。

大部分系统都会有一些应对突发情况的余量，能够吸收性能尖峰，将一些工作延迟到高峰期之后执行。但当对机器加压足够长时间之后，这些余量会被消耗尽，系统的短期尖峰也就无法维持原来的高性能。

有时候无法确认测试需要运行多长的时间才足够。如果是这样，可以让测试一直运行,持续观察直到确认系统已经稳定。

下面是一个在已知系统上执行测试的例子，图2-1显示了系统磁盘读和写吞吐量的时序图。

系统预热完成后，读I/O活动在三四个小时后曲线趋向稳定，但写I/O至少在八小时内变化还是很大，之后有一些点的波动较大，但读和写总体来说基本稳定了。

一个简单的测试规则，就是等系统看起来稳定的时间至少等于系统预热的时间。本例中的测试持续了72个小时才结束，以确保能够体现系统长期的行为。

一个常见的错误的测试方式是，只执行一系列短期的测试，比如每次60秒，并在此测试的基础上去总结系统的性能。

如果没有时间去完成准确完整的基准测试，那么已经花费的所有时间都是一种浪费。

2.3.3 获取系统性能和状态

在执行基准测试时，需要尽可能多地收集被测试系统的信息。最好为基准测试建立一个目录，并且每执行一轮测试都创建单独的子目录，将测试结果、配置文件、测试指标、脚本和其他相关说明都保存在其中。即使有些结果不是目前需要的，也应该先保存下来。

多余一些数据总比缺乏重要的数据要好，而且多余的数据以后也许会用得着。

需要记录的数据包括系统状态和性能指标，诸如CPU使用率、磁盘I/O、网络流量统计、SHOW GLOBAL STATUS 计数器等。

下面是一个收集MySQL测试数据的shell脚本:

#!/bin/shINTERVAL=5
PREFIX=$INTERVAL-sec- status
RUNFILE= /home/benchmarks/running
mysql -e 'SHOW GLOBAL VARIABLES' >> mysql-variables
while test -e $RUNFILE; do
file=$(date +%F_ %I)
sleep=$(date +%S .%N| awk "{print $INTERVAL - (\$1 % $INTERVAL)}")
sleep $sleep
ts="$(date +"TS %s.%N %F %T")"
loadavg="$(uptime)"
echo "$ts $loadavg" >> $PREFIX-${file}-status
mysql -e 'SHOW GLOBAL STATUS' >> $PREFIX-${file}-status &
echo "$ts $loadavg" >> $PREFIX-${ file}-innodbstatus
mysql -e 'SHOW ENGINE INNODB STATUS\G' >> $PREFIX- ${file}- innodbstatus &
echo "$ts $loadavg" >> $PREFIX-${file}-processlist
mysql -e ' SHOW FULL PROCESSLIST\G' >> $PREFIX-${file}-processlist &
echo $ts
done
echo Exiting because $RUNFILE does not exist.

这只是一段简单的代码很好地演示了该如何捕获测试的性能和状态数据。

从代码可以看出，只捕获了MySQL的部分数据，如果需要，则很容易通过修改脚本添加新的数据捕获。

MySQL - 查看 / 修改配置参数（Global Variables）

2.3.4 获得准确的测试结果

获得准确测试结果的最好办法，是回答一些关于基准测试的基本问题:

• 是否选择了正确的基准测试?
• 是否为问题收集了相关的数据?
• 是否采用了错误的测试标准?例如，是否对一个I/O密集型(I/O-bound) 的应用，采用了 CPU密集型(CPU-bound) 的测试标准来评估性能?
• 确认测试结果是否可重复。每次重新测试之前要确保系统的状态是一致的。
  • 如果是非常重要的测试，甚至有必要每次测试都重启系统。一般情况下，需要测试的是经过预热的系统，还需要确保预热的时间足够长(请参考前面关于基准测试需要运行多长时间的内容)是否可重复。
  • 如果预热采用的是随机查询,那么测试结果可能就是不可重复的。

如果测试的过程会修改数据或者schema,那么每次测试前，需要利用快照还原数据。在表中插入1000条记录和插入100万条记录，测试结果肯定不会相同。

数据的碎片度和在磁盘上的分布，都可能导致测试是不可重复的。一个确保物理磁盘数据的分布尽可能一致的办法是，每次都进行快速格式化并进行磁盘分区复制。

要注意很多因素，包括外部的压力、性能分析和监控系统、详细的日志记录、周期性作业，以及其他一些因素，都会影响到测试结果。

一个典型的案例，就是测试过程中突然有cron定时作业启动，或者正处于一个 巡查读取周期(Patrol Read cycle) ，抑或RAID卡启动了定时的一致性检查等。要确保基准测试运行过程中所需要的资源是专用于测试的。如果有其他额外的操作，则会消耗网络带宽，或者测试基于的是和其他服务器共享的SAN存储，那么得到的结果很可能是不准确的。

每次测试中，修改的参数应该尽量少。如果必须要一次修改多个参数， 那么可能会丢失一些信息。有些参数依赖其他参数，这些参数可能无法单独修改。有时候甚至都没有意识到这些依赖，这给测试带来了复杂性。

一般情况下，都是通过迭代逐步地修改基准测试的参数，而不是每次运行时都做大量的修改。举个例子，如果要通过调整参数来创造一个特定行为，可以通过使用 分治法(divide-and-conquer,每次运行时将参数对分减半) 来找到正确的值。

---

很多基准测试都是用来做预测系统迁移后的性能的，比如从Oracle迁移到MySQL。

这种测试通常比较麻烦，因为MySQL执行的查询类型与Oracle完全不同。

如果想知道在Oracle运行得很好的应用迁移到MySQL以后性能如何，通常需要重新设计MySQL的schema和查询(在某些情况下，比如，建立一个跨平台的应用时，可能想知道同一条查询是如何在两个平台运行的，不过这种情况并不多见)。

另外，基于MySQL的默认配置的测试没有什么意义，因为默认配置是基于消耗很少内存的极小应用的。有时候可以看到一些MySQL和其他商业数据库产品的对比测试，结果很让人尴尬，可能就是MySQL采用了默认配置的缘故。让人无语的是，这样明显有误的测试结果还容易变成头条新闻。

最后，如果测试中出现异常结果，不要轻易当作坏数据点而丢弃。应该认真研究并找到产生这种结果的原因。

测试可能会得到有价值的结果，或者一个严重的错误，抑或基准测试的设计缺陷。如果对测试结果不了解，就不要轻易公布。有一些案例表明，异常的测试结果往往都是由于很小的错误导致的，最后搞得测试无功而返。

2.3.5 运行基准测试并分析结果

通常来说，自动化基准测试是个好主意。这样做可以获得更精确的测试结果。

• 因为自动化的过程可以防止测试人员偶尔遗漏某些步骤，或者误操作。另外也有助于归档整个测试过程。

自动化的方式有很多，可以是一个Makefile文件或者一组脚本。脚本语言可以根据需要选择:shell、PHP. Perl 等都可以。要尽可能地使所有测试过程都自动化，包括装载数据、系统预热、执行测试、记录结果等。

---

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

一般在测试的实践中，可以取最好的结果值，或者所有结果的平均值，抑或从五个测试结果里取最好三个值的平均值。

可以根据需要更进一步精确化测试结果。还可以对结果使用统计方法，确定 置信区间(confidence interval) 等。不过通常来说，不会用到这种程度的确定性结果。

只要测试的结果能满足目前的需求，简单地运行几轮测试，看看结果的变化就可以了。如果结果变化很大，可以再多运行几次，或者运行更长的时间，这样都可以获得更确定的结果。

下面是一个非常简单的shell脚本，演示了如何从前面的数据采集脚本采集到的数据中抽取时间维度信息。脚本的输入参数是采集到的数据文件的名字。

#!/bin/sh
# This script converts SHOW GLOBAL STATUS into a tabulated format, one line
# per sample in the input, with the metrics divided by the time elapsed
# between samples .
awk '
BEGIN {printf "#ts .date time load QPS" ;fmt = " %.2f";
}
/^TS/ { # The timestamp lines begin with TS.ts = substr($2, 1, index($2, ".") - 1);load = NF- 2;diff = ts - prev_ts;prev_ts = ts;printf "\n%s %s %s %s", ts, $3, $4, substr($load, 1, 1ength($1oad)-1);
}
/Queries/ {printf fmt,($2 -Queries)/diff;Queries=$2
}
' "$@"

2.3.6 绘图的重要性

前面写的脚本的输出都可以定制作为gnuplot或者R绘图的数据来源。

假设使用gnuplot,假设输出的数据文件名是QPS-per- 5-seconds :

gnuplot> plot "QPS-per-5-seconds" using 5 W lines title "QPS"

该gnuplot命令将文件的第五列qps数据绘成图形，图的标题是QPS。图2-2是绘制出来的结果图。

2.4 基准测试工具

2.4.1 集成式测试工具

• ab
• http_load
• JMeter

2.4.2 单组件式测试工具

• mysqlslap
• MySQL Benchmark Suite
• SuperSmack
• Database Test Suite
• Percona’s TPCC-MySQL Tool
• sysbench

2.4.3 MySQL内置函数BENCHMARK()

MySQL内置的函数，用于测试特定操作或表达式。

benchmark(次数，操作)

比如比较MD5和SHA1的执行谁更快

2.5 基准测试案例

2.5.1 http_load

基于Linux平台的软件。
安装地址

wget http://www.acme.com/software/http_load/http_load-12mar2006.tar.gz

完成后将文件解压

tar xzvfhttp_load-12mar2006.tar.gz

进入文件编译（需要C环境）

make install

写一个文件，里面放入的为要测试的网址

如下为书中给出的网址

http://www.mysqlperformanceblog.com/
http://www.mysqlperformanceblog.com/page/2/
http://www.mysqlperformanceblog.com/mysql-patches/
http://www.mysqlperformanceblog.com/mysql-performance-presentations/
http://www.mysqlperformanceblog.com/2006/09/06/slow-query-log-analyzes-tools/

然后可以执行如下命令：

./http_ load -parallel 并发数目 -seconds 时长 文件名
./http_ load -rate 频率 -seconds 时长 文件名

-parallel ：并发进程数。
-fetches ：总计访问次数
-rate ：每秒访问频率
-seconds ：总计访问时间

我们运行后，可以得到如下结果

2.5.2 MySQL BenchMark Suit

MySQL基准测试套件(MySQL Benchmark Suite) 由一组基于Perl开发的基准测试工具组成。

如下方式，是运行全部测试

$ cd /usr/share/mysql/sql-bench/
sql-bench$ ./run-all-tests --server=mysql --user=root --1og --fast

运行全部测试需要比较长的时间，有可能会超过一个小时，其具体长短依赖于测试的硬件环境和配置。如果指定了–log命令行，则可以监控到测试的进度。

测试的结果都保存在output子目录中，每项测试的结果文件中都会包含一系列的操作计时信息。

笔者这里建议使用WorkBench学习，因为是可视化的，用起来也方便

sudo snap install mysql-workbench-community

如下图，我们可以看到安装完后有一个图标

它可以展示相关的关系图

官方文档

2.5.3 Sysbench

sysbench可以执行多种类型的基准测试，它不仅设计用来测试数据库的性能，也可以测试运行数据库的服务器的性能。

sudo apt install sysbench

实际上，Peter和Vadim最初设计这个工具是用来执行MySQL性能测试的(尽 管并不能完成所有的MySQL基准测试)。

强烈建议大家都能熟悉sysbench测试，在MySQL用户的工具包中，这应该是最有用的工具之一。尽管有其他很多测试工具可以替代sysbench的某些功能，但那些工具有时候并不可靠，获得的结果也不一定和MySQL性能相关。

例如，I/O性能测试可以用iozone、bonnie++ 等一系列工具， 但需要注意设计场景，以便可以模拟 InnoDB的磁盘I/O模式。而sysbench的I/O测试则和InnoDB的I/O模式非常类似，所以fileio选项是非常好用的。

• CPU基准测试

sysbench --test=cpu --cpu-max-prime=20000 run

该测试使用64位整数，测试计算素数直到某个最大值所需要的时间。

• sysbench的文件I/O基准测试

文件I/O(fileio)基准测试可以测试系统在不同I/O负载下的性能。这对于比较不同的硬盘驱动器、不同的RAID卡、不同的RAID模式，都很有帮助。可以根据测试结果来调整I/O子系统。文件I/O基准测试模拟了很多InnoDB的I/O特性。

测试的第一步是 准备(prepare) 阶段，生成测试用到的数据文件，生成的数据文件至少要比内存大。如果文件中的数据能完全放入内存中，则操作系统缓存大部分的数据，导致测试结果无法体现I/O密集型的工作负载。首先通过下面的命令创建一个数据集（书中用了150G，感觉有点太大了）:

sysbench --test=fileio --file-total-size=150G prepare

这个命令会在当前工作目录下创建测试文件，后续的运行(run)阶段将通过读写这些文件进行测试。

第二步就是运行(run) 阶段，针对不同的I/O类型有不同的测试选项:

seqwr	顺序写入
seqrewr 顺序重写
seqrd 顺序读取
rndrd 随机读取
rndwr 随机写入
rdnrw 混合随机读/写

可以组合出如下命令

sysbench --test=fileio --file-total-size=150G --file-test-mode=rndrw/
--init-rng=on --max-time=300 - -max-requests=0 run

这里给出书中的结果

测试完成后，运行清除(cleanup) 操作删除第一步生成的测试文件:

sysbench --test=fileio --file-total-size=150G cleanup

• sysbench的OLTP基准测试

OLTP基准测试模拟了一个简单的事务处理系统的工作负载。

下面的例子使用的是一张超过百万行记录的表，第一步是先生成这张表:

sysbench --test=oltp --oltp-table-size=1000000 --mysql-db=test/
--mysql-user=root prepare

生成测试数据只需要上面这条简单的命令即可。接下来可以运行测试，这个例子采用了8个并发线程，只读模式，测试时长60秒:

sysbench --test=oltp --oltp-table-size=1000000 --mysql-db=test --mysql-user=root/
--max-time=60 --oltp-read-only=on --max-requests=0 --num-threads=8 run

此外，sysbench还有其他测试，详情可阅读官方githubhttps://github.com/akopytov/sysbench。

附录

《高性能MySQL》
Baron Schwartz, Peter Zaitsev, Vadim Tkachenko 著
宁海元 周振兴 彭立勋 翟卫祥 刘辉 译