AI 助理
文档备案控制台
开发者社区 数据库 文章 正文

基于sysbench-0.5的MySQL自动化压测及分析出图

2017-11-16 1944
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介：

  本文是作者工作中需要对atlas（360开源的mysql中间件，可实现读写分离、分表、多从库负载均衡）以及后期对proxysql进行测试时所设计和采用的一套脚本。由于对中间件测试，要测试对比的维度较多,所以尽量将涉及到的因素都纳入脚本中以实现自动化的压测和分析过程。总体思路如下：

准备测试数据（这步在脚本之外）----运行脚本测试（线程数脚本内指定，每个条件测试三次）----脚本对每次测试输出过滤并格式化后写入数据库----脚本加“分析参数”将结果直观展示出来----亦或直接加“画图参数”画出图形（画图基于gnuplot）

下面为脚本使用说明截图

帮助信息：

wKioL1gA9_vBi8sVAAUi_ih9Afw578.png

进行测试：

wKiom1bYAeOA1GxwAAKll2FSJjs327.png

查看测试结果：

wKioL1gA_FWQYlLhAAxXSIWw6KQ525.png

对结果进行画图：

wKiom1gA-i_DtcQhAAB_1VY5wcQ438.png

效果图展示：

wKioL1gA-33y64FqAAAfUIYcEh0352.png

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

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

wKiom1bW8M-yS4DpAAAoCWAEXJI478.png

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

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

默认通过显式的使用begin和commit语句将如下模式的sql组合在一起形成一个事务（只读测试的话则没有写请求）

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 语句;

然后将此事务循环执行10000次。也就是只读测试共14w请求，混合测试18w请求。若觉得数量不够，可以修改common.lua中的设置

function set_vars()
   oltp_table_size = oltp_table_size or 10000
   oltp_range_size = oltp_range_size or 100
   oltp_tables_count = oltp_tables_count or 1
   oltp_point_selects = oltp_point_selects or 20 （原来10）
   oltp_simple_ranges = oltp_simple_ranges or 2 （原来1）
   oltp_sum_ranges = oltp_sum_ranges or 2 （原来1）
   oltp_order_ranges = oltp_order_ranges or 2 （原来1）
   oltp_distinct_ranges = oltp_distinct_ranges or 2 （原来1）
   oltp_index_updates = oltp_index_updates or 1

   oltp_non_index_updates = oltp_non_index_updates or 1

这样总的测试请求量会变成28w

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

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

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

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

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


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

准备数据：

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

1
2
3
4
5
#以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脚本以串行方式准备数据

1
2
3
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

开始测试：

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

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
#!/bin/sh
 
#通过sysbench测试mysql相关性能，并将关键数据存储于‘test.sysbenc_test’表中
 
#定义记录测试结果的mysql连接相关参数，本例我在测试机上记录测试结果
m_user= 'test'
m_passwd= 'test'
m_port= '3307'
m_host= '127.0.0.1'
 
#定义错误日志文件
log= /tmp/mysql_oltp .log
#定义测试线程
threds_num= '8 24 48 64 96 128 160 196 256'
 
#测试函数
sb_test() {
 
     #定义测试方式相关变量
     tables_count=16     #测试表的数量
     if  [  "$3"  ==  "read-only"  ]; then  read_only= 'on' ; else  read_only= 'off' ; fi     #根据脚本参数确定是否read-only
 
     #创建记录测试信息的表
     echo  -e  "\n---------------\n创建测测试结果表test.sysbench_test\n---------------"
     mysql -u$m_user -p$m_passwd -P$m_port -h$m_host <<EOF
         CREATE TABLE IF NOT EXISTS  test .sysbench_test (
         scenario varchar(30) NOT NULL DEFAULT  ''  COMMENT  '测试场景' ,
         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
     if  [ $? - ne  0 ]; then  exit  -1; fi
 
     #开始测试,每种条件测3次,分析时取平均值
     echo  -e  "\n---------------\n场景:$2 模式:$3\n---------------"
     for  i  in  {1..3}; do
         
         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的输出做筛选和格式化，以便插入数据库
             sysbench -- test = /usr/share/doc/sysbench/tests/db/oltp .lua --mysql-user=$6 --mysql-password=$7 --mysql-port=$5 --mysql-host=$4 --num-threads=$sb_threds run --oltp-skip-trx=on --oltp- read -only=$read_only > $log
             if  [ $? - ne  0 ]; then
                 echo  -e  "\nSysbench error! For more information see $log"
                 exit  -1
             fi
             result=$( cat  $log |  egrep   "read:|write:|read/write.*:|total:|total\ time:|approx\..*95.*:"  | sed  -r -e  "s/[0-9]+ \(//g"  -e  "s/\ per sec\.\)//g"  -e  "s/m?s$//g"  |  awk   '{printf("%s ",$NF)}' | sed  "s/\ /,/g"  |  sed  "s/,$//g" )
 
             #测试完成后立刻记录系统一分钟负载值，可近似认为测试过程中proxy的负载抽样
             load=$( ssh  -p22 $4  "uptime|awk -F: '{print \$NF}'|awk -F, '{print \$1}'"  2> /dev/null )
 
             #本次测试结果写入数据库
             mysql -u$m_user -p$m_passwd -P$m_port -h$m_host <<EOF 2> $log
                 INSERT INTO  test .sysbench_test (scenario,server_name,test_type,sb_threads,server_load,request_read,request_write,request_total,request_per_second,total_time,95_pct_time) 
                 VALUES ( '$2' , '$4' , '$3' , '$sb_threds' , '$load' ,$result);
EOF
     
             if  [ $? - ne  0 ]; then
                 echo  -e  "\n----------$sb_threds线程测试，第$i次插入数据库时失败----------"
                 echo  "INSERT VALUES ('$2','$4','$3',$sb_threds,$load,$result)"
                 exit  -2
             fi
             sleep  60     #让库歇一会，也让一分钟负载能够恢复到测试前的值
         done
 
     done
}
 
#结果分析函数
sb_analyse() {
      mysql -u$m_user -p$m_passwd -h$m_host -P$m_port <<EOF 2> $log
         SELECT
         scenario, 
         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 group by scenario,server_name,test_type,sb_threads
EOF
}
 
#画图函数
sb_chart() {
     sb_analyse >  /tmp/mysql_oltp .dat
 
     for  chart_type  in  "request_per_second"  "total_time"  "95_pct_time" ; do     #这里写死了关注的三个指标，也就是会画三张图
 
         col_num=0     #该行及下面这个for循环用于取得三个指标在数据中的列号
         for  col_name  in  ` cat  /tmp/aualyse .txt | awk  'NR<2 {print}' `; do
             let  col_num++
             if  [ $col_name == $chart_type ]; then  break ; fi
         done
         
         if  [ $chart_type ==  "request_per_second"  ]; then     #根据图表特点为不同的chart_type设置不同的key position
             key_pos= "bottom right"
             unit= ""
         elif  [ $chart_type ==  "total_time"  ]; then
             key_pos= "top right"
             unit= "(s)"
         elif  [ $chart_type ==  "95_pct_time"  ]; then
             key_pos= "top left"
             unit= "(ms)"
         fi
 
         plot_cmd= "set term png size 800,600;set output '/tmp/$chart_type.png';set title '$chart_type $unit';set grid;set key $key_pos;plot "
         
         if  [ $ # -eq 0 ];then
             #对分析结果中所有场景进行画图
             for  scenario  in  `mysql -u$m_user -p$m_passwd -h$m_host -P$m_port -s -e  "select distinct(scenario) from test.sysbench_test"  2> /dev/null `; do
                 sb_analyse |  awk  - v  scenario=$scenario  '$1 == scenario {print}'  >  /tmp/ "$scenario.dat"
                 plot_cmd=${plot_cmd} "'/tmp/" $scenario.dat "' using $col_num:xtic(4) title '$scenario' with linespoints lw 2,"
             done
             plot_cmd=$( echo  $plot_cmd |  sed  's/,$//g' )
             echo  $plot_cmd | gnuplot
         else
             #只绘制指定的场景
             for  scenario  in  $*; do
                 sb_analyse |  awk  - v  scenario=$scenario  '$1 == scenario {print}'  >  /tmp/ "$scenario.dat"
                 plot_cmd=${plot_cmd} "'/tmp/" $scenario.dat "' using $col_num:xtic(4) title '$scenario' with linespoints lw 2,"
             done
             plot_cmd=$( echo  $plot_cmd |  sed  's/,$//g' )
             echo  "$plot_cmd"  | gnuplot
         fi
     done
}
 
#脚本使用说明/参数判断
if  [ $ # -eq 1 ] && [ $1 == "-h" -o $1 == "--help" ];then
     echo  -e  "\nUsage: $0 {test test_scenario test_type mysql_host mysql_port mysql_user mysql_password} | {analyse} | {chart [scenario]...}\n"
     echo  ----------
     echo  -e  "测试: 请在脚本后跟上 test test_scenario test_type mysql_host mysql_port mysql_user mysql_password 7个参数 ！"
     echo  -e  "      test_type: read-only 或 read-write, 表示测试模式"
     echo  -e  "      其余4参数表示待测试MySQL连接相关信息,密码若包含特殊字符,将其置于单引号内"
     echo  -e  "----------"
     echo  -e  "分析: 请在脚本后跟上 analyse"
     echo  -e  "----------"
     echo  -e  "画图: 请在脚本后面跟上"
     echo  -e  "      会在/tmp/下生成request_per_second.png total_time.png 95_pct_time.png 三张图"        
     echo  -e  "      chart (对分析结果中的所有测试场景画图)"
     echo  -e  "      chart scenario ... (对指定的测试场景画图，场景名可查看analyse)\n"
     exit  -1
elif  [  "$1"  ==  "test"  -a  $ # -eq 7 ];then
     sb_test $1 $2 $3 $4 $5 $6 $7
elif  [  "$1"  ==  "analyse"  -a $ # -eq 1 ];then
     sb_analyse
elif  [  "$1"  ==  "chart"  ]; then
     #chart函数可不接参数,也可接任意个'测试场景'作为参数
     arg=($*)
     arg_len=${ #arg[@]}
     sb_chart ${arg[@]:1:$arg_len-1}
else
     echo  -e  "\nUsage: $0 {test test_scenario test_type mysql_host mysql_port mysql_user mysql_password} | {analyse} | {chart [scenario]...}\n"
fi
 
### by ljk 2016/10/14


清空测试数据：

1
2
3
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




     本文转自kai404 51CTO博客，原文链接：http://blog.51cto.com/kaifly/1747226 ，如需转载请自行联系原作者

文章标签：
云数据库 RDS MySQL 版
性能测试
关系型数据库
测试技术
数据库
MySQL
相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发，帮助IT从业者们通过“阿里云Web应用上云解决方案”，了解一个企业级Web应用上云的常见架构，了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。 &nbsp; 相关的阿里云产品：云数据库RDS MySQL 版 阿里云关系型数据库RDS（Relational Database Service）是一种稳定可靠、可弹性伸缩的在线数据库服务，提供容灾、备份、恢复、迁移等方面的全套解决方案，彻底解决数据库运维的烦恼。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/mysql&nbsp;
技术小胖子
目录
相关文章
顾翔
|
12月前
|
缓存 关系型数据库 BI
使用MYSQL Report分析数据库性能（下）
使用MYSQL Report分析数据库性能
顾翔
661 158
蒋星熠Jaxonic
|
11月前
|
存储 消息中间件 监控
MySQL 到 ClickHouse 明细分析链路改造：数据校验、补偿与延迟治理
蒋星熠Jaxonic，数据领域技术深耕者。擅长MySQL到ClickHouse链路改造，精通实时同步、数据校验与延迟治理，致力于构建高性能、高一致性的数据架构体系。
蒋星熠Jaxonic
519 2
MySQL 到 ClickHouse 明细分析链路改造：数据校验、补偿与延迟治理
顾翔
|
12月前
|
缓存 监控 关系型数据库
使用MYSQL Report分析数据库性能（中）
使用MYSQL Report分析数据库性能
顾翔
777 156
顾翔
|
12月前
|
缓存 监控 关系型数据库
使用MYSQL Report分析数据库性能（上）
最终建议：当前系统是完美的读密集型负载模型，优化重点应放在减少行读取量和提高数据定位效率。通过索引优化、分区策略和内存缓存，预期可降低30%的CPU负载，同时保持100%的缓冲池命中率。建议每百万次查询后刷新统计信息以持续优化
顾翔
832 161
凉凉心.
|
11月前
|
NoSQL 算法 Redis
【Docker】（3）学习Docker中 镜像与容器数据卷、映射关系！手把手带你安装 MySql主从同步 和 Redis三主三从集群！并且进行主从切换与扩容操作，还有分析 哈希分区 等知识点！
Union文件系统（UnionFS）是一种**分层、轻量级并且高性能的文件系统**，它支持对文件系统的修改作为一次提交来一层层的叠加，同时可以将不同目录挂载到同一个虚拟文件系统下(unite several directories into a single virtual filesystem) Union 文件系统是 Docker 镜像的基础。 镜像可以通过分层来进行继承，基于基础镜像（没有父镜像），可以制作各种具体的应用镜像。
凉凉心.
1011 6
蓝易云
|
存储 关系型数据库 MySQL
深入理解MySQL索引类型及其应用场景分析。
通过以上介绍可以看出各类MySQL指标各自拥有明显利弊与最佳实践情墁，在实际业务处理过程中选择正确型号极其重要以确保系统运作流畅而稳健。
蓝易云
359 12
蓝易云
|
存储 SQL 关系型数据库
MySQL的Redo Log与Binlog机制对照分析
通过合理的配置和细致的管理，这两种日志机制相互配合，能够有效地提升MySQL数据库的可靠性和稳定性。
蓝易云
434 10
奔跑的数据
|
数据采集 前端开发 JavaScript
深挖navigator.webdriver浏览器自动化检测的底层分析
本文详细讲解了如何通过技术手段破解浏览器 `navigator.webdriver` 检测，结合爬虫代理、多线程等策略，在豆瓣图书页面批量采集数据。具体包括：隐藏 Selenium 特征、配置代理突破 IP 限制、设置伪装用户、利用多线程提升效率。文章面向初学者，提供分步教程与示例代码，同时设有「陷阱警告」帮助规避常见问题。目标是从底层实现反检测，高效采集图书评分、简介、作者等信息，适合具备 Python 和 Selenium 基础的读者实践学习。
奔跑的数据
730 12
深挖navigator.webdriver浏览器自动化检测的底层分析
sysin
|
监控 安全 Linux
Arista CloudVision 2025.1 - 多云和数据中心网络自动化、监控和分析
Arista CloudVision 2025.1 - 多云和数据中心网络自动化、监控和分析
sysin
599 2
Arista CloudVision 2025.1 - 多云和数据中心网络自动化、监控和分析
技术自由圈/原疯狂创客圈
|
SQL 关系型数据库 MySQL
MySQL group by 底层原理详解。group by 执行 慢 原因深度分析。（图解+秒懂+史上最全）
MySQL group by 底层原理详解。group by 执行 慢 原因深度分析。（图解+秒懂+史上最全）
技术自由圈/原疯狂创客圈
1272 0
MySQL group by 底层原理详解。group by 执行 慢 原因深度分析。（图解+秒懂+史上最全）

热门文章

最新文章

  • 1
    PTS 3.0：可观测加持的下一代性能测试服务
  • 2
    性能测试脚本的编写和调试
  • 3
    阿里巴巴在应用性能测试场景设计和实现上的实践
  • 4
    阿里云性能测试工具PTS介绍
  • 5
    Android 深度性能测试：功能决定现在，性能决定未来！
  • 6
    【云吞铺子】业务系统性能压测最佳实践（二）--业务压测注意事项
  • 7
    Linux下压力测试工具推荐：WebBench
  • 8
    PTS报错问题之压测报错如何解决
  • 9
    如何集成PTS的JMeter OpenAPI搭建压测平台
  • 10
    压测 apache ab 初探
  • 1
    测试金字塔：构建高效自动化测试策略的基石
    748
  • 2
    测试金字塔：构建高效自动化测试策略的基石
    746
  • 3
    告别脆弱：构建稳定UI自动化测试的3个核心策略
    929
  • 4
    别再熬夜救火了！自动化运维正在重塑企业IT管理的底层逻辑
    386
  • 5
    2025年工作流自动化的15个趋势，如何影响企业的业务？
    641
  • 6
    Python定时爬取新闻网站头条：从零到一的自动化实践
    1646
  • 7
    n8n错误处理全攻略：构建稳定可靠的自动化工作流
    1522
  • 8
    在阿里云ECS上部署n8n自动化工作流：U2实例实战
    2319
  • 9
    Nessus Professional 10.10 Auto Installer for macOS Tahoe - Nessus 自动化安装程序
    274
  • 10
    Nessus Professional 10.10 Auto Installer for RHEL 10, AlmaLinux 10, Rocky Linux 10 - Nessus 自动化安装程序
    723

    • 相关课程

    更多
  • MySQL企业常见架构与调优经验分享
  • 阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
  • MySQL实战进阶
  • 阿里云数据库产品家族及特性
  • 数据库及SQL/MySQL基础
  • 云数据库MySQL快速入门

    • 相关电子书

    更多
  • 阿里云MySQL云数据库产品体系介绍
  • One Box： 解读事务与分析一体化数据库 HybridDB for MySQL
  • One Box：解读事务与分析一体化数据库HybridDB for MySQL

    • 推荐镜像

    更多
  • mysql
    • 下一篇
    一条命令迁移，帮你实现 OpenClaw 与 Hermes Agent 记忆互通！