通常来说，性能监控业务场景具有数据导入量大，表空间增长快的特点。为避免磁盘空间满，提高SQL执行效率，需要定期清理历史数据。根据数据采集频率和保留期限的不同，可以在应用程序中嵌入不同的定时器来删除历史数据。在业务上线初期，这种简单的定时清洗机制是有效的。但是随着业务的增长，尤其是数据激增的时候，上面提到的定时器很有可能失效。不仅无法清理数据，还会因为事务长期持有表锁，造成数据库阻塞和流控。下面给大家分享一个由于清理机制失效导致数据库故障的案例，给出一个工程方案，如何通过分区表和存储过程来清理数据。一、问题回顾今年年初，我们生产环境的云监控系统出现了一次短暂的故障。经排查，故障原因是OP应用定期删除性能库中的数据。具体原因是删除事务过大，超过了PXC集群同步复制写集。事务在本地提交后，无法同步到集群的其他两个节点上。最终在本地回滚。由于持有表锁时间过长，导致大量线程阻塞触发SystemLock，造成数据库流量控制，最终导致华北节点云监控数据更新缓慢。下面描述故障处理过程：1.Zabbix发布告警通知Zabbix发布告警通知：“华北节点OP性能库内存使用率超过80%”，时间为：2018/02/2706:14:05。注：OP为“移动云”门户系统的简称；OP性能库用于存储用户订购的云产品的性能数据，架构类型为3节点PXC多主集群架构。登录数据库查看，发现等待执行的线程数激增，数据库已经处于流量控制状态。导致数据库阻塞的SQL语句为：DELETEFROMperf_biz_vmWHERE'2018-02-2502:00:00'>CREATE_TIME该语句由OP应用发起，用于删除perf_biz_vm表两天前的历史数据。故障发生时，执行时间已超过4，根据执行计划，预计1小时内删除2亿行数据。最终语句没有执行成功，导致数据库流控。2.故障机制这里我们根据GaleraCluster的复制原理来分析故障的机制。首先，Galera集群节点之间的同步复制主要是基于广播写集和事务验证来实现多节点同时提交和冲突事务回滚等功能。另外，事务在本地节点执行时，采用乐观策略，在成功广播到所有节点后进行冲突检测。当检测到冲突时，首先回滚本地事务。如果没有检测到冲突，每个节点将独立异步执行队列中的写集。最后，交易在本地节点执行成功并返回给客户端后，其他节点保证交易会被执行。Galera复制的架构图如下：根据Galera复制原理，当删除事务在本地节点上提交成功后，本地节点通过write将事务集合复制到集群中的另外两个节点，然后每个节点独立异步地进行认证测试。由于要删除的数据量很大，事务已经超过了同步复制写集（生产环境写集的设置值为1G）。因此本地节点无法获取认证信息，交易不会被插入待执行队列进行物理提交，而是优先在本地回滚。错误日志如下：因为事务长期持有perf_bix_vm表的X锁，导致本地节点云主机的监控数据无法存储。随着等待线程的积累，本地节点的执行队列会越来越长，触发PXC集群机制的流控。该机制用于保证集群中所有节点的执行速度大于队列的增长速度，避免慢节点丢失事务。其实现原理是同一时间集群中只有一个节点可以广播消息，每个节点都会获得广播消息的机会。执行队列超过一定长度后，会广播一个FC_PAUSE消息。其他节点收到消息后，将暂停广播消息。随着慢节点（本地节点）事务的回滚，直到慢节点的执行队列长度减少到一定程度后，Galera集群数据同步再次恢复，解除流控。3.其他导致失败的因素OP性能库发生流控时，本地节点执行“DELETEFROMperf_biz_vmWHERE'2018-02-2502:00:00'>CREATE_TIME”语句占用BufferPool(即生产环境innodb_buffer_pool_size=128G)，加上数据库本身正常运行占用的内存，系统内存使用率超过80%的警告值。此时打开华北节点OP控制台，可以看到云监控数据更新缓慢：4.重建数据清洗机制中断到2月28日，历史数据清洗机制失效，导致业务数据量250G的form表，数据库存储空间严重不足，急需扩容。为了消除数据库安全隐患，释放磁盘空间，我们决定采用分区表+存储过程+事件的方案，重建数据库端的数据清洗机制。2.重建和清理机制通过分析以上失败案例，我们决定建立一个安全、健壮、高效的基于分区表和存储过程的数据库清理机制。通过查看执行计划可以看出，即使命中了索引，执行效率也很低，很容易触发系统锁。因此，要从根本上解决大表的数据清洗问题，就必须引入分区表。删除数据不进行DML操作，而是直接删除早期分区表（DDL）。因为writeset记录了执行delete操作时每一行的信息，writeset只记录了表的物理存储位置，表结构，表依赖的约束，触发器，索引，存储过程执行删除操作时。当表中的数据量很大时，使用drop操作会快几个数量级。分区表的另一个优点是不需要为应用程序修改代码。通过设置后台数据库，将表的时间字段作为分区字段，可以方便的对表进行拆分。需要注意的是查询字段必须是Partitionkey，否则会遍历所有分区表。下面来看看具体的实现过程：第一步：首先创建分区表。这里我们以perf_biz_vm表为例，新建一个相同表结构的表，命名为perf_biz_vm_new，使用create_time索引字段作为分区字段，按天分区并与主键创建联合索引，创建一个语句：代码如下：CREATETABLE`perf_biz_vm_new`(`CREATE_TIME`datetimeNOTCOMMENT'性能采集时间',`VM_ID`varchar(80)NOTCOMMENT'虚拟机ID',`PROCESSOR_USED`varchar(100)DEFAULTCOMMENT'CPU利用率(%)',`MEM_USED`varchar(100)DEFAULTCOMMENT'内存使用率(%)',`MEM_UTILITY`varchar(100)DEFAULTCOMMENT'可用内存(字节)',`BYTES_IN`varchar(100)DEFAULTCOMMENT'传入流量速率(Mbps))',`BYTES_OUT`varchar(100)DEFAULTCOMMENT'传出流量速率(Mbps)',`PROC_RUN`varchar(100)DEFAULTCOMMENT'CPU运行队列进程数',`WRITE_IO`varchar(100)DEFAULTCOMMENT'虚diskwriterate(Mb/s)',`READ_IO`varchar(100)DEFAULTCOMMENT'虚拟磁盘读取率(Mb/s)',`PID`varchar(36)NOT,PRIMARYKEY(`PID`,`CREATE_TIME`),KEY`mytable_categoryid`(`CREATE_TIME`)USINGBTREE,KEY`perf_biz_vm_vm_id_create_time`(`VM_ID`,`CREATE_TIME`))ENGINE=InnoDBDEFAULTCHARSET=utf8COMMENT='虚拟机性能采集表'/*!50500PARTITIONBYRANGECOLUMNS(CREATE_TIME)(PARTITIONp20180225VALUESLESTHAN('20180226')ENGINE=InnoDB,PARTITIONp20180226VALUEslessthan（'20180227'）引擎=InnoDB，PartitionP20180227ValuesThan（'20180228'）引擎=Innodb，Partitionp20180228ValuesThan（'20180229'2018029'）发动机=Innodb，partitionp20180229Valeless=Innod230230230230230230230230230233替换原来的旧表这里要注意，重命名操作会修改perf_biz_vm表的元数据。需要提前检查这张表是否有Delete、Update、Insert事务和DDL操作，否则冲突会产生元数据锁（MetadataLock）。我们的方法是提前停止业务端的定时器，在业务低的时候执行下面的语句，通过rename将旧表和新表进行交换，这样就可以使用新表了。如果在此期间有服务呼叫，服务会短时间断开。renametableperf_biz_vmtoperf_biz_vm_old;renametableperf_biz_vm_newtoperf_biz_vm;第三步：查看新表是否有数据写入，云监控页面数据显示正常，说明业务已经恢复。云主机监控数据的存储周期为两天，所以需要将两天前的旧表数据复制到新表中。这一步是通过脚本完成的。可以参考以下脚本：代码如下：#!/bin/bashfunctioninsert{end_time="$1$2"start_time="$3$4"mysql-u'user'-p'passwd'<'$start_time';commit;s.tableswhereTABLE_SCHEMA="monitor_alarm"andTABLE_NAME="perf_biz_vm";!}base_time="2018-02-272:00:00"whiletruedo#end_time=$(date-d"-1hour$base_time"+%Y-%m-%d""%H:%M:%S)end_time=$base_timestart_time=$(date-d"-1hour$end_time"+%Y-%m-%d""%H:%M:%S)#base_time=$end_timebase_time=$start_timeecho"Cur_time:$(date+%Y%m%d""%H%M%S)"|tee-a1.logecho"Range:$end_time$start_time"|tee-a1.loginsert${end_time}${start_time}|tee-a1.logsleep2doneStep4:写一个存储过程定期创建新分区并删除旧分区几天前的分区：代码如下：delimiter$$CREATEPROCEDURE`clean_partiton`(SCHEMANAMEVARCHAR(64),TABLENAMEVARCHAR(64),reserveINT)BEGIN注意：该存储过程适用于分区字段类型为datetime，按天分区，命名的分区p20180301格式说明获取表的最旧分区，判断是否为reservedays之前的分区，如果是则删除，每次只删除一个分区。提前14天创建分区。如果不重名，创建一个history_partition表，varchar(200)和datetime类型记录执行成功的SQL语句DECLAREPARTITION_NAMESVARCHAR(16);DECLAREOLD_PARTITION_NAMESVARCHAR(16);DECLARELESS_THAN_TIMESvarchar(16);DECLARECUR_TIMEINT;DECLARERETROWSINT;DECLAREDROP_PARTITIONVARCHAR(16);SETCUR_TIME=DATE_FORMAT(NOW,'%Y%m%d');BEGINSELECTPARTITION_NAMEINTODROP_PARTITIONFROMinformation_schema.partitionsWHEREtable_schema=SCHEMANAMEANDtable_name=TABLENAMEorderbyPARTITION_ORDINAL_POSITIONasclimit1;IFSUBSTRING(DROP_PARTITION,2)
