xu5762173
作者xu57621732016-05-11 17:18
数据库管理员, Ess

admin_move_table 在线迁移、分区并压缩表

字数 15993阅读 4784评论 2赞 3

1.     背景

在给某客户巡检时发现系统有张大表T1中的逻辑大小为493G左右,该表所在空间为32kb的SMS(系统管理空间)。由于这种类型的表空间最大文件大小限制为512G,一旦达到该限制, 对应表将变为不可写状态, 严重影响到相应业务。

为了从根本上解决问题,需要创建新的大型DMS(数据库管理空间)表空间, 把表T1迁移至新表空间中; 同时做成分区表并在线压缩,使用admin_move_table存储过程。这样做有以下几个好处:

a)      在大型DMS表空间中, 32KB页大小的容量限制为64TB, 解除容量限制.

b)      打开表的行压缩属性后, IBM官方宣称压缩率最高可达到90%(该比例依据数据重复率会有所不同), 可以减少存储空间, 并减少查询时的IO次数, 提升性能.

c)      以分区表的形式存放, 便于管理历史数据, 同时提高查询性能.

2.       admin_move_table介绍

2.1 使用范围

1)DB2 V9.7后,可以用alter tablespace 与reduce结合,回收未使用的存储空间,简化了表空间的碎片管理,但从以前版本升级过来的数据库的表空间还是使用过去的存储结构,可能需要在线移动表把已经存在的表移动到新建的可回收表空间。

2)突破表空间大小的限制, 变换数据、索引、LOB 表空间。

3) 不通过reorg实现表的压缩或解压

4)把表移动到不同类型的表空间中,如从SMS到DMS

5)重组表,降低HWM

6)把只读或不经常更新的表移动到单独的表空间,提高增量备份的性能

7)把Long/LOB数据移动到其他开启系统缓存的表空间,提高long/lob数据的读取性能

8)在线改变表的一些属性,如启动或者撤销MDC,启用或撤销表分区,改分区键,改列的类型,增加或删除列,修改列的顺序

9)多分区环境下在线重新分布数据

10)在线转换内嵌LOB的长度定义

 

2.2 限制

·      运行时限制:

o   TRUNCATE、IMPORTREPLACE、LOAD 和 AMT 并行执行

o  不触发trigger的load,alter,reorg应避免同时操作,会创建systoolspace表空间

o   如果表没有惟一索引,那么可能会出现死锁现象

o   插入、更新、删除(IUD) 操作频繁地并行执行,AMT 不会终止。

o  原表上有事件监视器不能迁移

 

·      表特征限制

o   如果表是外键关系的一部分,则不能移动该表。(V10.5前要先drop外键,最后重建, 只有从V10.5开始才支持FK)

o   只包含 LONG、LOB 或 XML 列的表不能移动, 如果表上有lob,xml,long等类型字段,必须要有唯一性索引

o   物化查询表(MQT) 不能移动。

o   类型化表不能移动。

o   范围集群表不能移动。

o   不支持hiddengenerated row

o   set integritypending的表不能迁移

 

在线移动表对整个系统都会产生一定的影响,具体情况取决于下列因素:

a)    移动过程中的每个操作都必须记入日志,将会消耗日志空间和IO.

b)    移动过程中该表新插入或更新的数据会记录到临时表中, 占用部分空间.

c)    移动过程中会产生大量IO, 对应表上的操作会相对缓慢, 对性能有影响.

d)    不建议并行运行admin_move_table,因为性能影响会更严重.

 

2.3 移动过程

ADMIN_MOVE_TABLE过程涉及三个表:

源表。源表是指数据将被移动走的表,通过参数形式传递给 ADMIN_MOVE_TABLE 存储过程。

目标表。目标表由ADMIN_MOVE_TABLE 根据传递入的参数定义新创建的表,所有源表中的数据将复制到目标表中,并且最后被命名成与源表相同的表名。

登台表。由ADMIN_MOVE_TABLE 存储过程创建,在表移动过程中用于临时保持对源表的所有 Update、Insert、Delete 操作。当移动完成后,该表将被删除。

数据移动过程还涉及一个表被成为协议表(protocoltable),这个表将用于保存数据移动的过程数据和配置信息,如数据移动使用的参数、COPY 开始的时间、结束时间、COPY 使用的选项等。表名为systools.admin_move_tale.

 

ADMIN_MOVE_TABLE过程将首先创建源表的影子副本。在复制阶段,DB2 将使用触发器捕获对源表的插入、更新和删除操作并将其放入到登台表中。完成复制阶段后,DB2 在登台表中捕获的数据更改操作将在影子副本中重现。最后 DB2 使用 ADMIN_MOVE_TABLE 过程将使表短暂脱机以交换对象名。ADMIN_MOVE_TABLE 默认情况下使用 INSERT Cursor 的方式将数据从源表复制到目标表,也可以指定选项使用 LOAD 方式复制数据以提高性能。

 

迁移过程中对原表进行的dml操作,通过触发器捕捉变化数据,通过中间表同步到目标表.

1.init

  验证权限,创建4个触发器,insert,update(前后值都记录),delete

2.copy

  从原表复制数据到目标表,复制阶段原表仍可访问.复制方式分COPY_USE_INDEXES、COPY_USE_LOAD和 CLUSTER三种. 如果在此时有用户对源表执行 DML语句,则 INIT阶段创建的触发器将会捕获到这些语句,并且将任何更新、删除、插入操作保存到登台表中。在复制阶段结束后,如果我们没有使用 COPY_WITH_INDEXES选项,则目标表上将会建立新的索引。而且如果需要的话,源表、目标表上将会建立辅助索引帮助提高 REPLAY阶段的性能。3.replay

  遍历中间表,将数据写到目标表,可能需要执行多次

4.verify

本阶段是可选的,用于检查目标表和源表是否在内容上等同。检查过程首先在源表、目标表上获取 S锁,然后 Replay源表上的所有变化,然后与目标表进行比较。如果表拥有唯一索引,则会比较两个表中都存在的列的所有值,如果没有唯一索引将不比较 LONG、LOB、XNML列的值。.验证工作是一个成本很高的操作,我们在使用时必须要判断是否真的需要这么做。VERIFY阶段只能在 COPY阶段或者 REPLAY阶段完成后开始。

5.swap

  最后一次replay时原表和目标表都加x锁,确保数据一致后,删除原表,rename目标表到原表,释放锁.

6.cleanup

  删除触发器及中间表.

 

2.4 使用方法

第一种 提供目标表的数据,索引和大对象的表空间名, 例如:callsysproc.admin_move_table('DB2INST1','TAB1','DATA_TBS','IDX_TBS','LOB_TBS','','','','','','MOVE')

第二种 事先在新的表空间中新建表,已指定数据,索引和大对象的表空间名,调用时提供表名, 例如:callsysproc.admin_move_table('DB2INST1','TAB1','TAB1_TARGET','','MOVE')

两种方式中,第一种是完全由存储过程控制,只需指定目标表空间,数据会自动转移到目标表空间. 第二种是移动前先建立目标表, 在完成数据移动后该名称将作为新表的名字,可以先对目标表进行一系列的定义上的变化.

 

这里详细描述第一种方法的参数意义

>>-ADMIN_MOVE_TABLE--(--tabschema--,--tabname--,---------------->

 

 >--data_tbsp--,--index_tbsp--,--lob_tbsp--,--mdc_cols--,-------->

 

                             .-,-------.

                             V |

 >--partkey_cols--,--data_part--,--coldef--,----options-+--,----->

 

 >--operation--)------------------------------------------------><

 

Ø  tabschema ,源表模式。

Ø  tabname,源表名称

Ø  data_tbsp,指定目标表的新数据存放表空间。如果此参数有值,则 index_tbsp and lob_tbsp 参数需要存在。为空表示不改变数据表空间

Ø  index_tbsp,指定目标表的新索引表空间。为空表示不改变表空间

Ø  lob_tbsp, 指定目标表的大对象表空间. 为空表示不改变表空间

Ø  mdc_cols,指定目标表的多维列(MDC),多列之间使用逗号隔开。如果值为 NULL 或者’ - ‘,目标表将不会使用 ORGANIZE BY DIMENSIONS 子句创建 MDC 表。如果值为空串或者’ -‘,DB2 将检查源表上是否指定了 MDC,如果存在则使用源表的 MDC。

Ø  partkey_cols,指定目标表的分区键列,多列之间使用逗号隔开。如果值未 NULL 或者’ - ‘,目标表将不会 PARTITIONING KEY 子句创建目标表。如果值为空串或者’ - ‘,DB2 将检查源表上是否指定了分区键,,如果存在则使用源表的分区键。

Ø  data_part,本参数提供目标表的数据分区详细规格。本语句定义了如何将表根据表的一个或多个列的值存放在多个存储目标上。如果值未 NULL 或者’ - ‘,目标表将不会 PARTITIONING RANGE 子句。如果值为空串或者’ - ‘,DB2 将检查源表上是否指定了分区模式,如果存在则使用源表的分区模式。

Ø  coldef,指定目标表的列定义,方便我们将列的修改为兼容的数据类型,不过列名必须相同。该参数提供了增加新列、删除存在的列功能,只有存在唯一索引或主键索引的表中才能删除列,同时列不能是主键或者唯一键的一部分。

Ø  options。多个选项可以使用逗号隔开:

KEEP。该选项将会使源表保存为另外一个名字的 COPY。如源表名为 T1,在数据移动完成后,源表将被命名为 T1AAAAVxo。我们可以在协议表中的 ORIGINAL 关键字下找到源表的新名字。

COPY_USE_LOAD。该选项使用不可恢复的db2Load API 从源表复制数据到目标表。如果使用该选项,则 FORCE 选项需要被包含。

COPY_WITH_INDEXES。此选项指定在 COPY 源表之前创建索引。默认情况下 DB2 时在源表 COPY 后创建索引的。使用该选项的好处是在 COPY 后不在需要每创建一个索引进行一次表扫描,索引创建受需要活动日志的事务控制;缺点是会使得 COPY 的性能下降,因为需要在目标表上维护索引,同时索引上有很多伪删除的键。

FORCE。如果指定此选项 SWAP阶段将不会检查源表是否修改了其定义。

NO_STATS。该选项指定不启动对目标表的 Runstats 命令或者复制统计信息。

COPY_STATS。该选项指定在 SWAP 阶段之前从源表中复制统计信息到目标表。

NO_AUTO_REVAL。该选项防止自动进行表的Revalid 操作,而是直接重新创建所有索引、视图。

REORG。该选项指定在 SWAP 之前对目标表进行脱机重组,可以用于重构数据字典。

Ø  Operation。该选项指定期望进行的操作,一般为 MOVE。我们也可以手工把 MOVE 拆分开,依次手工执行 INIT、COPY、REPLAY、VERIFY、SWAP、CLEANUP。当移动到某个阶段,我们想取消移动时,可以使用 CANCEL 操作。

 

如果迁移过程发生中断, 可以使用参数CANCEL替换命令中的MOVE, 取消整个过程.

 

2.5 最佳实践

在特定情况下应选择使用哪个 AMTCOPY 和 REPLAY 阶段的最佳实践。每个评估选项都有一个建议。

Ø  COPY 阶段中的 COPY_WITH_INDEXES 选项

使用该选项的优势:

不需要执行表扫描,没有长期运行的事务构建索引。

使用该选项的劣势:

由于索引维护导致 Copy 吞吐量的降低

建议:当较长的运行时间对 AMT COPY 阶段来说不是很重要时,或者 DB2 日志记录不再处于正常系统负载范围之内时,可以使用该选项。默认情况下不使用该选项,会在记录复制完成时构建索引。

Ø  COPY 阶段的 CLUSTER 选项

使用该选项的优势:

如果需要的话,可根据索引聚集目标表。

使用该选项的劣势:

额外的记录分类会导致 COPY 吞吐量降低。

建议:在应该聚集记录和目标时,或者较长的 AMT COPY 阶段运行时间不是很重要时,可以使用该选项。在默认情况下,SAP 系统中的表不会聚集目标表,除非源表上已定义了聚类索引。

Ø  COPY 阶段中的 LOAD 选项

使用该选项的优势:

与数组插入方法相比,COPY 阶段很快就能完成。

使用该选项的劣势:

使用 LOAD 实用程序加载表是一个单一事务。该事务很有可能在数据库中保存较早的活动日志,该日志仅在数据完成加载并且表索引构建完成时才能提交。索引创建方法由 LOAD 实用程序决定。在这个案例中,AMT 过程不能控制目标表上的索引构建方式。

建议:使用该选项在 AMT COPY 阶段实现高吞吐量,并密切监控 DB2 的活动日志空间。在默认情况下,会使用数据插入方法插入一个大小为 100 行的数组。DB2 9.7 Fix Pack 6 和 DB2 10 提供了可恢复负载,以避免必要的不可恢复负载备份。

Ø  REPLAY 阶段中的 REDIRECT 选项

使用该选项的优势:

该选项保存大量 REPLAY 阶段的插入/更新/删除操作,因此,可以减少REPLAY 阶段的运行时间。

使用该选项的劣势:

源表上的触发器开销有所增加。和 AMT 所用的 “标准” 触发器相比,源表上的并行插入/更新/删除操作需要很长的时间。

建议:当 REPLAY 阶段持续时间很短时,并且增加的源表开销对于移动该表所需的 AMT 时间来说可以接受时,可使用该选项。

 

以下示例使用load方式,测试过程中发现比cluster方式快一倍. 原表数据被保存在TABAEtLXco中.

CALL SYSPROC.ADMIN_MOVE_TABLE

                    ('DB2INST1', 'TAB', 

                    'NEW_TS', 'NEW_TS','NEW_TS', 

                    '', '', '', '', 

                    'KEEP, COPY_USE_LOADNONRECOVERABLE, FORCE', 'MOVE')

 

3.       迁移压缩并分区的实例

3.1 风险及时长预估

使用admin_move_table迁移过程不需要停止业务和数据库, 也不影响该表的正常使用。在移动过程中可以对该表执行插入、更新和删除 (IUD) 操作。有一个小的离线窗口(二级窗口),必须在其中使用一个独占锁来完成移动。

 

整个操作过程时间将受服务器CPU,磁盘性能影响,同时也受并发业务量影响,所以无法精确预估。因为该操作不需要中断业务操作,所以时间预估仅供参考,建议预留充分的时间。(测试时504MB的表压缩迁移耗费时间为40秒,对应512GB的表预估时间为11小时,该时间仅供参考。)

实际操作过程中时长可分步预估, 因为最耗时的操作是copy和replay阶段, copy阶段根据数据量来预估, replay阶段根据该表的使用情况而定.

 

Ø  估算COPY耗时

在 COPY 阶段开始后,通过以下SQL语句查询总行数和开始时间对应的value:

select char (tabschema,10) as schema,char(tabname,30) as tabname, key, char(value,50) as value

from systools.admin_move_table  where key in ('COPY_TOTAL_ROWS','COPY_START')with ur

并从SYSCAT.TABLES 的card字段中查询源表中大概的记录总数.

计算 COPY 性能和剩余时间:

                                  COPY_TOTAL_ROWS

COPY PERFORMANCE (rows/second) = ---------------------------

                                 (CURRENT_TIME – COPY-START)

 

                           (CARD- COPY_TOTAL_ROWS)

REMAINING TIME (seconds) = ------------------------

                             COPY PERFORMANCE

Ø  估算REPLAY耗时

在 REPLAY 阶段开始后,通过以下条SQL语句查询总行数和开始时间, replay已消耗时间对应的value:

select char(TABSCHEMA,10) as schema,char(TABNAME,30) as tabname, key,char(value,50) as value from systools.admin_move_table where key in('REPLAY_TOTAL_ROWS','REPLAY_START','REPLAY_TOTAL_TIME') with ur

通过使用表函数 MON_GET_TABLE 来查询中间表中的记录总数,例如:

select sum(rows_inserted) as rows_inserted, sum(rows_deleted) asrows_deleted        fromtable(mon_get_table(‘<schema>’,’<staging table>’,-2)) as t

计算 REPLAY 性能和剩余时间:

                                  REPLAY_TOTAL_ROWS

REPLAY PERFORMANCE (rows/second) = -----------------

                                   REPLAY_TOTAL_TIME

 

 

                                             ROWS_INSERTED

STAGING INFLOW (rows/second)     =----------------------------------

                                    (CURRENT_TIMESTAMP – REPLAY_START)

                                            ROWS_DELETED

STAGING OUTFLOW (rows/second)    =----------------------------------

                                   (CURRENT_TIMESTAMP – REPLAY_START)

                                     (ROWS_INSERTED -ROWS_DELETED)

REMAINING TIME (rows/second)     =-----------------------------------

                                   (STAGING OUTFLOW  – STAGING INFLOW)

 

3.2 表迁移前期准备工作

1)  预留足够的空间: 保证新建表空间文件系统中至少500G空间. 保证日志空间足够。

2)  表空间文件准备好目录,用于存放新建的表空间。

3)  检查该表的行数, 大致为154,446,156行

4)  预定分区方案:

该表部分字段定义如下, 没有xml和lob字段, 索引也只有一个主键.

数据示例:

数据从20140101开始, 至今9个季度, 总共1.5亿行数据, 据此我们决定按照季度分区,每个季度大概数据量为1600W行. 分区字段为D_QUARTER, 类型是char(5).

5)  在表迁移前,对源表开启表压缩功能 db2 “alter table T1 compress yes”

3.3 迁移过程

Operation

Command

为目标表建立新的索引表空间(客户系统环境为Win DB2 9.7.0.4, 磁盘分区来源为虚拟化磁盘, 所以下面不关注表空间容器位置问题.

)

db2 “create large tablespace TBS_IDX2 pagesize 32k managed by database using (file ‘F:TBSIDX2’ 20G) EXTENTSIZE 32 PREFETCHSIZE 32 BUFFERPOOL IDX_32K autoresize yes NO FILE SYSTEM CACHING DROPPED TABLE RECOVERY ON “

为目标表建立新的数据表空间

db2 “create large tablespace TBS_DATA1 pagesize 32k managed by database using (file ‘F:TBSDATA1_0’ 50G, file ‘F:TBSDATA1_1’ 50G) EXTENTSIZE 32 PREFETCHSIZE 32 BUFFERPOOL DT_32K autoresize yes NO FILE SYSTEM CACHING DROPPED TABLE RECOVERY ON “

使用ADMIN_MOVE_TABLE存储过程,创建分区表并移动数据(数据和lob字段放在TBS_DATA1表空间、索引放在TBS_IDX1表空间

db2 "call SYSPROC.ADMIN_MOVE_TABLE ('DB2ADMIN','T1','TBS_DATA1','TBS_IDX2','TBS_DATA1','','',

'(D_QUARTER) (

STARTING FROM (''20141'') ENDING AT (''20142'') EXCLUSIVE index in TBS_IDX2,

STARTING FROM (''20142'') ENDING AT (''20143'') EXCLUSIVE index in TBS_IDX2,

STARTING FROM (''20143'') ENDING AT (''20144'') EXCLUSIVE index in TBS_IDX2,

STARTING FROM (''20144'') ENDING AT (''20145'') EXCLUSIVE index in TBS_IDX2,

STARTING FROM (''20151'') ENDING AT (''20152'') EXCLUSIVE index in TBS_IDX2,

STARTING FROM (''20152'') ENDING AT (''20153'') EXCLUSIVE index in TBS_IDX2,

STARTING FROM (''20153'') ENDING AT (''20154'') EXCLUSIVE index in TBS_IDX2,

STARTING FROM (''20154'') ENDING AT (''20155'') EXCLUSIVE index in TBS_IDX2,

STARTING FROM (''20161'') ENDING AT (''20162'') EXCLUSIVE index in TBS_IDX2,

STARTING FROM (''20162'') ENDING AT (''20163'') EXCLUSIVE index in TBS_IDX2,

STARTING FROM (''20163'') ENDING AT (''20164'') EXCLUSIVE index in TBS_IDX2,

STARTING FROM (''20164'') ENDING AT (''20165'') EXCLUSIVE index in TBS_IDX2)

','','','MOVE') "

实时检查迁移进度,并估算剩余时间

db2 "select * from systool.admin_move_table with ur"

检查分区表是否创建成功,以及表数据量大小

db2 "SELECT char(TABSCHEMA,30) tabschema, char(TABNAME,40) tabname, DATA_PARTITION_ID, DATA_OBJECT_L_SIZE data_l_KB from sysibmadm.admintabinfo where tabname='T1' with ur"

 

3.4 迁移结果

迁移成功, init操作从5-9-17:31开始, 到cleanup结束时间为5-10-03:13, 共耗时近10小时, 迁移数据总数154,446,156行. 各阶段的起止时间都可以在表systools.admin_move_table中查询.

 

迁移完成后, 各分区逻辑大小情况, 数据逻辑空间仅50G左右.

4.      分区表的维护

1.        每年年末添加次年新的数据分区, 可根据情况选择数据表空间和索引表空间.(2017年的可在本次完成后添加)

db2 "alter tableT1 ADD PARTITION Q1_2017 STARTING FROM ('20171') ENDING AT ('20172') EXCLUSIVE index in TBS_IDX2"
db2 "alter table T1 ADD PARTITION Q2_2017 STARTING FROM ('20172') ENDINGAT ('20173') EXCLUSIVE index inTBS_IDX2"

db2 "alter tableT1 ADD PARTITION Q3_2017 STARTING FROM ('20173') ENDING AT ('20174') EXCLUSIVE index in TBS_IDX2"

db2 "alter tableT1 ADD PARTITION Q4_2017 STARTING FROM ('20174') ENDING AT ('20175') EXCLUSIVE index in TBS_IDX2"

 

如果要attach分区, 添加完成后执行一致性检查(收到该提示时才需要执行SQL3601W  The statement caused one or more tables toautomatically be placed in the Set Integrity Pending state.  SQLSTATE=01586):

db2 "setintegrity for T1 allow write access immediate checked "

 

2.        拆除旧有的数据分区,形成单独的表:

通过以下命令查询对应时间段的分区名称,找到PartitionName

db2 describe datapartitions for  table T1 show detail

通过以下命令拆除分区到指定的表中:

db2 "alter tableT1 DETACH PART <PartitionName> INTO <new_tab_Name> "

 

3.        重组分区表

REORG TABLEtabname [ALLOW NO/READ ACCESS] [ON DATA PARTITION partname]

REORG INDEXESALL FOR TABLE tabname  [ALLOWNO/READ/WRITE ACCESS] [ON DATA PARTITION partname]

REORG TABLE 可用于对单个分区进行重组。用 DATA PARTITION 子句指定一个具体分区的名字,将会仅对该分区的数据进行重组。当数据分区的重组完成后,它还要对因此而被打乱的所有索引数据(含非分区索引)进行重组。若没有使用 DATA PARTITION 子句的话,则会对全表所有分区依次进行数据重组和索引分区重组,最后还要对整个表上的非分区索引进行重组。

 

如果觉得我的文章对您有用,请点赞。您的支持将鼓励我继续创作!

3

添加新评论2 条评论

JcmJcm数据库管理员, 广东
2016-05-12 17:04
谢谢分享!
DB-TrendSetterDB-TrendSetter数据库架构师, 公司
2016-05-12 10:50
Ctrl+Enter 发表

作者其他文章

相关文章

相关问题

相关资料

X社区推广