sysbench与mysqlslap详解

问题描述

在编写代码之前，填充亿级数据之后，应对数据库做基准测试，即在还没有编写代码的时候了解当前设计的数据格式和表结构的性能基准是怎样的，以便在之后编程时有所比较，此阶段出现的典型问题如下：

1. 如何为刚搭建的MySQL数据库配置相关参数，例如8CPU、16MEM的服务器，应配置何种参数使该服务器最优？

2. 当前设计的数据结构与表结构在没有其他因素影响性能时，基准响应情况如何？

3. 当前负载均衡架构体系是否过多地影响了单台MySQL数据库的性能，例如，主从复制在当前架构中应当选择何种策略才不会过度损耗单台MySQL数据库的性能？

4. 当前单台MySQL数据库的最大承载访问量是多少？

5. 当MySQL数据库单表数据量过亿时，返回数据的速度极慢是正确的吗？

问题分析与解决方案

在搭建好MySQL数据库之后，通常需要对单台MySQL进行基准测试，即测试MySQL的数据量为亿级时的响应情况。如果知道单台MySQL在没有任何干扰情况下的响应速度，自然就可以知道当前单台MySQL可承载的访问量为多少。

具体方案是分别测试主从复制集群及单节点访问，从而得到两种响应结果，由此可以知道当前选择的MySQL集群策略是否过度影响了单台MySQL的性能消耗。当服务器不同、SQL语句不同、表结构及数据量不同时，单台MySQL最大承载访问量也不同，此时需要针对自身表结构进行性能测试。

从代码原理来讲，MySQL单表数据量过大确实更消耗性能，但是这部分消耗类似于HashMap。当数据条数超过4000时，HashMap的速度会很慢，但不代表它无法满足当前应用程序的需求，另外，其返回速度未必是极慢的。例如，对于单表亿级数据量的MySQL，在仅返回10条数据时，其返回的时间仍是毫秒级的。

解决方案：sysbench

sysbench是一个模块化、跨平台、开源的多线程基准测试工具，可以执行CPU、内存、线程、I/O、数据库等方面的性能测试，主要用于测试不同系统参数下数据库的负载情况，目前支持MySQL、Oracle和PostgreSQL数据库。

Sysbench的主要测试内容及含义如表所示：

使用sysbench测试时，通常分为以下三步：

1. prepare：造数据。

2. run：执行脚本进行测试。

3. cleanup：删除测试数据。

通常sysbench会在run步骤执行之后，获得相关的测试结果。

下载MySQL官网提供的sysbench安装文件，在Linux系统中进行编译和安装：

如果编译时提示缺少lib文件，则运行yum install mysql-devel命令，安装MySQL的lib文件。如果之前已经安装过lib文件，则忽略这行命令。

在安装结束之后，可以使用sysbench --version命令验证安装是否成功，如图所示：

如果在安装过程中出现如图所示的错误，可以暂时忽略。

在源码包中，sysbench内置了OLTP脚本，即内置了部分可以直接使用的测试工具。通常情况下，只要使用内置脚本，就可以实现大部分性能基准压测的需求，因此不用自定义Lua脚本进行压测。另外，如果用yum方式下载sysbench，则可能下载不到相关的脚本。

OLTP脚本位于下面的地址中：

sysbench的命令与参数

sysbench的一般命令行语法如下：

options是sysbench的基本参数，用于指定sysbench的并发度、压测时长、线程数、总请求数、等待时间等。options中的常用参数及释义如表所示：

testname是sysbench的基准测试名称，可选项包括fileio、memory和CPU，以及捆绑的Lua脚本的名称（如oltp_read_only）或一个定制的Lua脚本等。

command用于指定sysbench执行哪些测试命令，可选项包括prepare、run和cleanup等。

解决方案：mysqlslap

mysqlslap是MySQL官方从5.1.4版本开始提供的一个基准压测工具。它通过模拟多个并发客户端访问MySQL来执行压力测试，同时提供“高负荷攻击MySQL”数据性能报告，并且能很好地对比多个存储引擎在相同环境下的并发压力性能差异。通过mysqlslap --help命令可以获得可用的选项，在安装MySQL-client端时会自动安装mysqlslap。

在安装MySQL 5.1.4之后，通过whereis mysqlslap命令可以看到mysqlslap的存放位置，如图所示：

当mysqlslap在/usr/bin/目录下时，即可在控制台直接使用mysqlslap命令。输入mysqlslap --help命令，响应如图所示：

mysqlslap的命令与参数

mysqlslap的基本命令如下所示：

options中的参数及释义如表所示：

sysbench实战

使用sysbench压测CPU、内存和磁盘I/O

这里只准备了一台服务器作为MySQL服务器。该服务器内存1GB、硬盘20GB、CPU 1核、系统版本CentOS 6.5、MySQL版本5.1.73。

1. 压测CPU

使用如下命令压测Linux系统的CPU：

在压测过程中，可以通过top命令或从system-monitor管理器看到当前Linux系统的性能变化。在压测过程中，CPU使用率会突然飙升到100%，如图3-5所示。压测结果如图所示：

2. 压测内存

使用如下命令压测Linux系统的内存，压测结果如图所示：

3. 压测磁盘I/O

压测磁盘I/O同样需要三步：

1. prepare：往磁盘中存储测试数据。

2. run：读取测试数据。

3. cleanup：删除最开始存储的测试数据。

其中，在run这步中可以选择随机读取、顺序读取等方式。

执行如下命令，增加测试数据：

增加数据之前的系统容量如图所示：

增加测试数据的日志过程如图所示：

增加数据之后的系统容量如图所示：

执行如下命令，对磁盘进行压测：

压测结果如图所示：

执行如下命令，删除增加的测试数据：

删除测试数据的结果如图所示：

初次使用sysbench压测MySQL

在对MySQL进行压测之前，首先选取默认值，其次使用sysbench内置的OLTP脚本进行测试，最后修改数据库名称、账号和密码。压测MySQL同样需要prepare、run和cleanup三步。

执行如下命令，增加测试表，部分内容可以自定义填写。另外，在/etc/my.cnf（MySQL的配置文件）中可以查询mysql-socket参数。除此之外，在执行命令前最好手动在MySQL中创建要添加的实例库mytest1，否则有可能出现找不到实例库的错误。

命令执行成功，如图所示：

原本mytest1是空实例库，此时在它的下面新增了一个sbtest表，该表是由sysbench生成的，如图所示：

查询sbtest表可以发现，在初步测试时，sbtest表中包含sysbench新增的100条数据。数据条数是在命令行的oltp-table-size字段中定义的，部分数据内容如图所示：

输入如下命令，对sbtest表进行压测：

初步使用sysbench压测MySQL的结果如图所示：

输入如下命令，删除MySQL中的sbtest表：

删除结果如图所示：

深度使用sysbench压测MySQL

1. 测试数据库的TPS性能

2. 测试数据库的只读性能

3. 测试数据库的删除性能

4. 测试数据库的插入性能

5. sysbench的输出结果及释义

主要分为三部分：

1. 版本号及关键测试参数输出。

2. 中间统计结果输出。

3. 整体统计结果输出。

部分参数及释义如表所示：

mysqlslap实战

1. mysqlslap基础入门

mysqlslap只输出语句的命令如下所示，该命令通常用来查看在执行mysqlslap之后会输出哪些语句。在执行其他测试之前，建议优先使用如下语句输出测试内容。

在执行上述语句之后，需要输入MySQL的相关密码，如图所示：

在输入密码之后，mysqlslap会对输出的测试内容执行常规的创建表、写入数据、读取数据、删除表等相关操作，如下图所示：

如果一切正常，则在无任何报错的情况下，可以进行后续测试，输入如下命令：

测试结果如图所示：

上图中的基准测试结果释义如下所示：

• 运行所有查询的平均时间：0.001秒。

• 运行所有查询的最短时间：0.001秒。

• 运行所有查询的最长时间：0.001秒。

• 运行查询的客户端数：1。

• 每个客户端的平均查询数：0。

2. mysqlslap测试查询

下面的命令可以测试100个并发自动生成的SQL测试脚本，并执行1000次总查询所需的时间：

运行结果如图所示：

3. mysqlslap测试复杂表查询1

mysqlslap可以自动生成复杂表，下面模拟50个和100个用户分别执行5000次总查询，迭代整套测试5次所需的时间，命令如下所示：

执行结果如下图所示，每次迭代都会生成一个相应的测试报告。

当服务器性能不佳或者数据库允许连接数未达到上限时，将无法正常连接数据库，报错信息如图所示：

4. mysqlslap测试复杂表查询2

使用--number-int-cols选项，指定表中包含4个int型数据的列。使用--number-char-cols选项，指定表中包含35个char型数据的列。使用--engine选项，指定针对哪种存储引擎进行测试：

执行结果如图所示：

5. mysqlslap基于自定义语句压测

mysqlslap基于自定义语句压测的命令如下所示：

执行结果如图所示：

mysqlslap与sysbench的功能和用法类似，mysqlslap也可以自定义复杂查询语句或自定义复杂表进行相应的基准测试，其自由度与sysbench相同。

这两个基准测试工具几乎可以满足绝大部分MySQL基准压测场景。在实际压测过程中，建议自定义设置语句，保证压测时长大于半小时。若服务器性能优良，则测试表不应少于20个，单表数据不少于一亿条。

通常在使用mysqlslap进行基准测试之前，先用only-print命令输出接下来会自动执行的SQL语句，再将命令行存储为shell脚本，最后进行测试。

除mysqlslap与sysbench外，也可以用JMeter对数据库进行基准压测。

其他基准压测工具

数据库的本质是将数据存储到文件中，而相关类似的测试Linux的工具多如繁星，有兴趣的读者可以自行进行磁盘I/O压测。目前主流的第三方磁盘I/O压测工具有fio、Iometer和Orion，这三个工具各有千秋。

fio在Linux系统下使用比较方便，Imeter在Window系统下使用比较方便，Orion是Oracle专用的磁盘I/O测试工具，可以在不安装Oracle数据库的情况下模拟Oracle数据库场景的读和写。

fio是一个非常好用的磁盘I/O压测工具，它可以对硬件进行压测和验证，支持13种不同的磁盘I/O引擎，包括sync、磁盘mmap、磁盘libaio、磁盘posixaio、磁盘SGv3、磁盘splice、磁盘null、磁盘network、磁盘syslet、guasi和solarisaio等，MySQL的磁盘I/O引擎为libaio。

当使用sysbench和mysqlslap测试MySQL的基准都不理想时，可以使用fio对Linux系统进行详细的磁盘I/O压测，这样就可以更深入地了解存储与读取慢的原因了。例如，MySQL的配置与性能出现了问题，或者是Linux的硬件出现了故障。

例如，某项目组在管理Oracle数据库时曾因交换机老化，导致高并发下数据存储出现不定期异常，类似问题都可以通过磁盘I/O压测得到相应结果。

现在是云服务器时代，可能很少有公司会独立部署机房，语言类程序员对交换机、路由器、硬件防火墙、网闸等相关硬件的掌握能力相对一般，而云服务器的磁盘又很少出现“疑难杂症”，所以对磁盘I/O压测方面的内容简单了解即可。