MySQL 5.7和8.0 MHA結(jié)構(gòu)下sysbench壓測(cè)

2024-07-24 12:31:35

字體：大中小

供稿：網(wǎng)友

sysbench壓測(cè)環(huán)境：

sysbench server：172.16.7.105

壓測(cè)數(shù)據(jù)庫(kù)服務(wù)器：MHA架構(gòu)，172.16.7.100（主節(jié)點(diǎn)）、172.16.7.101、172.16.7.102

MySQL在172.16.7.105（安裝MySQL5.7.26）部署目錄：/opt/mysql/

主要壓測(cè)172.16.7.100主節(jié)點(diǎn)

172.16.7.100配置：

MySQL5.7.26

CPU：4核

內(nèi)存：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核

內(nèi)存：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.完成動(dòng)態(tài)庫(kù)文件的安裝

yum install -y openssl libtool

#可能遇到的報(bào)錯(cuò)

1.在make之前，需要修改lib庫(kù)，否則會(huì)得到一個(gè)報(bào)錯(cuò):/usr/bin/ld: cannot find -lmysqlclient_r

因?yàn)榘惭b中指向的lib是/opt/mysql/lib

cd /opt/mysql/lib

ln -s libmysqlclient.so.20.3.9 libmysqlclient_r.so

2.make的時(shí)候沒(méi)有明顯錯(cuò)誤，執(zhí)行./sysbench時(shí)報(bào)錯(cuò)

./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

如果報(bào)錯(cuò)為

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

那么就執(zhí)行

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對(duì)應(yīng)的include目錄，libs也是mysql對(duì)應(yīng)的lib目錄

完成安裝

make&&make install

（確認(rèn)沒(méi)有報(bào)錯(cuò)）

驗(yàn)證安裝

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

如果出現(xiàn)下列文字，說(shuō)明部署成功

[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]

...

增加環(huán)境變量

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

source /etc/profile

使用sysbench進(jìn)行測(cè)試

先到被測(cè)試的庫(kù)里建庫(kù)，建用戶(hù)，授權(quán)（這里通過(guò)105去測(cè)試100庫(kù)）

mysql -h 172.16.7.100 -uroot -p

...

create database sbtest; ##因?yàn)閘ua腳本里面這里設(shè)置的就是sbtest庫(kù)，庫(kù)名需要與腳本里的內(nèi)容保持一致

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';

會(huì)產(chǎn)生報(bào)錯(cuò)的操作（將oltp.lua中的mysiam修改成innodb后，會(huì)找不到表）

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

測(cè)試多表的壓測(cè)

執(zhí)行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

執(zhí)行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　　　　//線程數(shù)為4

--max-time=30　　　　　　//測(cè)試時(shí)間為30s

--report-interval=5　　　　//報(bào)告打印周期為5s

--oltp-read->

--max-requests=0                //最大執(zhí)行次數(shù)這里不做限制，只由max-time進(jìn)行限制

MySQL8.0我是用的30001端口

MySQL5.7用32線程壓測(cè)結(jié)果：

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　　　　 //讀總數(shù)，select語(yǔ)句

write: 126176　　　　//寫(xiě)總數(shù)，insert、delete、update語(yǔ)句

other: 63088　　　 //其它語(yǔ)句，如commit等

total: 630880           //總的執(zhí)行語(yǔ)句數(shù)

transactions: 31544 (1050.71 per sec.)　　//總的事物數(shù)（★每秒處理事物數(shù)：TPS★）

read/write requests: 567792 (18912.78 per sec.)　　//讀寫(xiě)請(qǐng)求次數(shù)（★每秒的讀寫(xiě)次數(shù)：QPS★）

other operations: 63088 (2101.42 per sec.)　　　　//其它操作的每秒執(zhí)行數(shù)

General statistics:

total time: 30.0216s　　　　　　　　//總時(shí)間

total number of events:

31544　　　　//★事物總數(shù)★

total time taken by event execution: 954.2759s　　 //所有事務(wù)耗時(shí)相加(不考慮并行因素)

response time:　　　　//應(yīng)答時(shí)間

min: 9.17ms　　　　//最小

avg: 30.25ms　　　　//平均

max: 389.96ms　　　　//最大

approx. 99 percentile: 94.36ms　　//99%語(yǔ)句執(zhí)行時(shí)間

Threads fairness:　　//線程公平性

events (avg/stddev): 985.7500/40.24

execution time (avg/stddev): 29.8211/0.03

需要重點(diǎn)關(guān)注的幾個(gè)測(cè)試結(jié)果

★總的事物數(shù)，每秒事務(wù)數(shù)TPS，QPS，時(shí)間統(tǒng)計(jì)信息（最大、最小、平均、99%以上語(yǔ)句響應(yīng)時(shí)間）★

通過(guò)sysbench結(jié)果來(lái)判定數(shù)據(jù)庫(kù)的能力主要還是通過(guò)TPS

執(zhí)行清除

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'...

（編輯：武林網(wǎng)）

上一篇：CentOS 6.7下源碼編譯安置MySQL 5.7.5

下一篇：Mysql 5.7 Gtid內(nèi)部學(xué)習(xí) Mysql開(kāi)啟初始化Gtid模塊