sysbench压力测试
1、sysbench介绍
sysbench 是一个模块化、支持多平台安装运行，多线程的基准测试工具，sysbench,不仅用来测试数据库的性能，也可以测试运行数据库的服务器的性能。
oltp 测试主要是结合了 lua 脚本，不需要修改源码，通过自定义 lua 脚本就可以实现不同业务类型的测试。
主要有以下几种方式的测试：
cpu 基准测试
磁盘 I/O 基准测试
内存连续读写性能测试
测试互斥锁性能
测试顺序读写性能
数据库性能 OLTP 基准测试
什么是 OLTP？
On-Line Transaction Processing 联机事务处理过程(OLTP)
也称为面向交易的处理过程，其基本特征是前台接收的用户数据可以立即传送到计算中心进行处理，并在很短的时间内给出处理结果，是对用户操作快速响应的方式之一。2、安装
# git clone https://github.com/akopytov/sysbench.git -- 通过git clone得到源码
# cd sysbench
# yum install automake -- 安装aclocal工具
# yum install openssl libtool -- 安装动态库文件
# aclocal
# autoconf
# ./configure --with-mysql-includes=/usr/local/mysql/include/ --with-mysql-libs=/usr/local/mysql/lib/
-- 关联mysql的头文件和库。也可以指定目录：--prefix=/usr/local/sysbench/
可通过# ./configure --help查看选项
# make
# make install
# sysbench --version
sysbench: error while loading shared libraries: libmysqlclient.so.20: cannot open shared object file: No such file or directory
解决办法：# ln -s /usr/local/mysql/lib/libmysqlclient.so.20 /usr/lib64
或者执行以下命令：
# echo "export LD_LIBRARY_PATH=/usr/local/mysql/lib/:$LD_LIBRARY_PATH" >> /etc/profile
# source /etc/profile
# sysbench --version
sysbench 1.1.0-faaff4f
简单安装方法：# yum install sysbench
# sysbench --help -- 查看帮助文档3、参数介绍
# sysbench --help
Usage:
sysbench [options]... [testname] [command]
Commands implemented by most tests: prepare run cleanup help
sysbench 压测需要 3 个步骤：
prepare(准备数据) -> run(运行测试) -> cleanup(清理数据)
General options:
--threads=N #创建测试线程的数量，默认值为 1
--events=N #限制事件的总数量，0 表示不限制，默认值为 0
--time=N #限制总共执行多长时间，单位是秒，默认是 10
--forced-shutdown=STRING #超过--time 后，等待多长时间强制关闭，单位是秒，默认 off
--thread-stack-size=SIZE #每个线程的堆大小，默认是 64k
--rate=N #平均事务率，0 表示不限制
--report-interval=N #定期报告统计数据的时间间隔，单位秒，默认为 0，表示不显示中间报告。
--report-checkpoints=[LIST,...] #转储完整的统计信息并在指定的时间点重置所有计数器。默认为关闭
--config-file=FILENAME #可以把命令参数写到一个文件中，指定这个文件
--tx-rate=N #该参数弃用，改为 --rate [0]
--max-requests=N #该参数弃用，改为--events [0]
--max-time=N #该参数弃用，改为 --time [0]
--num-threads=N #该参数弃用，改为 --threads [1]
mysql options:
--mysql-host=[LIST,...] # MySQL 服务器地址 ，默认 localhost
--mysql-port=[LIST,...] # MySQL 服务器端口 ，默认 3306
--mysql-socket=[LIST,...] # MySQL socket 文件
--mysql-user=STRING # MySQL user 默认 sbtest
--mysql-password=STRING # MySQL password 默认为空
--mysql-db=STRING # MySQL database name 默认 sbtest
--mysql-compression[=on|off] #是否使用压缩，默认为 off
Compiled-in tests:
fileio - File I/O test
cpu - CPU performance test
memory - Memory functions speed test
threads - Threads subsystem performance test
mutex - Mutex performance test4、使用说明
sysbench 压测 MySQL 有三个步骤：prepare --> run --> cleanup5、磁盘IO测试
5.1、说明：
# sysbench fileio help
sysbench --test=fileio \ # File IO测试。改为fileio
--file-num=4 \ # 测试文件数是4个
--file-block-size=8K \ # block size是8K
--file-total-size=1G \ # 4个文件的总大小是1G
--file-test-mode=rndrd \ # 测试方法是随机读
--file-extra-flags=direct \ # direct io，跳过缓存
--max-requests=0 \ # 一共发起多少请求，0表示任意。改为--events=0
--max-time=3600 \ # 测试3600s。改为--time=30
--num-threads=4 \ # 使用4个线程。改为--threads=4
--report-interval=3 \ # 每3秒产生报告
prepare # run or cleanup # prepare：生成文件、run：开始测试、cleanup：删除测试文件
其他说明 sysbench --test=fileio help
--file-num=N # 创建文件数
--file-block-size=N # block size大小
--file-total-size=SIZE # 文件数的大小总和
--file-test-mode=STRING # 测试模式 {seqwr, seqrewr, seqrd, rndrd, rndwr, rndrw} （顺序写，顺序读写，顺序读，随机读，随机写，随机读写）
--file-io-mode=STRING # 文件操作方式 {sync,async,mmap}
--file-extra-flags=STRING # 打开文件的额外标志 {sync,dsync,direct} []
--file-fsync-freq=N # 多少请求后执行fsync。默认是0，不执行
--file-fsync-all=[on|off] # 是否每次操作后都执行fsync
--file-fsync-end=[on|off] # 测完成后执行fsync，默认是on
--file-fsync-mode=STRING # 同步的方法 {fsync, fdatasync}默认是 [fsync]
--file-merged-requests=N # 最多多少IO请求被合并，默认为0，不合并
--file-rw-ratio=N # 读写比例默认是 [1.5]，即 3:2 5.2、生成测试文件： # sysbench fileio --file-num=4 --file-block-size=8K --file-total-size=1G --file-test-mode=rndrd --file-extra-flags=direct --events=0 --time=30 --threads=2 --report-interval=3 prepare
sysbench 1.1.0-faaff4f (using bundled LuaJIT 2.1.0-beta3)
4 files, 262144Kb each, 1024Mb total
Creating files for the test...
Extra file open flags: directio
Creating file test_file.0
Creating file test_file.1
Creating file test_file.2
Creating file test_file.3
1073741824 bytes written in 12.24 seconds (83.69 MiB/sec).5.3、开始测试： # sysbench fileio --file-num=4 --file-block-size=8K --file-total-size=1G --file-test-mode=rndrd --file-extra-flags=direct --events=0 --time=30 --threads=2 --report-interval=3 run
sysbench 1.1.0-faaff4f (using bundled LuaJIT 2.1.0-beta3)
Running the test with following options:
Number of threads: 2
Report intermediate results every 3 second(s)
Initializing random number generator from current time
Extra file open flags: directio
4 files, 256MiB each
1GiB total file size
Block size 8KiB
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 synchronous I/O mode
Doing random read test
Initializing worker threads...
Threads started!
[ 3s ] reads: 117.04 MiB/s writes: 0.00 MiB/s fsyncs: 0.00/s latency (ms,95%): 0.152
[ 6s ] reads: 117.12 MiB/s writes: 0.00 MiB/s fsyncs: 0.00/s latency (ms,95%): 0.152
[ 9s ] reads: 124.68 MiB/s writes: 0.00 MiB/s fsyncs: 0.00/s latency (ms,95%): 0.182
[ 12s ] reads: 111.04 MiB/s writes: 0.00 MiB/s fsyncs: 0.00/s latency (ms,95%): 0.169
[ 15s ] reads: 107.86 MiB/s writes: 0.00 MiB/s fsyncs: 0.00/s latency (ms,95%): 0.179
[ 18s ] reads: 112.23 MiB/s writes: 0.00 MiB/s fsyncs: 0.00/s latency (ms,95%): 0.163
[ 21s ] reads: 116.94 MiB/s writes: 0.00 MiB/s fsyncs: 0.00/s latency (ms,95%): 0.152
[ 24s ] reads: 115.26 MiB/s writes: 0.00 MiB/s fsyncs: 0.00/s latency (ms,95%): 0.155
[ 27s ] reads: 116.86 MiB/s writes: 0.00 MiB/s fsyncs: 0.00/s latency (ms,95%): 0.152
Throughput:
read: IOPS=14799.28 115.62 MiB/s (121.24 MB/s)
write: IOPS=0.00 0.00 MiB/s (0.00 MB/s)
fsync: IOPS=0.00 #每秒从内存向磁盘同步的次数
# IOPS = reads/s + writes/s，(121.24MB/s * 1024 /8KB =15518.72)换算得到IOPS的值，和14799差不多。
Latency (ms):
min: 0.06
avg: 0.13
max: 3.32
95th percentile: 0.16
sum: 59101.325.4、测试完删除数据文件： # sysbench fileio --file-num=4 --file-block-size=8K --file-total-size=1G --file-test-mode=rndrd --file-extra-flags=direct --events=0 --time=30 --threads=2 --report-interval=3 cleanup
sysbench 1.1.0-faaff4f (using bundled LuaJIT 2.1.0-beta3)
Removing test files... 注：如果是真实的测试 max-time设置成一周的时间，run期间可以使用iotop或者iostat进行观察。6、MySQL压测
使用sysbench自带的lua脚本(oltp_read_write.lua)来压测 MySQL
6.1、准备阶段： # mysql -uroot -p123 -e "create database sbtest;" -- 创建测试数据库
# sysbench /usr/local/share/sysbench/oltp_read_write.lua --mysql-host=192.168.1.112 --mysql-port=3306 --mysql-user=root --mysql-password='123' --mysql-socket=/tmp/mysql.sock --mysql-db=sbtest --db-driver=mysql --tables=10 --table-size=5000 --threads=4 prepare
sysbench 1.1.0-faaff4f (using bundled LuaJIT 2.1.0-beta3)
Initializing worker threads...
Creating table 'sbtest2'...
Creating table 'sbtest3'...
Creating table 'sbtest1'...
Creating table 'sbtest4'...
Inserting 5000 records into 'sbtest2'
Inserting 5000 records into 'sbtest3'
Inserting 5000 records into 'sbtest1'
Inserting 5000 records into 'sbtest4'
Creating a secondary index on 'sbtest2'...
Creating a secondary index on 'sbtest4'...
Creating a secondary index on 'sbtest3'...
Creating table 'sbtest6'...
Creating table 'sbtest7'...
Creating a secondary index on 'sbtest1'...
Inserting 5000 records into 'sbtest6'
Inserting 5000 records into 'sbtest7'
Creating table 'sbtest8'...
Inserting 5000 records into 'sbtest8'
Creating table 'sbtest5'...
Inserting 5000 records into 'sbtest5'
Creating a secondary index on 'sbtest6'...
Creating a secondary index on 'sbtest8'...
Creating a secondary index on 'sbtest7'...
Creating table 'sbtest10'...
Inserting 5000 records into 'sbtest10'
Creating a secondary index on 'sbtest5'...
Creating table 'sbtest9'...
Inserting 5000 records into 'sbtest9'
Creating a secondary index on 'sbtest10'...
Creating a secondary index on 'sbtest9'...6.2、测试阶段： # sysbench /usr/local/share/sysbench/oltp_read_write.lua --mysql-host=192.168.1.112 --mysql-port=3306 --mysql-user=root --mysql-password='123' --mysql-socket=/tmp/mysql.sock --mysql-db=sbtest --db-driver=mysql --tables=5 --table-size=1000 --threads=4 --report-interval=3 --time=30 run
sysbench 1.1.0-faaff4f (using bundled LuaJIT 2.1.0-beta3)
Running the test with following options:
Number of threads: 4
Report intermediate results every 3 second(s)
Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 3s ] thds: 4 tps: 524.67 qps: 10511.78 (r/w/o: 7360.75/2100.36/1050.68) lat (ms,95%): 10.27 err/s: 0.00 reconn/s: 0.00
[ 6s ] thds: 4 tps: 610.31 qps: 12208.23 (r/w/o: 8547.03/2440.25/1220.96) lat (ms,95%): 10.09 err/s: 0.33 reconn/s: 0.00
[ 9s ] thds: 4 tps: 600.71 qps: 12024.22 (r/w/o: 8417.96/2404.51/1201.76) lat (ms,95%): 10.27 err/s: 0.33 reconn/s: 0.00
[ 12s ] thds: 4 tps: 623.34 qps: 12462.71 (r/w/o: 8723.37/2492.68/1246.67) lat (ms,95%): 9.73 err/s: 0.33 reconn/s: 0.00
[ 15s ] thds: 4 tps: 471.57 qps: 9435.36 (r/w/o: 6605.95/1885.94/943.47) lat (ms,95%): 10.46 err/s: 0.00 reconn/s: 0.00
[ 18s ] thds: 4 tps: 621.05 qps: 12421.08 (r/w/o: 8694.76/2484.22/1242.11) lat (ms,95%): 9.91 err/s: 0.00 reconn/s: 0.00
[ 21s ] thds: 4 tps: 617.71 qps: 12362.78 (r/w/o: 8654.88/2471.82/1236.08) lat (ms,95%): 9.91 err/s: 0.67 reconn/s: 0.00
[ 24s ] thds: 4 tps: 610.96 qps: 12240.52 (r/w/o: 8571.10/2446.84/1222.59) lat (ms,95%): 9.91 err/s: 0.67 reconn/s: 0.00
[ 27s ] thds: 4 tps: 601.74 qps: 12049.81 (r/w/o: 8435.03/2410.29/1204.48) lat (ms,95%): 10.09 err/s: 1.00 reconn/s: 0.00
[ 30s ] thds: 4 tps: 611.68 qps: 12232.64 (r/w/o: 8563.22/2446.06/1223.36) lat (ms,95%): 10.09 err/s: 0.33 reconn/s: 0.00
SQL statistics:
queries performed:
read: 247744 #读总数
write: 70756 #写总数(update、insert、delete 语句数量)
other: 35381 #其他操作总数(SELECT、INSERT、UPDATE、DELETE之外的操作，例如COMMIT等)
total: 353881 #全部总数
transactions: 17685 (589.40 per sec.) #TPS，总事务数(每秒事务数)
queries: 353881 (11794.07 per sec.) #QPS，读写总数(每秒读写次数)
ignored errors: 11 (0.37 per sec.) #忽略的错误数
reconnects: 0 (0.00 per sec.)
Throughput:
events/s (eps): 589.4021
time elapsed: 30.0050s #--time 指定的压测时间
total number of events: 17685 #总发生事件数，一般与 transactions 相同
Latency (ms):
min: 2.81 #最小耗时
avg: 6.78 #平均耗时
max: 696.81 #最大耗时
95th percentile: 10.09 # 95%平均耗时
sum: 119949.42 #总响应时间
Threads fairness:
events (avg/stddev): 4421.2500/52.64
execution time (avg/stddev): 29.9874/0.006.3、清除数据： # sysbench /usr/local/share/sysbench/oltp_read_write.lua --mysql-host=192.168.1.112 --mysql-port=3306 --mysql-user=root --mysql-password='123' --mysql-socket=/tmp/mysql.sock --mysql-db=sbtest --db-driver=mysql --tables=10 --table-size=5000 --threads=4 cleanup
sysbench 1.1.0-faaff4f (using bundled LuaJIT 2.1.0-beta3)
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'...7、对CPU压测
# sysbench cpu help
sysbench 1.1.0-faaff4f (using bundled LuaJIT 2.1.0-beta3)
cpu options:
--cpu-max-prime=N upper limit for primes generator [10000] # 产生最大素数限制，默认为[10000]
例如：
# sysbench cpu --time=30 --threads=4 run
sysbench 1.1.0-faaff4f (using bundled LuaJIT 2.1.0-beta3)
Running the test with following options:
Number of threads: 4
Initializing random number generator from current time
Prime numbers limit: 10000
Initializing worker threads...
Threads started!
CPU speed:
events per second: 2277.93
Throughput:
events/s (eps): 2277.9316
time elapsed: 30.0009s # --time 执行测试的时间
total number of events: 68340 #总共创建的 events 数量
Latency (ms):
min: 0.86 #最小执行时间
avg: 1.75 #平均执行时间
max: 32.28 #最大执行时间
95th percentile: 7.70 #95% events 执行时间
sum: 119873.59 #总时间
Threads fairness:
events (avg/stddev): 17085.0000/8.46
execution time (avg/stddev): 29.9684/0.018、对内存压测
# sysbench memory help
sysbench 1.1.0-faaff4f (using bundled LuaJIT 2.1.0-beta3)
memory options:
--memory-block-size=SIZE # 内存块大小，默认 [1K]
--memory-total-size=SIZE # 传输数据的总大小[100G]
--memory-scope=STRING # 内存访问范围，参数{global,local}，默认为 [global]
--memory-hugetlb[=on|off] # 是否从 HugeTLB pool 分配内存，默认为 [off]
--memory-oper=STRING # 内存操作类型 {read, write, none} 默认为[write]
--memory-access-mode=STRING # 内存访问模式 {seq,rnd} 默认为[seq]最后更新于 2019-07-04 13:30:33 并被添加「mysql sysbench」标签，已有 4929 位童鞋阅读过。
本站使用「署名 4.0 国际」创作共享协议，可自由转载、引用，但需署名作者且注明文章出处