MySQL 5.7和8.0 MHA架构下sysbench压测

转转连接
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’…

相关推荐: 数据库如何创建

今天就跟大家聊聊有关数据库如何创建，可能很多人都不太了解，为了让大家更加了解，小编给大家开发云主机域名总结了以下内容，希望大家根据这篇文章可以有所收获。创建一个数据库的方法是：可以在登录mysql服务后，使用create命令来创建。具体操作是：1、执行命令【m…