什么是sysbench?
sysbench是一款基于LuaJIT的多线程基准测试工具,它最常用于数据库基准测试,但也可用于创建不涉及数据库服务器的任意复杂工作负载。
主要功能包括:
oltp_*.lua
: OLTP类数据库压测工具集fileio
: 文件系统级基准测试cpu
: CPU基准测试memory
: 内存访问基准测试threads
:线程调度基准测试mutex
: POSIX mutex基准压测
sysbench支持的OLTP数据库,包括MySQL、PostgreSQL、Oracle等。
本文主要介绍使用 sysbench进行MySQL性能压测。
1.安装
以Linux下安装为例。
安装方式有两种:
- 直接使用二进制按转包
- 通过编译安装
1.1 直接使用二进制安装包
curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bash
sudo yum -y install sysbench
1.2 通过源码编译安装
安装依赖库
yum -y install make automake libtool pkgconfig libaio-devel
# For MySQL support, replace with mysql-devel on RHEL/CentOS 5
yum -y install mariadb-devel openssl-devel
# For PostgreSQL support
yum -y install postgresql-devel
下载源码
wget https://github.com/akopytov/sysbench/archive/1.0.zip -O "sysbench-1.0.zip"
unzip sysbench-1.0.zip
cd sysbench-1.0
编译 & 安装
./autogen.sh
# Add --with-pgsql to build with PostgreSQL support
./configure
make -j
make install
2.MySQL压测
对MySQL进行压测分三个步骤,分别是准备数据、执行测试、以及清理数据。
分别对应command
: prepare
、run
、cleanup
。
2.1 准备数据
sysbench ./tests/include/oltp_legacy/oltp.lua
--mysql-host=192.168.1.10
--mysql-port=3306
--mysql-user=xxx
--mysql-password=xxxx
--mysql-db=orange_test
--oltp-test-mode=complex
--oltp-tables-count=10
--oltp-table-size=100
--threads=10
--time=120
--report-interval=10
prepare
其中,
./tests/include/oltp_legacy/oltp.lua
表示使用指定脚本进行测试。--mysql-host
MySQL服务器--mysql-port
MySQL端口--mysql-use
MySQL用户--mysql-password
密码--mysql-db
数据库,需要提前创建--oltp-test-mode
oltp测试模式--oltp-tables-count
创建表的数量--oltp-table-size
创建表的大小--threads
并发线程数--time
执行时间--report-interval
每隔多长时间输出统计数据
对于测试模式,包括simple、complex和nontrx,默认是complex。
simple模式下只测试简单的查询;
complex模式下,会测试增删改查,而且会使用事务,一般测试会用这种模式。
nontrx不仅测试查询,还测试插入更新等,但是不使用事务。
执行以后,会在测试的MySQL实例上创建数据表,便于后续测试。
Creating table 'sbtest1'...
Inserting 100 records into 'sbtest1'
Creating secondary indexes on 'sbtest1'...
Creating table 'sbtest2'...
Inserting 100 records into 'sbtest2'
Creating secondary indexes on 'sbtest2'...
Creating table 'sbtest3'...
Inserting 100 records into 'sbtest3'
Creating secondary indexes on 'sbtest3'...
Creating table 'sbtest4'...
Inserting 100 records into 'sbtest4'
Creating secondary indexes on 'sbtest4'...
Creating table 'sbtest5'...
Inserting 100 records into 'sbtest5'
Creating secondary indexes on 'sbtest5'...
Creating table 'sbtest6'...
Inserting 100 records into 'sbtest6'
Creating secondary indexes on 'sbtest6'...
Creating table 'sbtest7'...
Inserting 100 records into 'sbtest7'
Creating secondary indexes on 'sbtest7'...
Creating table 'sbtest8'...
Inserting 100 records into 'sbtest8'
Creating secondary indexes on 'sbtest8'...
Creating table 'sbtest9'...
Inserting 100 records into 'sbtest9'
Creating secondary indexes on 'sbtest9'...
Creating table 'sbtest10'...
Inserting 100 records into 'sbtest10'
Creating secondary indexes on 'sbtest10'...
创建的数据表
mysql> show tables like 'sbtest%';
+--------------------------+
| Tables_in_test (sbtest%) |
+--------------------------+
| sbtest1 |
| sbtest10 |
| sbtest2 |
| sbtest3 |
| sbtest4 |
| sbtest5 |
| sbtest6 |
| sbtest7 |
| sbtest8 |
| sbtest9 |
+--------------------------+
10 rows in set (0.00 sec)
mysql> desc sbtest1;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| k | int(10) unsigned | NO | MUL | 0 | |
| c | char(120) | NO | | | |
| pad | char(60) | NO | | | |
+-------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
2.2 执行测试
将上面的prepare
改为run
sysbench ./tests/include/oltp_legacy/oltp.lua
--mysql-host=192.168.1.10
--mysql-port=3306
--mysql-user=xxx
--mysql-password=xxxx
--mysql-db=orange_test
--oltp-test-mode=complex
--oltp-tables-count=10
--oltp-table-size=100
--threads=10
--time=120
--report-interval=10
run
output:
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 10
Report intermediate results every 10 second(s)
Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 10s ] thds: 10 tps: 212.91 qps: 4464.35 (r/w/o: 3165.79/858.94/439.62) lat (ms,95%): 80.03 err/s: 12.79 reconn/s: 0.00
[ 20s ] thds: 10 tps: 231.65 qps: 4867.97 (r/w/o: 3454.56/935.21/478.21) lat (ms,95%): 68.05 err/s: 14.90 reconn/s: 0.00
[ 30s ] thds: 10 tps: 229.01 qps: 4801.72 (r/w/o: 3403.08/926.52/472.11) lat (ms,95%): 70.55 err/s: 14.10 reconn/s: 0.00
[ 40s ] thds: 10 tps: 198.39 qps: 4152.25 (r/w/o: 2942.82/800.65/408.78) lat (ms,95%): 87.56 err/s: 12.00 reconn/s: 0.00
[ 50s ] thds: 10 tps: 219.61 qps: 4626.00 (r/w/o: 3283.04/889.14/453.82) lat (ms,95%): 75.82 err/s: 14.80 reconn/s: 0.00
[ 60s ] thds: 10 tps: 186.50 qps: 3876.44 (r/w/o: 2741.53/752.51/382.40) lat (ms,95%): 82.96 err/s: 9.20 reconn/s: 0.00
[ 70s ] thds: 10 tps: 197.58 qps: 4137.82 (r/w/o: 2934.03/796.73/407.06) lat (ms,95%): 80.03 err/s: 12.00 reconn/s: 0.00
[ 80s ] thds: 10 tps: 260.02 qps: 5454.01 (r/w/o: 3868.19/1049.58/536.24) lat (ms,95%): 63.32 err/s: 16.10 reconn/s: 0.00
[ 90s ] thds: 10 tps: 266.20 qps: 5601.80 (r/w/o: 3975.90/1075.50/550.40) lat (ms,95%): 58.92 err/s: 18.10 reconn/s: 0.00
[ 100s ] thds: 10 tps: 253.98 qps: 5378.84 (r/w/o: 3824.08/1027.71/527.06) lat (ms,95%): 64.47 err/s: 19.10 reconn/s: 0.00
[ 110s ] thds: 10 tps: 264.12 qps: 5570.84 (r/w/o: 3956.44/1067.77/546.63) lat (ms,95%): 62.19 err/s: 18.30 reconn/s: 0.00
[ 120s ] thds: 10 tps: 268.91 qps: 5632.11 (r/w/o: 3991.85/1086.14/554.12) lat (ms,95%): 57.87 err/s: 16.30 reconn/s: 0.00
SQL statistics:
queries performed:
read: 415464
write: 112702
other: 57575
total: 585741
transactions: 27899 (232.45 per sec.)
queries: 585741 (4880.23 per sec.)
ignored errors: 1777 (14.81 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 120.0217s
total number of events: 27899
Latency (ms):
min: 14.74
avg: 43.00
max: 217.65
95th percentile: 70.55
sum: 1199772.82
Threads fairness:
events (avg/stddev): 2789.9000/11.12
execution time (avg/stddev): 119.9773/0.01
2.3 清理数据
使用cleanup清理数据:
sysbench ./tests/include/oltp_legacy/oltp.lua
--mysql-host=192.168.1.10
--mysql-port=3306
--mysql-user=xxx
--mysql-password=xxxx
--mysql-db=orange_test
--oltp-test-mode=complex
--oltp-tables-count=10
--oltp-table-size=100
--threads=10
--time=120
--report-interval=10
cleanup
output:
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)
Dropping table 'sbtest1'...
Dropping table 'sbtest2'...
Dropping table 'sbtest3'...
Dropping table 'sbtest4'...
Dropping table 'sbtest5'...
Dropping table 'sbtest6'...
Dropping table 'sbtest7'...
Dropping table 'sbtest8'...
Dropping table 'sbtest9'...
Dropping table 'sbtest10'...
关于sysbench的使用方法,可通过sysbench --help
或查看固网获取更多信息。
3.参考
Just try, don't shy.
【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· AI与.NET技术实操系列:基于图像分类模型对图像进行分类
· go语言实现终端里的倒计时
· 如何编写易于单元测试的代码
· 10年+ .NET Coder 心语,封装的思维:从隐藏、稳定开始理解其本质意义
· .NET Core 中如何实现缓存的预热?
· 分享一个免费、快速、无限量使用的满血 DeepSeek R1 模型,支持深度思考和联网搜索!
· 基于 Docker 搭建 FRP 内网穿透开源项目(很简单哒)
· 25岁的心里话
· ollama系列01:轻松3步本地部署deepseek,普通电脑可用
· 按钮权限的设计及实现