MySQL进阶13 — 基准测试

概述

本章,您将学习 MySQL 的基准测试工具,内容包括两部分:

  • 自带的基准测试工具 mysqlslap
  • 第三方基准测试工具 sysbench

在正式部署 MySQL 到生产环境之前,通常都需要执行基准测试,原因主要是:

  • 判断当前 MySQL 实例的性能表现能否符合生产环境的需要
  • 通过模拟生产环境下的工作负载来动态调整配置项中的内容,避免上线后盲目调整配置项参数
  • 模拟业务的未来增长,评估硬件需求(CPU、内存、存储等)

mysqlslap 命令行工具

该命令的用法为 mysqlslap [OPTIONS],常用选项如下:

  • --defaults-file=# - 指定 MySQL 实例的配置文件
  • --auto-generate-sql - 使用 MySQL 自带的 SQL 脚本进行测试
  • --auto-generate-sql-add-autoincrement - 将自增长列添加到自动生成的表中
  • --auto-generate-sql-execute-number=N - 指定要自动生成的查询数量
  • --auto-generate-sql-guid-primary - 将基于 GUID 的主键添加到自动生成的表中
  • --auto-generate-sql-load-type=NAME - 指定要测试的负载类型,默认值为 mixed(随机混合执行 SELECT、INSERT、UPDATE 和 DELETE 操作,模拟真实应用的综合负载),其他可选的值有:read(仅生成 SELECT 查询语句,用于测试数据库的读取性能与并发查询能力)、write(仅生成 INSERT、UPDATE 和 DELETE 语句,聚焦写入吞吐量与事务处理能力)、key(基于主键或唯一索引执行查询,评估索引查找效率)、update(仅执行 UPDATE 操作,专门测试更新语句的并发性能与锁竞争情况)
  • --auto-generate-sql-secondary-indexes=N - 往自动生成的表中要添加的辅助索引的数量。辅助索引也称二级索引或非聚集索引,指除主键索引与唯一索引外的其他索引
  • --auto-generate-sql-unique-query-number=N - 自动测试时,要生成多少不同的查询,默认值为 10。例如,主键列有 1000 个非空非 null 的值,则此处可以选择 1000
  • --auto-generate-sql-write-number=# - 要执行多少行的插入,默认值为 100
  • --concurrency=N - 要模拟的并行客户端数
  • --engine=ENGINE_NAME - 创建表时所使用的存储引擎
  • --no-drop - 默认情况下,每次执行 mysqlslap 会自动创建一个临时库(默认为 mysqlslap 库)并在测试完毕后删除该库。启用该选项后,mysqlslap 会保留测试时的数据库以及表
  • --iterations=N - 每个客户端执行的测试轮数
  • --number-of-queries=N - 每个客户端执行查询数的上限
  • --create-schema=VALUE - 用自带的库来代替默认的 mysqlslap 库
  • --number-char-cols=N - 当使用了 --auto-generate-sql 选项后,控制数据类型为 varchar 的列的数量
  • --number-int-cols=N - 当使用了 --auto-generate-sql 选项后,控制数据类型为 int 的列的数量
  • --query=VALUE - 自定义的 SQL 语句,VALUE 必须为合法的 SQL 语句(可包含多条,用分号隔开),支持 select 、delete、update、insert ,如 --query="use world;select * from city;"
  • --host=HOST_NAME - 连接到 MySQL 实例的主机
  • --user=USER - 连接时使用的用户名
  • --password=PASSWORD - 连接时对应的用户密码

使用例子

基本使用:

# "\" 表示命令行交互未结束
Shell > /usr/local/mysql8/bin/mysqlslap --host=localhost --user=root --password="MyNewPass4!" --concurrency=200 --iterations=3  \
--auto-generate-sql  --auto-generate-sql-load-type=mixed --auto-generate-sql-add-autoincrement --engine=innodb  \
--number-of-queries=400 

对特定库的特定表进行基准测试:

Shell > /usr/local/mysql8/bin/mysqlslap --host=localhost --user=root --password="MyNewPass4!" --concurrency=100  --iterations=3 \
--engine=innodb  --create-schema="world" --query="select * from city;" --number-of-queries=300

测试不同的存储引擎:

Shell > /usr/local/mysql8/bin/mysqlslap --host=localhost --user=root --password="MyNewPass4!" --concurrency=100 --iterations=3  \
--auto-generate-sql  --auto-generate-sql-load-type=mixed --auto-generate-sql-add-autoincrement --engine=innodb,myisam  \
--number-of-queries=500 

第三方基准测试工具 sysbench

使用官方的存储库来安装 sysbench 预编译二进制软件包:

Shell > curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bash

Shell > dnf -y install sysbench

sysbench 命令的用法为 sysbench [options]... [testname] [command]

常用系统选项如下:

  • --threads=N - 线程数,默认 1,即模拟多少个客户端并发连接
  • --events=N - 事件总数的上限,默认 0 表示不限制。此处的事件不是指 MySQL 当中的事件(事件调度器),而是指 sysbench 当中的操作次数。
  • --time=N - 总的执行时间限制,以秒为单位,默认 10
  • --thread-stack-size=SIZE - 每个线程的堆栈大小,默认 64K
  • --rate=N - 控制每秒事务的生成速率,默认 0 表示不限制
  • --report-interval=N - 以秒为单位指定时间间隔,定期报告中间统计信息,默认为 0
  • --db-drivers=STRING - 指定数据库的驱动程序,默认值为 mysql

常用随机数选项如下:

  • --rand-type=STRING - 随机数的类型,允许的值有 uniform、 gaussian、special(默认)、pareto
  • --rand-spec-iter=N - 控制随机数生成的迭代次数,默认 12
  • --rand-spec-pct=N - 被视为 "special" 的值的百分比,默认 1 , 需要 --rand-type=special
  • --rand-spec-res=N - 要使用的 "special" 的值的百分比,默认 75 ,需要 --rand-type=special
  • --rand-seed=N - 控制随机数生成器的种子值。默认 0 值,表示使用当前时间作为种子
  • --rand-pareto-h=N - 控制帕累托分布的形状参数,N 的范围为 [0,2] ,默认 0.2。

连接 MySQL 的相关选项:

  • --mysql-host=# - 要连接的主机名或 IP 地址,默认为 localhost
  • --mysql-port=# - 主机名或 IP 地址的端口,默认为 3306
  • --mysql-socket=[LIST,...] - 指定 MySQL 的套接字
  • --mysql-user=# - 要连接的 MySQL 用户名,默认为 sbtest
  • --mysql-password=# - 对应连接用户的密码,默认空值
  • --mysql-db=STRING - 数据库名称,默认 sbtest
  • --mysql-compression[=on|off] - 是否启用 MySQL 客户端与服务器之间的数据压缩功能

用法中的 testname 可以是:

  • fileio - 测试文件 I/O
  • cpu - CPU 性能测试
  • memory - 内存速度测试
  • threads - 线程子系统性能测试
  • mutex - 互斥锁性能测试
  • .lua - 绝对路径的 LUA 脚本文件

语法中的 command 可以是:

  • prepare - 准备阶段,为需要的测试做准备
  • run - 运行阶段
  • cleanup - 清除阶段,在一些临时创建数据的测试中,删除 run 后的临时数据
  • help - 显示内置测试项或 .lua 的具体用法

CPU 性能测试

Shell > sysbench --threads=20 cpu run
...
CPU speed:
    events per second:  2417.68

General statistics:
    total time:                          10.0041s
    total number of events:              24190

Latency (ms):
         min:                                    0.73
         avg:                                    8.22
         max:                                  287.24
         95th percentile:                       97.55
         sum:                               198853.25

Threads fairness:
    events (avg/stddev):           1209.5000/21.67
    execution time (avg/stddev):   9.9427/0.05

内存性能测试

Shell > sysbench memory help
sysbench 1.0.20 (using system LuaJIT 2.1.0-beta3)

memory options:
  --memory-block-size=SIZE    size of memory block for test [1K]
  --memory-total-size=SIZE    total size of data to transfer [100G]
  --memory-scope=STRING       memory access scope {global,local} [global]
  --memory-hugetlb[=on|off]   allocate memory from HugeTLB pool [off]
  --memory-oper=STRING        type of memory operations {read, write, none} [write]
  --memory-access-mode=STRING memory access mode {seq,rnd} [seq]

Shell > sysbench --threads=20 memory --memory-block-size=1K --memory-total-size=100G  --memory-scope="global" \
--memory-oper="write" --memory-access-mode=seq  run
...
Total operations: 76035834 (7602575.63 per second)

74253.74 MiB transferred (7424.39 MiB/sec)

General statistics:
    total time:                          10.0002s
    total number of events:              76035834

Latency (ms):
         min:                                    0.00
         avg:                                    0.00
         max:                                  258.91
         95th percentile:                        0.00
         sum:                               120003.36

Threads fairness:
    events (avg/stddev):           3801791.7000/53735.37
    execution time (avg/stddev):   6.0002/0.46

磁盘 IO 测试

首先准备数据:

Shell > sysbench fileio help
fileio options:
  --file-num=N                  number of files to create [128]
  --file-block-size=N           block size to use in all IO operations [16384]
  --file-total-size=SIZE        total size of files to create [2G]
  --file-test-mode=STRING       test mode {seqwr, seqrewr, seqrd, rndrd, rndwr, rndrw}
  --file-io-mode=STRING         file operations mode {sync,async,mmap} [sync]
  --file-async-backlog=N        number of asynchronous operatons to queue per thread [128]
  --file-extra-flags=[LIST,...] list of additional flags to use to open files {sync,dsync,direct} []
  --file-fsync-freq=N           do fsync() after this number of requests (0 - don't use fsync()) [100]
  --file-fsync-all[=on|off]     do fsync() after each write operation [off]
  --file-fsync-end[=on|off]     do fsync() at the end of test [on]
  --file-fsync-mode=STRING      which method to use for synchronization {fsync, fdatasync} [fsync]
  --file-merged-requests=N      merge at most this number of IO requests if possible (0 - don't merge) [0]
  --file-rw-ratio=N             reads/writes ratio for combined test [1.5]

# 测试的模式为 "随机读写(rndrw)",文件操作模式为 "异步(async)"
Shell > sysbench --threads=4 fileio --file-num=128 --file-total-size=2G --file-test-mode="rndrw" --file-io-mode="async" prepare
...
128 files, 16384Kb each, 2048Mb total
Creating files for the test...
Extra file open flags: (none)
Creating file test_file.0
Shell > sysbench --thread
Creating file test_file.1
Creating file test_file.2
...
Creating file test_file.126
Creating file test_file.127
2147483648 bytes written in 13.28 seconds (154.20 MiB/sec).

# 开始测试
Shell > sysbench --threads=4 fileio --file-num=128 --file-total-size=2G --file-test-mode="rndrw" --file-io-mode="async" run
...
Extra file open flags: (none)
128 files, 16MiB each
2GiB total file size
Block size 16KiB
Number of IO requests: 0
Read/Write ratio for combined random IO test: 1.50
Periodic FSYNC enabled, calling fsync() each 100 requests.
Calling fsync() at the end of test, Enabled.
Using asynchronous I/O mode
Doing random r/w test
Initializing worker threads...

Threads started!

File operations:  ← 文件操作
    reads/s:                      29090.67
    writes/s:                     19398.44
    fsyncs/s:                     35186.72

Throughput:  ← 吞吐量
    read, MiB/s:                  454.54
    written, MiB/s:               303.10

General statistics:
    total time:                          10.0047s  ← 总花费时间
    total number of events:              836952  ← 完成总的事件数

Latency (ms):
         min:                                    0.00
         avg:                                    0.05
         max:                                   66.24
         95th percentile:                        0.24
         sum:                                39629.72

Threads fairness:
    events (avg/stddev):           209238.0000/2118.73
    execution time (avg/stddev):   9.9074/0.00

# 清理运行后的临时数据
Shell > sysbench --threads=4 fileio --file-num=128 --file-total-size=2G --file-test-mode="rndrw" --file-io-mode="async" run cleanup
Removing test files...

LUA 脚本文件测试

除了可以使用官方自带的测试项外,您还可以使用官方自带的 LUA 脚本文件进行测试,每一个 LUA 脚本文件的用法类似这样:

Shell > sysbench /usr/share/sysbench/oltp_common.lua help

使用下面的命令可以查看到所有的 lua 脚本文件:

# 其中,oltp_legacy 目录下是传统的 LUA 脚本文件
Shell > rpm -ql sysbench | grep -i "\.lua$"
/usr/share/sysbench/bulk_insert.lua
/usr/share/sysbench/oltp_common.lua
/usr/share/sysbench/oltp_delete.lua
/usr/share/sysbench/oltp_insert.lua
/usr/share/sysbench/oltp_point_select.lua
/usr/share/sysbench/oltp_read_only.lua
/usr/share/sysbench/oltp_read_write.lua
/usr/share/sysbench/oltp_update_index.lua
/usr/share/sysbench/oltp_update_non_index.lua
/usr/share/sysbench/oltp_write_only.lua
/usr/share/sysbench/select_random_points.lua
/usr/share/sysbench/select_random_ranges.lua
/usr/share/sysbench/tests/include/inspect.lua
/usr/share/sysbench/tests/include/oltp_legacy/bulk_insert.lua
/usr/share/sysbench/tests/include/oltp_legacy/common.lua
/usr/share/sysbench/tests/include/oltp_legacy/delete.lua
/usr/share/sysbench/tests/include/oltp_legacy/insert.lua
/usr/share/sysbench/tests/include/oltp_legacy/oltp.lua
/usr/share/sysbench/tests/include/oltp_legacy/oltp_simple.lua
/usr/share/sysbench/tests/include/oltp_legacy/parallel_prepare.lua
/usr/share/sysbench/tests/include/oltp_legacy/select.lua
/usr/share/sysbench/tests/include/oltp_legacy/select_random_points.lua
/usr/share/sysbench/tests/include/oltp_legacy/select_random_ranges.lua
/usr/share/sysbench/tests/include/oltp_legacy/update_index.lua
/usr/share/sysbench/tests/include/oltp_legacy/update_non_index.lua

以下演示的是 LUA 脚本文件的基准测试:

# 创建对应的库(免 MySQL 交互)
Shell > /usr/local/mysql8/bin/mysql -h localhost -P 3306 --user=root --password="MyNewPass4!" -e "create database sysbenchtest;"

Shell > /usr/local/mysql8/bin/mysql -h localhost -P 3306 --user=root --password="MyNewPass4!" -e "show databases;"
+--------------------+
| Database           |
+--------------------+
| home               |
| information_schema |
| locks              |
| mysql              |
| performance_schema |
| sys                |
| sysbenchtest       |
| world              |
+--------------------+

Shell > sysbench /usr/share/sysbench/oltp_common.lua help

oltp_common.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 queries per transaction [1]
  --mysql_storage_engine=STRING Storage engine, if MySQL is used [innodb]
  --non_index_updates=N         Number of UPDATE non-index queries per transaction [1]
  --order_ranges=N              Number of SELECT ORDER BY queries per transaction [1]
  --pgsql_variant=STRING        Use this PostgreSQL variant when running with the PostgreSQL driver. The only currently supported variant is 'redshift'. When enabled, create_secondary is automatically disabled, and delete_inserts is set to 0
  --point_selects=N             Number of point SELECT queries per transaction [10]
  --range_selects[=on|off]      Enable/disable all range SELECT queries [on]
  --range_size=N                Range size for range SELECT queries [100]
  --secondary[=on|off]          Use a secondary index in place of the PRIMARY KEY [off]
  --simple_ranges=N             Number of simple range SELECT queries per transaction [1]
  --skip_trx[=on|off]           Don't start explicit transactions and execute all queries in the AUTOCOMMIT mode [off]
  --sum_ranges=N                Number of SELECT SUM() queries per transaction [1]
  --table_size=N                Number of rows per table [10000]
  --tables=N                    Number of tables [1]

# 数据准备阶段
Shell > sysbench --threads=30 --report-interval=5 \
--mysql-host=localhost --mysql-port=3306 --mysql-socket="/tmp/mysql.sock" --mysql-user=root --mysql-password="MyNewPass4!" --mysql-db=sysbenchtest --mysql-socket=/tmp/mysql.sock \
/usr/share/sysbench/oltp_common.lua --auto_inc=on --create_secondary=on --mysql_storage_engine="innodb" --table_size=20000  --tables=10  prepare
...
Creating table 'sbtest4'...
Creating table 'sbtest1'...
Inserting 20000 records into 'sbtest6'
Inserting 20000 records into 'sbtest7'
Inserting 20000 records into 'sbtest1'
Inserting 20000 records into 'sbtest10'
Inserting 20000 records into 'sbtest3'
Inserting 20000 records into 'sbtest5'
Inserting 20000 records into 'sbtest2'
Inserting 20000 records into 'sbtest4'
Inserting 20000 records into 'sbtest8'
Inserting 20000 records into 'sbtest9'
Creating a secondary index on 'sbtest7'...
Creating a secondary index on 'sbtest1'...
Creating a secondary index on 'sbtest6'...
Creating a secondary index on 'sbtest2'...
Creating a secondary index on 'sbtest3'...
Creating a secondary index on 'sbtest10'...
Creating a secondary index on 'sbtest9'...
Creating a secondary index on 'sbtest8'...
Creating a secondary index on 'sbtest4'...
Creating a secondary index on 'sbtest5'...

# 运行基准测试,请注意这里选择的是 oltp_read_write.lua 文件
Shell > sysbench --threads=30 --report-interval=5 \
--mysql-host=localhost --mysql-port=3306 --mysql-socket="/tmp/mysql.sock" --mysql-user=root --mysql-password="MyNewPass4!" --mysql-db=sysbenchtest \
/usr/share/sysbench/oltp_read_write.lua --auto_inc=on --create_secondary=on --mysql_storage_engine="innodb" --table_size=20000  --tables=10  run
...
[ 5s ] thds: 30 tps: 796.89 qps: 16025.62 (r/w/o: 11231.87/3194.17/1599.58) lat (ms,95%): 73.13 err/s: 0.00 reconn/s: 0.00
[ 10s ] thds: 30 tps: 830.84 qps: 16607.80 (r/w/o: 11625.96/3320.16/1661.68) lat (ms,95%): 65.65 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            114380
        write:                           32680
        other:                           16340
        total:                           163400
    transactions:                        8170   (813.73 per sec.)
    queries:                             163400 (16274.51 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          10.0389s
    total number of events:              8170

Latency (ms):
         min:                                    3.19
         avg:                                   36.78
         max:                                  181.40
         95th percentile:                       68.05
         sum:                               300528.21

Threads fairness:
    events (avg/stddev):           272.3333/7.66
    execution time (avg/stddev):   10.0176/0.01

[ 5s ] thds: 30 tps: 796.89 qps: 16025.62 (r/w/o: 11231.87/3194.17/1599.58) lat (ms,95%): 73.13 err/s: 0.00 reconn/s: 0.00 这部分的输出说明:

  • thds: 30 - 即模拟30个客户端同时访问
  • tps: 796.89 - tps(transactions per second)即每秒的事务数
  • qps: 16025.62 (r/w/o: 11231.87/3194.17/1599.58) - qps(queries per second)即每秒的查询数。需要注意的是,qps 并不仅仅包括 SELECT 语句,也包括 INSERT、UPDATE、DELETE 以及事务控制语句(如 BEGIN、COMMIT)等,它是一个反映吞吐量的综合指标。r/w/o: 11231.87/3194.17/1599.58 可知,r w o 的比值约为 7:2:1,符合典型 OLTP 场景
  • lat (ms,95%): 73.13 - 95% 的请求延迟低于该值,是衡量响应稳定性的关键指标。从 73.13ms 降至 65.65ms,说明系统负载均衡能力良好
  • err/s: 0.00 reconn/s: 0.00 - err/s 和 reconn/s 均为 0 ,表示无 SQL 错误或连接断开重连
SQL statistics:
    queries performed:
        read:                            114380
        write:                           32680
        other:                           16340
        total:                           163400
    transactions:                        8170   (813.73 per sec.)
    queries:                             163400 (16274.51 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

这部分的输出是一个总体的统计信息:

  • 114380 - 总的 select 语句数
  • 32680 - 总的 delete、update、insert 语句数
  • 16340 - 总的其他语句的数量
  • 8170 - 表示 10 秒内完成了 8170 个事务(未指定 --time=N 选项,默认的 N 就是 10 )
  • 163400 - 表示总的 SQL 语句数(别被 queries 单词迷惑),163400/8170 约等于20,即每个事务平均包含 20 条 SQL 语句
General statistics:
    total time:                          10.0389s
    total number of events:              8170
  • 10.0389s - 表示总的执行时间
  • 8170 - 总的事务数
提示
在 sysbench 的 OLTP 脚本中,sysbench 将每个完整事务的执行视为一个 "事件"(event)。所以输出中的 "transactions: 8170" 和 "total number of events: 8170" 是同一个含义。sysbench 命令选项当中的 --event=N 指的事件总数的上限。
Latency (ms):
         min:                                    3.19
         avg:                                   36.78
         max:                                  181.40
         95th percentile:                       68.05
         sum:                               300528.21

这部分是延迟(单个请求从发出到完成所经历的时间)的输出信息,分别表示最小延迟、平均延迟、最大延迟、95% 请求的延迟、总延迟。

Threads fairness:
    events (avg/stddev):           272.3333/7.66
    execution time (avg/stddev):   10.0176/0.01

这部分表示线程的负载公平性

  • events (avg/stddev): 272.3333/7.66 - 表示每个线程平均执行 272 个事务,标准差为 7.66(表明线程的负载分配很均匀)
  • execution time (avg/stddev): 10.0176/0.01 - 所有线程总执行时间的均值为 10.0176 秒,标准差为 0.01(表明无明显线程阻塞或资源争抢,调度效率高)
Avatar photo

关于 陸風睿

GNU/Linux 从业者、开源爱好者、技术钻研者,撰写文档既是兴趣也是工作内容之一。Q - "281957576";WeChat - "jiulongxiaotianci",Github - https://github.com/jimcat8
用一杯咖啡支持我们,我们的每一篇[文档]都经过实际操作和精心打磨,而不是简单地从网上复制粘贴。期间投入了大量心血,只为能够真正帮助到您。
暂无评论

发送评论 编辑评论


				
|´・ω・)ノ
ヾ(≧∇≦*)ゝ
(☆ω☆)
(╯‵□′)╯︵┴─┴
 ̄﹃ ̄
(/ω\)
∠( ᐛ 」∠)_
(๑•̀ㅁ•́ฅ)
→_→
୧(๑•̀⌄•́๑)૭
٩(ˊᗜˋ*)و
(ノ°ο°)ノ
(´இ皿இ`)
⌇●﹏●⌇
(ฅ´ω`ฅ)
(╯°A°)╯︵○○○
φ( ̄∇ ̄o)
ヾ(´・ ・`。)ノ"
( ง ᵒ̌皿ᵒ̌)ง⁼³₌₃
(ó﹏ò。)
Σ(っ °Д °;)っ
( ,,´・ω・)ノ"(´っω・`。)
╮(╯▽╰)╭
o(*////▽////*)q
>﹏<
( ๑´•ω•) "(ㆆᴗㆆ)
😂
😀
😅
😊
🙂
🙃
😌
😍
😘
😜
😝
😏
😒
🙄
😳
😡
😔
😫
😱
😭
💩
👻
🙌
🖕
👍
👫
👬
👭
🌚
🌝
🙈
💊
😶
🙏
🍦
🍉
😣
Source: github.com/k4yt3x/flowerhd
颜文字
Emoji
小恐龙
花!
上一篇