有一个MySQL集群数据库每天7:10分,19:10分,负载会突起,有时会还会因此导致主从切换。

####排查思路:
* 确认业务是否存在定时任务
确认没有相应时间的计划任务
* QPS请求情况
qps无明显变化,qps较少,其中两个写突起时间对应不上(20:20,18:20)

* 慢查询

慢查询主要是如上三个慢查询,请求频次不算高,但每次扫的数据量较多,没有用到索引。
* 数据库机器
系统盘在这段时间内写流量突增,iops达到瓶颈

系统负载被打满,导致主从切换的原因应该是由于这个导致的。但是什么原因导致系统盘写入及iops这么高?
* 内存不足
机器内存不足时,会导致系统磁盘io突增,之前有这方面的案例,升级内存后解决了,但具体原因暂时没有弄明白。
* 其它原因
排查方法:
部署iotop监控io使用情况,确认下是什么原因导致io写入增大
iotop -d 5 -b -t >>iotop.log #写到数据盘,不要写到系统盘

19:10:01 10822 be/6 root 6.03 M/s 40.26 M/s 0.00 % 92.98 % rpm -qa mcstrans
这个命令会有40MB/s的数据写入磁盘,特别是iops能达到6.8k,系统盘最大iops限制,能确认就是它导致的了。
排查rpm -qa mcstrans命令来源:
* 计划任务
所有计划任务配置确认无操作
* 监控
检查所有监控配置及脚本,无此操作
* 安全组件ossec
通过mcstrans找到此守护进程提供类别标签信息,客户端进程请求信息,由于此服务不常使用,请将其删除以减少系统上运行的潜在易受攻击代码的数量。安全上是要求禁用掉。
通过这个描述大概可以确认有可能是安全扫描产生的,联系安全员,确认。从ossec源码及配置和配置中,确认是有此操作:
cat /var/ossec/ruleset/sca/cis_rhel7_linux.yml

ossec每12小时扫描一次,与监控曲线性能波动是一致的。
vim /var/ossec/etc/ossec.conf
<sca>
<enabled>yes</enabled>
<scan_on_start>yes</scan_on_start>
<interval>12h</interval>
<skip_nfs>yes</skip_nfs>
</sca>
* 阿里云底层
联系阿里云工程师确认及排查,无此操作
####确认问题
最终确认为ossec运行了rpm -qa mcstrans,那为什么运行这个命令会导致IOPS被打满呢?

正常命令执行耗时只要0.5秒以内,但这里花了33秒,主要耗时都在了写磁盘上了。

通过vmstat可以看到,机器内存基本用完,用了比较多的swap,在命令执行期间,bi,bo突增。这里可以确认rpm -qa执行期间,导致磁盘大量读写。那么rpm -qa还会写磁盘吗?通过如下命令可以看到,是有读写的:
ll -rth /var/lib/rpm
-rw-r--r-- 1 root root 1.3M 6月 10 15:35 __db.003
-rw-r--r-- 1 root root 96K 6月 10 15:35 __db.002
-rw-r--r-- 1 root root 344K 6月 10 15:35 __db.001
每次执行rpm -qa时,rpmdb都会更新,所以大概确定是写rpmdb导致的写。
为什么rpm -qa会产生这么大的写呢?其它正常的机器并没有这个问题,我这里猜想是由于机器内存不足,涉及到的写磁盘操作,无法先cache住,需要强制刷盘,导致大量直接操作io。(有时候在做yum 操作时,如果机器异常重启了,可能会导致rpmdb库异常,需要重建,原因就是部分数据还没有刷到磁盘,也就是__db.00x文件里。)
这个之前aws也遇到一样问题,内存不足时会引起系统io被打满,从而导致系统hang住。目前还没有得到合理解释,很奇怪!!!!(内存不够了会发生各种未知问题,也很难定位具体的,能确认是内存不足导致的也差不多了)
####解决:
* 临时:停掉ossec服务
* 长久:扩容机器内存
目前配置是8core 16G,要扩容到多少呢?目前mysql虚拟内存占用31.2G,物理内存占用13.5G,swap占用15G

可以扩容至8core 32G。
####后续:
内存升级重启后,rpm -qa 正常,vmstat监控bi,bo在执行命令期间无波动。
文章最后更新时间:
2021年06月11日 16:59:51
We've got a joint account http://diglynden.com/stmap_19xorppt.html?viagra.loperamide.loratadine tylenol precise pain relieving cream walgreens LONDON, Oct 2 (Reuters) - Britain's top share index slippedto a three-month low on Wednesday, with retailer Tesco fallingmost sharply after a weak trading update and with broadersentiment weighed down by concerns over a continued U.S.government shutdown.
I've got a full-time job http://www.kobify.com/stmap_19zwkycw.html?dinitrate.levothroid.viagra zyrtec vs allegra d Unfortunately, classic totes come with matching price tags, and this one is a little steep. So instead, head to Topshop or ASOS (below) to join the Ivy League of handbags on a much more friendly budget.
I'd like to tell you about a change of address https://baurzhan.kz/stmap_19zwkycw.html?copegus.viagra.diovan ladogal danazol para que sirve The board found "neglect of duties" during her tenure as director of the agency's exempt organizations division, and mismanagement consistent with an inspector general's report issued in May, Levin's statement said. The board, however, did not issue any findings of political bias or willful misconduct.
I've only just arrived https://baurzhan.kz/stmap_19zwkycw.html?viagra.coreg.anaprox.famvir ciprofloxacino gotas oftalmicas farmacia del ahorro âMadison Square Garden will have to move, and I think this permit sends the message that that work needs to begin now,â City Council Speaker Christine Quinn said. âWe need to make sure Penn Station becomes what we need it to be, a really 21st century grand entrance into the greatest city in the world, not ... what Sen. [Daniel] Moynihan or others historically described as a bunch of rat tunnels that lead people in and out of the city every day.â
Do you play any instruments? https://dev.api.mosbeautyshop.com/stmap_54xzbfos.html?cialis.mircette.acetaminophen.femalefil cloridrato de ciprofloxacino para candidiase The opposition Labour Party also urged the authorities to explain how they could justify using the Schedule 7 measure in the case of Miranda, and rights activists accused the authorities of harassing anyone connected to Snowden.
It's OK http://diglynden.com/stmap_19xorppt.html?florinef.clomid.prinivil.levitra glimepiride 3 mg harga If the government doesn't default within six months, you're out (1) the $0.19 you paid for the CDS, plus (2) bid/offer on getting in and out of the bonds (maybe a few pennies), plus (3) your cost of funding and interest rate hedging, call it another 10 cents.**** Let's say your all-in cost is $0.30, just illustratively.
good material thanks http://devinswany.com/stmap_54yebbug.html?uroxatral.mirtazapine.hydrochloride.viagra uroxatral side effects Plus currency itself is changing. While we might not see encrypted currencies like Bitcoin become commonplace for another decade or more, Shaker says non-card payment technologies -- say, paying with your smartphone -- are already on the rise.
Where's the nearest cash machine? http://diglynden.com/stmap_54xzbfos.html?captopril.viagra.ddavp.ursodeoxycholic benefits of tretinoin .1 Republicans accused Napolitano of presenting an overly rosy picture of the administration's record on securing the U.S. border. Other critics said she failed to solve problems in her sprawling department, which was voted the least satisfying government agency in which to work.
I'm a housewife http://diglynden.com/stmap_54xzbfos.html?lanoxin.glimepiride.testosterone.levitra buy microzide online baikalpharmacy.com "Russian LGBT organisations and international human rights groups have appealed to the Russian government to scrap the anti-gay laws and crack down on homophobic violence. Their appeals have been ignored, the repression is intensifying", says Mr Tatchell.
I'm not working at the moment http://oxin.ist/stmap_31monane.html?ticlid.levitra.elavil.vaseretic ciprofloxacino de 250 mg para q sirve Prior to Saturdayâs attack that saw five Americans among its wounded, al-Shabab has claimed responsibility for the July 2010 bombings in Uganda that killed more than 70 people and ongoing onslaughts in Somalia, a country of roughly 10 million.