测试方法
关键性指标
1. IOPS(Input/Output operations Per Second,每秒处理的I/O请求次数)
传统的机械磁盘的I/O能力(不管是吞吐量还是IOPS)进展缓慢,严重滞后于系统中的其他组件,因此磁盘I/O能力往往都称为整套系统中的瓶颈,这也是目前的现状,而判断磁盘的I/O能力的指标之一,就是IOPS。
需要说明一点,通常提到的磁盘读写能力,比如形容他每秒读300M,写200M,这个说的数据吞吐量(I/O)能力的另一个关键指标,但是IOPS指的可不是读写的数据吞吐量,IOPS指的是每秒能够处理的I/O请求次数。
什么是一次I/O请求呢?举个例子来说,读写100MB的文件,就是一次I/O请求,写入1B的数据,这也是一次I/O请求,反应快的朋友看到这肯定明白了,IOPS指标越高,那么单位时间能够响应的请求自然就越多。从理论上来讲,只要系统实际的请求数低于IOPS的能力,就相当于每一个请求都能即时得到响应,那么I/O就不会是瓶颈了。
不过,往深处想一想,旋即又产生了疑问,这个IOPS只是请求次数,可请求与请求不一样,就像前面说的,同样一个请求,读取100MB的文件所需要耗费的时间,肯定比写入1B的时间要长上不少,这中间除了寻道时间耗时,差异除妖体现在数据传输的时间上,而数据传输时间实际上就是最开始提到的吞吐量了。
如果想I/O系统的响应足够快,那么IOPS越高越好,或者换种说法,IOPS指标比较高的话,就更适合要快速响应的系统,尤其是对于短连接,小事务,轻量数据作为操作特点的OLTP系统,当然吞吐量也很重要,但是吞吐量对于磁盘来说基本是个确定的值,没什么讨论空间。
我们怎么衡量服务器的IOPS呢,在Linux下和Windows下都有很多性能测试工具,可以获得IOPS指标,比如Linux平台上较为流行的IOmeter/FIO,Windows平台下最常用的磁盘性能测试工具HD tune,输出的测试结果中均包含IOPS,不过对于传输的机械式磁盘,这个指标其实不必专门测试。
一方面是因为磁盘读写性能测试环境太多,只通过一个难有代表性,如果要搞多重全面测试的话,偏离了本章的注意;再其次,传统磁盘完成一个I/O请求所花费的时间受到3个方面因素的影响
- 寻道时间(Tseek) : 将磁头移动到数据所在的磁道上所需要的时间,通常都在3~15ms。
- 旋转延迟时间(Trotation) : 将盘头旋转,使所请求的数据所在扇区移动至磁头下方所需要的时间,这个时间跟磁盘的转速密切相关,转速越快,延迟越短,一般15000转的磁盘平均旋转延迟时间为2ms。
- 数据传输时间(Transfer) : 完成传输所请求的数据所需要的时间
考虑到数据传输时间跟要传输的数据量关系密切,而传输数据量又与吞吐量密切相关,这个变数太多,不过为了方便推算,我们假定要传输的数据很小,或者吞吐量极高,数据能在瞬间完成。也就是说,在计算时我们先忽略数据传输的时间,那么根据现有信息就可以计算出磁盘理论上的最大IOPS,计算公式为 IOPS = 1000ms/(寻道时间 + 旋转延迟时间)。
基于这一公式计算的话,单块SAS 15K转的硬盘,其中最大的IOPS=1000/(3+2),约为200个每秒。这是理论上的最大值,实际表现一定超不过这个值,如果是万转的磁盘或更低速的磁盘,那么这个指标还会更低。而且在实际场景中,数据顺序读个随机读时,寻道或旋转延迟肯定不同,因此单位时间内的请求响应能力肯定也都不一样,当然,相比前面的公式计算出的值,只会更低,不会更高。
要提高IOPS,目前来看就是拼硬件,传统方案是使用多块磁盘通过RAID调带后,使I/O读写能力获得提升。比如我们希望IOPS达到5000,那么理论上就需要5000/200=25块磁盘,组成RAID0来实现。
考虑到上面提供的IOPS指标只是理论值,实际表现往往低于这个值,因此应该留足富余,这里提到的数据都只是理论参考值,现实中需要根据实际情况操作。另外,不同RAID类型,计算公式也需要有所调整。
举例来说,RAID5上每个写的I/O操作,分别需要读写数据和校验位,计算后再写入数据和校验位,也就是说,对于RAID5,每个写I/O操作实际将产生4次I/O,若视同RAID5条带后的存储系统,写入时IOPS能达到5000/s,那就至少需要4 * 5000 / 200 = 100块磁盘
当然这也只是理论值,实际上极少会存在纯写入而不读取的系统,更多都是读写平均,或多读少写。假如仍然是套RAID5条带的存储系统,但平均下来系统有1/3时间在做写入操作,2/3在做读取操作,那么实际需要的磁盘数就可能变为(2/35000+41/3*5000)/200。
必须再次强调,这只是理论,实际情况需要考虑到各种细节,除了完全依赖于硬件的阵列算法,缓存命中率等,还有实际应用时数据访问特点都有可能对性能造成波动,总之一句话,不管是吞吐量还是IOPS,组RAID时磁盘数一定要留足富裕。
还有一个关键的现实因素,前面动辄就需要几十块盘起,在现实场景中极有可能不具备可操作性,因为通常MySQL数据库的数据文件都保存在本地磁盘,在显示场景中既有可能不具备可操作性，因为通常MySQL数据库的数据文件都保存在本地磁盘,而不会像Oracle数据库使用专有的独立存储,普通的2U x86服务器,磁盘挂满也就能插几十块,所以很多情况下不是买不起次破案,而是买来也挂不上去。就像前面计算单块盘的IOPS,普通x86服务器,整体磁盘的IOPS最大值也能计算出来。
基于现实背景考虑,I/O的处理能力尽管非常重要,但也只能是整体架构设计中的环节之一,单机性能再强也无法满足所有场景需要,因此集群方案在架构设计时就得考虑在内。
固态硬盘越发火热,由于SSD磁盘通过电子信息来工作,天生无机械构件,没有马达和磁片,即使是运行状态也完全静音,关键是避免了传统机械式磁盘在寻道的盘片旋转上的时间开销,这使得寻址时间超短,IOPS可以做到很高(相比传统磁盘而言),不同厂商的不同产品差距巨大。单块SSD磁盘IOPS达到几千只是刚起步,几万甚至几十万都有可能,像Fusion-IO这种甚至能突破百万IOPS。
固态硬盘SSD相比传统硬盘,主要是在IOPS方面有巨大优势,I/O延迟较低,不过在吞吐量指标上,相比传统磁盘的优势就不那么明显了(还是有优势)。由于自身实现原理,存在数据擦除操作,尽管可以通过一定的几千/算法尽量避免,但还是有可能在用来一阵时间之后响应变慢的情况。
同时SSD的IOPS数值受较多因素的影响,比如数据读写的特征(和传统磁盘一样),使用时间,系统配置,甚至驱动程序都有关系。此外,在使用寿命方面,相比传统磁盘也有一定差距,至于容量/性价比更是搞的离谱。
像我们之前的制定的5000 IOPS目标,如果对容量没有要求,那直接上一块SSD可能就满足了。听起来SSD相当不错,那是否往后就用SSD了?这个在可能的前提下,能选择SSD磁盘绝对应该选用SSD,只不过就目前来看,还不可能所有场景均使用SSD磁盘。制约固态磁盘普及的因素主要有两个,一方面是容量(已有较大改善),另一方面是价格(也在稳步下降)。不过,只要银子足够充足,多块SSD磁盘再做条带,性能嗷嗷叫,I/O不为瓶颈都不是梦。这种思路简单粗暴,但着实有效,只是这种确实耗费银两,多数企业不肯接受这个成本投入,再加上规模也没到这个级别,在有别的选择缓解瓶颈的前提下,还是通过别的方案来处理性能问题吧,这也是性能调优依然被视作较有技术含量的因素之一。
2. QPS(Query Per Second,每秒请求(查询)次数)
要注意这个参数,尽管所有数据库都有这个指标,单对于MySQL数据库来说,这个指标尤其重要,因为MySQL数据库中,尽管能拿来衡量性能的指标众多,可整租称得上实用又好用的,就是QPS了。顾名思义,就是每秒查询次数,还有比这更指标更直观反映系统(查询)性能的吗,这就像用IOPS衡量磁盘每秒钟能接受多少次请求,明确且直观。
这个指标的获取也比较简单,MySQL数据库原生就提供有QPS指标值,DBA可以在mysql命令行模式下执行status命令,返回的最后一行输出信息中就包含QPS指标,此外,通过mysqladmin命令附加status参数,也能查询到QPS指标。
不过MySQL原生提供的QPS指标,是该MySQL实例生命周期内的全局指标,这个平均值具备移动参考意思,可是我们都知道系统有忙有闲,闲时就不用说了,QPS完全可以为0,那么系统繁忙时变现如何,峰值承载的QPS能达到多少,这就得通过其他方式获取了。
MySQL提供了一系列的状态变量,其中有一项就是用来记录当前的请求次数,即Questions状态变量的值。尽管这也是MySQL实例生命周期内的全局指标,不过我们只要每隔一秒查询下这个变量值,并将相邻的两值相减,得到的就是精确的每一秒的实际请求数了,如果当前MySQL数据库恰处于,那么我们获取的值就可以视为该MySQL实例的QPS峰值响应能力。
MySQL数据库原生提供的QPS指标,就是通过Questions状态变量除以Uptime状态变量的值所得到的结果,即QPS=Questions/Uptime。我们自己计算QPS时,工时大致也是如此,只是把Uptime换成我们自己定义的时间单位就好了。
3. TPS(Transation Per Second,每秒事务数)
按说TPS指标应该更加重要,可是考虑到MySQL数据库这种开创式的插件式存储引擎的设计,必然存在着并非所有存储引擎都支持事务,这也导致TPS在MySQL数据库体系内,使用范围并不是100%,不过他依然很重要,因为最为流行的存储引擎之一InnoDB是支持事务的,因此这个参数还是需要我们重点关注的。
TPS参数MySQL原生没有提供,如果需要的话我们得自己算,计算方法去QPS同理,任然是基于MySQL数据库提供的一系列变量,计算公式为 :
TPS = (Com_commit + Com_rollback) / Seconds
这个公式里面又出现了两个状态变量,分别代表提交次数和回滚次数,Seconds就是我们定义的时间间隔,如果把这个换成Uptime,那么该公式就能计算该MySQL实例在本次生命周期中的瓶颈TPS。
获取关键性指标
前面提到的3个指标,IOPS先跳过,这个纯硬件指标,尽管对DB性能影响很大,不过硬件因素通常不是由DBA所能左右的,所以我们这里重点关注OPS和TPS,在系统资源层面,我们重点关注CPU占用情况。也就是说,我们会重点统计CPU和QPS,如果能统计到TPS(对象使用了支持事务的存储引擎),那就顺道将TPS指标也手机以下
1. 手动获取关键性指标
mysql> show global status like "%Questions%";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Questions | 138 |
+---------------+-------+
值是取到了,但是现在面临两个问题
1、QPS统计的是每秒产生的请求数,怎么让其按秒输出Questions状态变量的值呢?卡着秒取值吗...
2、当前的数据没有访问量,怎么让数据库繁忙起来?
2. 自动获取性能指标
先解决第一个问题,自动统计请求
# 每隔一秒查询一次,并返回差值
[root@jin mysql3307]# mysqladmin -h 127.0.0.1 -P 3307 -u root -pZj123$%^ extended-status -r -i 1 | grep Questions
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
| Questions | 140 |
| Questions | 1 |
| Questions | 1 |
| Questions | 1 |
| Questions | 1 |
| Questions | 1 |
通过灵活的使用mysqladmin命令,咱们还可以用来获取MySQL实例每秒查询,更新,删除数据等指标,如每秒SELECT,UPDATE数量
mysqladmin -h 127.0.0.1 -P 3307 -u root -pZj123$%^ extended-status -r -i 1 | grep -E "Com_select|Com_update"
再解决第二问题,使用mysqlslap,一款mysql自带的专用清理压测工具
在执行mysqlslap命令期间,mysqladmin命令仍需在运行,否则就看不到它的输出了。
mysqlslap -h 127.0.0.1 -P 3307 -u root -pZj123$%^ --query="select * from mysql.user;" --number-of-queries=100000 -c 30 -i 10 --create-schema=jssdb
参数解释
- --number-of-queries : 指定测试要执行的查询语句。
- -c, -concurrency : 指定测试执行请求的并行度
- -i,-iterations : 指定测试运行的次数
- --create-schema : 指定此次测试在哪个schema下执行
上面的命令翻译过来就是 : 在jssdb数据库下,模拟30个用户连接,共执行100万次请求。
mysqlslap命令功还支持其他的参数
总结
# 获取OPS信息
mysqlslap -h 127.0.0.1 -P 3307 -u root -pZj123$%^ --number-of-queries=100000 -c 30 -i 10 --create-schema=my_test -a
mysqladmin -h 127.0.0.1 -P 3307 -u root -pZj123$%^ extended-status -r -i 1 | grep Questions
# 获取TPS信息
mysqlslap -h 127.0.0.1 -P 3307 -u root -pZj123$%^ --number-of-queries=100000 -c 30 -i 10 --create-schema=my_test -a
mysqladmin -h 127.0.0.1 -P 3307 -u root -pZj123$%^ extended-status -r -i 1 | grep -E "Com_select|Com_update"
3. 自定义脚本获取性能指标
这里就不延伸了...
TPCC测试
专业名词
- TPC : 全称[T]ransaction [Processing] Performance [C]ouncil,是一家非盈利组织,该组织制定各种商业应用的基准测试规范,任意厂商或个人,都可以按照其规范来开发自己的应用程序
- TPC-C : 由TPC退出的一套基准测试程序,主要用于联机事务类应用的测试,最后那个字母C仅是序号,在他之后还有TPC-D,TPC-R,TPC-W,不过也废弃了。TPC-C这个关键不是完全因为他没有被废弃,这只是一部分原因,最主要的原因是TPCC是套基准,不管是有了新硬件,还是软件出了新版本,为显示出新产品的优势,总得拿出些数值做对比,TPCC就是数值之一,各大厂商都着力在吹。TPC-C针对联机事务类应用和决策支持类应用(或称数据仓库)另有一套标准,就是TPC-H。
- TPCC-MYSQL : 由Percona基于TPCC规范开发的一套mysql基准测试程序,也就是本书的主角,这里先不多作介绍,因为本节剩下的篇幅全是介绍他。
"高性能MySQL"的作者之一Vadim Tkachenko,在该书的第二章提到过一款名为dbt2的TPC-C测试工具,书中也提供了使用该工具的例子,可能是作者自己都觉得该工具不大好使,因此后台作者就开发了我们将要提到的主角 : tpcc-mysql
tpcc-mysql
安装
代码可以从github下载
cd tpcc-mysql-master/src
make
如果没有报错的话,就是安装好了,安装好了之后,tpcc-mysql-master下会生成两个可执行文件
- tpcc_load : 用于初始化数据
- tpcc_start : 用于执行基准测试
使用
1. 创建测试数据库
mysqladmin -h 127.0.0.1 -P 3306 -u root -pZj123$%^ create tpcc
2. 导入数据
mysql -h 127.0.0.1 -P 3306 -u root -pZj123$%^ tpcc < create_table.sql
3. 倒入索引
mysql -h 127.0.0.1 -P 3306 -u root -pZj123$%^ tpcc < add_fkey_idx.sql
4. 初始化数据
语法 : pcc_load -h server_host -P port -d database_name -u mysql_user -p mysql_password -w warehouses -l part -m min_wh -n max_wh * [part]: 1=ITEMS 2=WAREHOUSE 3=CUSTOMER 4=ORDERS
tpcc_load -h 127.0.0.1 -P 3306 -u root -pZj123$%^ -d tpcc -w 10
5. 测试
语法 : 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 -t trx_file
与连接相关的参数都比较好懂,这里介绍下从-r开始的的参数
- -r warmup_time : 指定预热时间,以秒为单位,默认是10秒,主要母的是为了将数据加载到内存
- -l running_time : 指定测试执行的时间,以秒为单位,默认是20秒
- -i report_interval : 指定生成报告的间隔时间
- -f report_file : 将测试中各项操作的记录输出到指定文件内保存
- -t trx_file : 输出更详细的操作信息到指定文件内保存
命令如下
# 开始压测指定仓库10个，并发10个，预热10s，持续300s，每隔10s生成报告，并生成日志文件tpcc_mysql.log：
./tpcc_start -h 127.0.0.1 -P 3306 -u root -pZj123$%^ -d tpcc -w 10 -c 10 -r 100 -l 300 -f tpcc_mysql.log -t tpcc_mysql.rtx
返回结果
***************************************
*** ###easy### TPC-C Load Generator ***
***************************************
option h with value '127.0.0.1'
option P with value '3306'
option u with value 'root'
option p with value 'Zj123$%^'
option d with value 'tpcc'
option w with value '10'
option c with value '10'
option r with value '100'
option l with value '300'
option f with value 'tpcc_mysql.log'
option t with value 'tpcc_mysql.rtx'
<Parameters>
[server]: 127.0.0.1
[port]: 3306
[DBname]: tpcc
[user]: root
[pass]: Zj123$%^
[warehouse]: 10
[connection]: 10
[rampup]: 100 (sec.) --预热时长
[measure]: 300 (sec.) --测试时长
RAMP-UP TIME.(100 sec.) --预热结束
MEASURING START. --测试开始
10, trx: 28, 95%: 3776.729, 99%: 5182.355, max_rt: 7222.578, 28|4839.867, 2|2649.252, 2|8888.263, 4|28777.471
20, trx: 21, 95%: 4728.754, 99%: 5173.055, max_rt: 5173.142, 19|2625.231, 3|768.530, 1|5638.004, 1|13718.509
...
STOPPING THREADS.......... --结束压测
<Raw Results> --第一次统计结果
[0] sc:0 lt:1557 rt:0 fl:0 avg_rt: 1063.3 (5) --new-order，新订单业务成功次数（success），延迟次数(late)，重试次数(retry)，失败次数(failure)，平均重试次数
[1] sc:36 lt:1521 rt:0 fl:0 avg_rt: 433.4 (5) --payment，支付业务统计
[2] sc:11 lt:145 rt:0 fl:0 avg_rt: 532.8 (5) --order-status,订单状态业务统计
[3] sc:0 lt:153 rt:0 fl:0 avg_rt: 3633.0 (80) --delivery 发货业务统计
[4] sc:0 lt:158 rt:0 fl:0 avg_rt: 6656.3 (20) --stock-level库存业务统计
in 300 sec.
<Raw Results2(sum ver.)> --第二次测试结果
[0] sc:0 lt:1557 rt:0 fl:0
[1] sc:36 lt:1521 rt:0 fl:0
[2] sc:11 lt:145 rt:0 fl:0
[3] sc:0 lt:153 rt:0 fl:0
[4] sc:0 lt:158 rt:0 fl:0
<Constraint Check> (all must be [OK]) --所有的都必须OK才行
[transaction percentage]
Payment: 43.48% (>=43.0%) [OK] --支付成功次数，结果大于43%，为OK，结果小于43%，为NG；
Order-Status: 4.36% (>= 4.0%) [OK] --订单状态
Delivery: 4.27% (>= 4.0%) [OK] --发货状态
Stock-Level: 4.41% (>= 4.0%) [OK] --库存状态
[response time (at least 90% passed)] --响应耗时指标必须超过90%通过才行
New-Order: 0.00% [NG] * --表示not good
Payment: 2.31% [NG] *
Order-Status: 7.05% [NG] *
Delivery: 0.00% [NG] *
Stock-Level: 0.00% [NG] *
<TpmC>
311.400 TpmC --TpmC结果值（每分钟事务数，该值是第一次统计结果中的新订单事务数除以总耗时分钟数，例如本例中是：1557/5=311.4），TpmC/60=tps
解释
10, trx: 28, 95%: 3776.729, 99%: 5182.355, max_rt: 7222.578, 28|4839.867, 2|2649.252, 2|8888.263, 4|28777.471
20, trx: 21, 95%: 4728.754, 99%: 5173.055, max_rt: 5173.142, 19|2625.231, 3|768.530, 1|5638.004, 1|13718.509
第一项为我们定义的任务执行时间,他是按照每10秒为一个区间进行输出。后面分别为在10s内新订单事物数,每个给定区间的新订单事务的95%响应时间,每个给定区间的新订单事务的99%响应时间,新订单事物最大响应的时间,其他事物数和最大响应时间。
所以上面10的一列为
164 10秒内执行了28次事务
95% 事务平均响应时间为3776.729秒
99% 事务平均响应时间为5182.355秒
max_rt 事务最长响应时间为7222.578秒
TPCC没有收集系统性能指标,需要通过其他途径获取期间的系统性能指标,重点关注CPU和系统平均负载。
数据库参数配置优化
连接相关参数
1. max_connections : 指定MySQL服务端最大并发连接数,范围为1~10万,默认值为151。
这个参数非常重要,因为它决定了同时最多能有多少个会话链接到MySQL服务;其次这个参数很有意思,改参数的默认值随着版本的不同一致在变来变去,单总体值的范围都比较保守。我们可以忽略默认值,直接手动指定参数值。设定该参数时,根据数据库服务器的配置和性能,一般设置在500~2000之间都没有太大的问题。
2. max_connect_error : 指定允许连接不成功的最大尝试次数,值的范围从1~2^64(32位的平台最大为2^32),在5.6.6版本默认值是100,在之前的版本中,默认值为10。
这个参数很重要,如果尝试连接的错误数量超过该参数指定值,则服务器就不再允许新的连接,实际表现就是拒绝,尽管MySQL仍在提供服务,但无法创建新连接了。如果出现这种情况怎么处理呢?在已经连接的会话中执行FLUSH HOSTS,使状态清零,或者重新启动数据库服务,这个代价就太高了,一版不会选择。这个参数的默认值较小,建议加大,一般设置在10万以上的量级。
3. interactive_timeout和wait_timeout : 这两个参数都与连接会话的自动超时断开有关,前者用于指定关闭交互连接前等待的时间,后者用于指定关闭非交互连接前的等待时间,单位均是秒,默认值为28800,即8个小时。
是否是交互模式,取决于用户端创建连接,调用mysql_real_connect()函数时指定的CLIENT_INTERACTIVE选项,如果指定了这个选项,那么wait_timeout的值就会被interactive_timeout值覆盖,这是基于MySQL的实现机制进行的设置,因此这两个参数必须被同时设置(如果不能确定所使用的客户端到低使用交互还是非交互模式)。这个参数的参数值设置有些讲究,不能太小,否则可能会出现连上去后,一会会话就被断开;可是又不能太大,否则可能存在长时间不操作,但占据着连接资源(如果前端应用层没有主动断开的话)。建议设置的时长不要超过24小时,即参数值小于86400,应能满足绝大多数的需求。
4. skip-name-resolve : 可以将之简单理解为禁用DNS解析,注意,这个是服务端的行为,连接时不检查客户端主机名,而只使用IP。如果指定了这个参数,那么在创建用户及授予权限时,HOST列必须是IP而不能是主机名。建议启用本参数,对于加快网络连接速度有移动帮助(相当于跳过了域名解析)。
5. back_log : 指定MySQL连接请求队列中存放的的最大连接请求数量,在5.6.6版本之前,默认是50,最大值不超过65535。进入5.6.6版本之后,默认值为-1,表示由MySQL自行调节,所谓自行调节其实也是有规则,即50 + (max_connections / 5)。
该参数用来应对这种场景--短时间内有大量的连接请求,MySQL主线程无法及时为每一个连接请求分配(或创建)连接的线程,MySQL也不会直接拒绝,他会将一部分请求放到等待队列中待处理,这个等待队列的长度就是back_log的参数值,若等待队列也被放满了,那么后续的连接请求才会被拒绝。
文件相关参数
有一些参数在前面已经说过了,这里就不重复了
1. sync_binlog : 指定同步二进制文件的频率,默认是0
简单来说,要性能就设置该参数值为0,为了安全则指定该参数值为1。
sync_binlog从字面上看就是为了同步二进制日志文件,二进制日志文件在物理上看就是一堆保存在磁盘上的文件。我们都知道磁盘的性能不理想,因此对于频繁写入的场景,所做的操作可能无法立刻写到磁盘,对于二进制日志来说,他也有自己的缓存区(这部分参数由binlog_cache_size管理),一般会先写到缓存中,而sync_binlog的作用在于,何时刷新缓存中的日志到磁盘中,当设置为0(默认值)时,就标识MySQL不关注二进制文件何时刷新,完全交由MySQL来管理何时刷新到磁盘,这种情况性能最好,但是存在风险,如果MySQL服务宕机,那这部分未刷新到磁盘的数据就丢失了。
当sync_binlog的值设置为n(大于0的整数)时,就表示MySQL每进行n次事务后,就同步binary log到磁盘(使用fdatasync())。如果 将sync_binlog的参数设置为1,这种情况下就算数据库服务崩溃,最多丢失一条语句的数据或者一个事务的数据,当然,每个事务同步肯定会对性能有影响,因此,大家希望在性能和安全性方面取得平衡的话,可以考虑适当增大sync_binlog的参数值,2/4/6/8/16都行,sync_binlog最大能支持无符号的bigint数据类型所能支持的最大值(2^64)。
2. expire_logs_days : 指定设置二进制日志文件的生命周期,超出将被自动删除,参数值以天为单位,值的范围从0~99,默认值为0。
当该参数设置为0时,表示不自动删除二进制文件,需要DBA手动清理。一般将该参数值设置为7~14之间。自动清除操作一般会在启动或二进制日志被flushed时,手动删除二进制日志可以通过PURGE BINARY LOGS语句进行,不建议直接在操作系统层删除物理文件。
3. max_binlog_size : 指定二进制日志文件的大小,值的范围从4KB~1GB,默认为1GB。
单个二进制日志文件不可能无限增长,就算写入了,操作系统也不一定支持。因此需要指定单个文件的最大可用空间,这正是由系统参数max_binlog_size进行控制
对于比较繁忙的系统,数百MB很正常,可以以设置为512M。当日志文件大小达到max_binlog_size指定的大小时,就会创建新的二进制文件。不过max_binlog_size并不能严格的控制日志文件的大小,生成的文件可能会超过参数指定的值。这通常会出现在二进制日志快被写满时,又执行了一个超大的事务,由于事务特性决定相关事件必须连续,因此该事件会被写到同一日志文件,这就可能造成日志文件的实际大小超出参数值的现象,但是没关系,不会有其他的影响。
4. local_file : 指定是否允许从客户端本地加载数据,该参数值为布尔型,默认为允许。这个参数可以说是LOAD DATA INFILE的专用参数,用户可以根据实际需求设置为ON或OFF。
5. open_files_limit : 指定操作系统允许mysql进程使用的文件描述符数量,改参数的参数值受较多因素影响,正常情况下,MySQL会按照规则从下列3个条件中,选择最大的一项作为参数值。
- 10 + max_connections + (table_open_cache * 2)
- mac_connections * 5
- 启动时指定的open_files_limit参数值大小,如果未指定默认为500
缓冲控制参数
1. binlog_cache_size : 指定二进制日志事务缓存区的大小,默认值为32KB,最大可以支持到2^64
为事务指定缓存区,用于缓存二进制日志SQL语句,注意这里提到了"事务",也就是说,这个缓存区仅用于事务。只有当MySQL服务中有支持事务的存储引擎,并且启用了二进制日志记录,每个客户端在操作事务时,才会被分配二进制日志缓存。一般来说,改参数设置为8MB或16MB即可满足大多数场景,如果经常使用大量含有多条SQL语句的事务,可以通过调高该参数来获得性能的提升,不过最大建议不要超过64MB,这主要是考虑到这个参数是为每一个连接(支持事务)客户端分配内存,如果链接的客户端较多,每个会话二进制日志缓存占用过多,也不利于系统的整体性能
2. max_binlog_cache_size : 本参数功能与binlog_cache_size参数密切相关,主要用来指定binlog能够使用的最大内存去。如果单个事务中执行多个语句所需内存超过该参数设置的话,则服务器会抛出Multi-statement required more than 'max_binlog_cache_size' bytes of storage的错误。一般建议改参数为binlog_cache_size的两倍大小即可。
3. binlog_stmt_cache_size : 可以理解为非事务语句的binlog_cache_size
4. table_open_cache : 指定MySQL同时能打开的表对象的数量。
在5.6.8版本之前,默认值为400,5.6.8之后提升为2000;table_open_cache是在5.1.3版本引入的,之前名为table_cache
5. thread_cache_size : 指定MySQL为快速重用缓存的进程数量。范围从0~16384,默认值为0
一般当客户端终端连接后,为了后续再有连接创建时,能够快速创建成功,MySQL会将客户端终端的链接放入缓存区,而不是马上终端并释放资源。这样当有新的客户端请求连接时,就可以快速创建成功。因此本参数最好保持一定数量,建议在300~500之间。线程的缓存命中率也是一项比较重要的监控指标,计算规则为(1 - Thread_created/Connections) * 100%。可以通过计算该项指标的值,来优化和调整thread_cache_size参数。
6. query_cache_size : 指定用于缓存查询结果集的内存区大小,改参数的值应为1024的整倍数。
这个参数的设定很讲究,既不能太小,查询缓存至少会需要40KB的空间分配给其自身结构(具体大小要看操作系统结构),太小时缓存的结果集就没有意义,热点数据保存不了多少,而且总是很快被刷新出去;但是也不能太大,否则可能占据过多的内存资源,影响整机性能,再说太大也没有意义,但只要数据源发生变更,缓存中的数据也就自动失效了,这种情况下分配多大都没有意义。因此需要综合考虑,查询缓存不是万能的,应用不当不仅无助于性能的提升,还有可能降低系统性能。这个不仅仅适用于query_cache_size,不过内存类参数就是如此,讲究一个平衡,过犹不及。最好不要超过256MB
7. query_cache_limit : 用来控制查询缓存,能够缓存的单条SQL生成的最大结果集,默认是1MB,超出的就不要进入查询缓存。这个大小对很多场景都足够了,缩小可以考虑,加大就不用了
8. query_cache_min_res_unit : 指定查询缓存最下分配的块大小,默认为4KB,最大能支持2^64。
当查询能够被被缓存时,那么其查询的结果(发送到客户端)就会被保存在查询缓存区中,知道该查询过期,基于OLTP的特点,查询结果集通常不会太大,查询缓存一经需要就会为其分配空间(块)保存数据,当一个块被写满,则新的块又会被分配。考虑到频繁操作代价比较昂贵(时间成本),因此查询缓存再为结果集分配空间时,默认会按照本参数的值进行分配,并视情况可增大到query_cache_limit指定的值。
本参数值的大小同样需要认真考量。如果查询多数是小结果集,那么当指定的块比较大时,就可能会导致内存分裂,这种情况下降低query_cache_min_res_unit可能就更适合。如果查询都是大结果集的话,那么增长改参数值可能就更适合。
9. query_cache_type : 设置查询缓存的类型。支持全局或会话级设置,可选类型有3个
- 0(OFF) : 不使用查询缓存,注意该选项并不会关闭查询缓存区,如果不想分配查询缓存区的内存空间,还是需要抢query_cache_size参数值设置为0
- 1(ON) : 缓存除SELECT SQL_NO_CACHE之外的查询结果,也是本参数的默认选项
- 2(DEMAND) : 只缓存SELECT SQL_CACHE的查询结果
若要了解查询缓存的实际使用应用情况,有一系列以Qcache开头的状态变量可供灿开,可以通过show global variables获取这些状态变量的值,辅助分析查询缓存的设置是否合适。比如说,通过状态变量计算查询缓存的命中率,公式如下 : Qcache_hits * 100 / (Qcache_hits + Qcache_insets)
10. sort_buffer_size : 指定单个会话能够使用的排序区大小,默认值为256KB,最大支持2^64
一般单个会话执行的语句进行排序操作时,会使用这部分空间,如果要排序的数据再sort_buffer_size指定的区域内就可以完成排序,那么所有操作都是在内存中进行,性能自然很好,否则MySQL就不得不使用临时表来交换排序,要知道临时表可是创建在磁盘上的文件,这个性能相比内存中的运行效率,相差不知道多少倍。
通常当发现状态变量sort_merge_passes值比较大时,可以考虑增加sort_buffer_size参数值的代销,应该能够有效提升查询效率。不过需要注意,这部分空间一经设置,所有会话将按此分配排序区,哪怕并非所有会话都需要这么大的排序区,因此,如果设置过大,也有可能对系统的性能造成影响。考虑到他是基于会话的,如果分配的空间过大,同时MySQL服务的会话数很多,那么仅这一块内存的占用就不容忽视,基于这一因素,这个参数一版设置在1~4MB之间即可。
11. read_buffer_size : 指定顺序读取时的数据缓存区大小,默认是128KB,最大能够支持到2GB。
从表中读取数据时也会应用缓存,从表中读取数据其实有两种方式,一是顺序读取(通常是全表扫描),另一种是随机读取(通常是索引扫描)。当采用顺序方式读取时,数据就会保存在read_buffer_size指定的缓存区中。该参数的参数值在设置时应为4KB的整倍数,实际上,当设置为一个非4KB整倍数时,MySQL也会强制将其降为最接近的4KB整倍数。改参数最大不超过2GB,一版来说,适当加大本参数,对于提升全表扫描速率会有帮助。
12. read_rnd_buffer_size : 指定随机读取时的数据缓存区大小,默认是256KB,最大能够支持到4GB。
当以随机方式读取数据时,数据就会保存在本参数的数据缓存区中。为该参数指定一个较大的值,能够有效提高ORDER BY语句的执行效率,不过需要注意,read_buffer_size参数和read_rnd_buffer_size参数所指定的值都是针对单个会话,因此不建议指定太大的值,如有需要,可以在session级别单独进行设置。
13. join_buffer_size : 指定表join操作时的缓存区大小,默认为256KB(5.6.6版本之前为128KB),最大支持2^64。
不管是索引扫描,索引范围扫描,还是不使用索引的全表扫描的JOIN操作,数据所使用的内存都是由join_buffer_size参数指定。通常来说,最好,最快的连接方式仍是使用索引,只有当创建的所有索引不生效时,才会使用这部分空间来存储表连接时的数据。参与FULL JOIN的每一个表都需要有自己独立的Join Buffer,所以这个参数分配的话,至少是两个。对于比较复杂的多表连接查询,还可能使用到多个join缓存区,对于这个缓存的设置,建议参考sort_buffer_size的方式,全局值设置的保守些,对于特殊的查询可以单独设置session级别的更适合的参数值
14. net_buffer_length : 指定单个客户端与MySQL服务端交互时,相关信息的缓存区大小,默认是16KB,最大能够支持到1MB。
每个客户端的链接都需要与服务端进行交互,交互的信息也需要缓存,以保存连接信息,语句的结果集等,这类缓存池的起始大小就是由net_buffer_length参数决定,而后会动态增长,直到达到max_allowed_packet参数指定值。该参数默认值即可满足大多数场景需求,不建议进行修改,如果内存着实有限,可以考虑适当减小。
15. max_allowed_packet : 指定网络传输时,单次传输的数据包大小。在5.6.6版本之前默认为1MB,进入5.6.6版本之后,默认是4MB大小。
这个参数与前面提到的net_buffer_length相关联,数据包初始化时被置为net_buffer_length参数指定的值,不过最大可以增长到max_allowed_packet指定的参数值大小。该参数默认情况下较小,建议增加,特别是当使用了大字段类型(如BLOB)时,该参数值最大不超过1GB,应该设置成1024的倍数。
16. bulk_insert_buffer_size : 指定批量插入时的缓存区大小,默认是8MB,最大可以支持到2^64。
该参数用于加速像INSERT...SELECT,INSERT...VALUES,...以及LOAD DATA INFILE这类语句。这是个会话级的参数,可以动态调整,实际使用过程中,可以无视需求加大(比如恢复mysqldump导出的数据),如果批量插入的机会不多的话,也就无所谓了,保持默认值即可。
17. max_heap_table_size : 指定内存表(Memory引擎表对象)的最大可用空间,默认为16MB,最大可支持到2^64。
该值可用来计算内存表的max_rows值。需要注意的是,修改该参数的值,不会影响当前已经存在的内存表,除非又通过CREATE/ALTER/TRUNCATE重建内存表。默认值16MB确实比较小,可以适当增加本参数的值。
18. tmp_table_size : 指定内部内存临时表的最大可用空间(实际大小将取决于tmp_table_size和max_heap_table_size两参数的最小值)。
当内存临时表达到最大值时,MySQL自动将其转换成保存在磁盘上的MyISAM类型的表对象。如果内存超大,并且临时表需要执行复杂的GROUP BY查询,那么可以适当增加tmp_table_size(以及max_heap_table_size)参数的值。可以通过show global variables查看
MyISAM专用参数
1. key_buffer_size : 指定MyISAM表索引的缓存区的大小,该缓存区为所有线程共用,注意这里说的是共享使用,而不是针对单个会话。默认缓存区大小是8MB,对于32位平台来说,该参数的最大值为4GB,64位平台则无此限制。
该参数不是越大越好,即使数据库服务器中只有MyISAM引擎的表对象,建议此设置值最大也不要超过物理内存的25%,更何况现在已经是InnoDB引擎的天下了,本参数最多指定个128MB就顶天了
对于纯MyISAM引擎对象的数据库服务,key_buffer_size的可配置性还是很强的,尽管缓存区只有一个,但是他也能配置成多个键值缓存区,分别对应较热/较冷等不同热点的缓存数据,此外还有专门的CACHE INDEX/LOAD INDEX INTO CACHE等语句,用于加载或处理缓存中的数据等。
要检查MyISAM索引缓存区应用的性能怎么样,也可以通过一些状态变量中给出的指标值。
计算缓存命中率可以使用 : 1 - (key_reads / key_read_requests) * 100,这个值最好能无限接近于1。
计算缓存写的比例可使用公式 : key_writes / key_write_requests,如果有大量的更新或删除,那么这个值也应该趋近于1。
2. key_cache_block_size : 指定索引缓存的块大小,值的范围从512B到16KB,默认是1KB。
注意这个参数不仅仅是在缓存区中保存键值的大小,而且也决定了从磁盘*.MYI文件中读取索引键值时,一次读取的块大小,因此本参数的设置最好能与磁盘的IO能力综合考虑,使之相匹配,已获得好的I/O性能
3. myisam_sort_buffer_size : 指定MyISAM引起排序时的缓存区大小,默认是8MB,最大能支持到2^64B。
与sort_buffer_size的功能差不多,只不过这个参数是MyISAM引擎专用,当执行REPAIR TABLE或CREATE/ALTER INDEX重建索引时会用到,为MyISAM引擎表对象的索引排序分配缓存区。
4. myisam_max_sort_file_size : 当重建MyISAM索引(REPAIR TABLE/ALTER TABLE/LOAD DATA INFILE)时,MySQL运行操作的临时文件最大空间,32位平台默认为2GB,64位平台可以理解为无限制(已达EB量级)。如果文件大小超过了该参数值,则索引创建时会把key_cache_size用上,但是那个缓存区太小,缓存中的频繁数据交换将导致速度慢很多。因此如果索引文件超出该参数值,并且在磁盘空间由空闲的情况下,提高该参数值能提高性能。
InnoDB专用参数
1. innodb_buffer_pool_size : 指定InnoDB引擎专用的缓存区大小,用来缓存表对象的数据及索引信息,默认值为128MB,最大能支持(2^64 - 1)B
innodb_buffer_pool_size是个全局参数,其所分配的缓存区将供所有被访问到的InnoDB表对象使用,如果数据库中是以InnoDB为主,那么本参数值就越大越好,官方文档中也是这么建议,可以将这个值设置为服务器物理内存的80%。不过,这说的是理想情况,实际设置时还是需要考虑各种因素,以避免内存分配超量,造成操作系统级别的换页操作。此外，MySQL服务启动时,初始化内存区时所花费的时间也会与该内存区大小成正比,因此设置超大内存时,还需要考虑对MySQL数据库服务启动速度的影响。InnoDB缓存命中率也可以通过状态变量的值进行计算,公式为1 - (innodb_buffer_pool_reads/innodb_buffer_pool_read_request) * 100,命中率越接近100%越好,说明几乎所有要请求的数据,都能从内存中获取,效率自然刚刚的。
2. innodb_buffer_pool_instances : 指定InnoDB缓存池分为多少个区域来使用,值的范围从1~64,默认值为-1,表示由InnoDB自行调整
当InnoDB缓存池较大(如今动辄以GB计算)时,就需要考虑如何高效利用这个空间,内存资源很宝贵,尽管内存中操作数据的效率相比磁盘要高上许多,但也不是说数据放到内存就一定会快,能不能用好可是很有讲究的,尤其是当InnoDB缓存池能够使用数GB甚至数十GB内存空间时,将之当做整块区域操作M管理成本显然太高。基于这一点,InnoDB缓存池能够被分块处理,innodb_buffer_pool_instances参数就是用来指定InnoDB缓存池的个数。每个区块有自己的LRU列表及相关的数据结构,这就相当于把一块大的缓存池换分成多个小的缓存池来管理,不同链接的读写操作的是不同的缓存也,以提高并发性能。只有当innodb_buffer_pool_size参数值大于1GB时本参数才有效,默认是8(32位平台默认是1)。那么本参数怎么设置合适呢？可以参照InnoDB缓存池的大小,以GB为单位,每GB指定一个instances。例如当innodb_buffer_pool_size设置为16GB时,则指定innodb_buffer_pool_instances设置为16即可。
3. innodb_max_dirty_pages_pct : 指定InnoDB缓存池中的脏页(即已经被修改,但未同步到数据文件)比例,本参数值的范围时0~99,默认值是75。
InnoDB更新innodb_buffer_pool_size中的数据时,并不会实时将数据写回到磁盘,而是等待相关的触发时间,本参数就是指定缓存数据中被改动数据未刷新到磁盘的最大百分比。如果数据库的写操作比较频繁,建议适当降低这个比率值,意见上MySQL宕机后的恢复时间,当然这样也会带来更多的I/O操作。
4. innodb_thread_concurrency : 指定InnoDB内部最大线程数,值的范围为0~1000,默认值为0。
当线程数达到该参数指定数量时,后面的线程将被置入FIFO队列中进入等待状态,不过当参数值设置为0时,就表示没有限制,完全交由InnoDB自己去管理可创建线程数量。这个参数争议较大,而且从MySQL版本演进过程中,改参数默认值的变化也可以看出这一点。在5.1.12版本之前这个默认曾数次变更,先是8,然后是20,后又变成8,再之后又改成20,从5.5开始默认值就是0。这个值可以直接设置成0,由InnoDB自己管理。
5. innodb_flush_method : 用来控制InnoDB刷新数据文件及日志文件的方式,仅作用于Linux,UNIX操作系统,与I/O吞吐量密切相关。InnoDB默认使用fsync()系统调用刷新数据文件和日志文件,此外还有O_DSYNC,O_DIRECT,O_DIRECT_NO_FSYNC几个选项。
当指定为O_DSYNC选项时,InnoDB将使用O_SYNC方法打开并刷新日志文件,使用fsync()刷新数据文件;若指定为O_DIRECT,则会使用O_DIRECT(在Sloaris系统中则使用directio()调用)打开数据文件,使用fsync刷新数据文件和日志文件;若指定为O_DIRECT_NO_FSYCN选项,在刷新I/O时他会使用O_DIRECT,不过之后会跳过fsync()系统调用,这种选项不使用与XFS文件系统。
根据硬件配置的不同,指定O_DIRECT或O_DIRECT_NO_FSYNC在性能方面的表现可能正好相反。例如,对于使用支持写保护的硬件的RAID卡,使用O_DIRECT选项可以避免InnoDB缓存和操作系统层缓存的双重缓存写,而对于将数据文件及日志文件保存在基于SAN存储系统,在对应大量的SELECT语句时,使用O_DSYNC选项可能更快一些。
6. innodb_data_home_dir : 指定InnoDB数据文件保存的路径,默认保存在MySQL的datadir参数指定的路径下
7. innodb_data_file_path : 指定InnoDB数据文件名及文件大小
8. innodb_file_per_table : 指定是否将每个InnoDB表对象存储到独立的数据文件。
9. innodb_undo_directory : 指定InnoDB引擎的UNDO表空间数据文件存储路径。
10. innodb_undo_logs : 指定回滚段的数量,默认值是0,值的范围从0~128
11. innodb_undo_tablespaces : 指定InnoDB回滚段表空间(其实也是数据文件)的数量,这些文件就会创建到innodb_undo_directory参数指定的路径下
12. innodb_log_files_in_group : 指定InnoDB日志文件组中日志文件的数量
13. innodb_log_group_home_dir : 指定InnoDB日志文件的保存位置
14. innodb_log_file_size : 指定InnoDB单个日志文件的大小
15. innodb_log_buffer_size : 指定InnoDB日志缓存区的大小,最小256KB,最大则不超过4GB,默认为8MB。
为本参数指定一个适当的值,能够延缓未提交事务向磁盘日志文件的写操作频率,因此对于较大事务的应用,可以考虑加大该缓存池以节省次磁盘I/O,通常4~8MB都是合适的,除非事务量极多,写入量极高,否则再大恐怕也没有意思。
16. innodb_flush_log_at_trx_commit : 指定InnoDB刷新log buffer中的数据到日志文件的方式,默认值为1,可选值为0/1/2,可选的参数值尽管不多,不过这个参数在设置时还是很讲究的,同时他还跟sync_binlog参数有所关联。
当参数为0时,则log buffer每秒像日志文件写入一次,并写入次磁盘,但是在事务提交前不做任何操作(不要同步数据文件),在此期间,MySQL进程崩溃,则会导致丢失最后一秒的事务;当参数的值为1时,只要事务提交或回滚,就会将缓存中的数据写入日志文件,并且明确触发文件系统同步数据;当为2时,log buffer在遇到事务提交时,会将缓存写向日志文件,但是并不会触发文件系统层的同步写入。这里稍稍有些不保险,他只是条用了文件系统的文件写入操作,在这种情况下,若MySQL进程崩溃,那么数据还是安全的(不影响操作系统层的缓存刷新),不过若操作系统崩溃或主机掉电,那么可能导致丢失最后一秒钟的事务。
所以，设置为1时可以达到比较高的安全性,设置成不为1则可以提高性能(但是当数据库崩溃时可能会丢失数据)。
17. innodb_flush_log_at_timeout : 指定每隔n秒属性日志,默认值为0,值的范围为0~27000,这是5.6版本新引入的参数,只有当innodb_flush_log_at_trx_commit指定为2时才有效。
18. innodb_lock_wait_timeout : 指定InnoDB事务等待行锁的超时时间,以秒为单位,默认为50秒。
单个事务尝试获得一行数据时,如果该行数据被InnoDB的其他事务所锁定,那么该事务会先进入等待状态,等待本参数指定的时间后若仍未成功获得，则抛出下列错误:ERROR 1205 (HY000) : Lock wait timeout exceeded;try restarting transaction。
当发生锁超时,那么当前语句并没能被成功执行,当前事务也不会显示回滚(如果自动回滚,可以在启动服务器时指定innodb_rollback_on_timeout选项加以控制)。该参数仅作用于InnoDB行锁,对表锁无效,对死锁也无效,因为InnoDB能够自动检测到死锁,并自动回滚一个事务,这种情况下该参数也不起作用。
19. innodb_fast_shutdown : 指定InnoDB引擎的关闭模式,有0/1/2三种选择,默认为1。
当为0时,InnoDB的关闭时间最长,他要完成所有数据清除以及插入缓存区的合并;参数值为1时是快速关闭模式,InnoDB会跳过上述操作,直接关闭;当参数值为2时,InnoDB首先刷新其日志文件到磁盘,而后执行冷关闭,如果MySQL崩溃了,那么未提交的事务中的数据就丢失了,下次启动会执行故障恢复。一版保持默认值即可,除非遇到紧急情况,需要立刻关闭数据库,否则不要设置为2。
参数优化实例
当前用来测试的服务器拥有1GB的物理内存,假定峰值最大连接数为10个,表对象均使用MyISAM或InnoDB两种存储引擎,其中以后者为主,可以按照下面的思路配置
1. 为操作系统预留20%的内存,约为200M
2. 与现场相关的几个关键参数如下
sort_buffer_size=2M
read_buffer_size=2M
read_rnd_buffer_size=2M
join_buffer_size=2M
预计链接数到达峰值时,线程最大可能占用10 * (2 + 2 + 2 + 2) = 80M
3. MyISAM引擎的表对象不多,主要是系统的对象,记录条数有限,因此与之相关的几个缓存区就没必要分配太多内存,相关设置参数如下
key_buffer_size=16M
key_cache_block_size=64K
myisam_sort_buffer_size=64M
4. 剩下的空间 1000M - 200 - 80 = 720M, 就可以全部分配给InnoDB的缓存池,设定相关参数如下
innodb_buffer_pool_size=720M
innodb_thread_concurrency=8
innodb_flush_method=O_DIRECT
innodb_log_buffer_size=16M
innodb_flush_log_at_trx_commit=2
其他的查询缓存池,二进制日志缓存等配置相对来说占用的空间有限,这里就不一一提及了。MySQL的初始文件内容如下
sort_buffer_size=2M
read_buffer_size=2M
read_rnd_buffer_size=2M
join_buffer_size=2M
key_buffer_size=16M
key_cache_block_size=64K
myisam_sort_buffer_size=64M
innodb_buffer_pool_size=720M
innodb_thread_concurrency=8
innodb_flush_method=O_DIRECT
innodb_log_buffer_size=16M
innodb_flush_log_at_trx_commit=2
5. 测试提升
还是使用tpcc命令,./tpcc_start -h 127.0.0.1 -P 3306 -u root -pZj123$%^ -d tpcc -w 10 -c 10 -r 100 -l 300 -f tpcc_mysql.log -t tpcc_mysql.rtx,结果如下,看结果提升还是挺大的,看<TpmC>部分的每分钟处理订单量有311.400变成了817.800
...
10, trx: 136, 95%: 1218.908, 99%: 1831.904, max_rt: 2236.249, 134|1867.656, 13|1527.122, 12|4758.197, 15|7153.962
20, trx: 176, 95%: 1663.076, 99%: 1845.112, max_rt: 2769.179, 178|1855.024, 18|397.819, 18|2977.147, 17|2180.252
...
<Raw Results>
[0] sc:0 lt:4089 rt:0 fl:0 avg_rt: 490.3 (5)
[1] sc:48 lt:4042 rt:0 fl:0 avg_rt: 211.0 (5)
[2] sc:28 lt:381 rt:0 fl:0 avg_rt: 238.3 (5)
[3] sc:0 lt:408 rt:0 fl:0 avg_rt: 1926.3 (80)
[4] sc:4 lt:406 rt:0 fl:0 avg_rt: 592.6 (20)
in 300 sec.
<Raw Results2(sum ver.)>
[0] sc:0 lt:4089 rt:0 fl:0
[1] sc:48 lt:4042 rt:0 fl:0
[2] sc:28 lt:381 rt:0 fl:0
[3] sc:0 lt:408 rt:0 fl:0
[4] sc:4 lt:406 rt:0 fl:0
<Constraint Check> (all must be [OK])
[transaction percentage]
Payment: 43.48% (>=43.0%) [OK]
Order-Status: 4.35% (>= 4.0%) [OK]
Delivery: 4.34% (>= 4.0%) [OK]
Stock-Level: 4.36% (>= 4.0%) [OK]
[response time (at least 90% passed)]
New-Order: 0.00% [NG] *
Payment: 1.17% [NG] *
Order-Status: 6.85% [NG] *
Delivery: 0.00% [NG] *
Stock-Level: 0.98% [NG] *
<TpmC>
817.800 TpmC
...
分析慢查询日志
慢查询日志会有以下的问题
- 慢查询日志变得越来越大,记录越来越多,逐行查看已不现实。
- 大量的慢查询日志,可能只是由某几条SQL语句触发的,这几条SQL语句有可能正是导致瓶颈的最大嫌疑,怎么快速找出他们呢?
针对这两个问题,可以使用mysqldumpslow和mysqlsla两个命令解决
mysqldumpslow
这个命令是mysql自带的,专门用来分析慢查询日志的工具,这是一段用perl语言编写的脚本。他能将类似的SQL语句(即语句抽象,将SQL语句相同但语句的值不同)归为一组显示。
就像下面的源文件
/usr/sbin/mysqld, Version: 5.7.23-log (MySQL Community Server (GPL)). started with:
Tcp port: 0 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
# Time: 2018-11-25T08:19:18.067587Z
# User@Host: root[root] @ [192.168.23.1] Id: 3
# Query_time: 0.471453 Lock_time: 0.101151 Rows_sent: 5002 Rows_examined: 7508
use chinabrands_db;
SET timestamp=1543133958;
/* ApplicationName=DataGrip 2018.2.5 */ select *
from goods where goods_title like "%c%c%";
# Time: 2018-11-25T08:19:55.087293Z
# User@Host: root[root] @ [192.168.23.1] Id: 3
# Query_time: 0.485758 Lock_time: 0.000112 Rows_sent: 5002 Rows_examined: 19584
SET timestamp=1543133995;
/* ApplicationName=DataGrip 2018.2.5 */ select *
from goods where goods_title like "%b%b%";
# Time: 2018-11-25T08:19:59.257479Z
# User@Host: root[root] @ [192.168.23.1] Id: 3
# Query_time: 0.317772 Lock_time: 0.000129 Rows_sent: 5002 Rows_examined: 6916
SET timestamp=1543133999;
/* ApplicationName=DataGrip 2018.2.5 */ select *
from goods where goods_title like "%a%a%";
使用该命令分析之后的结果为
[root@jin mysql]# mysqldumpslow jin-slow.log
Reading mysql slow query log from jin-slow.log
Count: 3 Time=0.39s (1s) Lock=0.03s (0s) Rows=5002.0 (15006), root[root]@[192.168.23.1]
/* ApplicationName=DataGrip N.N.N */ select *
from goods where goods_title like "S"
Count: 1 Time=0.00s (0s) Lock=0.00s (0s) Rows=0.0 (0), 0users@0hosts
这个命令本身的参数比较少,常用的就更少了(mysqldumpslow本身使用就比较少),介绍一些比较有用的参数
- -s t : 按照总的查询时间排序
- -s at : 按照平均查询时间排序
- -s l : 按照总的锁定时间排序
- -s al : 按照平均锁定时间排序
- -s s : 按照总的记录行数排序
- -s as : 按照平均记录行数排序
- -s c : 按照语句执行次数排序
- -r : 按照排序规则倒序输出,也就是说先执行-s参数指定规则,而后将数据倒序输出
- -t : 控制输出的SQL语句的数量。比如慢查询中有共有10000条记录,愁向后产生100条不同的SQL语句,但是实际上后面的90条,每个只执行了一次,前面的10
条SQL语句产生的9910次慢查询,对于DBA来说,重点关注前面10条就行了。这个参数就是做这个的
mysqlsla
安装
- 先安装依赖
yum -y install perl-CPAN perl-Time-HiRes perl-DBI perl-DBD-MySQL
perl -MCPAN -e shell
- 安装mysqlsla
perl Makefile.PL
make && make install
使用
mysqlsla -lt slow SlowLogPath
使用mysqlsla分析上面的错误日志
[root@jin mysqlsla]# mysqlsla -lt slow /var/lib/mysql/jin-slow.log
Report for slow logs: /var/lib/mysql/jin-slow.log
4 queries total, 1 unique
Sorted by 't_sum'
Grand Totals: Time 4 s, Lock 0 s, Rows sent 135.53k, Rows Examined 199.97k
______________________________________________________________________ 001 ___
Count : 4 (100.00%)
Time : 3.968118 s total, 992.029 ms avg, 317.772 ms to 2.693135 s max (100.00%)
Lock Time (s) : 101.969 ms total, 25.492 ms avg, 112 ?s to 101.151 ms max (100.00%)
Rows sent : 33.88k avg, 5.00k to 120.52k max (100.00%)
Rows examined : 49.99k avg, 6.92k to 165.96k max (100.00%)
Database : chinabrands_db
Users :
root@ 192.168.23.1 : 75.00% (3) of query, 75.00% (3) of all users
root@localhost : 25.00% (1) of query, 25.00% (1) of all users
Query abstract:
SET timestamp=N; SELECT * FROM goods WHERE goods_title LIKE "S";
Query sample:
SET timestamp=1543133958;
select *
from goods where goods_title like "%c%c%";
mysqlsla支持分析二进制日志,普通查询日志,慢查询日志等
在命令行指定参数时,参数的格式一般为--option,不过-option也能被支持,甚至能够支持简写形式,比如--top可以简写成-to,只要简写后的值是唯一的就行。有些选项为了方便使用还指定了别名,比如--database可以用--db或-D。
mysqlsla常用参数
1. --log-type(-lt) : TYPE LOGS
用来指定分析的日志文件类型,作为重要也是最常用的选项。这个参数目前有3种选项。
- slow : 慢查询日志
- general : 普通查询日志
- binary : 二进制日志,注意需要先通过mysqlbinlog处理
2. --abstract-in(-Ai) N
用于抽象处理in(...)语句,默认不启用
该参数主要用来抽象in语句的表现方式,后面指定的N参数值用于定义in值(数据)的范围,是指是个语句的分组统计方法,比如当指定-Ai 10时,如果in中的值的数量在0~10之间的抽象成一条语句,10~20之间的又抽象成一条语句,依次类推,抽象后的in语句就会变成in(S0-9),in(S10-19),in(S20-29)...的形式,每个in数量的语句都被抽象成一条。
例如,分析指定的慢查询日志,对于in语句按照值的个数每100个做分级。
mysqlsla -lt slow -Ai slowlog.log
3. --abstruct-values(-Av)
抽象values语句,按照values值的个数显示,默认不启用
默认情况下,比如像VALUES ('FOO', 'BAR'...)抽象后就变成与VALUES ('S','S'...)类似的形式。如果指定了-Av选项,那么上述形式就变成了VALUES ('S')2。跟--Ai参数功能类似,只是处理的策略不同。
4. --explain(-ex)
显示每条查询的执行计划,默认不启用,不过某些场景下还是有用处的。
5. --databases(-db)(-D) DATABASES
当指定了explain选项时需要指定本参数,以便于能够到正确的数据库中获取语句执行的执行计划,可以一次指定多个数据库名,相互间以逗号分隔。
由于使用explain语句时需要知道该语句查询的对象属于哪个数据库,因此-D选项是必要的。注意UDL日志不支持执行计划。执行explain时,mysqlsla会根据-D执行的db名逐个尝试,知道成功(失败也不会报错)。
6. --microsecond-symbol(-us) STRING
已字符串方式显示毫秒值,默认显示为us,某些字符集下可能让人看起来感觉像乱码一样。
7. --statement-filter (-sf) CONDITIONS
过滤SQL语句类型,默认不启用。指定的CONDITIONS格式为 ： +-,[TYPE]
[+-]只出现一次,用来表示包含(+)或不包含(-)。
[TYPE]可以指定多个,相互间以逗号分隔即可,用来指定具体的过滤关键字,比如:SELECT/CREATE/DROP/UPDATE/INSERT。
8. --top N
只显示topN的查询,默认值为10.
例如,分析指定的慢查询日志,并列出前50条:
关注系统状态
MySQL服务在做什么
SHOW [FULL] PROCESSLIST
这个命令会将每一个连接的线程,作为一条独立的记录输出,每一条记录都包括下列值
- Id : 当前链接的标识ID号,自增序列
- User : 当前链接所使用的用户
- Host : 来访的服务器
- Db : 当前访问的数据库,如果未选择任何数；据,则本列会显示为NULL
- Command : 标识当前所执行的操作类型。具体信息可参考https://dev.mysql.com/doc/refman/5.6/en/thread-commands.html
- Time : 该会话保持在当前这个状态的时间,以秒为单位,每次更换状态时,时间即被充值。
- State : 显示当前会话的状态,比如是在检查表还是发送数据,是在注册master还是在等待接收日志等,这个状态的数量就更多了,MySQL官方文档从8.12.5.2到8.12.5.10小节均是在描述各种状态,有兴趣的可以看看
- Info : 当前连接正在执行的操作,通常是SQL语句。默认情况下,Info列只会显示当前所执行的前100个字符,如果希望查看完整的内容,可以在执行show processlist时加上full关键字。如果当前什么也没做(Command中标记当前操作类型为Sleep),则本列会显示为NULL。
除了通过show peocesslist命令获取线程的信息外,执行mysqladmin命令附加processlist选项,又或者查询information_schema.processlist表对象,都能够获取相对应的信息。在执行show processlist时,如果用户拥有super权限,那就能看到所有的线程信息,否则只能看到自有的(当前用户创建的线程)
对于有用过show processlist操作经验的朋友,可能会发现,某些时间刷新processlist,会发现用户列为"unauthenticated user",看到未认证的用户这类字眼,不用担心,不是被攻击了,这个状态其实是那些已经与服务端建立了连接但是还没有完成验证的用户。
MySQL中的每个连接,都是独立的线程,对于异常的可以使用KILL命令清除。
例如,要清除48号进程
kill 48;
对于KILL命令来说,实际上有两种清除策略。
- 直接杀掉线程,即
kill thread_id - 终止线程当前正在执行的操作,即
kill query thread_id
注意,kill命令执行后,MySQL可能不是马上就干掉指定的线程,如果该线程正在执行操作,那么MySQL需要先中止该操作,涉及数据读写的话,那就该提交的提交,该回滚的回滚(相当于执行kill query thread_id),等到确认无误后才正在杀掉线程
MySQL语句在做什么
show processlist可以查看当前系统的线程信息,但如果需要查看SQL语句在执行时具体做了什么,可以用show profiles和show profile命令
show profiles
show profiles用于显示最近执行的语句(以及语句执行的时间开销)。show profiles显示执行语句相关的信息时,受制于两方面的因素:
- 首先,资源统计是一个名为profiling的状态变量控制,因此得先确定当前系统是否启用了资源统计,若未启用,那么show profiles命令会返回空
- 所显示的最近执行语句条数,由系统变量profiling_history_size控制,默认为15,最大值不超过100。
show profile命令用于显示(单个)语句执行时的详细资源信息。默认将显示最近(show profiles中记录的)执行过的语句所使用的总和信息,不过他支持for query子句,当指定for query n,就可以查询具体的某条语句执行时所使用的资源信息,同时show profile还支持limit子句,这样就可以用来限制输出的记录。
语法 :
show profile [type[,type]...]
[for query n]
[limit row_count [OFFSET offset]]
type :
ALL|BLOCK IO|CONTEXT SWITCHES|CPU|IPC|MEMORY|PAGE FAULTS|SOURCE|SWAPS
默认情况下,show profile只显示Status和Duration列,其中Status列中内容,与show processlist命令中的State内容相同。另外show profile还有一个type关键字,对于可选的type关键字来说,他可以控制show profile命令输出下列附加信息
- ALL : 显示所有信息
- BLOCK IO : 显示输入/输出的块数量
- CONTEXT SWITCHED : 显示换页操作的
后面的都丢失了,我去...