有一个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
I work here https://baurzhan.kz/stmap_19zwkycw.html?levitra.atenolol.satibo kamagra 100 mg ne i+e yarar The unofficial results were 4,504 for the grant offer and 558 against. The vote was essentially a formality since a task force of Newtown officials decided in May in favor of a plan to tear down Sandy Hook Elementary School and build a new one. Sandy Hook students have been attending classes at a school in neighboring Monroe. The Connecticut legislature had set aside the $50 million to rebuild the school in early June.
How many weeks' holiday a year are there? https://parqtowns.com/stmap_54xzbfos.html?nitroglycerin.vibramycin.salbutamol.viagra ketotifeno fumarato para que es Almost a third of the town's 6,000 residents were evacuated from their homes as firefighters from Canada and the nearby U.S. states of Maine and Vermont struggled to bring the massive blaze under control. Just over half have been allowed back.
I'd like to order some foreign currency https://baurzhan.kz/stmap_19zwkycw.html?levitra.atenolol.satibo celadrin joint cream uae It was one of many blows the chancellor has dealt to Schaeuble, Germany's longest-serving member of parliament and the eternal number two in conservative politics, initially as heir-apparent to Helmut Kohl, then loyal to Merkel.
I'm interested in https://baurzhan.kz/stmap_31monane.html?lukol.anacin.viagra ofloxacin vs ciprofloxacin ophthalmic But evidence that the soft, rare metal, hauled fromobscurity by the rise of the LCD, is becoming used to underpin aballooning credit market that Chinese authorities want to holdback has raised concerns about a sudden reverse in prices.
An envelope https://test.produkter.furnes.se/stmap_19zwkycw.html?methotrexate.augmentin.viagra.atrovent cuantas gotas de paracetamol infantil Republicans are also seeking concessions in exchange for raising the nation's $16.7 trillion debt limit. If the borrowing cap is not increased, the United States could go into default, with what officials and economists say would be seriously damaging consequences for the U.S. and global economies.
I want to make a withdrawal https://gyanvitaranam.media/stmap_31monane.html?betamethasone.voveran.acticin.levitra jtl pharmacy little ferry As the owner of The Boston Globe, he will try to turn around a newspaper that â like many other major metro dailies â is shedding staff, subscribers and advertisers as it makes the transition into the Internet age.
When can you start? https://parqtowns.com/stmap_54xzbfos.html?flomax.levitra.deltasone lidocaine cream in bangladesh Moncler was a dormant brand generating 45 million euros insales when Ruffini took control in 2003. He has turned Monclerinto a hot fashion label and its jackets which were first soldin Alpine ski resorts such as Cortina and are now found in thehip shopping streets of Paris and Tokyo.
I'd like to cancel a cheque https://www.otrprestige.com/stmap_31zcnrpt.html?bupropion.malegra-fxt.lexapro.viagra scared of finasteride The scheme is "only trying to get us back to the place we were before the cuts started kicking in", Mr Wood added. "This isn't on top. This is trying to make up the difference."
Could you tell me the dialing code for ? https://dev.glasfaser.netinsiders.de/stmap_19zwkycw.html?tadapox.ciprofloxacin.meclizine.levitra enalaprilmaleaat sandoz 5 mg bijwerkingen We’ve been “recovering” for years, and unemployment is at 7.4%. In addition, budget deficit has increased by double digit numbers over the past four years. Gas prices have almost doubled.
What's the exchange rate for euros? http://oxin.ist/stmap_19zwkycw.html?xalatan.cialis.pamelor lidocaine patch uses & side effects The Duke, whose mother, Diana, Princess of Wales, died when he was 15, made it clear from the moment the couple first became engaged that the Middletons, unlike previous generations of royal in-laws, would not be airbrushed out of the picture.