基于sysbench-0.5的MySQL自动化压测及分析方案

113次阅读

共计 6497 个字符，预计需要花费 17 分钟才能阅读完成。

本文是作者工作中需要对 atlas（360 开源的 MySQL 中间件，可实现读写分离、分表、多从库负载均衡）进行测试时所设计和采用的一套脚本。由于对 atlas 测试，要测试对比的维度较多，脚本也相对复杂，这里我将脚本简化为适用于简单的 mysql 压测（当然若有同样需求的亲，作者也会将脚本共享，一起探讨哦）。总体思路如下：

准备测试数据（这步在脚本之外）—- 运行脚本测试（多线程，测试多次）—- 脚本对每次测试输出过滤并格式化后写入数据库 —- 脚本通过 sql 语句将结果直观展示出来（该结果很容易导入 excel，绘制趋势图）

其实我承认这个标题写的有些“大了”，该套脚本并未实现完全自动化的实现准备数据、测试、分析、绘图的整个流程（关于绘图，gnuplot 完全可以基于格式化的分析结果自动绘制出趋势图。这个惭愧，小主没搞定啊~~）

下面为“测试”和“分析”的使用截图，将分析结果粘贴到记事本然后导入到 excel 即可绘制趋势图。

基于 sysbench-0.5 的 MySQL 自动化压测及分析方案

接下来说说 sysbench-0.5，对于数据库的测试较 0.4 版本有较大不同，之前有内建的 –test=oltp 方法，现在改成了外部的 lua 脚本形式，这样更灵活，也方便用户构建自己的测试模型。

这些相关的 lua 脚本位于”/usr/share/doc/sysbench/tests/db/“目录，其内脚本如下图所示

基于 sysbench-0.5 的 MySQL 自动化压测及分析方案

我们需要了解我们最有可能用到的三个脚本：common.lua（公共脚本）、oltp.lua（oltp 测试主脚本）和 parallel_prepare.lua（并行准备数据）。common.lua 中定义了一些选项的默认值（故而，这些选项的值既可以通过命令行指定也可直接修改该脚本里对应值来更改）.

简单说一下 oltp.lua 脚本的逻辑:

默认通过显式的使用 begin 和 commit 语句将如下模式的 sql 组合在一起形成一个事务（只读测试的话则没有写请求），然后将此事务循环执行 oltp_table_size 次（若没有指定，则为 10000 次）。也就是如果测试命令中不加 –oltp-table-size=, 则只读测试共 14w 请求，混合测试 18w 请求。若觉得数量不够，可以设置 –oltp-table-size=100000 或者更高。

10 条 SELECT c FROM sbtest6 WHERE id=5047;

1 条 SELECT c FROM sbtest16 WHERE id BETWEEN 5050 AND 5050+99;

1 条 SELECT SUM(K) FROM sbtest7 WHERE id BETWEEN 5039 AND 5039+99;

1 条 SELECT c FROM sbtest7 WHERE id BETWEEN 4987 AND 4987+99 ORDER BY c;

1 条 SELECT DISTINCT c FROM sbtest7 WHERE id BETWEEN 13 AND 13+99 ORDER BY c;

1 条 UPDATE sbtest1 SET k=k+1 WHERE id=1234;

1 条 UPDATE sbtest2 SET c=’78864443858-59732318638′ where id=2345;

1 条 DELETE FROM sbtest11 WHERE id=4958;

1 条 INSERT 语句;

以上是通过 lua 脚本里总结出来的，各位也可查看下这些 lua 脚本，来更好的理解测试的逻辑过程。

一般来说，对 MySQL 做压测会基于两种需求：

一种是通过压测来大致评估 MySQL 实例的最大能力，这种适合给定时长来测；

另一种就是来对比某些改动前后的性能变化（如版本升级、参数调整等），这种适合给定请求数来测。

以作者的小经验来看，后者要更多一些，所以我的测试模式也是趋向于后者的。

前提功课做好了，接下来一起看一下本例的测试过程

准备数据 ：

在被测的两台 mysql 上分别执行

# 以 8 线程并发创建 16 张 50w 数据的表
sysbench --test=/usr/share/doc/sysbench/tests/db/parallel_prepare.lua \
         --mysql-table-engine=innodb --oltp-table-size=500000 --mysql-user=user \
         --mysql-password='passwd' --mysql-port=3306 --mysql-host=192.168.1.33 \
         --oltp-tables-count=16 --num-threads=8 run

还有另外一种方式，用 oltp.lua 脚本以串行方式准备数据

sysbench --test=/usr/share/doc/sysbench/tests/db/oltp.lua --mysql-table-engine=innodb \
         --oltp-table-size=500000 --mysql-user=user --mysql-password='passwd' \
         --mysql-port=3306 --mysql-host=192.168.1.33 --oltp-tables-count=16 prepare

开始测试 ：

sh /root/shells/mysql_oltp_test.sh test read-only 192.168.1.44 3306 user passwd

下面为脚本内容，注释挺详细，我想就无需多说了。

#!/bin/sh

#定义记录测试结果的 mysql 的连接相关参数，本例我在测试机上记录测试结果
m_user='test'
m_passwd='test'
m_port='3307'
m_host='127.0.0.1'

#定义测试线程
threds_num='4 8 16 32 48 64 96 128 160'

#测试函数
sb_test() {

  #定义测试方式相关变量
  tables_count=16    #测试表的数量
  if ["$2" == "read-only"];then read_only='on';else read_only='off';fi    #根据脚本参数确定是否 read-only

  echo -e "\n---------------\n 测试 $3, $2 模式 \n---------------"

  for i in 1 2 3;do    #开始测试, 每种条件测 3 次, 分析时取平均值
    echo

    for sb_threds in $threds_num;do    #按照指定的 sysbench 线程测试
      printf "%-10s %s\n" $sb_threds 线程 第 $i 次运行...
      
      #result 作为每次最小测试单元的结果，根据 sysbench 测试结果各参数的出现顺序，以 request_read、request_write、request_total、request_per_second、total_time、95_pct_time 为顺序插入表中。下条命令中，egerp 之后的操作是为了对 sysbench 的输出做筛选和格式化，以便插入数据库
      result=$(sysbench --test=/usr/share/doc/sysbench/tests/db/oltp.lua --mysql-user=$5 --mysql-password=$6 --mysql-port=$4 --mysql-host=$3 \
               --oltp-tables-count=$tables_count --num-threads=$sb_threds run --oltp-skip-trx=on --oltp-read-only=$read_only  |  \
               egrep -w "read|write|read/write|total\ time:|approx"|awk '{printf $0}'|tr -s [:space:]| \
               sed -e 's/approx\.\ 95//g' -e 's/sec\.//g' -e 's/[a-zA-Z:\(\)/]//g'|sed "s/[^]\+/'&',/g"|sed 's/,$//g')
    
      #测试完成后立刻记录系统一分钟负载值，可近似认为测试过程中的负载抽样
      load=$(ssh -p1022 $3 "uptime|awk -F:'{print \$NF}'|awk -F,'{print \$1}'")

      #创建记录测试信息的表
      mysql -u$m_user -p$m_passwd -P$m_port -h$m_host <<EOF 2> /dev/null
      CREATE TABLE IF NOT EXISTS test.sysbench_test (server_name varchar(15) NOT NULL COMMENT '被测 DB name',
        test_type varchar(15) NOT NULL COMMENT 'read-only,read-write,insert 等',
        sb_threads int(11) NOT NULL DEFAULT '0' COMMENT 'sysbench 测试线程',
        server_load decimal(12,2) NOT NULL DEFAULT '0.00' COMMENT '以当前线程测试完后立刻记录一分钟负载值',
        request_total int(11) NOT NULL DEFAULT '0',
        request_read int(11) NOT NULL DEFAULT '0',
        request_write int(11) NOT NULL DEFAULT '0',
        request_per_second decimal(12,2) NOT NULL DEFAULT '0.00',
        total_time decimal(12,2) NOT NULL DEFAULT '0.00' COMMENT '单位秒',
        95_pct_time decimal(12,2) NOT NULL DEFAULT '0.00' COMMENT '单位毫秒'
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
EOF
      #本次测试结果写入数据库
      mysql -u$m_user -p$m_passwd -P$m_port -h$m_host <<EOF 2> /dev/null
      INSERT INTO test.sysbench_test (server_name,test_type,sb_threads,server_load,request_read,request_write,request_total,request_per_second,total_time,95_pct_time) 
      VALUES ('$3','$2','$sb_threds','$load',$result);
EOF
    
      if [$? -ne 0];then
          echo "\n----------$sb_threds 线程测试，第 $i 次插入数据库时失败 ----------"
          exit -2
      fi
      sleep 60    #让库歇一会，也让一分钟负载能够恢复到测试前的值
    done
    
  done
}

#结果分析函数
sb_analyse() {

  mysql -u$m_user -p$m_passwd -h$m_host -P$m_port  <<EOF
  SELECT 
    server_name,
    test_type,
    sb_threads,
    convert(avg(server_load),decimal(12,2)) as server_load,
    convert(avg(request_total),decimal(12,0)) as request_total,
    convert(avg(request_read),decimal(12,0)) as request_read,
    convert(avg(request_write),decimal(12,0)) as request_write,
    convert(avg(request_per_second),decimal(12,2)) as request_per_second,
    convert(avg(total_time),decimal(12,2)) as total_time,
    convert(avg(95_pct_time),decimal(12,2)) as 95_pct_time
  FROM test.sysbench_test where sb_threads in ($(echo $threds_num|sed -e 's/[^]\+/&,/g' -e 's/,$//g')) group by server_name,test_type,sb_threads
EOF

}

#脚本使用说明 / 参数判断（这里我小偷懒了一下，对输入只做了些基本的判断）if [$# -eq 0];then
  echo -e "\n---------------------------------------"
  echo -e "测试：请在脚本后跟上 test  test_type  mysql_host  mysql_port  mysql_user  mysql_password 6 个参数！"
  echo -e "test_type: read-only 或 read-write, 表示测试模式"
  echo -e "其余 4 参数表示待测试 MySQL 连接相关信息, 密码若包含特殊字符, 将其置于单引号内"
  echo -e "---------------------------------------"
  echo -e "分析：请在脚本后跟上 analyse"
  echo -e "---------------------------------------\n"
  exit -1
elif ["$1" == "test"] & [$# -eq 6];then
  sb_test $1 $2 $3 $4 $5 $6
elif ["$1" == "analyse"] & [$# -eq 1];then
  sb_analyse
else
  echo -e "参数不正确，请检查"
fi

### by ljk 2016/3/1

结果分析 ：

sh /root/shells/mysql_oltp_test.sh analyse

将打印出的结果复制到记事本，然后作为数据源导入到 excel 中，即可按需作图啦（暂时没能引用 gnuplot 直接将结果绘制成图，再次小遗憾一下！）

分享一下我对单台 mysql 和 atlas（一主一从和一主两从）进行的压测结果

清空测试数据 ：

sysbench --test=/usr/share/doc/sysbench/tests/db/parallel_prepare.lua \
--mysql-user=user --mysql-password='passwd' --mysql-port=3306 \
--mysql-host=192.168.1.22 --oltp-tables-count=16 --num-threads=8 cleanup

本文永久更新链接地址 ：http://www.linuxidc.com/Linux/2016-10/136105.htm

正文完

星哥说事-微信公众号