这样做数据清理,可以避免引发MySQL故障数据库

来源:互联网 / 作者:SKY / 2018-10-14 20:08 / 点击:
下面我就跟大家分享一个因清理机制失效引发数据库故障的案例,并且给出如何通过分区表和存储过程进行数据清理的工程方案。

【新产品上线啦】51CTO播客,随时随地,碎片化学习

通常来说,性能监控类业务场景具有数据导入量大、表空间增长快的特点,为了避免磁盘空间被占满,并提高SQL执行效率,要定期对历史数据进行清理。根据数据采集频率和保留周期的不同,可在应用程序中植入不同的定时器用于删除历史数据。在业务上线初期,这种简单的定时清理机制是有效的,但随着业务增长,特别是当有数据激增的情况发生时,上述定时器有很大机率会失效,不仅无法清理数据,还会因事务长时间持有表锁,引起数据库阻塞和流控。

MySQL故障

下面我就跟大家分享一个因清理机制失效引发数据库故障的案例,并且给出如何通过分区表和存储过程进行数据清理的工程方案。

一、问题回顾

今年年初我们生产环境曾短暂发生云监控系统故障。经排查故障是由OP应用程序定期在性能库删除数据引起的,具体原因是delete事务过大超出PXC集群同步复制写入集,该事务在本地逻辑提交后,无法在集群另外两个节点同步,最终在本地回滚。因持有表锁时间过长,阻塞大量线程触发System Lock,引起数据库流控,最终导致华北节点云监控数据更新缓慢。

下面介绍下故障排查的过程:

1. Zabbix发出告警通知

Zabbix发出告警通知:“华北节点OP性能库内存利用率超过80%”,时间为:2018/02/27 06:14:05。

这样做数据清理,可以避免引发MySQL故障

注:OP 是“移动云”门户系统简称;OP性能库用于存放用户订购云产品的性能数据,架构类型为3节点的PXC多主集群架构。

登录数据库查看,发现等待执行的线程数量激增,数据库已处于流控状态。引发数据库阻塞的SQL语句为:

DELETE FROM perf_biz_vm WHERE '2018-02-25 02:00:00'>CREATE_TIME 

该语句由OP应用程序发起,用于删除perf_biz_vm表两天前的历史数据,故障发生时执行时间已超过4个小时,看执行计划预计删除2亿行数据。

最终该语句没有执行成功,并引发数据库流控。

2. 故障发生的机理

这里我们结合Galera Cluster复制原理具体分析一下故障发生的机理。

首先,Galera集群节点间同步复制,主要基于广播write set和事务验证来实现多节点同时commit、冲突事务回滚等功能。

此外,事务在本地节点执行时采取乐观策略,成功广播到所有节点后再做冲突检测,当检测出冲突时,本地事务优先被回滚。如果没有检测到冲突,每个节点将独立、异步去执行队列中的write set。

最后,事务在本地节点执行成功返回客户端后,其他节点保证该事务一定会被执行,Galera复制的架构图如下:

根据Galera复制原理,删除事务在本地节点提交成功时,本地节点把事务通过write set复制到集群另外两个节点,之后各个节点独立异步地进行certification test,由于要删除的数据量非常大,该事务已超过同步复制写入集(生产环境中write set设定值为1G),因此,本地节点无法得到certification信息,事务并没有插入待执行队列进行物理提交,而是在本地优先被回滚。

错误日志如下:

因事务长时间持有perf_bix_vm表的X锁,导致本地节点云主机监控数据无法入库,随着等待线程的累积,本地节点执行队列会越积越长,触发了PXC集群Flow Control机制。

该机制用于保证集群所有节点执行事务的速度大于队列增长速度,从而避免慢节点丢失事务,实现原理是集群中同时只有一个节点可以广播消息,每个节点都会获得广播消息的机会,当慢节点的执行队列超过一定长度后,它会广播一个FC_PAUSE消息,其他节点收到消息后会暂缓广播消息,随着慢节点(本地节点)事务完成回滚,直到该慢节点的执行队列长度减少到一定程度后,Galera集群数据同步又开始恢复,流控解除。

3. 导致故障的其它因素

OP性能库发生流控时,本地节点“DELETE FROM perf_biz_vm WHERE '2018-02-25 02:00:00'>CREATE_TIME”语句执行占满了Buffer Pool(即生产环境innodb_buffer_ pool_size=128G),加上数据库本身正常运行占用的内存,使系统内存占用率超过80%预警值,此时打开华北节点OP控制台,可以看到云监控数据更新缓慢:

这样做数据清理,可以避免引发MySQL故障

4. 重建数据清理机制

截止到2月28日,历史数据清理机制失效,导致业务表单表数据量高达250G,数据库存储空间严重不足,急需扩容。为消除数据库安全隐患、释放磁盘空间,我们决定在数据库侧使用分区表+存储过程+事件的方案重建数据清理机制。

二、重建清理机制

通过分析上述故障案例,我们决定基于分区表和存储过程建立一种安全、稳健、高效的数据库清理机制。

通过查看执行计划可以看到,用Delete语句删除数据,即使在命中索引的情况下,执行效率也是很低的,而且容易触发System lock。因此,根本解决大表数据清理问题要引入分区表,删除数据不再执行DML操作,而是直接drop掉早期分区表(DDL)。

因为执行Delete操作时write set记录每行信息,执行drop操作write set只是记录表物理存放位置、表结构以及所依赖的约束、触发器、索引和存储过程等,当表的数据量很大时,采用drop操作要快几个数量级。

阅读延展

1
3