用sysbench快速装载MySQL测试数据

问题描述

我们在对数据库进行性能测试的时候最常用到的测试工具是sysbench，这款工具功能强大，已经成为业界测试主流数据库性能的标准，目前用sysbench测试的时候采用的测试数据量一般是64张表，每张表10000000笔数据，这个数据量大概有150GB，一般情况下这些数据是用sysbench的prepare参数通过oltp.lua脚本一条一条的生成随机数据，再一条一条的插入到数据库中，这个准备过程类似于重复的插入单条数据到数据库，所以比较慢，正常情况下准备64张表，每表1000W笔数据需要10几个小时，即使是SSD磁盘也需要4个小时，这个过程漫长，浪费时间，对于经常需要做性能测试的人来说不可忍受。

需求描述

也许是sysbench工具在设计的时候考虑到各个数据库的通用性，才没有考虑装载数据比较慢的问题，不过sysbench提供了强大大lua自定义接口，针对以上装载数据慢的问题，已经有人发布了oracle和PostgreSQL快速插入数据方法，了解到oracle用的sqlldr，PG用的也是类似的方法，不过都需要有客户端来完成数据装载，这种方法是利用工具先生成需要插入的数据，然后批量装载到数据库，这样，比一条一条的生成、插入数据会快很多，MySQL的LOAD DATA INFILE类似于这种方式，我们可以设计sysbench提供的lua接口，实现一个lua脚本，通过 load data infile的方式来加速数据装载。

原理介绍

在使用sysbench测试数据库性能的时候，最常用到的两个命令是参数是prepare和run，分别对应于数据准备和测试执行，prepare阶段使用单线程来插入数据，效率低下，例如，以下命令是用prepare的方式单线程执行oltp.lua来插入数据

sysbench –test=./sysbench/tests/db/oltp.lua  --oltp-tables-count=64 --oltp-table-size=10000000 --mysql-host=xx.xx.xx.xx --mysql-port=xxxx --mysql-db=testdbx --mysql-user=root --mysql-password=xxxxxx  prepare

而run的方式是可以指定并行参数多线程并发来执行，例如，在执行阶段，sysbench产生了32个线程来执行测试任务。

sysbench --test=./sysbench/tests/db/xxx.lua  --oltp-tables-count=64 --oltp-table-size=10000000 --mysql-host= xx.xx.xx.xx --mysql-port= xxxx --mysql-db=testdbx --mysql-user=root --mysql-password= xxxxxx  --num-threads=32 run

那么，我们为什么不用run的方式来并行造数据呢，这样，我们就可以并行执行MySQL load data infile以加速数据的加载。

用这种方式，只需实现一个生成原始数据的工具，再实现一个lua脚本用来加载数据就可以了。

数据的生成

Sysbench的表结构比较简单，数据的生成主要是生成随机数，这里为了提高效率，采用c语言来完成数据生成的代码。

代码保存为gendata.c

编译上面c语言的方式如下：

 gcc gendata.c -o gendata

生成gendata可执行文件后，执行gendata 加上要生成的数据量，就可以生成需要的数据了，例如：

./gendata 2
 1,0,89972079288-04286136280-27548240250-91378609072-62125630986-31031597636-48809504488-66368343240-64737407520-08070207568,16823019800-26761011658-25884599296-42689212400-64702665950
 2,2,98945650624-93173592422-60892765221-01539165792-94510835936-02724198826-08373766004-71825491844-16642099974-59473284405,76897098852-02055441101-97671756059-41804656608-33688540610

这样就生成了两笔数据

可以通过重定向将数据输出到文件里，例如，以下方式生成1000W笔数据，并保存到testdb1.dat中：

./gendata 10000000 > testdb1.dat

实际测试生成1000W笔数据只需30秒左右。

数据加载脚本的设计

数据加载使用sysbench提供的lua接口来实现，这里实现了一个可以使用mysql load data infile方式加载数据的lua脚本

脚本保存为loaddata.lua并放在. /sysbench/tests/db/目录下

脚本中会自动调用gendata工具生成数据文件，然后用并行的方式将文件加载到数据库中。

测试与验证

加载数据使用sysbench run的方式启动多个线程来并行执行loaddata.lua脚本，例如：

sysbench --test=./sysbench/tests/db/loaddata.lua  --oltp-tables-count=64 --oltp-table-size=100000000 --mysql-host=xx.xx.xx.xx --mysql-port=xxxx --mysql-db=testdbx --mysql-user=root --mysql-password=xxxx --num-threads=4 run

实际测试中发现同样的测试环境这种方式会比sysbench的方式快10倍左右，在数据加载的过程中磁盘性能会成为瓶颈，磁盘性能越高，加载越快。