db2清理大批量数据的实践分享.docxVIP

“案发”当日（２月２９日，四年才相逢的日子）：ecif数据库中一张关键表crossindex（ecif客户与其他源系统客户对照关系表，下文均用表A代替）在批量作业处理时异常增大，停止时该表数据为1.4亿条数据。数据异常增大的原因本文就不在赘述了，详细原因足够写一个长篇回忆录了，说多了全是眼泪。之前做过统计，此表正常数据应当为1200万条左右。看到下图结果的时候，有点小崩溃。一个简单的count（1）后查询“仅”用了3分20秒，异常数据总条数在1.3亿条左右，并且查看了当时的表空间，数据表空间已剩余不多，索引表空间全部被占满了。确切的说是因为索引表空间占满了，批量数据才得以停止，否则数据量会更大。命题：一个有业务系统使用的数据库，需要将其中的一张表，从１.4亿条数据中删除1.3亿，最终只保留有用的1200万数据，且索引表空间已满，无法增加表空间。磁盘空间不足，无法备份。前提：幸运的是，数据库之前做过优化。具体有以下几个参数：db2set DB2_SKIPINSERTED=ondb2set DB2_EVALUNCOMMITTED=on这两个参数主要是提高数据库的并行处理能力，确保批量程序处理过程中，其它应用可以查询数据。其实就是延迟行锁定，当在表扫描或索引扫描期间执行行锁定时，DB2?会先锁定已扫描的每一行然后再确定该行是否符合查询要求，直到确定某行符合查询要求为止。调整数据库锁列表比例，控制应用在过多情况占用锁资源；update db cfg for ecifdb using MAXLOCKS 30（目前生产环境大多数系统是采取自动配置，大小为97%）调整锁资源大小；update db cfg for ecifdb using LOCKLIST 204800（目前生产环境大多数系统是采取自动配置，大小为496864页）调整锁等待超时时长；update db cfg for ecifdb using LOCKTIMEOUT 40（目前生产环境大多数系统是采取自动配置，大小为1800秒）直白的说，有了以上数据库参数的调整，即使我们去进行数据删除时，也不会导致数据库的崩溃，毕竟数据量有点大。解题：1、介于环境、磁盘空间因素，想将里面有效的数据导出后再load空表，最后将导出数据再load回去，这种方式不可取。2、由于索引表空间已经全部占满，删除时需要额外开销维护索引，所以必须先对索引表空间进行处理，否则依旧是删不动的。根据删除条件，暂时只保留时间戳上的索引IDX_CROSSINDEX_CREATE_TM，将该表剩余的3个索引暂时drop。DROP INDEX ECIFMODEL.IDX_CROSSINDEX_CONT_ID DROP INDEX ECIFMODEL.IDX_CROSSINDEX_SRC_SYS_CUST_NODROP INDEX ECIFMODEL.IDX_CROSSINDEX_UPDATE_TM3、在通过分析批量日志后，发现从2016年2月29日18点到2016年3月1日凌晨4点后均生成的是垃圾数据。很关键的是这张表有时间戳字段且有对应索引，删除效率应该不会太低。如果直接delete，数据库的事务日志肯定会顶满，最终删除方式只能以不记数据库日志的方式进行删除，其中NOT LOGGED的属性在这个事务结束(commit)之后就不再有效。db2 connect to ecifdbdb2 commitdb2 alter table CROSSINDEX activate not logged initially db2 delete from CROSSINDEX where create_src_sys_cd = 99 and create_TM timestamp(2016-03-01 03:00:00)db2 commit4、为了能实现删除，采取每次只删除有限时间段内的数据，以上数据根据测算，大概有500万条左右，在执行删除语句的过程中,打开db2监控视图，db2 –d ecifdb之后按l根据上图所示，在CPU以及磁盘IO读写均占用100%的情况下，数据吞吐量大概每秒4万条左右。粗略计算了下，删除这些数据大概需要60分钟左右，当然这只是理想状态。由于之前的数据库锁列表比例的设置，在删除过程中，仍然有交易上来访问数据库，删除的吞吐量会大大缩小。5、为了证实执行删除操作时，实时交易没有影响，拨打96155测试电话银行，服务确实正常。虽然是在凌晨2点之后操作，但中途仍遇到有客户在做交易，可能会将删除的动作回滚。最终，在删除了3个小时之后，数据量终于到达了1200万左右。删除过后，查看该表状态，一切正常。6、清理了1.3亿数据后，数据表空间并没有降下来，最后再将此表进行reorg操作db2 reorg tab