下面我就跟大家分享一个因清理机制失效引发数据库故障的案例, 并且给出如何通过分区表和存储过程进行数据清理的工程方案.
通常来说, 性能监控类业务场景具有数据导入量大, 表空间增长快的特点, 为了避免磁盘空间被占满, 并提高 SQL 执行效率, 要定期对历史数据进行清理. 根据数据采集频率和保留周期的不同, 可在应用程序中植入不同的定时器用于删除历史数据. 在业务上线初期, 这种简单的定时清理机制是有效的, 但随着业务增长, 特别是当有数据激增的情况发生时, 上述定时器有很大机率会失效, 不仅无法清理数据, 还会因事务长时间持有表锁, 引起数据库阻塞和流控.
下面我就跟大家分享一个因清理机制失效引发数据库故障的案例, 并且给出如何通过分区表和存储过程进行数据清理的工程方案.
一, 问题回顾
今年年初我们生产环境曾短暂发生云监控系统故障. 经排查故障是由 OP 应用程序定期在性能库删除数据引起的, 具体原因是 delete 事务过大超出 PXC 集群同步复制写入集, 该事务在本地逻辑提交后, 无法在集群另外两个节点同步, 最终在本地回滚. 因持有表锁时间过长, 阻塞大量线程触发 System Lock, 引起数据库流控, 最终导致华北节点云监控数据更新缓慢.
下面介绍下故障排查的过程:
1. Zabbix 发出告警通知
Zabbix 发出告警通知:"华北节点 OP 性能库内存利用率超过 80%", 时间为: 2018/02/27 06:14:05.
注: 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 控制台, 可以看到云监控数据更新缓慢:
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 索引字段做分区字段, 按天做分区并与主键一起创建联合索引, 创建语句:
代码如下:
- CREATE TABLE `perf_biz_vm_new` (
- `CREATE_TIME` datetime NOT COMMENT '性能采集时间',
- `VM_ID` varchar(80) NOT COMMENT '虚拟机 ID',
- `PROCESSOR_USED` varchar(100) DEFAULT COMMENT 'CPU 利用率(%)',
- `MEM_USED` varchar(100) DEFAULT COMMENT '内存的使用率(%)',
- `MEM_UTILITY` varchar(100) DEFAULT COMMENT '可用内存量(bytes)',
- `BYTES_IN` varchar(100) DEFAULT COMMENT '流入流量速率(Mbps)',
- `BYTES_OUT` varchar(100) DEFAULT COMMENT '流出流量速率(Mbps)',
- `PROC_RUN` varchar(100) DEFAULT COMMENT 'CPU 运行队列中进程个数',
- `WRITE_IO` varchar(100) DEFAULT COMMENT '虚拟磁盘写入速率(Mb/s)',
- `READ_IO` varchar(100) DEFAULT COMMENT '虚拟磁盘读取速率(Mb/s)',
- `PID` varchar(36) NOT ,
- PRIMARY KEY (`PID`,`CREATE_TIME`),
- KEY `mytable_categoryid` (`CREATE_TIME`) USING BTREE,
- KEY `perf_biz_vm_vm_id_create_time` (`VM_ID`,`CREATE_TIME`)
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='虚拟机性能采集表'
- /*!50500 PARTITION BY RANGE COLUMNS(CREATE_TIME)
- (PARTITION p20180225 VALUES Less THAN ('20180226') ENGINE = InnoDB,
- PARTITION p20180226 VALUES Less THAN ('20180227') ENGINE = InnoDB,
- PARTITION p20180227 VALUES Less THAN ('20180228') ENGINE = InnoDB,
- PARTITION p20180228 VALUES Less THAN ('20180229') ENGINE = InnoDB,
- PARTITION p20180229 VALUES Less THAN ('20180230') ENGINE = InnoDB) */
Step 2: 用新的分区表替换原有旧表. 这里需要注意的是, 执行 rename 操作会对 perf_biz_vm 表的元数据进行修改, 需提前检查有无对此表的 Delete,Update,Insert 事务与 DDL 操作, 否则冲突会产生元数据锁(Metadata Lock).
我们的做法是提前将业务侧的定时器停掉, 并在业务低谷时执行如下语句, 将旧表和新表通过 rename 的方式互换, 让新表纳入使用. 期间若有业务调用, 则会短暂断开业务.
- rename table perf_biz_vm to perf_biz_vm_old;
- rename table perf_biz_vm_new to perf_biz_vm;
Step 3: 查看到新表有数据写入, 云监控页面数据显示正常, 说明业务恢复. 云主机监控数据的保存周期是两天, 因此需要将旧表两天前的数据拷贝到新表, 该步骤通过脚本来完成, 可参考以下脚本:
代码如下:
- #!/bin/bash
- function insert{
- end_time="$1 $2"
- start_time="$3 $4"
- MySQL -u'user' -p'passwd' << !
- use monitor_alarm_openstack;
- set innodb_flush_log_at_trx_commit=0;
- start transaction;
- insert into perf_biz_vm select * from perf_biz_vm_old where create_time < '$end_time' and create_time > '$start_time';
- commit;
- select TABLE_ROWS from information_schema.tables where TABLE_SCHEMA ="monitor_alarm" and TABLE_NAME="perf_biz_vm";
- !
- }
- base_time="2018-02-27 2:00:00"
- while true
- do
- #end_time=$(date -d "-1hour $base_time" +%Y-%m-%d" "%H:%M:%S)
- end_time=$base_time
- start_time=$(date -d "-1hour $end_time" +%Y-%m-%d" "%H:%M:%S)
- #base_time=$end_time
- base_time=$start_time
- echo "Cur_time: $(date +%Y%m%d" "%H%M%S)" | tee -a 1.log
- echo "Range: $end_time $start_time" | tee -a 1.log
- insert ${
- end_time
- } ${
- start_time
- } | tee -a 1.log
- sleep 2
- done
Step 4: 编写存储过程用于定期创建新的分区, 并删除几天前旧的分区:
代码如下:
- delimiter $$
- CREATE PROCEDURE `clean_partiton`(SCHEMANAME VARCHAR(64), TABLENAME VARCHAR(64),reserve INT)
- BEGIN
注:
该储存过程适用于分区字段类型为 datetime, 按天分区且命名为 p20180301 格式规范的分区表
获取最旧一个分区, 判断是否为 reserve 天前分区, 是则进行删除, 每次只删除一个分区
提前创建 14 天分区, 判断命名不重复则创建
创建 history_partition 表, varchar(200)和 datetime 类型. 记录执行成功的 SQL 语句
- DECLARE PARTITION_NAMES VARCHAR(16);
- DECLARE OLD_PARTITION_NAMES VARCHAR(16);
- DECLARE LESS_THAN_TIMES varchar(16);
- DECLARE CUR_TIME INT;
- DECLARE RETROWS INT;
- DECLARE DROP_PARTITION VARCHAR(16);
- SET CUR_TIME = DATE_FORMAT(NOW,'%Y%m%d');
- BEGIN
- 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 ;
- IF SUBSTRING(DROP_PARTITION,2) < DATE_FORMAT(CUR_TIME - INTERVAL reserve DAY, '%Y%m%d') THEN
- SET @sql = CONCAT( 'ALTER TABLE', SCHEMANAME, '.', TABLENAME, 'drop PARTITION', DROP_PARTITION, ';' );
- PREPARE STMT FROM @sql;
- EXECUTE STMT;
- DEALLOCATE PREPARE STMT;
- INSERT INTO history_partition VALUES (@sql, now);
- END IF;
- end;
- SET @__interval = 1;
- create_loop: LOOP
- IF @__interval > 15 THEN
- LEAVE create_loop;
- END IF;
- SET LESS_THAN_TIMES = DATE_FORMAT(CUR_TIME + INTERVAL @__interval DAY, '%Y%m%d');
- SET PARTITION_NAMES = DATE_FORMAT(CUR_TIME + INTERVAL @__interval -1 DAY, 'p%Y%m%d');
- IF(PARTITION_NAMES != OLD_PARTITION_NAMES) THEN
- 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) ;
- IF RETROWS = 0 THEN
- SET @sql = CONCAT( 'ALTER TABLE', SCHEMANAME, '.', TABLENAME, 'ADD PARTITION (PARTITION', PARTITION_NAMES, 'VALUES LESS THAN ("',LESS_THAN_TIMES, '"));' );
- SET @__interval=@__interval+1;
- SET OLD_PARTITION_NAMES = PARTITION_NAMES;
- END LOOP;
- END
- $$
- delimiter ;
Step 5: 创建名称为 clean_perf_biz_vm 的事件, 并在每天凌晨 00:30:00 的时候调用 clean_partition 存储过程创建下一个新分区, 并删除两天前的旧分区.
- delimiter |
- 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)
- ON COMPLETION PRESERVE
- do
- begin
- call clean_partition('monitor_alarm','perf_biz_vm','2');
- end |
- delimiter;
Step 6: 处理 perf_biz_vm_old 旧表, 在业务低谷期执行如下操作: drop table if exists perf_biz_vm_old,Drop 掉整张旧表的时间约为 3min, 并释放了 150G 的磁盘空间. 需要注意的是, 虽然 drop table 的时间较短, 仍会产生短暂的阻塞, 因为 drop table 触发的是实例锁, 因此需要在业务低谷期进行操作, 并实时观察数据库情况.
从下图可以看到, 实际 drop 过程中记录到的等待接收队列的长度瞬时值为 169, 最高达到 202:
至此, 改造全部完成, 我们已在数据库侧建立起安全, 稳健, 高效的数据清理机制.
三, 结语
虽然本方案强调了存储过程的使用, 但上述存储过程是基于简单的 create 和 drop 操作, 并没有涉及复杂的逻辑和计算. MySQL 是 OLTP 应用, 最擅长的还是增, 删, 查, 改这样简单的操作, 对逻辑计算分析类的应用并不适合, 所以尽量避免使用复杂的存储过程.
当然, 也并不是所有场景都适合使用分区表, 在很多 DBA 看来分区表在某些场景下是禁止使用的, 一般会采用切表的形式进行拆分, 本方案中使用时间做分区字段, 应用程序中查询语句基本都能命中分区, 对于 Select,Insert 等语句的执行性能是有所提升的.
来源: http://database.51cto.com/art/201810/584919.htm