使用 Sysbench 对数据库进行性能测试

想要测试一下 MySQL InnoDB 和 MyISAM 两种引擎在不同状态下的查询效率, 之前使用过 sysbench 对 InnoDB 优化做过一波测试, 时间有点久, 对使用方式有点忘记了, 正好趁这个机会梳理一下; 也刚好对自定义脚本测试做一个简介.

sysbench 是一个跨平台的基准测试工具, 不仅支持数据库性能测试, 还支持测试 CPU性能、磁盘IO性能、调度程序性能、内存分配及传输速度、POSIX线程性能 测试; 本文主要对 数据库性能 测试做一个梳理

MySQL 其实自带了一个压力测试工具 mysqlslap, 但是对自定义测试不太友好. 但是在实际的项目中, 可能就是需要对现有的数据做测试, 所以 sysbench 的适用范围要比 mysqlslap 更广

安装 sysbench 和基本使用方法

本文使用测试机器配置: 系统: Ubuntu 20.04; MySQL: MariaDB 10.5; 系统配置: 1核2G

$ curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.deb.sh | sudo bash
$ sudo apt -y install sysbench

安装好之后我们先运行一个简单的示例, 对工具的使用有一个基本的了解

$ sysbench --db-driver=mysql --mysql-user=root \
	--mysql-password=test123456 --mysql-db=tests --threads=1 \
	--events=100 --time=10 --rate=0 --report-interval=0  \
	/usr/share/sysbench/bulk_insert.lua run

sysbench 1.0.20 (using system LuaJIT 2.1.0-beta3)

Running the test with following options:
Number of threads: 1
Initializing random number generator from current time


Initializing worker threads...

Threads started!

SQL statistics:
    queries performed:
        read:                            0  # 读取数据执行次数
        write:                           1  # 写入数据执行次数
        other:                           0  # 其他操作执行次数
        total:                           1  # 总执行次数
    transactions:                        100    (15675.30 per sec.) # 事务执行次数    	(每秒事务次数)
    queries:                             1      (156.75 per sec.)   # 查询执行次数    	(每秒查询次数)
    ignored errors:                      0      (0.00 per sec.)     # 忽略错误数      (每秒忽略错误次数)
    reconnects:                          0      (0.00 per sec.)     # 数据库重连次数	(每秒重连次数)	

General statistics:
    total time:                          0.0043s	# 总允许时间
    total number of events:              100		# 总执行事件次数

Latency (ms):
         min:                                    0.00 	# 最小请求响应时间
         avg:                                    0.00 	# 平均请求响应时间
         max:                                    0.19 	# 最大请求响应时间
         95th percentile:                        0.00 	# 95%请求响应时间
         sum:                                    0.33 	# 总请求响应时间

Threads fairness:
    events (avg/stddev):           100.0000/0.00 	# 执行的事件总数
    execution time (avg/stddev):   0.0003/0.00 		# 执行耗时

命令格式:

$ sysbench [options]... testname command

• testname 说明: 需要运行的测试类型, 可以内置的测试, 也可以是自定的 lua 脚本; 内置测试有: fileio (文件 I/O 测试)、cpu (CPU 性能测试)、memory (内存测试)、threads (POSIX 线程性能测试、mutex (调度程序性能测试)、

• command 说明: command 是传递给 testname 的内置名称或制定的自定义脚本的, 只有 run 命令是所有内置测试和脚本都支持的, 自定义脚本可以自定义自己特有的命令; 一般 prepare 代表测试前资源准备, 例如: 创建测试表, 写入测试数据等; run 代表开始执行测试; cleanup 代表清除创建的测试数据, 例如: prepare 创建的测试表、测试数据等; help 查看内置测试或自定义脚本的使用帮助

常用 options 参数选项:

参数名	参数说明
–db-driver	mysql 或 pgsql, 数据库驱动, 默认 mysql
–mysql-user	MySQL 用户名, 默认值 sbtest
–mysql-port	MySQL 端口, 默认值 3306
–mysql-password	MySQL 登陆密码, 默认值为空
–mysql-db	MySQL 测试库名称, 默认值 sbtest
–threads	并发线程数, 默认值为 1
–events	希望测试事件运行的总次数
–time	当前测试总执行时间, 默认值 10, 单位秒
–rate	平均事务速率, 0 表示不限制, 默认 0
–report-interval	设置定期报告中间统计的时间间隔, 0 表示不设置, 默认值 0

其他参数可以通过 sysbench --help 查看.

注意: 如果当前时间已到达 --time 设置的秒数, 不管当前已执行次数是否达到 --events 的值, 都会停止测试; 反之如果当前已执行次数已达到 --events 设置的值, 不管当前时间是否达到 --time 设置的秒数, 测试也会停止

sysbench 自带了几个基本的数据库测试脚本, 都放在 /usr/share/sysbench 目录下. 我们可以通过 sysbench script_name help 的方式查看某个脚本的使用方式. 例如:

$ sysbench /usr/share/sysbench/oltp_read_only.lua help

sysbench 1.0.20 (using system LuaJIT 2.1.0-beta3)

oltp_read_only.lua options:
  --auto_inc[=on|off]           Use AUTO_INCREMENT column as Primary Key (for MySQL), or its alternatives in other DBMS. When disabled, use client-generated IDs [on]
  --create_secondary[=on|off]   Create a secondary index in addition to the PRIMARY KEY [on]
  --delete_inserts=N            Number of DELETE/INSERT combinations per transaction [1]
  --distinct_ranges=N           Number of SELECT DISTINCT queries per transaction [1]
  --index_updates=N             Number of UPDATE index queris per transaction [1]
  ....

不是所有脚本或内置测试都支持 help 命令, 在自定义脚本环节我们会讲述使用帮助是怎样声明的.

自定义测试脚本

完整代码: https://gogs.ijunj.com/common/sysbench-testsl

sysbench 只提供了几个基础的测试脚本, 当我们需要对现有数据测试或者需要进行更深层次的测试时, 就需要自定义测试脚本去达到测试的目的; sysbench 的脚本通过 Lua 编写, sysbench 为我们提供了一套相当完整的 API, 只需要参照它提供的基础测试脚本就能快速的上手; 当然这需要你有一定的 Lua 基础

标准 测试脚本都要实现三个函数: thread_init、thread_done、event; 其中 thread_init 和 thread_done 是在每个线程中都会被执行一次, 也就是说这两个函数被 --threads 参数的值影响, --threads 值为几, 它们就会被执行几次. thread_init 中一般实现线程初始化的操作, 例如: 连接数据库、SQL 语句拼装、参数绑定等操作; thread_done 则是实现资源释放的函数, 例如: 断开数据库连接等; events 中就是需要测试的主要逻辑实现, 它受 --events 参数值影响, 值为几 event 函数就会被执行几次 (--time 时间充足的情况下);

如果你不需要执行 run 命令的话可以不实现上述的三个函数, 例如下面这个例子, 整个脚本只创建和删除测试数据:

function cmd_prepare()
	-- initialize the sysbench mysql driver
    local drv = sysbench.sql.driver()
    -- represents the connection to MySQL
    local con = drv:connect()

    local query_str = [[
CREATE TABLE `type_test_tb` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `str` varchar(64) NOT NULL DEFAULT '',
  `num` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
) ENGINE=innodb DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC]]

    print("Creating table type_test_tb: \n")
    print(query_str)
    -- Execute the SQL statement to create the test table
    con:query(query_str);

    query_str = "INSERT INTO type_test_tb (str, num) VALUES"

    -- Initialize the batch write channel
    con:bulk_insert_init(query_str)
    for i = 1, 10 do
        query_str = string.format("('%s', %d)",
                tostring(sysbench.rand.pareto(1, 10)),
                sysbench.rand.pareto(1, 10))

        -- Write data to the test table one by one
        con:bulk_insert_next(query_str)
    end

    -- Disable the batch write channel
    con:bulk_insert_done()
end

function cmd_cleanup()
    local drv = sysbench.sql.driver()
    local con = drv:connect()

    con:query("DROP TABLE IF EXISTS type_test_tb")
    print("Drop table type_test_tb success")
end

-- Statement custom commands
sysbench.cmdline.commands = {
    prepare = { cmd_prepare, sysbench.cmdline.PARALLEL_COMMAND },
    cleanup = { cmd_cleanup, sysbench.cmdline.PARALLEL_COMMAND },
}

整个示例的主要逻辑是声明了两个命令, prepare 命令创建一张表, 并写入 10 条测试数据; cleanup 命令删除通过 prepare 创建的表; 当执行 sysbench ./test.lua command 时, 对应的函数就会被执行一次. sysbench.rand.pareto(min, max) 是 sysbench 提供的随机数 API.

只有 run 命令才会调用 thread_init、thread_done、event 函数, 自定义只会调用声明命令时设置的函数, 且只会调用一次

上述示例中, 所有的参数都是写死的, 比如我需要指定表的 存储引擎; 或者我需要制定表中生成测试数据的条数, 我需要逐条测试 10 条的插入速度、100 条插入速度, 1w 条插入速度. 不能每次都去修改脚本吧, 所有就需要给脚本添加一些它自己的参数. 只需要在脚本中添加:

sysbench.cmdline.options = {
    storage_engine = { "Storage engine, if MySQL is used", "innodb" },
    table_size = { "Number of rows per table", 100000 },
    column = { "Column of table by query", "num" },
}

通过 sysbench.cmdline.options 设置脚本参数之后, 就可以通过 sysbench.opt.xxx 的方式获取传递的参数, 例如: sysbench.opt.table_size. 也可以通过 sysbench ./test.lua help 查看使用帮助

实现 run 命令:

function thread_init()
    drv = sysbench.sql.driver()
    con = drv:connect()

    local query_str = string.format("select * from type_test_tb " ..
            " where %s = ?", sysbench.opt.column)
    stmt = con:prepare(query_str)
    local type = sysbench.opt.column == 'num' and sysbench.sql.type.INT or sysbench.sql.type.VARCHAR

    params = {stmt:bind_create(type, 64)}
    stmt:bind_param(unpack(params))
end

function thread_done()
    stmt:close()
    con:disconnect()
end

function event()
    local num = sysbench.rand.pareto(1, sysbench.opt.table_size)
    num = sysbench.opt.column == 'num' and num or tostring(num);
    params[1]:set(num)

    stmt:execute()
end

在实现 thread_init、thread_done、event 三个命令时, 线程内共用的变量, 需要生命为全局变量, 例如: 数据库连接, 预加载等; 注: 当使用 预加载 的方式写入语句时, stmt:bind_create(type, [len]) 参数为字符串类似时, 需要设置字符串的长度