近期有套系统软件数据库查询礼拜天一直报警,CPU利用率超出90%,刚开始由开发设计那里再跟踪解决,因为我就没参加,之后发现没进度就登陆上来看过下,随后开展了一部分提升,提升后实际效果還是较为显著的,实际提升全过程文中会做详尽的论述。

一、状况叙述

数据库查询网络服务器CPU利用率超出90%,而此数据库查询构架为mycat相匹配的一主三从(以前一主二从,因为CPU利用率高,开发设计那里对库干了拓展,从负载平衡的视角减少CPU工作压力,从实际效果上看没做到需有的实际效果),在其中mycat的负载对策是3,即全部读操作分派到从库上进行,但具体是主库抓包软件发现也会出现很多的查寻操作。

MySQL案例05:CPU负载优化 简单网络管理协议 数据库  第1张

zabbix监管数据信息:

MySQL案例05:CPU负载优化 简单网络管理协议 数据库  第2张

二、难题缘故

 1、mycat读写分离,读操作为什么会分发到主库

历经tcpdump抓包软件和general_log抓包软件剖析,的确存有着很多的select操作,刚开始原以为是mycat的读写能力负载的配备有什么问题(设定了读写能力负载2,造成全部数据库查询均分发select),之后查验发现mycat配备的是1,后改成3,状况依然。这儿我也想起了是不是启用存储过程、事务管理的打开和关掉,历经检测和追踪发现,的确在事务管理手动式打开和关掉的状况下,select查寻操作会分发到主库。这类缘故是mysql默认设置刚开始autocommit=1,也就是默认设置全自动递交,而一旦程序流程根据set autocommit=0;update ..../select ..../select ...;commit;set autocommit=1;相近这类操作,那麼这种事务管理会分发到主库,造成主库拥有很多的select查寻。

2、cpu负载往往高,是因为很多的select操作造成的

在并发量大的状况下,很多的sql操作会造成cpu資源耗费比较严重,尤其是在sql实行比较慢的状况下,因此 大家最先要做的便是对负载较高的时间范围,爬取有关的SQL开展剖析,对于mysql大家自然要剖析的便是慢系统日志。

三、处理方式

 依据监管发现CPU负载在周一到周日,礼拜天的负载特别是在比较严重,和开发设计沟通交流后获知此系统软件关键应对的人群是一些礼拜天工作的朋友,这儿就对负载较高的14点至22点做慢日志分析系统,发现竟然慢系统日志非常少,这类状况是异常的。

MySQL案例05:CPU负载优化 简单网络管理协议 数据库  第3张

慢日志分析系统:pt-query-digest  --since='2018-05-20 14:00:00' --until='2018-05-20 22:00:00' slow.log  >slow.txt

备注名称:mysql5.7的慢系统日志时间格式默认设置是utc时间(比大家慢8钟头,可根据下边的主要参数调节)

SHOW GLOBAL VARIABLES LIKE 'log_timestamps';
set global log_timestamps=SYSTEM;

  MySQL案例05:CPU负载优化 简单网络管理协议 数据库  第4张

历经剖析,这种慢系统日志并不是造成CPU负载高的关键缘故,那麼为何这一时间范围慢系统日志那么少,又造成CPU负载那么比较严重呢?去查询mysql主要参数发现,慢系统日志设定的是三秒,这儿动态性调节为一秒后,一段时间后,爬取到许多有关的慢SQL,实际慢日志分析系统和提升我这里就不多说了,只不过是索引优化,SQL改变哪些的。

MySQL案例05:CPU负载优化 简单网络管理协议 数据库  第5张

提升后实际效果以下:(5.21日18:30做的提升)

MySQL案例05:CPU负载优化 简单网络管理协议 数据库  第6张

 到此CPU负载基础平稳,提升告一段落,需进一步监管。

20180522一天运作数据信息以下:

MySQL案例05:CPU负载优化 简单网络管理协议 数据库  第7张

提升后几日的观查实际效果以下:

MySQL案例05:CPU负载优化 简单网络管理协议 数据库  第8张

 四、TCPDUMP抓包软件脚本制作

tcpdump主要参数:

   -a    将IP地址和广播地址转化成姓名;
   -d    将配对信息包的编码以大家可以了解的选编文件格式得出;
   -dd   将配对信息包的编码以c语言汇编程序的文件格式得出;
   -ddd   将配对信息包的编码以十进制的方式得出;
   -e    在輸出行复印出数据链路层的头顶部信息内容,包含源mac和目地mac,及其传输层的协议书;
   -f    将外界的Internet详细地址以数据的方式复印出去;
   -l    使规范輸出变成缓存行方式;
   -n    特定将每一个监视到数据文件中的网站域名转化成IP地址后显示信息,不把IP地址转化成姓名;
      -nn:   特定将每一个监视到的数据文件中的网站域名转化成IP、端口号从运用名字转化成服务器端口后显示信息
   -t    在輸出的每一行不复印时间格式;
   -v    輸出一个略微详尽的信息内容,比如在ip库中能够 包含ttl和服务项目的信息内容;
   -vv   輸出详尽的报文格式信息内容;
   -c    在接到特定的包的数量后,tcpdump便会终止;
   -F    从特定的文档中载入关系式,忽视其他的关系式;
   -i    特定监视的网线端口;
     -p:    将网卡设置为非掺杂方式,不可以与host或broadcast一起应用
   -r    从特定的文档中载入包(这种包一般通过-w选择项造成);
   -w    立即将包载入文档中,并不剖析和复印出去;
     -s snaplen         snaplen表示从一个包中提取的字节。0表明包不断开,抓详细的数据文件。默认设置得话 tcpdump 只显示信息一部分数据文件,默认设置68字节数。
   -T    将监视到的包立即表述为特定的种类的报文格式,普遍的种类有rpc (远程控制全过程启用)和snmp(简易网络安全管理协议书;)
     -X      告知tcpdump指令,必须把协议书头移动和包內容都原封不动的显示信息出去(tcpdump会以16进制和ASCII的方式显示信息),这在开展协议书剖析时是肯定的神器。
-G 载入輸出汇报间隔时间,企业秒
src host ipaddr 源地址
dst host ipaddr 总体目标详细地址

 tcpdump抓包软件脚本制作

#!/bin/bash

date  "%Y-%m-%d %H:%M:%I"
tcpdump -i eth1 -s0 -G600 -l -w - dst port 3306 | strings | perl -e '
while(<>) { chomp; next if /^[^ ] [ ]*$/;
    if(/^(SELECT|UPDATE|DELETE|INSERT|SET|COMMIT|ROLLBACK|CREATE|DROP|ALTER|CALL)/i)
    {
        if (defined $q) { print "$q\n"; }
        $q=$_;
    } else {
        $_ =~ s/^[ \t] //; $q.=" $_";
    }
}'
date  "%Y-%m-%d %H:%M:%I"

、MYSQL实行次序

实行次序:from... where...group by... having.... select ... order by...