分类: MySQL
使用sysbench对MySQL进行性能测试

sysbench是一个模块化的、跨平台、多线程基准测试工具,主要用于评估测试各种不同系统参数下的系统、数据库负载情况,项目地址:https://github.com/akopytov/sysbench
sysbench主要支持以下几种测试模式:

CPU运算性能
磁盘IO性能
调度程序性能
内存分配及传输速度
POSIX线程性能
数据库性能(OLTP基准测试)

目前sysbench主要支持 Mysql,Drizzle,PgSQL,Oracle等几种数据库。
快速安装:

yum -y install make automake libtool pkgconfig libaio-devel        
yum -y install mariadb-devel openssl-devel    # For MySQL support, replace with mysql-devel on RHEL/CentOS 5
yum -y install postgresql-devel        #For PostgreSQL support
curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bash
yum -y install sysbench

查看版本

[root@hongsin-monitor]# sysbench --version
sysbench 1.0.15

测试脚本路径

[root@hongsin-monitor sysbench]# pwd
/usr/share/sysbench
[root@hongsin-monitor sysbench]# ll
total 64
-rwxr-xr-x 1 root root  1452 Jul  4 04:06 bulk_insert.lua
-rw-r--r-- 1 root root 14369 Jul  4 04:06 oltp_common.lua
-rwxr-xr-x 1 root root  1290 Jul  4 04:06 oltp_delete.lua
-rwxr-xr-x 1 root root  2415 Jul  4 04:06 oltp_insert.lua
-rwxr-xr-x 1 root root  1265 Jul  4 04:06 oltp_point_select.lua
-rwxr-xr-x 1 root root  1649 Jul  4 04:06 oltp_read_only.lua
-rwxr-xr-x 1 root root  1824 Jul  4 04:06 oltp_read_write.lua
-rwxr-xr-x 1 root root  1118 Jul  4 04:06 oltp_update_index.lua
-rwxr-xr-x 1 root root  1127 Jul  4 04:06 oltp_update_non_index.lua
-rwxr-xr-x 1 root root  1440 Jul  4 04:06 oltp_write_only.lua
-rwxr-xr-x 1 root root  1919 Jul  4 04:06 select_random_points.lua
-rwxr-xr-x 1 root root  2118 Jul  4 04:06 select_random_ranges.lua
drwxr-xr-x 4 root root  4096 Oct 20 16:48 tests

写性能测试,数据准备
sh
sysbench --test=/usr/share/sysbench/oltp_write_only.lua --mysql-host=192.168.255.90 --mysql-port=3306 --mysql-user=ice --mysql-password=xxxxxx --mysql-db=a1 --report-interval=10 --max-requests=0 --time=120 --threads=4500 --tables=10 --table-size=100000 prepare
WARNING: the --test option is deprecated. You can pass a script name or path on the command line without any options.
sysbench 1.0.15 (using bundled LuaJIT 2.1.0-beta2)

Initializing worker threads...

Creating table 'sbtest9'...Creating table 'sbtest8'...
Creating table 'sbtest3'...
Creating table 'sbtest4'...Creating table 'sbtest6'...

Creating table 'sbtest1'...
Creating table 'sbtest7'...
Creating table 'sbtest5'...
Creating table 'sbtest2'...
Creating table 'sbtest10'...
Inserting 100000 records into 'sbtest4'
Inserting 100000 records into 'sbtest10'
Inserting 100000 records into 'sbtest1'
Inserting 100000 records into 'sbtest2'
Inserting 100000 records into 'sbtest5'
Inserting 100000 records into 'sbtest8'
Inserting 100000 records into 'sbtest6'
Inserting 100000 records into 'sbtest9'
Inserting 100000 records into 'sbtest7'
Inserting 100000 records into 'sbtest3'
Creating a secondary index on 'sbtest10'...
Creating a secondary index on 'sbtest5'...
Creating a secondary index on 'sbtest8'...
Creating a secondary index on 'sbtest9'...
Creating a secondary index on 'sbtest6'...
Creating a secondary index on 'sbtest7'...
Creating a secondary index on 'sbtest1'...
Creating a secondary index on 'sbtest2'...
Creating a secondary index on 'sbtest4'...
Creating a secondary index on 'sbtest3'...

参数的解释:

--threads=4500 表示发起4500个并发连接
--oltp-read-only=off 表示不要进行只读测试,也就是会采用读写混合模式测试
--report-interval=10 表示每10秒输出一次测试进度报告
--rand-type=uniform 表示随机类型为固定模式,其他几个可选随机模式:uniform(固定),gaussian(高斯),special(特定的),pareto(帕累托)
--time=120 表示最大执行时长为 120秒
--max-requests=0 表示总请求数为 0,因为上面已经定义了总执行时长,所以总请求数可以设定为 0;也可以只设定总请求数,不设定最大执行时长
--tables=10 表示10个表
--table-size=100000 单表100000条记录
--percentile=99 表示设定采样比例,默认是 95%,即丢弃1%的长请求,在剩余的99%里取最大值

运行测试

sysbench --test=/usr/share/sysbench/oltp_write_only.lua --mysql-host=192.168.255.90 --mysql-port=3306 --mysql-user=ice --mysql-password=hongsinCS --mysql-db=a1 --report-interval=10 --max-requests=0 --time=120 --threads=4500 --tables=10 --table-size=100000 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.15 (using bundled LuaJIT 2.1.0-beta2)

Running the test with following options:
Number of threads: 4500
Report intermediate results every 10 second(s)
Initializing random number generator from current time

Initializing worker threads...

Threads started!

[ 10s ] thds: 4500 tps: 618.00 qps: 4951.50 (r/w/o: 0.00/1950.97/3000.53) lat (ms,95%): 8038.61 err/s: 91.70 reconn/s: 0.00
[ 20s ] thds: 4500 tps: 562.83 qps: 3806.01 (r/w/o: 0.00/1849.30/1956.71) lat (ms,95%): 15934.78 err/s: 227.01 reconn/s: 0.00
[ 30s ] thds: 4500 tps: 434.80 qps: 3051.53 (r/w/o: 0.00/1599.01/1452.51) lat (ms,95%): 22842.77 err/s: 226.20 reconn/s: 0.00
[ 40s ] thds: 4500 tps: 479.70 qps: 3310.41 (r/w/o: 0.00/1803.90/1506.50) lat (ms,95%): 28352.44 err/s: 224.60 reconn/s: 0.00
[ 50s ] thds: 4500 tps: 495.10 qps: 3403.11 (r/w/o: 0.00/1910.30/1492.80) lat (ms,95%): 28867.59 err/s: 219.60 reconn/s: 0.00
[ 60s ] thds: 4500 tps: 453.20 qps: 3140.89 (r/w/o: 0.00/1769.59/1371.29) lat (ms,95%): 28867.59 err/s: 215.80 reconn/s: 0.00
[ 70s ] thds: 4500 tps: 469.90 qps: 3246.01 (r/w/o: 0.00/1848.80/1397.20) lat (ms,95%): 28867.59 err/s: 228.90 reconn/s: 0.00
[ 80s ] thds: 4500 tps: 466.59 qps: 3238.67 (r/w/o: 0.00/1859.68/1378.99) lat (ms,95%): 29926.15 err/s: 225.20 reconn/s: 0.00
[ 90s ] thds: 4500 tps: 441.30 qps: 3084.43 (r/w/o: 0.00/1765.82/1318.61) lat (ms,95%): 29926.15 err/s: 225.30 reconn/s: 0.00
[ 100s ] thds: 4500 tps: 434.20 qps: 3026.20 (r/w/o: 0.00/1737.20/1289.00) lat (ms,95%): 29926.15 err/s: 217.40 reconn/s: 0.00
[ 110s ] thds: 4500 tps: 471.70 qps: 3247.39 (r/w/o: 0.00/1872.59/1374.80) lat (ms,95%): 32161.14 err/s: 218.40 reconn/s: 0.00
[ 120s ] thds: 4500 tps: 476.39 qps: 3289.44 (r/w/o: 0.00/1892.36/1397.07) lat (ms,95%): 31023.52 err/s: 232.80 reconn/s: 0.00
SQL statistics:

queries performed:
    read:                            0         读操作
    write:                           233015     --写总数
    other:                           197026  --其他操作总数(SELECT、INSERT、UPDATE、DELETE之外的操作,例如COMMIT等)
    total:                           430041  --全部总数
transactions:                        62617  (486.84 per sec.)  --总事务数(每秒事务数)
queries:                             430041 (3343.55 per sec.) --查询数(每秒查询数)
ignored errors:                      27087  (210.60 per sec.)  --忽略错误数
reconnects:                          0      (0.00 per sec.)  --重新连接次数

General statistics:

total time:                          128.6147s  --运行总时间
total number of events:              62617  --事件总数

Latency (ms):

     min:                                    2.09
     avg:                                 8957.64
     max:                                99069.80
     95th percentile:                    27846.48
     sum:                            560900840.54

Threads fairness: #线程平均数

events (avg/stddev):           13.9149/3.92
execution time (avg/stddev):   124.6446/2.39
清理数据:

sysbench --test=/usr/share/sysbench/oltp_write_only.lua --mysql-host=192.168.255.90 --mysql-port=3306 --mysql-user=ice --mysql-password=hongsinCS --mysql-db=a1 --report-interval=10 --max-requests=0 --time=120 --threads=4500 --tables=10 --table-size=100000 cleanup
WARNING: the --test option is deprecated. You can pass a script name or path on the command line without any options.
sysbench 1.0.15 (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'...

可以根据/usr/share/sysbench下面的lua脚本进行读、写、更新,随机等等测试,条件允许建议时间1小时以上,以便测试数据准


相关博文:

发表新评论