数据库MySQL的性能基线收集及压力测试

建立基线的作用：
计算机科学中，基线是项目储存库中每个工件版本在特定时期的一个“快照”。

比如我们现在有并发事物，那么在某时刻发起一个事物会产生当前数据的快照，那么这个快照就相当理解为一个基线，那么所谓的性能数据的基线就是正常数据收集后的一段时间或者业务数据的负载，将它提供一个正式标准，随后的工作基于此标准，并且只有经过授权后才能变更这个标准。建立一个初始基线后，以后每次对其进行的变更都将记录为一个差值，直到建成下一个基线。

什么是基线

1.当前运行状态记录、快照，其作用是将其与未来的状态做对比，那么未来的时刻产生的关键时刻的新状态则作为下一次的状态做对比

2.未来时刻产生关键事件后的新状态，作为下一个基线

基线收集，需关注的重点

1.系统负载
2.MySQL运行状态

记录以上两个重点以便用于优化，比如做一次优化之前先记录一周的状态，比如最高、最低、平均等

优化的工作结束之后，我们再去逐渐观察，这样我们就能知道大概情况是如何

但是有些时候有些误区，比如在优化之前tps可能1000 ，优化后可能还是1000，但是但从tps来看的话可能没有什么，但是我们还需要关注它的iops等信息有所下降，这也是一种优化，所以要多纬度的去对比

建议收集基线数据的状态信息（需要关注的瓶颈）

系统层需关注的信息：

·CPU：%user、%idle、%sys、%iowait      #最通用的几个指标
·IO：tps、await、svctm、%util

·内存：free（free、shared、buffers、cached）、used，以及swap

内存的利用率越多越好，所以我们主要关注空闲、使用、还有swap ，si so

当前是否使用swap 是否频繁使用swap 也需要关注     

数据库层需关注的信息：

MySQL：tps、rt、lock、hit ratio、waits

rt = response time         
lock = row lock、table lock
hit ratio = cache/buffer hit ratio
waits = Innodb_buffer_pool_wait_free / Innodb_log_waits / Table_locks_waited / Innodb_row_lock_current_waits / Innodb_row_lock_waits

在找瓶颈的时候优先这几个地方进行分析，针对分析结果再定义优化方案

工作成果量化

所谓的量化，其实无非是对比（相同条件下、相同场景下）

相同条件下：比如说同一套硬件，以前之能跑tps到1000，但是优化后tps能达到5000，这是很大的变化

相同场景下：之前的资源利用率是10% 但优化后降到了5%

相同业务负载下，基线数据产生变化

比如同样的用户量 同样的设备，经过优化的之后，tps是降低还是提升，util、cpu等

相同基线数据情况下，业务数据发生变化

建立目标，考证方案可行及总结

我们需要建立一套标准来验证我们的方法是否可行
·建立目标
·针对当前基线中存在的瓶颈进行优化
·常见瓶颈以IO为主（磁盘IO、网络IO）
·制定相对应的方案，找到优化的尝试路径
·CPU：%user、%idle、%sys、%iowait
·IO：tps、await、svctm、%util
·内存：free（free、shared、buffers、cached）、used，以及swap
·MySQL：tps、rt、lock、hit ratio、waits

假设做的优化，针对IO 那么重点记录的是io相关的情况，然后在优化完再判断util等io相关的数值是否产生了变化

也就是说优化工作是需要针对当前基线数据中的瓶颈进行的，

通常的瓶颈，目前来说还是磁盘IO和网络IO

优化IO途径的建议：

1. 换SSD，PCIE-SSD（提高IO效率，普通SAS盘5000以内的iops，而新设备可达到数万或者数十万iops）
2. 少做IO的活（合并多次读写为一次，或者前端加内存CACHE；或者优化业务，消除IO）
3. 加大内存（更多hot data和dirty data放在内存中，减少物理IO）
4. 调整文件系统为xfs（相比ext3、ext4提高IOPS能力，高io负载下表现更佳）
5. 调整raid级别为raid 1+0（相比raid1、raid5等提高IOPS能力）
6. 调整写cache策略为wb或force wb（利用阵列卡cache，提高iops）
7. io scheduler（优先使用deadline，如果是SSD，则使用noop

所以我们想要优化IO（磁盘IO或网络IO，）的瓶颈，通常是只要能用钱解决的事情通常就不是事情了，换个设备通常比大量时间花费在优化上要来的强

网络IO很少，因为通常情况下mysql的tps实际能达到1-2000算是不错的，除非用高配服务器，那么这种情况下它的网络交互也不是问题，所以这种情况下网络IO一般不会成为瓶颈，如果是高配的话，一般网卡的性能要提高上去，比如多网卡绑定的方式，或者换成万兆网卡，正常情况下千兆网卡就够了

在找瓶颈的时候优先这几个地方进行分析，针对分析结果再定义优化方案

通用的优化方案
·CPU：更换更好（将主频更高一些）、更多核心的CPU（主要看硬件不够用还是核心数不够用，比如频繁切换cpu）

比如在在多实例的情况下，最好是有多核心的cpu，另一种，如果运算任务比较重的话这时我们还同时需要提高单CPU的运算能力，也就是提高主频

·IO：更换IOPS性能更高的设备，例如SSD，PCI-E SSD

·内存：增加内存，合理分配

·MySQL服务：升级版本，使用Percona/MariaDB分支版本以支持更高TPS或者降低锁竞争粒度（主要是降低锁的力度，将大锁拆成小锁，这样可以观测其平衡如何，如果这些小锁拆分比较好，会带来比较大的tps的提升，或者个别参数的调整，因为有可能我们某些参数设置不当而导致tps上不去也是有可能的）

通常情况下优先升级IO设备，比如硬盘，以前的硬盘用的是7200转的盘，现在将其更换为SATA硬盘，或则直接换SSD等；内存也是一样的，直接加内存即可，这样可以有效的利用内存来缓存数据减少IOPS，提高整体TPS

mysql的压力测试

基准压力测试目的
·采购新设备，评估新设备性能

抛去设备的新旧架构等，我们需要对其进行评估新设备的性能

·开发新项目，评估数据库容量

新项目上线，我们需要评估新项目存在哪些瓶颈

·新系统上线前，预估/模拟数据库负载

比如新的操作系统，比如centos7 ，那么我们想试一试换成7的话性能与以前的版本的性能差别在哪里

而且还需要模拟新项目的并发等，我们需要判断一下我们的单台服务器能否扛住多少，多台服务器能扛住多少

·更换数据库版本，评估性能变化

·除了性能，还需要关注可靠性

数据库运行一段时间是否稳定，是否可持续

如果发现一些异常，比如数据库不释放内存等也是非常麻烦的事情，最怕的就是内存溢出

测试模型设计

·明确测试的核心目标、诉求

测试新版本性能/可靠性

测试新系统性能/可靠性

测试新机器性能/可靠性 

测试新业务性能

·排除干扰，专注测试目的

不要跑额外的服务，导致性能受到影响

·确定测试环境

构建一个合理、合适、科学的测试环境，不会和现实环境差距太大，硬件、系统、配置相当

·确定测试过程中的衡量和变量

每一次对比测试循环中，只变更少数因素，不要一次性变更太多因素

·保证测试结果的可重复性

保证每个循环都至少有3次测试，每次持续至少30分钟，排除最好和最差的测试结果

压测需要注意事项
·不能只在本地进行压测

压力测试工具不能跟数据库在一起，因为压力测试工具本身就会产生一些负载，也会产生一些cpu或者消耗一些内存，这样就不科学，所以建议将其分割开来

·压测数据量小

内存可能有32G，但是压力测试数据可能有10G，这样它会可能将数据全部放在内存里面，那如果将所有数据放在内存里

·压测时间过短

瓶颈刚出现的时候压测已经结束了，肯定是不靠谱的

·压测模式太少

肯定要求有比较复杂的读写、随机读写、顺序读写等分别都要压力测试

·压力负载过大或过小

通常需要关注的值：

    %user, %iowati, %util, svctm, iops, tps

尤其是 ： %user, %iowati, %util, svctm rt（响应时间） 不要过大

· 每轮测试完毕要净化环境，如果有条件的话要将数据重新生成一次，如果没有条件的话无论如何要清理cache并重启mysqld：

     #清除 OS cache

     echo 3 > /proc/sys/vm/drop_caches

     service mysqld restart

MySQL测试工具的使用

常用测试工具：
·sysbench
Primarily for MySQL OLTP benchmarking，By MySQL AB

老牌测试工具，如果只做一些cpu测试倒是可以使用这个工具，模拟模式以及表的模拟稍微偏简单一些

主要针对于：cpu、threads、mutex、memory、fileio、oltp

·tpcc-mysql（重点）
Primarily for MySQL OLTP benchmarking，By Percona

·tpch
Primarily for OLAP benchmarking

·tcpcopy
  模拟生产环境真实请求

·其他
   mysqlslap
   sql-bench

以上工具出现的常见场景：

·fio做专业的IO压力测试

·用tpcc-mysql做MySQL的OLTP压力测试

·用tcpcopy做在线压力模拟测试

比如我们当前业务每秒的交易数达到一千个，现在想模拟每秒交易数为一万个，一种是可以写一个脚本进行模拟，另一种使用tcpcopy将线上用户的请求引入到某个测试环境下，比如当前有10台服务器将其全部引到某个服务器上，比如以前有10台服务器而测试机只有一台服务器，现在将它全部引到某个测试服务器上，相当于压力瞬间大了10倍

或者将线上的前端服务器上的请求复制到其他服务器上然后这些前端服务器将压力施加到测试服务器上，也是可以的

使用tpcc-mysql对数据库做压力测试

TPC-C是专门针对联机交易处理系统（OLTP系统）的规范，一般情况下我们也把这类系统称为业务处理系统。

tpcc-mysql是percona基于TPC-C(下面简写成TPCC)衍生出来的产品，专用于MySQL基准测试。其源码放在launchpad上，用bazaar管理。

下载tppc-mysql

官方下载：
安装epel源后以便安装bzr客户端：

[root@mysql_node1 tools]# wget http://dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm

[root@mysql_node1 tools]# rpm -ivh epel-release-6-8.noarch.rpm

然后就可以开始安装bzr客户端了：

[root@mysql_node1 tools]#  yum install bzr

之后，就可以开始用bzr客户端下载tpcc-mysql源码了

cd /tmp
bzr branch  percona-dev/perconatools/tpcc-mysql

或使用中转下载：

wget  http://imysql.com/wp-content/uploads/2014/09/tpcc-mysql-src.tgz

tpcc-mysql的主要工作流程

主要是模拟交易，流程如下：

首先需要订单--产生支付--仓库发货--物流--确认收货

等类似于电商的过程，然后来模拟高并发来完成一个典型电商的业务

tpcc-mysql的业务逻辑及其相关的几个表作用如下：
  ·New-Order：新订单，主要对应 new_orders 表
  ·Payment：支付，主要对应 orders、history 表
  ·Order-Status：订单状态，主要对应 orders、order_line 表
  ·Delivery：发货，主要对应  order_line 表
  ·Stock-Level：库存，主要对应 stock 表
其他相关表：
·客户：主要对应 customer 表
·地区：主要对应 district 表
·商品：主要对应 item 表
·仓库：主要对应 warehouse 表

我们做的只需要指定仓库的数量即可，比如一个仓库支撑多少个区域的用户等

安装tpcc-mysql

安装过程无非就是解压并执行make 即可

无论是使用源码包、rpm、二进制包 只要能达到目的即可，根据自己需要来进行安装

[root@mysql_node1 tpcc-mysql]# cd /tmp/

[root@mysql_node1 tmp]# wget http://imysql.com/wp-content/uploads/2014/09/tpcc-mysql-src.tgz

[root@mysql_node1 tmp]# tar xf tpcc-mysql-src.tgz

[root@mysql_node1 tpcc-mysql]# ll

total 36

-rw-r--r--. 1 root root  851 Sep 14 15:05 README

-rw-r--r--. 1 root root 1621 Sep 14 15:05 add_fkey_idx.sql

-rw-r--r--. 1 root root  317 Sep 14 15:05 count.sql

-rw-r--r--. 1 root root 3105 Sep 14 15:05 create_table.sql

-rw-r--r--. 1 root root  763 Sep 14 15:05 drop_cons.sql

-rw-r--r--. 1 root root  477 Sep 14 15:05 load.sh

drwxr-xr-x. 2 root root 4096 Oct 20 15:51 schema2

drwxr-xr-x. 5 root root 4096 Oct 20 15:51 scripts

drwxr-xr-x. 2 root root 4096 Oct 20 15:51 src

[root@mysql_node1 tpcc-mysql]# make

TPCC压测前的准备

初始化测试库环境

[root@mysql_node1 tmp]# cd /tmp/tpcc-mysql

[root@mysql_node1 tpcc-mysql]# ls

README  add_fkey_idx.sql  count.sql  create_table.sql  drop_cons.sql  load.sh  schema2  scripts  src

创建库并导入表结构

[root@mysql_node1 tpcc-mysql]# mysqladmin -uroot -p 'mypass' -f create  tpcc1000

[root@mysql_node1 tpcc-mysql]# mysql -uroot -pmypass -f tpcc1000 < create_table.sql

初始化完毕后，就可以开始加载测试数据了
tpcc_load用法如下：

tpcc_load [server] [DB] [user] [pass] [warehouse]

或者

tpcc_load [server] [DB] [user] [pass] [warehouse] [part] [min_wh] [max_wh]

选项 warehouse 意为指定测试库下的仓库数量。

如果默认不是3306端口的话可以使用以下方式：

#由于是虚机所以这里先创建30个库，一般建议在实际生成环境上创建的库要大于500

[root@node1 tpcc-mysql]# ./tpcc_load 10.12.33.61:3306 tpcc1000 root mypass 30

*************************************

*** ###easy### TPC-C Data Loader  ***

*************************************

<Parameters>

     [server]: 10.12.33.61

     [port]: 3306

     [DBname]: tpcc1000

       [user]: root

       [pass]: mypass

  [warehouse]: 100

TPCC Data Load Started...

Loading Item

.................................................. 5000

.................................................. 10000

###略####

如果不是使用tcp/ip方式来连接数据库的话，它默认会直接读取本地mysql.sock文件

所以我们最好使用ip:prot的方式来连接数据库

500 为初始化多少个库的数据量,这里由于是虚拟机所以量稍微少一些

进行TPCC测试

tpcc_start 工具用于tpcc压测，其用法如下

tpcc_start -h server_host -P port -d database_name -u mysql_user -p mysql_password \
    -w warehouses -c connections -r warmup_time -l running_time \
    -i report_interval -f report_file

参数解释

-w     指定仓库数量
-c     指定并发连接数
-r     指定开始测试前进行warmup的时间，进行预热后，测试效果更好
-l     指定测试持续时间
-i     指定生成报告间隔时长
-f     指定生成的报告文件名

-r     指定开始测试前进行warmup的时间，进行预热后，测试效果更好
-l     指定测试持续时间

一个完整的测试案例

首先在数据库进行授权

mysql> GRANT ALL ON *.* TO 'root'@'%' IDENTIFIED BY 'mypass';

使用tpcc进行压测

#由于是虚机所以这里先创建30个库，一般建议在实际生成环境上创建的库要大于500

#tpcc_start -hlocalhost -d tpcc1000 -u tpcc_user -p "tpcc_password" -w 30 -c 32 -r 120 -l 3600 \

  -f tpcc_mysql_20140921.log >> tpcc_caseX_$(date +%F).log 2>&1

将其进行简化：

[root@node1 tpcc-mysql]# ./tpcc_start -h10.12.33.61 -d tpcc1000 -u root -p 'mypass' -w 30 -c 32 -r 120 -l 1800 >> /tmp/tpcc_caseX_$(date +%F).log 2>&1

以上的意思是只将结果输出出来没有必要将整个过程输出, 3600 表示1小时

查看输出的结果

本轮tpcc压测的一些基本信息
[root@node1 ~]# more /tmp/tpcc_caseX_$(date +%F).log

***************************************

*** ###easy### TPC-C Load Generator ***

***************************************

option h with value '10.12.33.61'           #目标主机

option d with value 'tpcc1000'              #需要进行压测的数据库

option u with value 'root'                  #用户名

option p with value 'mypass'                #密码

option w with value '30'                    #数据库数量

option c with value '32'                    #并发线程数量

option r with value '120'                   #预热时长

option l with value '1800'                  #压测总时长，半小时

<Parameters>

     [server]: 10.12.33.61

     [port]: 3306

     [DBname]: tpcc1000

       [user]: root

       [pass]: mypass

  [warehouse]: 30

[connection]: 32

     [rampup]: 120 (sec.)

    [measure]: 1800 (sec.)

RAMP-UP TIME.(120 sec.)

预热结束并且开始进行压测，为了模拟过程所以缩短了一点

MEASURING START.

#每10秒钟输出一次压测数据

  10, 117(104):17.862|21.403, 108(1):4.954|6.161, 10(0):3.733|5.880, 12(0):19.999|41.257, 13(13):19.999|54.707

  20, 104(92):14.839|16.684, 106(0):2.932|4.367, 12(0):1.042|1.083, 10(0):15.888|16.643, 12(12):19.999|47.363

  30, 94(89):15.864|19.204, 101(0):3.613|4.152, 9(0):0.999|1.039, 9(0):19.232|20.073, 8(8):19.999|40.352

  40, 105(89):14.086|15.177, 99(0):3.690|4.408, 9(0):1.180|2.024, 12(0):19.999|21.756, 11(11):19.999|46.144

#############以下略过################

分别表示的意思：

总共6列 以逗号进行分隔

-- 第一列，第N次10秒

-- 第二列，总成功执行压测的次数(总推迟执行压测的次数):90%事务的响应时间|本轮测试最大响应时间
-- 第三列，新订单业务成功执行次数(推迟执行次数):90%事务的响应时间|本轮测试最大响应时间
-- 第四列，支付业务的结果，后面几个的意义同上
-- 第五列，发货业务的结果，后面几个的意义同上
-- 第六列，库存业务的结果，后面几个的意义同上

#如果超过5毫秒就会发生一次推迟

压测结束结果解读

[root@node1 ~]# tail -100  /tmp/tpcc_caseX_$(date +%F).log

找到以下结果信息

<Raw Results>

  [0] sc:1264  lt:14714  rt:0  fl:0         # New-Order，新订单业务成功(success,简写sc)次数，延迟(late,简写lt)次数，重试(retry,简写rt)次数，失败(failure,简写fl)次数

  [1] sc:15834  lt:138  rt:0  fl:0          # Payment，支付业务统计，其他同上

  [2] sc:1580  lt:17  rt:0  fl:0            # Order-Status，订单状态业务统计，其他同上

  [3] sc:1598  lt:0  rt:0  fl:0             # Delivery，发货业务统计，其他同上

  [4] sc:0  lt:1602  rt:0  fl:0             # Stock-Level，库存业务统计，其他同上

in 1800 sec.

#一次的采样结果是不可信的

#以下第二次粗略统计结果，其他同上

<Raw Results2(sum ver.)>

  [0] sc:1264  lt:14714  rt:0  fl:0    

  [1] sc:15835  lt:138  rt:0  fl:0

  [2] sc:1580  lt:17  rt:0  fl:0

  [3] sc:1598  lt:0  rt:0  fl:0

  [4] sc:0  lt:1602  rt:0  fl:0

下面所有业务逻辑结果都必须为 OK 才行，如果哪怕有一个不是OK那么久是NG，如果有任何一个结果是NG的话表明本次结果是不能采信的，

<Constraint Check> (all must be [OK])

[transaction percentage]

        Payment: 43.46% (>=43.0%) [OK]      #支付成功次数(上述统计结果中 sc + lt)总成功+延迟的结果 必须大于43.0%，否则结果为NG，而不是OK，所以如果不是大于43%那么结果也是不能采信的

   Order-Status: 4.35% (>= 4.0%) [OK]       #订单状态，其他同上

       Delivery: 4.35% (>= 4.0%) [OK]       #发货

    Stock-Level: 4.36% (>= 4.0%) [OK]       #库存

[response time (at least 90% passed)]      #响应耗时指标必须超过90%通过才行，所谓的相应时常就是小于5毫秒的测试标准，超过5毫秒肯定是不可以的，如果有10%以上的响应时常说明本次结果不可采信的

#下面几个响应耗时指标全部 100% 通过才可以，如果低于90%的话，那么肯定是NG ，我这里出现了2个NG

        New-Order: 7.91%  [NG] *           

        Payment: 99.14%   [OK]

        Order-Status: 98.94%  [OK]

        Delivery: 100.00%   [OK]

        Stock-Level: 0.00%  [NG] *

<TpmC>

                 532.600 TpmC           # tpmc表示每分钟的tps