ITPub博客

首页 > 数据库 > MySQL > MySQL 5.7和8.0 MHA架构下sysbench压测

MySQL 5.7和8.0 MHA架构下sysbench压测

MySQL 作者:18141908802 时间:2019-08-29 14:14:32 0 删除 编辑

转转连接   http://blog.itpub.net/22996654/viewspace-2655331/


sysbench压测环境:


sysbench server:172.16.7.105

压测数据库服务器:MHA架构,172.16.7.100(主节点)、172.16.7.101、172.16.7.102

MySQL在172.16.7.105(安装MySQL5.7.26)部署目录:/opt/mysql/

主要压测172.16.7.100主节点

172.16.7.100配置:

MySQL5.7.26

CPU:4核

内存:8G

innodb_buffer_pool_size = 28G

innodb_log_file_size = 512M

innodb_log_files_in_group = 2

innodb_log_buffer_size=16M

MySQL8.0.17

CPU:4核

内存:8G

innodb_buffer_pool_size = 28G

innodb_log_file_size = 512M

innodb_log_files_in_group = 2

innodb_log_buffer_size=16M



前置工作

1.完成MySQL的安装

2.完成动态库文件的安装

yum install -y openssl libtool 



#可能遇到的报错

1.在make之前,需要修改lib库,否则会得到一个报错:/usr/bin/ld: cannot find -lmysqlclient_r

因为安装中指向的lib是/opt/mysql/lib

cd /opt/mysql/lib

ln -s libmysqlclient.so.20.3.9 libmysqlclient_r.so

2.make的时候没有明显错误,执行./sysbench时报错

./sysbench: error while loading shared libraries: libmysqlclient.so.18: cannot open shared object file: No such file or directory

ln -s /opt/mysql/lib/libmysqlclient.so.18 /usr/lib64

如果报错为

/usr/local/sysbench/bin/sysbench: error while loading shared libraries: libmysqlclient.so.20: cannot open shared object file: No such file or directory

那么就执行

ln -s /opt/mysql/lib/libmysqlclient.so.20 /usr/lib64



下载sysbench:


wget http://imysql.com/wp-content/uploads/2014/09/sysbench-0.4.12-1.1.tgz

部署:

tar -xf sysbench-0.4.12-1.1.tgz

mv sysbench-0.4.12-1.1 sysbench

cp -r sysbench /usr/local/sysbench



cd /usr/local/sysbench

./autogen.sh

#生成configure文件

./configure --prefix=/usr/local/sysbench/  --with-mysql=/opt/mysql/  --with-mysql-includes=/opt/mysql/include/ --with-mysql-libs=/opt/mysql/lib/



注意此处的includes必须是mysql对应的include目录,libs也是mysql对应的lib目录

完成安装

make&&make install

(确认没有报错)



验证安装

/usr/local/sysbench/bin/sysbench --help

如果出现下列文字,说明部署成功

[root@GTID01 bin]# /usr/local/sysbench/bin/sysbench --help

Missing required command argument.Usage: sysbench [general-options]... --test= [test-options]... command

General options:

  --num-threads=N            number of threads to use [1]

  --max-requests=N            limit for total number of requests [10000]

...

增加环境变量

echo -e "export PATH=\$PATH:/usr/local/sysbench/bin" >> /etc/profile

source /etc/profile



使用sysbench进行测试

先到被测试的库里建库,建用户,授权(这里通过105去测试100库)

mysql -h 172.16.7.100 -uroot -p

...

create database sbtest; ##因为lua脚本里面这里设置的就是sbtest库,库名需要与脚本里的内容保持一致

create user tpcc@'%' identified by 'tpcc';

grant all privileges on sbtest.* to tpcc@'%';

flush privileges;



MySQL8.0需要加:


alter user tpcc@'%' identified with 'mysql_native_password' by 'tpcc';



会产生报错的操作(将oltp.lua中的mysiam修改成innodb后,会找不到表)

cd /usr/local/sysbench/sysbench/tests/db/

cp oltp.lua oltp_innodb.lua

sed -i 's/myisam/innodb/g' /usr/local/sysbench/sysbench/tests/db/oltp_innodb.lua



测试多表的压测

执行prepare

sysbench --mysql-host=172.16.7.100 --mysql-port=3306 --mysql-user=tpcc --mysql-password=tpcc --test=/usr/local/sysbench/sysbench/tests/db/oltp.lua --oltp_tables_count=10 --oltp-table-size=100000 --rand-init=on prepare



执行run

sysbench --mysql-host=172.16.7.100 --mysql-port=3306 --mysql-user=tpcc --mysql-password=tpcc --test=/usr/local/sysbench/sysbench/tests/db/oltp.lua --oltp_tables_count=10 --oltp-table-size=100000 --num-threads=4 --oltp-read---report-interval=5 --rand-type=uniform --max-time=30 --max-requests=0 --percentile=99 run

--num-threads=4    //线程数为4

--max-time=30      //测试时间为30s

--report-interval=5    //报告打印周期为5s

--oltp-read->

--max-requests=0                //最大执行次数这里不做限制,只由max-time进行限制



MySQL8.0我是用的30001端口




MySQL5.7用32线程压测结果:


sysbench 0.5:  multi-threaded system evaluation benchmark




Running the test with following options:


Number of threads: 32


Report intermediate results every 5 second(s)


Random number generator seed is 0 and will be ignored






Threads started!




[   5s] threads: 32, tps: 842.19, reads/s: 11867.59, writes/s: 3376.34, response time: 172.23ms (99%)


[  10s] threads: 32, tps: 1013.21, reads/s: 14187.90, writes/s: 4053.03, response time: 96.36ms (99%)


[  15s] threads: 32, tps: 1195.60, reads/s: 16709.15, writes/s: 4778.79, response time: 63.71ms (99%)


[  20s] threads: 32, tps: 1095.19, reads/s: 15341.21, writes/s: 4383.35, response time: 87.04ms (99%)


[  25s] threads: 32, tps: 1111.01, reads/s: 15567.40, writes/s: 4446.26, response time: 78.19ms (99%)


[  30s] threads: 32, tps: 1045.20, reads/s: 14628.00, writes/s: 4174.40, response time: 85.90ms (99%)


OLTP test statistics:


    queries performed:


        read:                            441616


        write:                           126176


        other:                           63088


        total:                           630880


    transactions:                        31544  (1050.71 per sec.)


    deadlocks:                           0      (0.00 per sec.)


    read/write requests:                 567792 (18912.78 per sec.)


    other operations:                    63088  (2101.42 per sec.)




General statistics:


    total time:                          30.0216s


    total number of events:              31544


    total time taken by event execution: 954.2759s


    response time:


         min:                                  9.17ms


         avg:                                 30.25ms


         max:                                389.96ms


         approx.  99 percentile:              94.36ms




Threads fairness:


    events (avg/stddev):           985.7500/40.24


    execution time (avg/stddev):   29.8211/0.03






read: 441616     //读总数,select语句


write: 126176    //写总数,insert、delete、update语句


other: 63088    //其它语句,如commit等


total: 630880           //总的执行语句数


transactions: 31544 (1050.71 per sec.)  //总的事物数(★每秒处理事物数:TPS★)


read/write requests: 567792 (18912.78 per sec.)  //读写请求次数(★每秒的读写次数:QPS★)


other operations: 63088 (2101.42 per sec.)    //其它操作的每秒执行数


General statistics:


total time: 30.0216s        //总时间


total number of events: 

31544    //★事物总数★


total time taken by event execution: 954.2759s   //所有事务耗时相加(不考虑并行因素)


response time:    //应答时间


min: 9.17ms    //最小


avg: 30.25ms    //平均


max: 389.96ms    //最大


approx. 99 percentile: 94.36ms  //99%语句执行时间


Threads fairness:  //线程公平性


events (avg/stddev): 985.7500/40.24


execution time (avg/stddev): 29.8211/0.03



需要重点关注的几个测试结果

★总的事物数,每秒事务数TPS,QPS,时间统计信息(最大、最小、平均、99%以上语句响应时间)★

通过sysbench结果来判定数据库的能力主要还是通过TPS




执行清除

sysbench --mysql-host=172.16.7.100 --mysql-port=3306 --mysql-user=tpcc --mysql-password=tpcc --mysql-db=sbtest --oltp-tables-count=10 --oltp-table-size=100000 --num-threads=16 --max-requests=0 --max-time=30 --report-interval=1 --test=/usr/local/sysbench/sysbench/tests/db/oltp.lua cleanup

sysbench 0.5: multi-threaded system evaluation benchmark

Dropping table 'sbtest1'...

Dropping table 'sbtest2'...

Dropping table 'sbtest3'...





来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/31429259/viewspace-2655397/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2017-01-16

  • 博文量
    103
  • 访问量
    49823