sysbench是一款开源的多线程性能测试工具，可以执行 CPU/内存/线程/ IO/数据库等方面的性能测试。
数据库目前支持MySQL/Oracle/PostgreSQL。
安装
sysbench的源码可以在github上面下载的到，sysbench的主页
https://github.com/akopytov/sysbench
本文下载的sysbench-1.0.zip版本
1、上传到服务器，解压
[root@localhost ~]#unzip sysbench-1.0.zip
[root@localhost ~]#cd sysbench-1.0
2、安装依赖库
[root@localhost ~]#yum install automake libtool -y
3、开始安装
[root@localhost sysbench-1.0]#./autogen.sh
[root@localhost sysbench-1.0]#./configure --with-mysql-includes=/usr/include/mysql --with-mysql-libs=/usr/lib64/mysql
[root@localhost sysbench-1.0]#make
[root@localhost sysbench-1.0]#make install
问题：usr/include下没有mysql目录
解决： rpm -ivh mysql-community-devel-5.7.41-1.el7.x86_64.rpm
4、执行命令
[root@localhost mysql]# sysbench --help
[root@localhost mysql]# sysbench --version
mysql数据库测试
1、使用自带oltp.lua脚本测试
测试使用命令：
#/root/sysbench-1.0/src/sysbench --test=/root/sysbench-1.0/tests/include/oltp_legacy/oltp.lua --mysql-host=mysql-host-ip --mysql-port=3306 --mysql-user=envision --mysql-password=password --mysql-db=dbtest --oltp-tables-count=10 --oltp-table-size=500000 --report-interval=10 --rand-init=on --max-requests=0 --oltp-read-only=off --time=120 --threads=30 [ prepare | run | cleanup ]
##三选一
prepare 准备；
run 运行；
cleanup 清理数据。
注意最后一行，一项测试开始前需要用prepare来准备好表和数据，run执行真正的压测，cleanup用来清除数据和表。
|
命令 |
描述 |
|---|---|
|
--db-driver=mysql |
mysql 数据库驱动 |
|
--time=300 |
300 秒测试时长 |
|
--threads=64 |
64 个测试线程 |
|
--report-interval=2 |
输出压测信息的间隔时间为 2 秒 |
|
--mysql-host=10.0.12.17 |
数据库地址 |
|
--mysql-port=3306 |
数据库端口 |
|
--mysql-user=root |
数据库用户 |
|
--mysql-password=123456 |
数据库密码 |
|
--mysql-db=dbtest |
测试库名称 |
|
--tables=10 |
测试库表数量 |
|
--table_size=1000000 |
测试表数据量 |
|
oltp_read_write |
读写性能测试 |
|
oltp_read_only |
只读性能测试 |
|
oltp_delete |
删除性能测试 |
|
oltp_update_index |
更新索引字段性能测试 |
|
oltp_update_non_index |
更新非索引字段性能测试 |
|
oltp_insert |
插入性能测试 |
|
oltp_write_only |
写入性能测试 |
|
--db-ps-mode=disable |
禁止 ps 模式 |
|
cleanup |
清理表和数据 |
|
run |
运行测试 |
|
prepare |
构建表和测试数据 |
--rand-init=on 表示每个测试表都是用随机数据来填充的
--max-requests=0 表示总请求数为 0，因为上面已经定义了总执行时长，所以总请求数可以设定为 0；也可以只设定总请求数，不设定最大执行时长
1、创建测试数据库
mysql> create database dbtest;
2、准备数据
128个并发连接，10张表 每个表填充10W条数据 最大请求时间120s
注意password修改下密码
/root/sysbench-1.0/src/sysbench --test=/root/sysbench-1.0/tests/include/oltp_legacy/oltp.lua --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=XXX --mysql-db=dbtest --oltp-tables-count=10 --oltp-table-size=100000 --report-interval=10 --rand-init=on --max-requests=0 --oltp-read-only=off --time=120 --threads=128 prepare
3、执行sysbench测试，输出报告
/root/sysbench-1.0/src/sysbench --test=/root/sysbench-1.0/tests/include/oltp_legacy/oltp.lua --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=XXX --mysql-db=dbtest --oltp-tables-count=10 --oltp-table-size=100000 --report-interval=10 --rand-init=on --max-requests=0 --oltp-read-only=off --time=120 --threads=128 run
[root@localhost ~]# /root/sysbench-1.0/src/sysbench --test=/root/sysbench-1.0/tests/include/oltp_legacy/oltp.lua --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=XXX --mysql-db=dbtest --oltp-tables-count=10 --oltp-table-size=100000 --report-interval=10 --rand-init=on --max-requests=0 --oltp-read-only=off --time=120 --threads=128 run WARNING: the --test option is deprecated. You can pass a script name or path on the command line without any options. sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2) Running the test with following options: Number of threads: 128 Report intermediate results every 10 second(s) Initializing random number generator from current time Initializing worker threads... Threads started! [ 10s ] thds: 128 tps: 3200.07 qps: 64200.49 (r/w/o: 44954.29/12833.56/6412.63) lat (ms,95%): 95.81 err/s: 0.00 reconn/s: 0.00 [ 20s ] thds: 128 tps: 3106.57 qps: 62142.56 (r/w/o: 43501.35/12427.77/6213.44) lat (ms,95%): 95.81 err/s: 0.00 reconn/s: 0.00 [ 30s ] thds: 128 tps: 3062.69 qps: 61255.33 (r/w/o: 42879.88/12250.27/6125.18) lat (ms,95%): 101.13 err/s: 0.00 reconn/s: 0.00 [ 40s ] thds: 128 tps: 3126.52 qps: 62525.24 (r/w/o: 43764.11/12508.09/6253.04) lat (ms,95%): 97.55 err/s: 0.00 reconn/s: 0.00 [ 50s ] thds: 128 tps: 3094.41 qps: 61873.55 (r/w/o: 43318.11/12366.53/6188.92) lat (ms,95%): 97.55 err/s: 0.00 reconn/s: 0.00 [ 60s ] thds: 128 tps: 2959.48 qps: 59207.85 (r/w/o: 41441.48/11847.61/5918.75) lat (ms,95%): 106.75 err/s: 0.00 reconn/s: 0.00 [ 70s ] thds: 128 tps: 3065.02 qps: 61301.92 (r/w/o: 42909.79/12261.98/6130.14) lat (ms,95%): 101.13 err/s: 0.00 reconn/s: 0.00 [ 80s ] thds: 128 tps: 3111.97 qps: 62241.92 (r/w/o: 43573.19/12444.78/6223.94) lat (ms,95%): 97.55 err/s: 0.00 reconn/s: 0.00 [ 90s ] thds: 128 tps: 3059.54 qps: 61208.95 (r/w/o: 42840.02/12249.65/6119.27) lat (ms,95%): 101.13 err/s: 0.00 reconn/s: 0.00 [ 100s ] thds: 128 tps: 3020.08 qps: 60374.48 (r/w/o: 42265.80/12068.72/6039.96) lat (ms,95%): 108.68 err/s: 0.00 reconn/s: 0.00 [ 110s ] thds: 128 tps: 3017.48 qps: 60337.60 (r/w/o: 42239.42/12063.42/6034.76) lat (ms,95%): 102.97 err/s: 0.00 reconn/s: 0.00 [ 120s ] thds: 128 tps: 2982.78 qps: 59646.26 (r/w/o: 41748.76/11933.23/5964.27) lat (ms,95%): 99.33 err/s: 0.00 reconn/s: 0.00 SQL statistics: queries performed: read: 5154814 write: 1472804 other: 736402 total: 7364020 transactions: 368201 (3067.61 per sec.) queries: 7364020 (61352.16 per sec.) ignored errors: 0 (0.00 per sec.) reconnects: 0 (0.00 per sec.) General statistics: total time: 120.0272s total number of events: 368201 Latency (ms): min: 5.19 avg: 41.72 max: 564.96 95th percentile: 99.33 sum: 15360102.92 Threads fairness: events (avg/stddev): 2876.5703/22.42 execution time (avg/stddev): 120.0008/0.01
参考网上对结果字段解释
2、不使用lua脚本测试
1、准备数据
/root/sysbench-1.0/src/sysbench --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=XXX --mysql-db=tbtest --report-interval=10 --time=120 --threads=60 --tables=10 --table_size=100000 oltp_read_write --db-ps-mode=disable prepare
[root@localhost ~]# /root/sysbench-1.0/src/sysbench --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=XXX --mysql-db=tbtest --report-interval=10 --time=120 --threads=60 --tables=10 --table_size=100000 oltp_read_write --db-ps-mode=disable prepare sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2) Initializing worker threads... Creating table 'sbtest10'... Creating table 'sbtest9'... Creating table 'sbtest8'... Creating table 'sbtest3'... Creating table 'sbtest7'... Creating table 'sbtest5'... Creating table 'sbtest6'... Creating table 'sbtest2'... Creating table 'sbtest1'... Creating table 'sbtest4'... Inserting 100000 records into 'sbtest3' Inserting 100000 records into 'sbtest8' Inserting 100000 records into 'sbtest1' Inserting 100000 records into 'sbtest10' Inserting 100000 records into 'sbtest7' Inserting 100000 records into 'sbtest2' Inserting 100000 records into 'sbtest9' Inserting 100000 records into 'sbtest5' Inserting 100000 records into 'sbtest4' Inserting 100000 records into 'sbtest6' Creating a secondary index on 'sbtest3'... Creating a secondary index on 'sbtest8'... Creating a secondary index on 'sbtest6'... Creating a secondary index on 'sbtest7'... Creating a secondary index on 'sbtest1'... Creating a secondary index on 'sbtest4'... Creating a secondary index on 'sbtest10'... Creating a secondary index on 'sbtest2'... Creating a secondary index on 'sbtest5'... Creating a secondary index on 'sbtest9'... [root@localhost ~]#
2、读写性能测试
/root/sysbench-1.0/src/sysbench --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=XXX --mysql-db=tbtest --report-interval=10 --time=120 --threads=60 --tables=10 --table_size=100000 oltp_read_write --db-ps-mode=disable run
[root@localhost ~]# /root/sysbench-1.0/src/sysbench --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=XXX --mysql-db=tbtest --report-interval=10 --time=120 --threads=60 --tables=10 --table_size=100000 oltp_read_write --db-ps-mode=disable run
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 60
Report intermediate results every 10 second(s)
Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 10s ] thds: 60 tps: 2752.52 qps: 55130.31 (r/w/o: 38597.11/11022.57/5510.64) lat (ms,95%): 65.65 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 60 tps: 2731.82 qps: 54642.58 (r/w/o: 38250.67/10928.28/5463.64) lat (ms,95%): 68.05 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 60 tps: 2827.91 qps: 56561.80 (r/w/o: 39594.64/11311.04/5656.12) lat (ms,95%): 65.65 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 60 tps: 2765.69 qps: 55313.67 (r/w/o: 38720.81/11061.47/5531.39) lat (ms,95%): 68.05 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 60 tps: 2835.90 qps: 56725.83 (r/w/o: 39703.62/11350.41/5671.80) lat (ms,95%): 65.65 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 60 tps: 2822.08 qps: 56430.67 (r/w/o: 39503.87/11282.63/5644.17) lat (ms,95%): 65.65 err/s: 0.00 reconn/s: 0.00
[ 70s ] thds: 60 tps: 2768.20 qps: 55376.74 (r/w/o: 38765.46/11074.79/5536.49) lat (ms,95%): 68.05 err/s: 0.00 reconn/s: 0.00
[ 80s ] thds: 60 tps: 2835.15 qps: 56694.02 (r/w/o: 39686.12/11337.80/5670.10) lat (ms,95%): 66.84 err/s: 0.00 reconn/s: 0.00
[ 90s ] thds: 60 tps: 2751.86 qps: 55050.70 (r/w/o: 38533.14/11013.64/5503.92) lat (ms,95%): 69.29 err/s: 0.00 reconn/s: 0.00
[ 100s ] thds: 60 tps: 2774.50 qps: 55474.81 (r/w/o: 38831.94/11093.98/5548.89) lat (ms,95%): 68.05 err/s: 0.00 reconn/s: 0.00
[ 110s ] thds: 60 tps: 2789.60 qps: 55790.03 (r/w/o: 39055.65/11155.19/5579.19) lat (ms,95%): 69.29 err/s: 0.00 reconn/s: 0.00
SQL statistics:
queries performed:
read: 4680480
write: 1337280
other: 668640
total: 6686400
transactions: 334320 (2785.63 per sec.)
queries: 6686400 (55712.53 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 120.0146s
total number of events: 334320
Latency (ms):
min: 5.05
avg: 21.53
max: 209.10
95th percentile: 66.84
sum: 7198874.23
Threads fairness:
events (avg/stddev): 5572.0000/24.29
execution time (avg/stddev): 119.9812/0.01
3、只读性能测试
/root/sysbench-1.0/src/sysbench --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=XXX --mysql-db=tbtest --report-interval=10 --time=120 --threads=60 --tables=10 --table_size=100000 oltp_read_only --db-ps-mode=disable run
[root@localhost ~]# /root/sysbench-1.0/src/sysbench --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=XXX --mysql-db=tbtest --report-interval=10 --time=120 --threads=60 --tables=10 --table_size=100000 oltp_read_only --db-ps-mode=disable run sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2) Running the test with following options: Number of threads: 60 Report intermediate results every 10 second(s) Initializing random number generator from current time Initializing worker threads... Threads started! [ 10s ] thds: 60 tps: 6159.22 qps: 98605.21 (r/w/o: 86280.87/0.00/12324.34) lat (ms,95%): 36.89 err/s: 0.00 reconn/s: 0.00 [ 20s ] thds: 60 tps: 6263.99 qps: 100227.70 (r/w/o: 87699.72/0.00/12527.99) lat (ms,95%): 39.65 err/s: 0.00 reconn/s: 0.00 [ 30s ] thds: 60 tps: 6239.22 qps: 99834.38 (r/w/o: 87355.94/0.00/12478.43) lat (ms,95%): 33.72 err/s: 0.00 reconn/s: 0.00 [ 40s ] thds: 60 tps: 6234.90 qps: 99750.00 (r/w/o: 87280.20/0.00/12469.80) lat (ms,95%): 33.72 err/s: 0.00 reconn/s: 0.00 [ 50s ] thds: 60 tps: 6227.11 qps: 99631.12 (r/w/o: 87176.91/0.00/12454.22) lat (ms,95%): 33.72 err/s: 0.00 reconn/s: 0.00 [ 60s ] thds: 60 tps: 6234.64 qps: 99757.94 (r/w/o: 87288.56/0.00/12469.38) lat (ms,95%): 34.33 err/s: 0.00 reconn/s: 0.00 [ 70s ] thds: 60 tps: 6238.51 qps: 99816.22 (r/w/o: 87339.39/0.00/12476.83) lat (ms,95%): 35.59 err/s: 0.00 reconn/s: 0.00 [ 80s ] thds: 60 tps: 6258.42 qps: 100125.76 (r/w/o: 87608.73/0.00/12517.03) lat (ms,95%): 37.56 err/s: 0.00 reconn/s: 0.00 [ 90s ] thds: 60 tps: 6250.12 qps: 100006.17 (r/w/o: 87506.03/0.00/12500.15) lat (ms,95%): 38.25 err/s: 0.00 reconn/s: 0.00 [ 100s ] thds: 60 tps: 6244.77 qps: 99917.64 (r/w/o: 87428.19/0.00/12489.44) lat (ms,95%): 34.95 err/s: 0.00 reconn/s: 0.00 [ 110s ] thds: 60 tps: 6250.76 qps: 100008.92 (r/w/o: 87507.39/0.00/12501.53) lat (ms,95%): 37.56 err/s: 0.00 reconn/s: 0.00 [ 120s ] thds: 60 tps: 6245.25 qps: 99924.33 (r/w/o: 87433.84/0.00/12490.49) lat (ms,95%): 34.33 err/s: 0.00 reconn/s: 0.00 SQL statistics: queries performed: read: 10479532 write: 0 other: 1497076 total: 11976608 transactions: 748538 (6237.09 per sec.) queries: 11976608 (99793.46 per sec.) ignored errors: 0 (0.00 per sec.) reconnects: 0 (0.00 per sec.) General statistics: total time: 120.0125s total number of events: 748538 Latency (ms): min: 2.32 avg: 9.62 max: 238.33 95th percentile: 36.24 sum: 7197827.80 Threads fairness: events (avg/stddev): 12475.6333/119.34 execution time (avg/stddev): 119.9638/0.01
3、删除性能测试、更新索引字段性能测试、更新非索引字段性能测试、插入性能测试、写入性能测试类似，替换下参数
参数见上面表格
/root/sysbench-1.0/src/sysbench --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=XXX --mysql-db=tbtest --report-interval=10 --time=120 --threads=60 --tables=10 --table_size=100000 $替换测试模型参数--db-ps-mode=disable run
4、清除数据
/root/sysbench-1.0/src/sysbench --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=XXX --mysql-db=tbtest --report-interval=10 --time=120 --threads=60 --tables=10 --table_size=100000 oltp_read_write --db-ps-mode=disable cleanup
[root@localhost ~]# /root/sysbench-1.0/src/sysbench --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=XXX --mysql-db=tbtest --report-interval=10 --time=120 --threads=60 --tables=10 --table_size=100000 oltp_read_write --db-ps-mode=disable cleanup 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'..
