客服微信
【前言】
sysbench是一个开源的、模块化的、跨平台的多线程性能测试工具，可以用来进行CPU、内存、磁盘I/O、线程、数据库的性能测试，针对多个单独的表的测试，灵活修改lua脚本。
01
sysbenc编译部署
yum install autoconf automak mysql-devel libtool 下载依赖包tar -xzvf sysbench-master.tga 解压源码cd sysbench-master./autogen.sh编译makemake installsysbench –version //确认一下版本是否为1.1
编译完成后在sysbench-master/src/目录会产生sysbench二进制，可以将sysbench二进制文件拷贝到sysbench-master目录方便执行。
Lua脚本在sysbench-master/src/lua目录。
[root@tdsql1 sysbench-master]# lsaclocal.m4 ChangeLog config.status COPYING install-sh Makefile missing out.sh.bak README-WIN.txt snap sysbench_pkg.tgzautogen.sh config configure cpuload.sh libtool Makefile.am mkinstalldirs README.md rpm src testsautom4te.cache config.log configure.ac debian m4 Makefile.in out.sh README-Oracle.md scripts sysbench third_party
02
检查脚本
/tdsql/tdsql_for_test/tdsql_for_student/1/sysbench-master/src/luavi vim oltp_common.luaextra_table_options = extra_table_options .. " shardkey=id" （186行）没有这个分布式报错con:query("select sleep (10)") （199）query = "INSERT IGNORE INTO sbtest" .. table_num .. "(k, c, pad) VALUES" (207行）如果没有这个参数,表有重复会报错，有这个参数,插入重复直接不插入了 replace 直接覆盖
03
创建用户密码
(管理平台创建分布数据库）
1）登录
[root@tdsql2 ~]# mysql -h 10.206.0.4 -P15002 -uyunbee -pYunbee123Welcome to the MariaDB monitor. Commands end with ; or \g.Your MySQL connection id is 4865Server version: 5.7.17-11-V2.0R540D002-20191226-1152-log Source distributionCopyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.MySQL [(none)]> show databases;+--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema || sys || sysdb || test || xa |+--------------------+7 rows in set (0.00 sec)
2）创建db
create database yunbee；MySQL [(none)]> create database yunbee;Query OK, 1 row affected (0.01 sec)use yunbee;MySQL [(none)]> create database yunbee;Query OK, 1 row affected (0.01 sec)show tables;MySQL [yunbee]> show tables;Empty set (0.00 sec)
4.加载数据
Sysbench装载数据相关参数介绍
--mysql-host：proxy或者db的ip地址
--mysql-port： proxy或者db的port
--mysql-user：数据库用户名
--mysql-password：数据库密码
--mysql-db：database name
--tables ：装载表的个数
--table-size :每张表的行数
--db-driver：指定db driver类型为mysql（pg，mysql。。。。）
--threads：用户连接数（客户端并发连接数）
cd /tdsql/tdsql_for_test/tdsql_for_student/1/sysbench-master[]sysbench 1.1.0 (using bundled LuaJIT 2.1.0-beta3)Initializing worker threads...Creating table 'sbtest6'...Creating table 'sbtest1'...Creating table 'sbtest5'...Creating table 'sbtest7'...Creating table 'sbtest2'...Creating table 'sbtest4'...Creating table 'sbtest9'...Creating table 'sbtest3'...Creating table 'sbtest10'...Creating table 'sbtest8'...Inserting 1000000 records into 'sbtest10'Inserting 1000000 records into 'sbtest7'Inserting 1000000 records into 'sbtest5'Inserting 1000000 records into 'sbtest6'Inserting 1000000 records into 'sbtest1'Inserting 1000000 records into 'sbtest8'Inserting 1000000 records into 'sbtest4'Inserting 1000000 records into 'sbtest9'Inserting 1000000 records into 'sbtest2'Inserting 1000000 records into 'sbtest3'Creating a secondary index on 'sbtest10'...Creating a secondary index on 'sbtest1'...Creating a secondary index on 'sbtest6'...Creating a secondary index on 'sbtest8'...Creating a secondary index on 'sbtest2'...Creating a secondary index on 'sbtest4'...Creating a secondary index on 'sbtest3'...Creating a secondary index on 'sbtest9'...Creating a secondary index on 'sbtest7'...Creating a secondary index on 'sbtest5'...
加载完成
MySQL [yunbee]> show tables;+------------------+| Tables_in_yunbee |+------------------+| sbtest1 || sbtest10 || sbtest2 || sbtest3 || sbtest4 || sbtest5 || sbtest6 || sbtest7 || sbtest8 || sbtest9 |+------------------+10 rows in set (0.00 sec)MySQL [yunbee]> select count(*) from sbtest1;+----------+| count(1) |+----------+| 1000000 |+----------+1 row in set (0.08 sec)
05
压力测试
Sysbench压测场景
--range_size ：查询范围，默认值100行
--point_selects =每个事务包含point select的个数，默认值1
--simple_ranges ：每个事务包含range select的个数，默认值1
--sum_ranges =每个事务包含sum() select的个数，默认值1
--order_ranges =每个事务包含order by select的个数，默认值1
--distinct_ranges =每个事务包含distinct select的个数，默认值1
--index_updates =每个事务包含index select的个数，默认值1
--non_index_updates =每个事务包含更新非索引字段的个数，默认值1
--delete_inserts =每个事务包含delete和insert的个数，默认值1
--range_selects =开启或者关闭范围查询，默认值false
--time 运行时间设为0表示不限制时间
--report-interval 运行期间日志，单位为秒
--events 最大请求数量，定义数量后可以不需要--time选项
在上面的语句后面加上 prepare，执行
在上面的语句后面加上 run，执行
在上面的语句后面加上 cleanup，执行
prepare用于准备测试需要的数据，准备完后执行run来测试，测试完成后不要忘记执行cleanup来清除测试数据
root@tdsql1 sysbench-master]# ./sysbench --threads=10 ./src/lua/oltp_read_write.lua --table-size=1000000 --tables=10 --point_selects=2 --range_selects=2 --index_updates=2 --non_index_updates=1 --delete_inserts=1 --report-interval=1 --mysql-host=10.206.0.4 --mysql-port=15002 --mysql-user=yunbee --mysql-password=Yunbee123 --mysql-db=yunbee --time=120 --max-requests=0 runsysbench 1.1.0 (using bundled LuaJIT 2.1.0-beta3)Running the test with following options:Number of threads: 10Report intermediate results every 1 second(s)Initializing random number generator from current timeInitializing worker threads...Threads started![ 1s ] thds: 10 tps: 42.87 qps: 435.66 (r/w/o: 103.68/236.28/95.71) lat (ms,95%): 267.41 err/s: 0.00 reconn/s: 0.00[ 119s ] thds: 10 tps: 51.99 qps: 467.93 (r/w/o: 103.98/259.96/103.98) lat (ms,95%): 248.83 err/s: 0.00 reconn/s: 0.00[ 120s ] thds: 10 tps: 53.00 qps: 467.98 (r/w/o: 100.00/261.99/106.00) lat (ms,95%): 240.02 err/s: 0.00 reconn/s: 0.00SQL statistics:queries performed:read: 12354 总select数据量write: 30885 总写请求数DML语句数量other: 12354total: 55593 总请求数（r+w+o的总和）transactions: 6177 (51.42 per sec.) tps 每秒完成的事物数queries: 55593 (462.82 per sec.) qps 每秒请求数ignored errors: 0 (0.00 per sec.) 每秒忽略的错误数reconnects: 0 (0.00 per sec.) 每秒重连数据General statistics:total time: 120.1188s 压测的时间120秒total number of events: 6177 总的事件数，一般与trancation相同Latency (ms):min: 59.95avg: 194.36 95%的语句平均响应时间max: 438.6195th percentile: 257.95 95%语句的平均响应时间sum: 1200569.67 总耗时Threads fairness:events (avg/stddev): 617.7000/7.94execution time (avg/stddev): 120.0570/0.05
一般关注的指标主要有:
response time avg：平均响应时间（后面的95%的大小可以通过–percentile=98的方式去更改）。
transactions：精确的说是这一项后面的TPS，但如果使用了–skip-trx=on，这项事务数为0，需要用total number of events去除以总时间，得到tps（其实还可以分为读tps和写tps）。
queries：用它除以总时间，得到吞吐量QPS。
