mysql与mariadb性能测试方法

本方法来自于阿里云的MySQL性能白皮书，原文地址：https://help.aliyun.com/document_detail/35264.html?spm=a2c4g.11174359.6.771.pUKh2n

但是我在实践过程中发现，一些细节部分由于多出了空格，导致方法并不能使用，曾经提交给阿里云客服但是没有回应。

本篇文章意在给予读者正确的使用方法和所得结果的参数分析。

首先创建测试机（我是在openstack云上做的这些操作，所以是创建了trove的mysql实例，mysql实例作为测试机可以测试mysql和mariadb实例。），然后创建被测试机（mysql或mariadb实例）。我们会由测试机发起测试，以得出被测试机的性能。

在测试机上完成以下操作：

下载sysbench

git clone https://github.com/akopytov/sysbench.git
cd sysbench/

切换到0.5的分支：

git checkout -b fix-EAS-8389 remotes/origin/0.5

更新源（阿里源，用来下载其他的包）:

vi /etc/apt/sources.list
deb http://mirrors.aliyun.com/ubuntu/ trusty main restricted universe multiverse
deb http://mirrors.aliyun.com/ubuntu/ trusty-security main restricted universe multiverse
deb http://mirrors.aliyun.com/ubuntu/ trusty-updates main restricted universe multiverse
deb http://mirrors.aliyun.com/ubuntu/ trusty-proposed main restricted universe multiverse
deb http://mirrors.aliyun.com/ubuntu/ trusty-backports main restricted universe multiverse
deb-src http://mirrors.aliyun.com/ubuntu/ trusty main restricted universe multiverse
deb-src http://mirrors.aliyun.com/ubuntu/ trusty-security main restricted universe multiverse
deb-src http://mirrors.aliyun.com/ubuntu/ trusty-updates main restricted universe multiverse
deb-src http://mirrors.aliyun.com/ubuntu/ trusty-proposed main restricted universe multiverse
deb-src http://mirrors.aliyun.com/ubuntu/ trusty-backports main restricted universe multiverse

sudo apt-get update

安装需要的包：

sudo apt-get install libmysqlclient-dev
sudo apt-get install automake autoconf automake make libtool bzr

安装sysbench

./autogen.sh
./configure --prefix=/usr --mandir=/usr/share/man
make
make install

至此测试机上的准备工作完成，然后在被测试机上做准备工作（没有前端管理界面的可以自行通过mysql命令行进行创建，这里不再赘述）。

1、通过前端页面在被测试机上创建一个数据库
2、通过前端页面在被测试机上创建一个用户，并且赋予用户对步骤1创建的数据库的权限。

阿里测试方法
1、准备数据（注意修改mysql-host，mysql-user，mysql-password，mysql-db）：

sysbench --num-threads=32 --max-time=3600 --max-requests=999999999 --test=oltp.lua --oltp-table-size=10000000 --oltp-tables-count=64 --db-driver=mysql --mysql-table-engine=innodb --mysql-host=192.168.23.128 --mysql-port=3306 --mysql-user=admin --mysql-password=123 --mysql-db=nova prepare

开始测试压力性能（注意修改mysql-host，mysql-user，mysql-password，mysql-db）：

sysbench --num-threads=32 --max-time=3600 --max-requests=999999999 --test=oltp.lua --oltp-table-size=10000000 --oltp-tables-count=64 --db-driver=mysql --mysql-table-engine=innodb --mysql-host=192.168.23.128 --mysql-port=3306 --mysql-user=admin --mysql-password=123 run

清除数据（注意修改mysql-host，mysql-user，mysql-password，mysql-db）：

sysbench --num-threads=32 --max-time=3600 --max-requests=999999999 --test=oltp.lua --oltp-table-size=10000000 --oltp-tables-count=64 --db-driver=mysql --mysql-table-engine=innodb --mysql-host=192.168.111.177 --mysql-port=3306 --mysql-user=admin --mysql-password=123 --mysql-db=nova cleanup

结果分析：

sysbench 0.5: multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 8
Report intermediate results every 10 second(s)
Random number generator seed is 0 and will be ignored

Threads started!
– 每10秒钟报告一次测试结果，tps、每秒读、每秒写、99%以上的响应时长统计
[ 10s] threads: 8, tps: 1111.51, reads/s: 15568.42, writes/s: 4446.13, response time: 9.95ms (99%)
[ 20s] threads: 8, tps: 1121.90, reads/s: 15709.62, writes/s: 4487.80, response time: 9.78ms (99%)
[ 30s] threads: 8, tps: 1120.00, reads/s: 15679.10, writes/s: 4480.20, response time: 9.84ms (99%)
[ 40s] threads: 8, tps: 1114.20, reads/s: 15599.39, writes/s: 4456.30, response time: 9.90ms (99%)
[ 50s] threads: 8, tps: 1114.00, reads/s: 15593.60, writes/s: 4456.70, response time: 9.84ms (99%)
[ 60s] threads: 8, tps: 1119.30, reads/s: 15671.60, writes/s: 4476.50, response time: 9.99ms (99%)
OLTP test statistics:
queries performed:
read: 938224 – 读总数
write: 268064 – 写总数
other: 134032 – 其他操作总数(SELECT、INSERT、UPDATE、DELETE之外的操作，例如COMMIT等)
total: 1340320 – 全部总数
transactions: 67016 (1116.83 per sec.) – 总事务数(每秒事务数)TPS
deadlocks: 0 (0.00 per sec.) – 发生死锁总数
read/write requests: 1206288 (20103.01 per sec.) – 读写总数(每秒读写次数)QPS
other operations: 134032 (2233.67 per sec.) – 其他操作总数(每秒其他操作次数)

General statistics: – 一些统计结果
total time: 60.0053s – 总耗时
total number of events: 67016 – 共发生多少事务数
total time taken by event execution: 479.8171s – 所有事务耗时相加(不考虑并行因素)
response time: – 响应时长统计
min: 4.27ms – 最小耗时
avg: 7.16ms – 平均耗时
max: 13.80ms – 最长耗时
approx. 99 percentile: 9.88ms – 超过99%平均耗时

Threads fairness:
events (avg/stddev): 8377.0000/44.33
execution time (avg/stddev): 59.9771/0.00

https://help.aliyun.com/document_detail/35264.html?spm=a2c4g.11174359.6.771.pUKh2n