|
|
51CTO旗下网站
|
|
移动端

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

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

作者:DBAplus社群来源:今日头条|2018-10-14 15:52

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

MySQL故障

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

一、问题回顾

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

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

1. Zabbix发出告警通知

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

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

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

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

  1. 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操作要快几个数量级。

分区表的另一个好处是对于应用程序来说不用修改代码,通过对后端数据库进行设置,以表的时间字段做分区字段,就可以轻松实现表的拆分,需要注意的是查询字段必须是分区键,否则会遍历所有的分区表,下面看一下具体的实施过程:

Step 1:首先,创建分区表。在这里我们就以perf_biz_vm表为例,创建相同表结构的新表,并把它命名为perf_biz_vm_new,利用create_time索引字段做分区字段,按天做分区并与主键一起创建联合索引,创建语句:

代码如下:

  1. CREATE TABLE `perf_biz_vm_new` ( 
  2.  
  3. `CREATE_TIME` datetime NOT COMMENT '性能采集时间', 
  4.  
  5. `VM_ID` varchar(80) NOT COMMENT '虚拟机ID', 
  6.  
  7. `PROCESSOR_USED` varchar(100) DEFAULT COMMENT 'CPU利用率(%)', 
  8.  
  9. `MEM_USED` varchar(100) DEFAULT COMMENT '内存的使用率(%)', 
  10.  
  11. `MEM_UTILITY` varchar(100) DEFAULT COMMENT '可用内存量(bytes)', 
  12.  
  13. `BYTES_IN` varchar(100) DEFAULT COMMENT '流入流量速率(Mbps)', 
  14.  
  15. `BYTES_OUT` varchar(100) DEFAULT COMMENT '流出流量速率(Mbps)', 
  16.  
  17. `PROC_RUN` varchar(100) DEFAULT COMMENT 'CPU运行队列中进程个数', 
  18.  
  19. `WRITE_IO` varchar(100) DEFAULT COMMENT '虚拟磁盘写入速率(Mb/s)', 
  20.  
  21. `READ_IO` varchar(100) DEFAULT COMMENT '虚拟磁盘读取速率(Mb/s)', 
  22.  
  23. `PID` varchar(36) NOT , 
  24.  
  25. PRIMARY KEY (`PID`,`CREATE_TIME`), 
  26.  
  27. KEY `mytable_categoryid` (`CREATE_TIME`) USING BTREE, 
  28.  
  29. KEY `perf_biz_vm_vm_id_create_time` (`VM_ID`,`CREATE_TIME`) 
  30.  
  31. ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='虚拟机性能采集表' 
  32.  
  33. /*!50500 PARTITION BY RANGE COLUMNS(CREATE_TIME) 
  34.  
  35. (PARTITION p20180225 VALUES LESS THAN ('20180226') ENGINE = InnoDB
  36.  
  37. PARTITION p20180226 VALUES LESS THAN ('20180227') ENGINE = InnoDB
  38.  
  39. PARTITION p20180227 VALUES LESS THAN ('20180228') ENGINE = InnoDB
  40.  
  41. PARTITION p20180228 VALUES LESS THAN ('20180229') ENGINE = InnoDB
  42.  
  43. PARTITION p20180229 VALUES LESS THAN ('20180230') ENGINE = InnoDB) */ 

Step 2:用新的分区表替换原有旧表。这里需要注意的是,执行rename操作会对perf_biz_vm表的元数据进行修改,需提前检查有无对此表的Delete、Update、Insert事务与DDL操作,否则冲突会产生元数据锁(Metadata Lock)。

我们的做法是提前将业务侧的定时器停掉,并在业务低谷时执行如下语句,将旧表和新表通过rename的方式互换,让新表纳入使用。期间若有业务调用,则会短暂断开业务。

  1. rename table perf_biz_vm to perf_biz_vm_old; 
  2. rename table perf_biz_vm_new to perf_biz_vm; 

Step 3:查看到新表有数据写入,云监控页面数据显示正常,说明业务恢复。云主机监控数据的保存周期是两天,因此需要将旧表两天前的数据拷贝到新表,该步骤通过脚本来完成,可参考以下脚本:

代码如下:

  1. #!/bin/bash  
  2. function insert{  
  3. end_time="$1 $2"  
  4. start_time="$3 $4"  
  5. mysql -u'user' -p'passwd' << !  
  6. use monitor_alarm_openstack;  
  7. set innodb_flush_log_at_trx_commit=0 
  8. start transaction;  
  9. insert into perf_biz_vm select * from perf_biz_vm_old where create_time < '$end_time' and create_time > '$start_time';  
  10. commit;  
  11. select TABLE_ROWS from information_schema.tables where TABLE_SCHEMA ="monitor_alarm" and TABLE_NAME="perf_biz_vm" 
  12.  
  13.  
  14. base_time="2018-02-27 2:00:00"  
  15. while true  
  16. do  
  17. #end_time=$(date -d "-1hour $base_time" +%Y-%m-%d" "%H:%M:%S)  
  18. end_time=$base_time  
  19. start_time=$(date -d "-1hour $end_time" +%Y-%m-%d" "%H:%M:%S)  
  20. #base_time=$end_time  
  21. base_time=$start_time  
  22. echo "Cur_time: $(date +%Y%m%d" "%H%M%S)" | tee -a 1.log  
  23. echo "Range: $end_time $start_time" | tee -a 1.log  
  24. insert ${end_time} ${start_time} | tee -a 1.log  
  25. sleep 2  
  26. done 

Step 4:编写存储过程用于定期创建新的分区,并删除几天前旧的分区:

代码如下:

  1. delimiter $$  
  2. CREATE PROCEDURE `clean_partiton`(SCHEMANAME VARCHAR(64), TABLENAME VARCHAR(64),reserve INT)  
  3. BEGIN 

注:

  • 该储存过程适用于分区字段类型为datetime,按天分区且命名为p20180301格式规范的分区表
  • 获取最旧一个分区,判断是否为reserve天前分区,是则进行删除,每次只删除一个分区
  • 提前创建14天分区,判断命名不重复则创建
  • 创建 history_partition 表,varchar(200)和datetime类型。记录执行成功的SQL语句
  1. DECLARE PARTITION_NAMES VARCHAR(16);  
  2. DECLARE OLD_PARTITION_NAMES VARCHAR(16);  
  3. DECLARE LESS_THAN_TIMES varchar(16);  
  4. DECLARE CUR_TIME INT;  
  5. DECLARE RETROWS INT;  
  6. DECLARE DROP_PARTITION VARCHAR(16);  
  7. SET CUR_TIME = DATE_FORMAT(NOW,'%Y%m%d');  
  8. BEGIN  
  9. SELECT PARTITION_NAME INTO DROP_PARTITION FROM information_schema.partitions WHERE table_schema = SCHEMANAME AND table_name = TABLENAME order by PARTITION_ORDINAL_POSITION asc limit 1 ;  
  10. IF SUBSTRING(DROP_PARTITION,2) < DATE_FORMAT(CUR_TIME - INTERVAL reserve DAY, '%Y%m%d') THEN  
  11. SET @sql = CONCAT( 'ALTER TABLE ', SCHEMANAME, '.', TABLENAME, ' drop PARTITION ', DROP_PARTITION, ';' );  
  12. PREPARE STMT FROM @sql;  
  13. EXECUTE STMT;  
  14. DEALLOCATE PREPARE STMT;  
  15. INSERT INTO history_partition VALUES (@sql, now);  
  16. END IF;  
  17. end;  
  18. SET @__interval = 1 
  19. create_loop: LOOP  
  20. IF @__interval > 15 THEN  
  21. LEAVE create_loop;  
  22. END IF;  
  23. SET LESS_THAN_TIMES = DATE_FORMAT(CUR_TIME + INTERVAL @__interval DAY, '%Y%m%d');  
  24. SET PARTITION_NAMES = DATE_FORMAT(CUR_TIME + INTERVAL @__interval -1 DAY, 'p%Y%m%d');  
  25. IF(PARTITION_NAMES != OLD_PARTITION_NAMES) THEN  
  26. SELECT COUNT(1) INTO RETROWS FROM information_schema.partitions WHERE table_schema = SCHEMANAME AND table_name = TABLENAME AND LESS_THAN_TIMES <= substring(partition_description,2,8) ;  
  27. IF RETROWS = 0 THEN  
  28. SET @sql = CONCAT( 'ALTER TABLE ', SCHEMANAME, '.', TABLENAME, ' ADD PARTITION (PARTITION ', PARTITION_NAMES, ' VALUES LESS THAN ( "',LESS_THAN_TIMES, '" ));' );  
  29. SET @__interval=@__interval+1;  
  30. SET OLD_PARTITION_NAMES = PARTITION_NAMES;  
  31. END LOOP;  
  32. END  
  33. $$  
  34. delimiter ; 

Step 5:创建名称为clean_perf_biz_vm的事件,并在每天凌晨00:30:00的时候调用clean_partition存储过程创建下一个新分区,并删除两天前的旧分区。

  1. delimiter | 
  2.  
  3. CREATE DEFINER=’root’@’localhost’ event clean_perf_biz_vm on schedule every 1 day starts DATE_ADD(DATE_ADD(CURDATE,INTERVAL 1 DAY),INTERVAL 30 MINUTE) 
  4.  
  5. ON COMPLETION PRESERVE 
  6.  
  7. do 
  8.  
  9. begin 
  10.  
  11. call clean_partition(‘monitor_alarm’,’perf_biz_vm’,’2’); 
  12.  
  13. end | 
  14.  
  15. delimiter; 

Step 6:处理perf_biz_vm_old旧表,在业务低谷期执行如下操作:drop table if exists perf_biz_vm_old,Drop掉整张旧表的时间约为3min,并释放了150G的磁盘空间。需要注意的是,虽然drop table的时间较短,仍会产生短暂的阻塞,因为drop table触发的是实例锁,因此需要在业务低谷期进行操作,并实时观察数据库情况。

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

从下图可以看到,实际drop过程中记录到的等待接收队列的长度瞬时值为169,最高达到202:

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

至此,改造全部完成,我们已在数据库侧建立起安全、稳健、高效的数据清理机制。

三、结语

虽然本方案强调了存储过程的使用,但上述存储过程是基于简单的create和drop操作,并没有涉及复杂的逻辑和计算。MySQL是OLTP应用,最擅长的还是增、删、查、改这样简单的操作,对逻辑计算分析类的应用并不适合,所以尽量避免使用复杂的存储过程。

当然,也并不是所有场景都适合使用分区表,在很多DBA看来分区表在某些场景下是禁止使用的,一般会采用切表的形式进行拆分,本方案中使用时间做分区字段,应用程序中查询语句基本都能命中分区,对于Select、Insert等语句的执行性能是有所提升的。

【编辑推荐】

  1. 老司机也必须掌握的MySQL优化指南
  2. 8种手动和自动备份MySQL数据库的方法
  3. 高性能数据库连接池的内幕
  4. OSDI '18重磅解密:蚂蚁金服实时金融级分布式图数据库GeaBase
  5. 关于MySQL通用查询日志和慢查询日志分析
【责任编辑:赵宁宁 TEL:(010)68476606】

点赞 0
分享:
大家都在看
猜你喜欢

读 书 +更多

鸟哥的Linux私房菜 基础学习篇(第二版)

本书全面而详细地介绍了Linux操作系统。全书分为5个部分:第一部分着重说明Linux的起源及功能,如何规划和安装Linux主机;第二部分介绍Linu...

订阅51CTO邮刊

点击这里查看样刊

订阅51CTO邮刊