生产数据库性能优化之reorg和表重建

生产数据库性能优化之reorg和表重建
生产数据库的性能调优是一个系统的工程，它不仅要求DBA熟悉db2的工作原理和各种性能指标，还要求DBA也要熟悉操作系统、存储等其他知识，甚至要求DBA熟悉应用的设计原理以及它使用数据库的方式等。这里，我们只就生产数据库性能优化之reorg和表重建进行初步的探讨。
1. reorg的作用
要进行高效率的数据访问和获得最佳工作负载性能，具有组织良好的表数据是很关键的。在对表数据进行许多更改之后，逻辑上连续的数据可能会位于不连续的物理数据页上，比如说对某个表新增一个字段，或者表的某列是可变长度的，这样在插入或者更新操作时有可能会导致行溢出。另外，在删除大量行后，也会造成表空间的数据碎片。这些情况下都会降低数据的访问速度，从而影响到数据库的性能。
表重组操作会整理数据碎片，降低表空间高水位，减少浪费的空间，并对行进行重新排序以合并溢出记录，从而加快数据访问速度并最终提高查询性能。还可以指定根据特定索引来重新排序数据，以便查询通过最少次数据读取操作就可以访问数据。既可重组系统目录表，也可以重组数据库表。
考虑以下因素，以确定是否应重组表： 
· 对查询所访问的表进行了大量的插入、更新和删除活动
· 对于使用具有高集群率的索引的查询，其性能发生了明显变化
· 在执行 RUNSTATS 以刷新统计信息后，性能没有得到改善
· REORGCHK 命令指示需要重组表
· 综合考虑查询性能不断降低所浪费的成本和重组表所需的成本（包括 CPU 时间、经过的时间和 REORG 实用程序在完成重组操作之前锁定表造成的并行性降低），以确定是否进行表重组。
2. 生产数据库要不要做reorg
DB2提供了在线或离线执行reorg的选项。默认情况下，离线 REORG 允许其他用户读这个表，ALLOW READ ACCESS 是默认选项。可以通过指定 ALLOW NO ACCESS 选项来限制对表的访问。在线 REORG （也称inplace REORG）支持对表的读或写访问，ALLOW WRITE ACCESS 是默认选项。
联机重组与脱机重组的比较
特征 脱机重组 联机重组
性能 快 慢
完成时数据的集群因子 良好 非最佳集群
并行性（对表的访问） ALLOW NO ACCESS
ALLOW READ ACCESS（默认） ALLOW READ ACCESS
ALLOW WRITE ACCESS（默认）
数据存储空间要求 非常大 不是非常大
日志记录存储空间要求 不是非常大 非常大
用户控制（暂停和重新启动重组过程的能力） 较少控制 较多控制
可恢复性 完全可恢复或完全不可恢复：成功或失败。 可恢复
索引重建 进行 不进行
支持所有类型的表 是 否
指定除集群索引外的索引 是 否
使用临时表空间 是 否
 
     对于生产数据库，如果有运维时间窗口，建议执行离线的reorg，并且在执行离线reorg时使用临时表空间（-USE–tbspace-name-）。对于没有运维时间窗口的，可以尝试做在线的reorg，但是因为在线的reorg时间会特别的长，需要人为控制，避开业务高峰期。
3. 生产数据库做reorg的步骤
生产数据库做reorg的步骤：REORGCHK->REORG->RUNSTATS
reorg的同时，可以用db2pd -db ibps -reorgs 查看阶段和进度
db2“reorgtableIBPS.TBLLMSGLOGuseTEMPSPACE1”db2pd -db ibps -reorgs                           
Table Reorg Stats:
Address            TableName          Start               End                 PhaseStart          MaxPhase   Phase      CurCount   MaxCount   Status  Completion
0x07000006EA3328A8  TBL_LMSGLOG        05/07/2011 20:43:02 n/a                 05/07/2011 20:43:02 3          
Build      61247      1818892    Started 0 
     
Table Reorg Stats:
Address            TableName          Start               End                 PhaseStart          MaxPhase   Phase      CurCount   MaxCount   Status  Completion
0x07000006EA3328A8 TBL_LMSGLOG        05/07/2011 20:43:02 n/a                 05/07/2011 20:46:53 3          Replace    204160     814851     Started 0 
 
Table Reorg Stats:
Address            TableName          Start               End                 PhaseStart          MaxPhase   Phase      CurCount   MaxCount   Status  Completion
0x07000006EA3328A8 TBL_LMSGLOG        05/07/2011 20:43:02 n/a                 05/07/2011 20:53:46 3          IdxRecreat 105276     185740     Started 0  
 
4. 表重建的应用和步骤
之所以要提出表重建的方案，是因为在实际中，存在这样的情况，比如针对某个7*24小时业务系统的表TBL_LMSGLOG，为了减少给该表“瘦身”，会不定期的将表中的数据导出，并导入到归档库，然后删除已经归档成功的数据，生产库的表只保留近3个月的数据。
针对这种情况，我们选择在维护时间段（需要特殊申请）进行离线reorg，该表的总大小为240GB左右，每次reorg都需要将近1个小时，针对这个表的特点，也就是说每次删除的数据量比较大，跟剩余的数据量处于同一数量级，我们可以选择对该表进行重建，并且重建后的表放在单独的表空间中，通过数据迁移来实现对表的维护，同样达到reorg的目的，数据迁移的方式也有很多，比较快的方式是利用load cursor，数据不落地。
 重建表的步骤，比如原表空间为A_TBSPACE, 原表为A_TABLE
1) 利用db2look导出A_TABLE的表定义和索引定义
2) 新建表空间B_TBSPACE
3) 在B_TBSPACE中按照A_TABLE的表定义新建表B_TABLE
4) 利用load cursor方式实现从A_TABLE到B_TABLE的数据迁移
select current time from syscat.tables fetch first 1 rows only;
DECLARE c1 CURSOR FOR SELECT * FROM A_TABLE; 
LOAD FROM c1 of CURSOR INSERT INTO B_TABLE NONRECOVERABLE; 
select current time from syscat.tables fetch first 1 rows only;
5) 删除原表A_TABLE的主键和索引，将A_TABLE重名为A_TABLE_BAK, 
6) 将表B_TABLE重命名为A_TABLE，并重建A_TABLE的主键和索引
7) 检查数据有效性，如有失效的package，需要rebind一下。
验证表或试图的状态
db2 “select substr(tabschema,1,10) as tabschema,substr(tabname,1,30) as tabname,status from syscat.tables where status != ‘N’ with ur”
验证package的状态
db2 “select substr(PKGSCHEMA,1,10) as PKGSCHEMA,substr(PKGNAME,1,30) as PKGNAME,VALID from syscat.PACKAGES  where VALID !=’Y’ with ur”
验证routine的状态
db2 “select substr(ROUTINESCHEMA,1,10) as ROUTINESCHEMA,substr(ROUTINENAME,1,30) as ROUTINENAME,ROUTINETYPE,VALID from syscat.ROUTINES  where VALID!=’Y’ and VALID !=” with ur”
8) 对表A_TABLE执行RUNSTATS
 
5. 具体例子的对比
下表是我们实际维护过程中的对比情况，总耗时差不多，均为50分钟左右。
维护 类型 数据量变化 耗时
第一次 离线reorg 表占用空间： 164GB
reorg后: 92GB，
释放: 72.2GB reorg：42分钟
(build：12分钟+replace 16分钟+IDX Recreat 14分钟)
runstats:10分钟
第二次 表重建 表占用空间：240GB
迁移数据量:107GB load cursor：27分钟
重建主键和索引：11分钟
runstats：12分钟
 
6. 其他思路
虽然上面的两种思路都实现了表的优化维护，但是还有没有其他思路呢？
另外一种比较好的思路是根据表的字段情况，可以按照时间段将原表改造成分区表，每个月的数据建立一个数据分区，这样删除三个月之前数据就可以直接detach对应的分区，在db2V9.7之前的话，detach后还需要对全局索引进行维护，如果数据库版本是db2 V 9.7的话，可以将索引移植成分区索引，每个分区索引由多个索引分区（index partition）组成，每个索引分区只对相应的数据分区（data partition）的数据作索引。
其实在db2V9.7还有一个比较好的功能，就是可以对分区表的单个分区进行表和索引重组，这个新特性使得用户可以仅对某一个分区进行重组，并仅在该分区上对其它事务的读写权限进行限制。这样可以最大程度的缩小重组命令对其他事务的影响，提高事务的并发度。这样当用户使用分区重组时，在表上没有非分区索引的情况下，重组命令将完全只在一个分区上进行，从而对其他分区上的事务没有任何影响。
这里只简单列一下分区重组的相关选项，供参考。
db2 V9.7分区重组命令 REORG TABLE 里各选项搭配的实现效果
  默认读写控制选项 ALLOW NO ACCESS ALLOW READ ACCESS
ON DATA PARTITION part_i
( 没有非分区索引 *) 默认为 ALLOW READ ACCESS part_i: 不可访问 
part_other: 均可读可写 part_i: 仅可读 
part_other: 均可读可写
ON DATA PARTITION part_i
( 有非分区索引存在 *) 默认为 ALLOW NO ACCESS 所有分区均不可访问 返回 SQL1548N 错误
未指定单个分区 
( 即全表重组 ) 默认为 ALLOW NO ACCESS 所有分区均不可访问 返回 SQL1548N 错误
  
7. 小结
本文简述了生产数据库性能优化的两种常见手段：reorg和表重建，这两种方法无优劣之分，这里只是提供一些思路供生产数据库维护中采用。至于实际操作过程中采取哪种手段，则需要根据表的具体情况而定。