一、前言

做为一名性能工程师掌握对 MySQL 的性能测试是非常必要的，本文基于 Sysbench 对MySQL OLTP（联机事务处理）的 BenchMark 测试案例详细介绍具体方法。

二、测试环境

1、服务器配置

数据库服务器：

操作系统：CentOS 7.6 64位
CPU：8核
内存：16GB
磁盘：500GB，最大吞吐量150 MB/s
数据库版本：MySQL Community Server 8.0.37
网络：局域网

测试服务器：

操作系统：CentOS 7.6 64位
CPU：8核
内存：16GB
磁盘：500GB，最大吞吐量150 MB/s
测试软件：sysbench-1.0.12
网络：局域网
2、测试拓扑

📢注意：

尽量不要在 MySQL 本服务器上进行测试，一方面可能无法体现网络（哪怕是局域网）的影响，另一方面，sysbench 的运行（并发数较高时）会影响挤压 MySQL 服务器性能。
在开始 MySQL 测试之前，应针对数据库服务器做好 BenchMark 测试。

三、测试工具安装

Sysbench是一款基于LuaJIT的，模块化多线程基准测试工具，常用于数据库基准测试。通过内置的数据库测试模型，采用多线程并发操作来评估数据库的性能。了解Sysbench更多详情，请访问：https://github.com/akopytov/sysbench。

本次测试使用的Sysbench版本为1.0.12，具体的安装命令如下：

# wget -c https://github.com/akopytov/sysbench/archive/1.0.12.zip
# yum install autoconf libtool mysql mysql-devel vim unzip
# unzip 1.0.12.zip
# cd sysbench-1.0.12
# ./autogen.sh
# ./configure
# make
# make install
#sysbench --version

显示以下内容说明已安装成功。

四、测试步骤

请根据实际信息，替换数据库、连接IP与用户密码。

1、导入数据

（1）使用 MySQL 命令或第三方工具登录数据库，并创建测试数据库 “loadtest” 。

mysql -u root -P 3306 -h -p -e "create database loadtest"

（2）使用 sysbench 命令导入测试背景数据到 “loadtest” 数据库。

sysbench
--test=/usr/local/share/sysbench/tests/include/oltp_legacy/oltp.lua
--db-driver=mysql --mysql-db=loadtest --mysql-user=root
--mysql-password= --mysql-port=3306 --mysql-host= --oltp-tables-count=64 --oltp-table-size=10000000 --num-threads=20 prepare

脚本参数及其含义：

--test：指定要运行的测试脚本，这里选择的是一个OLTP（在线事务处理）负载测试脚本。oltp.lua是一个预定义的脚本，用于模拟常见的数据库操作。
--db-driver：指定数据库驱动程序，这里选择的是 MySQL。
--mysql-db：指定要测试的 MySQL 数据库名称，这里是loadtest数据库。
--mysql-user：指定用于连接 MySQL 数据库的用户名，这里是 root 用户。
mysql-password：指定用于连接 MySQL 数据库的密码，这里为空，意味着没有设置密码（不推荐在生产环境中使用空密码）。
--mysql-port：指定 MySQL 服务器监听的端口，这里是默认的 3306 端口。
--mysql-host：指定 MySQL 服务器的主机地址，这里为空，表示连接本地数据库。
--oltp-tables-count：指定用于测试的表的数量，这里是 64 个表。
--oltp-table-size：指定每个表中的行数，这里是 10,000,000 行。表示每个表有一千万条记录。
--num-threads：指定测试时使用的线程数，这里是 20 个线程。表示并发 20 个线程进行测试。
prepare：测试提前准备数据

本文是生成 64 张表，每张表有1千万数据，合计导入6亿4千万条数据。

显示下面信息说明已经成功完成测试数据生成：

WARNING: the --test option is deprecated. You can pass a script name or path on the command line without any options.
WARNING: --num-threads is deprecated, use --threads instead
sysbench 1.0.12 (using bundled LuaJIT 2.1.0-beta2)

......
Inserting 10000000 records into 'sbtest63'
Creating secondary indexes on 'sbtest63'...
Creating table 'sbtest64'...
Inserting 10000000 records into 'sbtest64'
Creating secondary indexes on 'sbtest64'...
[root@ecs-825d-1113052 ~]#

生产的表结构如下：

CREATE TABLE sbtest (
id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
k INTEGER UNSIGNED DEFAULT '0' NOT NULL,
c CHAR(120) DEFAULT '' NOT NULL,
pad CHAR(60) DEFAULT '' NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB

生产数据样例如下：

这里用到 oltp.lua 这个关键脚本，我们单独拿出分析下，源码如下：

[root@ecs-825d-1113052 ~]# cat /usr/local/share/sysbench/tests/include/oltp_legacy/oltp.lua
-- 匹配test路径并检查
pathtest = string.match(test, "(.*/)")
if pathtest then
   dofile(pathtest .. "common.lua")
else
   require("common")
end

-- 线程初始化函数
function thread_init()
   -- 设置变量
   set_vars()

   -- 检查数据库驱动和表引擎类型
   if (((db_driver == "mysql") or (db_driver == "attachsql")) and mysql_table_engine == "myisam") then
      local i
      local tables = {
   
   }
      -- 为每个表构建锁定语句
      for i=1, oltp_tables_count do
         tables[i] = string.format("sbtest%i WRITE", i)
      end
      -- 设置锁定和解锁查询
      begin_query = "LOCK TABLES " .. table.concat(tables, " ,")
      commit_query = "UNLOCK TABLES"
   else
      -- 默认使用事务的开始和提交语句
      begin_query = "BEGIN"
      commit_query = "COMMIT"
   end
end

-- 获取范围查询的条件字符串
function get_range_str()
   local start = sb_rand(1, oltp_table_size)
   return string.format(" WHERE id BETWEEN %u AND %u",
                        start, start + oltp_range_size - 1)
end

-- 定义事件函数
function event()
   local rs
   local i
   local table_name
   local c_val
   local pad_val
   local query

   -- 随机选择一个表
   table_name = "sbtest".. sb_rand_uniform(1, oltp_tables_count)
   -- 如果没有跳过事务，则开始事务
   if not oltp_skip_trx then
      db_query(begin_query)
   end

   -- 如果不是仅写操作
   if not oltp_write_only then
      -- 执行点查询
      for i=1, oltp_point_selects do
         rs = db_query("SELECT c FROM ".. table_name .." WHERE id=" .. sb_rand(1, oltp_table_size))
      end

      -- 如果需要执行范围查询
      if oltp_range_selects then
         -- 简单范围查询
         for i=1, oltp_simple_ranges do
            rs = db_query("SELECT c FROM ".. table_name .. get_range_str())
         end

         -- 范围求和查询
         for i=1, oltp_sum_ranges do
            rs = db_query("SELECT SUM(K) FROM ".. table_name .. get_range_str())
         end

         -- 范围排序查询
         for i=1, oltp_order_ranges do
            rs = db_query("SELECT c FROM ".. table_name .. get_range_str() .. " ORDER BY c")
         end

         -- 范围去重查询
         for i=1, oltp_distinct_ranges do
            rs = db_query("SELECT DISTINCT c FROM ".. table_name .. get_range_str() .. " ORDER BY c")
         end
      end
   end

   -- 如果不是只读操作
   if not oltp_read_only then
      -- 执行索引更新
      for i=1, oltp_index_updates do
         rs = db_query("UPDATE " .. table_name .. " SET k=k+1 WHERE id=" .. sb_rand(1, oltp_table_size))
      end

      -- 执行非索引更新
      for i=1, oltp_non_index_updates do
         c_val = sb_rand_str("###########-###########-###########-###########-###########-###########-###########-###########-###########-###########")
         query = "UPDATE " .. table_name .. " SET c='" .. c_val .. "' WHERE id=" .. sb_rand(1, oltp_table_size)
         rs = db_query(query)
         if rs then
            print(query)
         end
      end

      -- 执行删除和插入操作
      for i=1, oltp_delete_inserts do
         i = sb_rand(1, oltp_table_size)
         rs = db_query("DELETE FROM " .. table_name .. " WHERE id=" .. i)
         c_val = sb_rand_str("###########-###########-###########-###########-###########-###########-###########-###########-###########-###########")
         pad_val = sb_rand_str("###########-###########-###########-###########-###########")
         rs = db_query("INSERT INTO " .. table_name ..  " (id, k, c, pad) VALUES " .. string.format("(%d, %d, '%s', '%s')",i, sb_rand(1, oltp_table_size) , c_val, pad_val))
      end
   end

   -- 如果没有跳过事务，则提交事务
   if not oltp_skip_trx then
      db_query(commit_query)
   end
end

这段 oltp.lua 代码的主要步骤如下：

路径匹配与加载配置：
- 检查并获取脚本的路径。
- 如果路径存在，加载common.lua 文件；否则使用 require 函数加载模块。
线程初始化 (thread_init)：
- 初始化变量。
- 根据数据库驱动和表引擎类型，决定是否使用锁表操作。
- 如果数据库驱动是 mysql 或 attachsql 且表引擎为 myisam，则构建锁定和解锁查询语句。
- 否则，使用默认的事务控制语句（BEGIN 和 COMMIT）。
获取范围查询字符串 (get_range_str)：
- 随机生成一个起始ID。
- 返回一个用于范围查询的条件字符串，指定查询范围为从起始ID到起始ID加上范围大小减去1。
事件处理 (event)：
- 定义事件函数，该函数是 Sysbench 测试的核心部分
- 事件函数包括以下操作：
  - 随机选择一个表。
  - 如果没有跳过事务，则开始事务。
  - 根据配置执行不同类型的查询和更新操作，包括点查询、范围查询、索引更新、非索引更新、删除和插入操作。
  - 范围查询包括简单范围查询、求和范围查询、排序范围查询和去重范围查询。
  - 如果没有跳过事务，则提交事务。

这段代码是典型的OLTP（联机事务处理）负载测试脚本，通过模拟多种数据库操作（查询、更新、删除、插入），来评估数据库在高并发访问场景下的性能表现。

2、压测数据

sysbench
--test=/usr/local/share/sysbench/tests/include/oltp_legacy/oltp.lua
--db-driver=mysql --mysql-db=loadtest --mysql-user=root
--mysql-password= --mysql-port=3306 --mysql-host=--oltp-tables-count=64
--oltp-table-size=10000000 --max-time=3600 --max-requests=0
--num-threads=200 --report-interval=3 --forced-shutdown=1 run

脚本参数及其含义：

--test：指定要运行的测试脚本，这里选择的是一个OLTP（在线事务处理）负载测试脚本。oltp.lua是一个预定义的脚本，用于模拟常见的数据库操作。
--db-driver：指定数据库驱动程序，这里选择的是 MySQL。
--mysql-db：指定要测试的 MySQL 数据库名称，这里是 loadtest 数据库。
--mysql-user：指定用于连接 MySQL 数据库的用户名，这里是 root 用户。
mysql-password：指定用于连接 MySQL 数据库的密码，这里为空，意味着没有设置密码（不推荐在生产环境中使用空密码）。
--mysql-port：指定 MySQL 服务器监听的端口，这里是默认的 3306 端口。
--mysql-host：指定 MySQL 服务器的主机地址，这里为空，表示连接本地数据库。
--oltp-tables-count：指定用于测试的表的数量，这里是 64 个表。
--oltp-table-size：指定每个表中的行数，这里是 10,000,000 行。表示每个表有一千万条记录。
--max-time：指定测试的最大持续时间为3600秒（1小时）。
--max-requests：指定要执行的最大请求数。值为0表示请求数不受限制，直到达到最大时间。
--num-threads：指定测试时使用的线程数，这里是 200 个线程。表示并发 200 个线程进行测试。
--report-interval：指定报告中间结果的时间间隔（每3秒报告一次）。
--forced-shutdown：指定如果达到最大时间，Sysbench应该强制关闭测试（1表示启用）。
run：开始运行测试的命令。

简要说明就是并发200线程，压测1小时，每3秒打印一次结果等。

3、清理数据

测试完成后，可以运行以下脚本清理测试数据：

sysbench
--test=/usr/local/share/sysbench/tests/include/oltp_legacy/oltp.lua
--db-driver=mysql --mysql-db=loadtest --mysql-user=root
--mysql-password= --mysql-port=3306 --mysql-host= --oltp-tables-count=64 --oltp-table-size=10000000--max-time=3600 --max-requests=0 --num-threads=200 cleanup

脚本参数及其含义：

--test：指定要运行的测试脚本，这里选择的是一个OLTP（在线事务处理）负载测试脚本。oltp.lua是一个预定义的脚本，用于模拟常见的数据库操作。
--db-driver：指定数据库驱动程序，这里选择的是 MySQL。
--mysql-db：指定要测试的 MySQL 数据库名称，这里是 loadtest 数据库。
--mysql-user：指定用于连接 MySQL 数据库的用户名，这里是 root 用户。
mysql-password：指定用于连接 MySQL 数据库的密码，这里为空，意味着没有设置密码（不推荐在生产环境中使用空密码）。
--mysql-port：指定 MySQL 服务器监听的端口，这里是默认的 3306 端口。
--mysql-host：指定 MySQL 服务器的主机地址，这里为空，表示连接本地数据库。
--oltp-tables-count：指定用于测试的表的数量，这里是 64 个表。
--oltp-table-size：指定每个表中的行数，这里是 10,000,000 行。表示每个表有一千万条记录。
--max-time：指定测试的最大持续时间为3600秒（1小时）。
--max-requests：指定要执行的最大请求数。值为0表示请求数不受限制，直到达到最大时间。
--num-threads：指定测试时使用的线程数，这里是 200 个线程。表示并发 200 个线程进行测试。
cleanup：测试完成后对数据库进行清理。

五、结果解析

以下为压测过程中打印的结果：

[ 3522s ] thds: 200 tps: 153.98 qps: 3119.87 (r/w/o: 2155.68/656.24/307.95) lat (ms,95%): 1235.62 err/s: 0.00 reconn/s: 0.00
[ 3525s ] thds: 200 tps: 157.36 qps: 2992.89 (r/w/o: 1997.37/680.79/314.72) lat (ms,95%): 4358.09 err/s: 0.00 reconn/s: 0.00
[ 3528s ] thds: 200 tps: 85.33 qps: 1852.86 (r/w/o: 1400.23/281.98/170.65) lat (ms,95%): 1258.08 err/s: 0.00 reconn/s: 0.00

测试结束后，查看输出文件，如下所示：

FATAL: The --max-time limit has expired, forcing shutdown...
SQL statistics:
    queries performed:
        read:                            5358024
        write:                           1530377
        other:                           765297
        total:                           7653698
    transactions:                        382581 (106.24 per sec.)
    queries:                             7653698 (2125.42 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

Number of unfinished transactions on forced shutdown: 200

General statistics:
    total time:                          3601.0196s
    total number of events:              382581

Latency (ms):
         min:                                  4.72
         avg:                               1881.83
         max:                              10972.92
         95th percentile:                   4128.91
         sum:                            719951371.94

Threads fairness:
    events (avg/stddev):           1913.9050/24.88
    execution time (avg/stddev):   3599.7569/1.81

是不是有点晕，那我们稍微翻译下，如下所示：

FATAL: The --max-time limit has expired, forcing shutdown...
#SQL统计部分表明了总查询量以及每秒执行的查询和事务数量。这些数据有助于了解数据库的处理能力和性能表现。
SQL statistics（SQL统计信息）:
    queries performed（查询执行情况）:
        read（读查询）:                          5358024
        write（写查询）:                         1530377
        other（其它查询）:                        765297
        total（总查询）:                         7653698
    transactionss（事务）:                        382581 (106.24 per sec.) (每秒106.24次)
    queries（查询）:                             7653698 (2125.42 per sec.) (每秒2125.42次)
    ignored errors（忽略的错误）:                      0      (0.00 per sec.) (每秒0次)
    reconnect（重连）s:                          0      (0.00 per sec.) (每秒0次)

# 强制关闭时未完成的事务数量为200，表明在测试过程中有200个事务未能完成，这可能与测试环境或配置有关。
Number of unfinished transactions on forced shutdown: 200

General statistics（一般统计信息）:
    total time（总时间）:                          3601.0196s
    total number of events（事件总数）:              382581

#延迟数据展示了不同百分位的延迟情况，这些数据对分析数据库响应时间和性能瓶颈很有用。
Latency（延迟） (ms):
         min（最小延迟）:                                  4.72
         avg（平均延迟）:                               1881.83
         max（最大延迟）:                              10972.92
         95th percentile（95%分位延迟）:                4128.91
         sum（延迟总和）:                          719951371.94

#线程公平性数据表明，每个线程处理的事件数的平均值和标准差，以及每个线程的执行时间的平均值和标准差。
Threads fairness(线程公平性):
    events (avg/stddev)（事件（平均值/标准差））:           1913.9050/24.88
    execution time (avg/stddev)（执行时间（平均值/标准差））:   3599.7569/1.81

这些数据展示了MySQL在高并发负载下的性能情况，主要关注点包括：

查询和事务的执行率：每秒查询和事务数量表明了数据库的吞吐量。
延迟：延迟数据（平均、最大和95%分位）显示了数据库的响应时间和性能瓶颈。
未完成事务：强制关闭时未完成的事务数提示了潜在的事务处理问题。
线程公平性：线程间的负载均衡情况，标准差较低表示负载分配较为均衡。

主要关注的性能指标有：

TPS ：Transaction Per Second，数据库每秒执行的事务数，每个事务中包含18条SQL语句。
QPS ：Query Per Second，数据库每秒执行的SQL数，包含insert、select、update、delete等。
延迟：Latency，数据库执行的事务耗时。

Sysbench默认提交的事务中包含18条SQL语句，具体执行语句和条数如下：

主键SELECT语句，10条：
SELECT c FROM {rand_table_name} where id={rand_id};

范围SELECT语句，4条：
SELECT c FROM {rand_table_name} WHERE id BETWEEN {rand_id_start} AND ${rand_id_end};
SELECT SUM(K) FROM {rand_table_name} WHERE id BETWEEN {rand_id_start} AND ${rand_id_end};
SELECT c FROM {rand_table_name} WHERE id BETWEEN {rand_id_start} AND ${rand_id_end} ORDER BY c;
SELECT DISTINCT c FROM {rand_table_name} WHERE id BETWEEN {rand_id_start} AND ${rand_id_end} ORDER BY c;

UPDATE语句，2条：
UPDATE {rand_table_name} SET k=k+1 WHERE id={rand_id}
UPDATE {rand_table_name} SET c={rand_str} WHERE id=${rand_id}

DELETE语句，1条：
DELETE FROM {rand_table_name} WHERE id={rand_id}

INSERT语句，1条：
INSERT INTO {rand_table_name} (id, k, c, pad) VALUES ({rand_id},{rand_k},{rand_str_c},${rand_str_pad})

这些结果可以用于性能调优和瓶颈分析，从而提升 MySQL 数据库在实际应用中的表现。

从Sysbench测试结果来看，这台MySQL服务器在高并发负载下的性能表现有以下几个关键点：

事务处理能力：
- 每秒事务数（TPS）为106.24次。
- 总事务数为 382581。
查询处理能力：
- 每秒查询数（QPS）为 2125.42次。
- 总查询数为 7653698。
延迟：
- 平均延迟为 1881.83 毫秒，较高，说明在负载压力下，响应时间比较长。
- 最大延迟为 10972.92 毫秒，非常高，表明在高负载下可能存在严重的性能瓶颈。
- 95% 分位延迟为 4128.91 毫秒，表示大多数请求的响应时间在 4 秒以上，体验较差。
未完成事务：
- 强制关闭时未完成的事务数为 200，表明在高负载下有一部分事务未能及时处理完成。
线程公平性：
- 每个线程处理的事件数的标准差为 24.88，表明线程间的负载分配较为均衡。
- 每个线程的执行时间的标准差为 1.81，表明线程执行时间也较为一致。

这里我们可以对比下某云的测试结果：

数据服务器资源监控数据：

我们可以看到CPU峰值到75%左右，磁盘峰值写入速率达50MB/s，峰值读取速率达 100MB/s。

六、最后

我们可以看到从测试结果的结果来看，MySQL数据库的性能表现并不好，那么我们接下来应对MySQL数据库进行性能调优并再次验证，希望本文能对你的工作带来一点点帮助，如果有用别忘了点个赞，多谢。

性能工具之 MySQL OLTP Sysbench BenchMark 测试示例

一、前言

二、测试环境

1、服务器配置

2、测试拓扑

三、测试工具安装

四、测试步骤

1、导入数据

2、压测数据

3、清理数据

五、结果解析

六、最后

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

性能工具之 MySQL OLTP Sysbench BenchMark 测试示例

一、前言

二、测试环境

1、服务器配置

2、测试拓扑

三、测试工具安装

四、测试步骤

1、导入数据

2、压测数据

3、清理数据

五、结果解析

六、最后

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像