Informix 数据库表迁移工具在实际中的应用

http://www.ibm.com/developerworks/cn/data/library/techarticles/dm-0708lixq/
http://www.ibm.com/developerworks/cn/data/library/techarticles/dm-0802lixq/index.html

李晓勤 (lxqdfdt@163.com), 软件工程师, 中国建设银行
2007 年 8 月 27 日

本文主要对 Informix 提供的 onunload/onload、unload/load 等表级迁移工具进行详细探讨:分析两者存在的差异,不同情况适用哪个工具,以及如何提高迁移效率等,同时给出一些具体实施步骤及实施脚本。

在实际应用中,数据库管理员经常需要对数据库表进行备份或迁移。Informix 提供了多种表级迁移工具,如 onunload/onload、unload/load、 dbunload/dbload。其中 dbunload/dbload 与 unload/load 较为相似,本文主要对前两种工具进行详细探讨:分析两者存在的差异,不同情况适用哪个工具,以及如何提高迁移效率等,同时给出一些具体实施步骤及实施脚本。

unload/load 与 dbunload/dbload 比较
描述
unload 语法简单、使用灵活、操作方便,是数据库管理员经常使用的表级迁移工具。其输出结果为一个以分隔符分隔各字段的 ASCII 文件。
dbunload 以二进制形式、磁盘页为单位将指定表的数据写入磁带或硬盘文件。

语法
清单 1. 语法
Unload to 文件名
Select col1, col2, …, coln {字段名列表}
From tabnameWhere 条件子句
Order by 排序子句

从语法上看,unload由于使用select语句作为检索工具,从而决定了unload工具的灵活、方便。通过指定字段列表、查询条件等,可完成下列操作:

卸载所有列或特定列
卸载所有行或特定行
卸载多表连接字段及记录
对取得的数据进行计算
对取得的数据进行排序
装入表可使用 Load 命令:
清单 2.
Load from 文件名 insert into tabname values (col1,col2,..)
Onunload/onload语法:
onunload [-l] [-t ] [-b] [-s] [-d] :table
onload [-l] [-t ] [-b] [-s] [-d] [-i] [-fd] [-fi] [-c] :table

onload只能使用onunload生成的磁带设备文件或磁盘文件进行装载。

卸载内容

Unload 只卸载迁移表的数据,不包含表数据模式及索引数据。
Onunload 卸载迁移表数据模式、数据页、索引页信息。

卸载表的锁定状态

Unload 工具:
在数据库非日志状态下或脏读隔离级别下,可卸载表数据,即使迁移表被独占。
在日志数据库以非脏读隔离级卸载独占的表时,返回锁冲突错误。
对卸载表的加锁情况:在非日志数据库、或脏读、或提交读隔离级别下,对卸载表不锁定;在日志数据库中,以游标稳定读、重复读隔离级别卸载表,对表记录不同数据集加共享锁。

Onunload 工具:
不能对加有独占锁的表进行卸载,包括任意粒度的锁定:表级锁、行锁、页锁。
卸载时对卸载表加表级共享锁。
装载时对表的锁定状况

load 工具:

在日志数据库中,对装载表加排他锁。锁定粒度缺省情况下为行锁或页锁(建表时确定)。当装载数据量较大时,可显示将锁模式更改为表级锁。在装载过程中不能对表进行访问。
在非日志数据库中,对装载表不加锁,对装载成功的记录可随时访问。
Onload 工具:对装载表加互斥锁。

数据库日志状态

Unload/load 装卸载不受数据库日志状态的限制。
Onunload 不受数据库日志限制,onload 要求数据库必须无日志。

卸载粒度

unload 卸载粒度包括部分字段或全部字段,部分记录或全表记录,是迁移工具中粒度最小的,也是该工具灵活方便的原因之一。
Onunload 卸载粒度为数据库级或表级。

输出结果

Unload 输出以分隔符分隔各字段的 ASCII 文件。分隔符可在卸载命令 delimiter 选项中显式指定。
Ononload 输出为二进制数据文件。

更改表数据模式

Unload/load 可更改表的数据模式。通过指定 select 语句的字段名列表,编辑 dbschema 的表结构输出 SQL 语句完成表数据模式的更改。
Onunload/onload 不能更改表数据模式。

迁移目的表所在数据库空间

Load 根据建表脚本,对非分片表不指定 dbspaces 时,缺省迁移到表所在数据库的 dbspaces,分片表迁移到同源表的数据库空间,均可重新指定。
onload 对非分片表不指定 dbspaces 时缺省迁移到 rootdbs,分片表迁移到同源表的数据库空间,均可重新指定。

迁移目的平台

Unload/load 卸载数据为 ASCII 文件,故为迁移提供了极大的灵活性,可迁移到异构的硬件平台、操作系统、数据库中。
Onunload/onload要求目的数据库服务器与源数据库服务器具有相同的页大小、相同或兼容数据库服务器版本。

效率

Unload/load由于卸载数据为文本数据,在卸载过程中需要将各字段转换为ASIIC字符,故卸载效率不很高。
Onunload/onload采用二进制装卸、以数据页为单位进行,所以运行速度更快。

迁移步骤
ONUNLOAD/ONLOAD 迁移步骤:
源主机:
准备磁带,若采用磁盘文件方式,则准备充足的磁盘空间,并创建一用于存放卸载数据的空文件。
采用onunload卸载表,结果存放在磁带或磁盘文件中。
磁带或磁盘文件移到目的主机上。
目的主机:
去掉目的数据库的日志。
采用onload创建表结构、装载表数据及索引数据。
目的数据库恢复日志。
UNLOAD/LOAD 迁移步骤:
源主机:
准备充足的磁盘空间。
采用unload工具卸载表数据,使用dbschema 工具卸载表的数据模式,生成建表SQL语句。
磁盘数据文件及数据模式SQL语句移到目的主机上。
源主机:
根据需要编辑生成的数据模式SQL语句,并创建空表。
采用load工具将的数据文件装载至数据库中。

两种工具的不同应用环境

以下论述对不同应用环境,应采用哪种迁移工具或采用哪种工具最佳。

更改数据模式的迁移

Onunload/onload工具不能更改迁移表的数据模式,而unload/load可更改,故采用unload/load工具。

实现方法:对 dbschem 卸出的 SQL 语句进行编辑,可改变:表的字段名、约束、权限、表名、封锁模式、初始 EXTENTS、后续 EXTENTS、索引名、分片信息等。

更改粒度的迁移

采用 unload/load 工具。

实现方法:编辑 dbschem 卸出的 SQL 语句,增加或删除部分字段,结合 unload 的 select 语句,选择部分字段或多表连接产生更多字段,实现字段级粒度的更改;通过 select 语句中条件语句筛选符合的记录,实现记录级粒度的更改。

跨操作系统平台、不同INFORMIX数据库服务器版本、机器页大小不符等情况的表迁移

采用unload/load工具。

由于unload工具输出的文件是ASICC文件,为此提供了迁移的广阔途径。如将unix系统下卸出的文件,传至windows系统中,通过execl工具的“导入外部数据”功能,将文本数据通过电子表格形式展现给用户。

目的数据库带有日志

采用unload/load工具。Onload要求装载表所在的数据库必须无日志。

适用于数据日志不能停的数据库环境,如OLTP(联机事务处理)数据库环境。

分片表的迁移

Unload/load工具可实现表迁移过程中各种分片需求,如将分片表迁移为非分片表,非分片表迁移为分片表、更改分片策略等。在此不再详述其实施步骤,主要论述onunload/onload工具在分片表迁移中的应用。

目的表有聚簇索引

带有聚集索引的表的记录在物理存储上严格按聚集索引的顺序存放,也就是聚集索引记录与数据记录的存储顺序一致。采用聚集索引,查询时扫描的数据量较普通索引减少了。所以对于经常查询、很少增删的表可以充分利用聚集索引的优点提高查询速度。

有聚簇索引,且聚簇程度较高的表迁移:

卸载时:unload工具是以rowid顺序卸载数据,onunload是以表空间的表空间信息顺序卸载数据,即卸载的数据保持与源表相同的物理顺序。

装载时:load工具是以数据文件行的顺序装载记录,onload也是以二进制数据文件顺序装载数据页,从而保证目的表的记录物理顺序与源表保持一致,目的表与源表的聚簇程度相同。所以,两种工具均可实现聚簇程度高的迁移。

源表无聚簇索引,或有聚簇索引但聚簇程序较低:

由于onload装载的目的表与源表的聚簇程度,对聚簇程序低或无聚族索引的表迁移只能在装载后使用alter index tabname to cluster 命令重建聚簇索引。创建聚簇索引,需要化费大量的时间及空间,如果是海量表,代价更大,性能较低。

故建议采用unload/load工具卸装,在卸载时将数据排序,即以聚簇索引顺序卸载数据,这样在装载数据后,无需再进行聚簇处理。

整理迁移表磁盘空间

提高数据库性能,最明显的方法莫过于优良的磁盘空间布局策略。在实际应用中,由于建表时对表的增长趋势估计不足,以及表经过频繁更新,造成表大量磁盘空间闲置,出现磁盘碎片等情况,为此,我们需要对表磁盘空间重新整理,提高数据库性能。

1)回收删除记录的磁盘空间

我们知道,INFORMIX数据库磁盘空间一旦分配给表,将永久被该表占用,即便页面上的记录被删空。只有当表被删除,IDS才回收表的磁盘空间。表的磁盘页包括以下类型:bitmap(位图页,用于跟踪页的使用情况)、数据页(存放数据,包括被删空记录的页)、索引页、未使用的页。

表在使用过程中,经过频繁更新操作,使得数据页中存有大量的删除记录,这些空间在插入新记录时可重新使用。但当删除操作远远大于插入操作时,这些空间大多数被闲置,不仅造成磁盘空间浪费,而且增加了磁盘I/O,降低缓冲命中率。这时我们需要对该表的磁盘空间进行整理。

那么采用哪种迁移工具可回收删除记录的磁盘空间呢?由于Onunload工具对分配给表已使用的磁盘页进行卸载,即便该页中所有的记录被删除。所以onunload/onload不能完成表磁盘空间整理工作。而unload工具只卸载当前有效记录,对已删除的记录并不卸载,所以采用unload/load对表重新卸装,回收磁盘已删除记录的空间。

2)消除表空间交错,防止Extnets超界

IDS以extnes为单位分配表磁盘空间,并且限制每个表的extents个数。表的初始extent及后续extent在建表时确定,后续extent大小可采用alter语句随时进行调整,但不能调整以前的extents分布情况。

在实际应用中,用户在建表初期往往无法准确估计表的增长趋势,对增长较快的表extent设置较小,由于并发等因素,形成同一数据库空间的多个表的表空间发生交错。表空间发生交错将引发以下问题:

单个表的extents不连续,造成extents个数较多,严重时将超出IDS允许的数目,引发记录插入失败。
表extents的物理不连续,在检索数据时造成磁盘臂动作较多,顺序读取数据搜索时间长,降低I/O性能。
对于这种情况,采用两种工具均可完成磁盘空间整理。

实现方法:

查找extents个数较多的表,见附录。
对extents个数较多的表,充分估计表的增长趋势,确定适当的extent大小,以防形成大量不连续extents;
使用unload/load工具:编辑建表sql,设置足够的初始extent、后续extent个数,重新装载数据。
使用onunload/onload工具:重新卸装表,装载后使用alter命令修改后续extents大小
释放未使用页中的磁盘空间,提高磁盘利用率。
与 2)中情况相反,建表时如果对表的初始 extent 及后续 extent 设置太大,而表的增长为缓慢,这将造成大量磁盘空间闲置,而这些空间又不能被其它表利用。为此,需要重新整理表磁盘空间,提高磁盘利用率。

onunload对未使用的页并不卸载,卸载二进制文件较小,但装载表的extents设置同源表,在装载时仍造成了大量闲置空间。这主要是因为onunload从系统目录表systoles中读取信息,将结果存放在onunload输出中,onload在装载表时使用这些信息创建表模式,包括初始及后续extents个数。而unload卸载有效记录数据,可通过调整表的初始及后续extents大小,对表重装以回收未使用磁盘空间。

实现方法:

统计源表已使用extents大小,将该值确定为初始extent值。
估计表的增长趋势,确定适当的后续extent大小,该值不要太大,以免造成大量磁盘空间长期闲置。
编辑表的数据模式脚本,设置初始extent、后续extent大小。
装载数据。

迁移日志数据库更新频繁的表

Onunload 在有日志的数据库卸载时,并发程度上比 onload 低:

1)对卸载表的锁定检查:

Onunload工具在卸载前判断表是否有排它锁,如果有,无论哪一粒度级排它锁定,都将导致卸载失败;而Unload工具在卸载过程中只检查当前检索到的数据锁定情况,如果有排它锁(页锁或行锁),才导致卸载失败;甚至可对onunload设置脏读模式,对锁定数据也可成功卸载。

2)对卸载表的加锁:

Onunload工具在卸载期间要对卸载表加共享锁;

Unload工具对卸载表根据需要设置四种不同读隔离级,在脏读、提交读(缺省)隔离级下,对表不加锁,在重复读、游标稳定读级别下,对数据集的不同范围加共享锁。

从上分析来看,onunload工具在卸载期间对迁移表加共享锁,影响其它进程对该表的并发更新操作。在并发较为重要的OLTP环境中,如果对更新频繁的表或有更新操作的海量表进行卸载,出现锁碰撞的几率很大,并发性能降低。

建议采用unload/load工具。unload在卸载期间不对表加锁(缺省情况),不会影响其他进程的并发操作;只对当前检索到的数据检查其锁定情况,所以锁碰撞的几率大大降低,在行锁模式下,出现锁碰撞的可能性非常小。在对卸出的表数据要求不很精确,如用于测试、开发、趋势统计等表迁移,还可通过设置脏读隔离级,在保证成功卸载的同时,不检查表及记录锁状况,减小了系统开销,进一步提高了性能。

海量表的迁移

如果表的数据量很大,如几十个G等,卸载及装载将很耗时。unload/load采用ASICC方式卸装数据,在数据量很大的情况下,性能较低,如果表有索引,就创建索引一项,也是很耗时的。而onload/onload工具采用二进制磁盘页方式卸载及装载数据,卸装数据中包含索引数据,所以效率更高。

对海量表迁移建议采用onunload/onload工具。

需要说明的是,onunload输出的二进制卸载的数据占用空间比unload工具大(见测试2),尤其表有大量删除记录、索引较多的时候,所以首先估计卸载数据空间大小,准备充足的磁带或磁盘空间,保证顺利地卸载。估计方法见附录。

提高 unload/load 性能的技巧

1.去掉数据库(表)日志,提高 load 速度
使用 load 工具进行批量装载时,如果数据库有日志,效率很低;尤其数据量较大时,还可能出现锁溢出、长事务等导致装载失败;对于失败的装载为保证数据一致性, IDS 自动回滚。回滚很耗时,尤其当长事务超出排它高水准线( LTXEHWM )时,其他用户的进程将被挂起,性能进一步恶化。这在联机事务处理环境中,势必影响其他业务的正常运行。为此,对有日志的数据库进行大量数据装载时,尽量去掉数据库日志,提高效率。
对于不能去掉日志的数据库环境,如果 Informix 版本为V9系列,可采用表级日志更改功能,即在装载时停止装载表的日志,装载结束后恢复表日志。
实施步骤:
1) 删除装载表的参照性约束及索引,只能对没有参照性约束及索引的表改变表级日志,所以在装载前先删除表索引及参照性约束,在装载完毕后重新创建。
2) 取掉目的表级日志
ALTER TABLE TABNAME TYPE(RAW)
3) 采用 LOAD 工具装载,创建索引及参照性约束
4) 增加目的表表级日志
ALTER TABLE TABNAME TYPE(STANDARD)

2.采用多进程,加速 unload/load 卸装速度
目前大多数用户数据库服务器配置较高,具有多个 CPU ,物理内存也较大,如果采用单进程卸装海量表,一方面运行速度缓慢,而另一方面大量系统资源闲置。为此,建议采用多进程,加速卸载速度。针对不同的实际情况,可采用不同方法启用多进程:
将需要装卸的多张表分配到不同的进程中并发装卸,可减少整体装卸载时间;
将海量表分配到多个进程中并发卸装,可减少海量表的卸载时间
多进程与单进程卸装速度的见测试6。
需要说明的是,多进程适合运行在资源充分、负载较轻、具有多个 CPU 的主机上,而对资源较少,或负载已重的主机,启动多进程反而会降低性能,甚至影响其他应用程序的执行。一般来说,在多 CPU 机器上可启动少于物理 CPU 个进程,同时将分割的多个输入输出文件分布在不同设备上。
采用多进程装载海量表,如果装载的数据库有日志,情况较为复杂,需要特别注意锁溢出与长事务的出现,为此采用以下方法:
采用小事务,即每个进程每次装载的数据量适当小。
多进程并发装载同一表,防止锁溢出,表锁设置为页级锁,而非行锁。
去掉表的索引,加速每个进程事务的提交。

3.去掉索引,加速 load 装载速度
索引的根本目的是提高查询效率,但在插入操作时,索引的存在却严重地影响效率。当插入数据量非常大时,索引页重建量也很大,索引结点的分裂也相当频繁, I/O 操作显著增大。同时索引页的分裂,导致需要更多的内存空间来存放分裂前相同的数据量,从而降低缓存效率。
下列情况索引的存在甚至导致装载失败:采用多进程向有日志数据库装载数据时,如果表有索引,锁模式为页锁,则会由于锁碰撞导致装载失败。这是因为每个进程对其插入的索引页加锁,当其他进程插入的记录需要在加锁的索引页上重建的索引时,势必引起索引页上锁碰撞,导致装载失败。这种情况可将表的锁模式改为行锁得到解决,但对于海量数据而言,采用行锁模式不仅锁管理开销庞大,性能低下,而且很可能由于锁溢出而装载失败。所以去掉索引,保证装载的成功。

4. 调整 IDS 性能参数,加速索引创建
调整 IDS 与性能相关的参数,可加速索引创建:
数据表分片,为数据并行扫描提供可能。
设置 MAX_PDQPRIORITY 及 PDQPRIORITY 参数,启用 PDQ 功能。需要说明,启动 PDQ 功能,将会给操作系统带来很大的负载, PDQ 将占用更多的内存、 CPU VP 、扫描线程、磁盘 I/O 资源。应充分考虑在 OLTP 环境对应用程序的影响。运用 PDQ 技术应该选择多 CPU 的机器。
配置多个临时数据库空间,提高排序速度。

其他说明

使用 unload 工具迁移,对静态表而言,卸载的文本与源表保持一致;对动态表而言,由于表的更新随时发生,从而造成卸载的文本与源表不一致性。为保证数据的一致性,可考虑将表加锁卸载。加锁会降低并发,需要权衡两方面的因素。装载时如果表有并发追加操作,同样存在上述问题。为此可对表更名装载,核对正确后,再删除原表,更名新表为目的表。onunload/onload 工具对卸装表加锁,故不存在上述问题。

测试

1.onunload/onload 与 unload/load 的装卸载速度测试

清单1 . 测试1

主 机:HP-V260小型机,配置有8个CPU,8G内存,操作系统为HP UNIX
版 本:informix Dynamic Server Version 7.30
测试表:test1 ,列长:63, 记录数:8900000,有一复合唯一索引。
测试结果:
命令 耗时: real user sys
Unload 12:03.47 5:17.41 2:49.06
Onunload 6:08.35 0.02 0.00
Onload 16:22.89 0.01 0.01
Load 22:13.01 11:59.24 42.19
Load后创建索引: 7:11.74 0.00 0.01

清单2 . 测试2

主 机:HP V2600小型机,配有4个CPU,8G内存,操作系统为HP UNIX
版 本:informix Dynamic Server Version 9.30
测试表:test2,列长:24, 记录数:5009641,有一复合唯一索引。
测试结果:
命令 耗时: real user sys
Unload 6:51.30 5:26.23 16.57
Onunload 1.38.00 0 0
Onload 11:27.62 0.01 0.01
Load 33:17:85 4:02.81 13.59

结论:
Onload/onunload 比 load/unload 性能差异与数据量、主机有关。
理论上讲,如果数据库中索引占用比例较大,用unload卸出速度较快,但装入后创建索引较慢; Onload卸装较稳定,只与数据量大小有关。

2. onunload 与 unload 卸载数据占用空间大小比较

测试表 test 2
test 2 表列长:24,记录数:5009641,页大小:P=2K,有一复合索引。

数据页申请空间 数据页占用空间 索引页占用空间 合计
1262696页 1252722页 418004页 (1252722+418004)P=3421646848字节

test 2 测试结果
Unload 输出文件大小 Onunload 输出文件大小
970781283 字节 3422552064 字节

结论: unload 卸出的数据文本比 onunload 卸出的二进制数据文件小。

3. 测试 onunload/onload 对磁盘碎片的整理情况

1) 测试前统计,测试表 test3 在 sysextents 中有 60 个 extents,说明 extents 不连续。
2) 将测试表采用 onunload/onload 卸装。
3) 卸装后统计,测试表 test3 在 sysextents 中仅有 5 个 extents,说明对 extents 进行了整理。

注意:Extents 个数大于 1,可能是测试表太大, IDS 需要多次分配磁盘空间;也可能有并发进程在同一数据库空间有写操作。

4. onunload 对删除的记录卸装的测试

1) 新建一表 test4,装载数据,从而使表每个数据页的 rowid 连续;
2) 对 test4 表使用 onunload 卸载,卸出文件占用磁盘空间为 1858076672K;
3) 采用 mod(rowid, 3)=1 条件删除 test4 表中大量记录;
4) 采用 onload 装载测试表,同时将新装载表更名为 test4_1
5) 使用 onunload 卸载 test4_1 表,卸出文件占用磁盘空间仍为 1858076672K;
6) 检测 test4、test4_1 表的 rowid ,结果一致

结论:onunload/onload 并不清理记录已删除的空间。

5. 测试 PDQPRIORITY 的并行效率

清单 3. 测试 PDQPRIORITY 的并行效率

版 本:informix Dynamic Server Version 9.30
主 机:HP V2600小型机,配有4个CPU,8G内存,操作系统为HP UNIX
表状态:列长:427 记录数:500万
测试序号 测试条件 测试结果
1 set_pdqpriority 0 创建5个索引 24:16
2 set_pdqpriority 100 创建5个索引 07:32

结论:启用 PDQ 可加速索引的创建。

6. 复杂测试及对比:

单进程与多进程装载效率比较
索引表与无索引表装载效率比较
日志与无日志装载效率比较

测试环境

版 本:Informix Dynamic Server Version 9.30
主 机:HP V2600 小型机,配有 4 个CPU,8G 内存,操作系统为 HP UNIX
装载文件:行数 rows=8500000,列宽 rowsize=580。

表 1. 测试结果对比表
序号 日志方式 装载时索引状况 多进程 测试目的 结果
1 数据库及表有日志 无索引 单进程 长事务回滚 在耗时 15 分钟、装载 800 多万时出现长事务,回滚耗时 5 小时
2 数据库及表有日志 无索引 6 个进程 小事务多进程 12 分钟
3 数据库无日志  无索引 1 个进程 无索引非日志单进程装载 18 分钟
4 数据库无日志  无索引 6 个进程 无索引非日志多进程装载 4 分钟
5 数据库无日志  有索引 6 个进程 有索引非日志多进程装载 18 分钟
结论:去掉索引及数据库日志、采用多进程装载时,可显著提高装载效率。
附录
1.onunload 工具卸载空间大小的估计
1) 估算表的数据量
清单4. 估算表的数据量
Select ti_npdata, ti_nrows
from sysmaster:systabinfo a, sysmaster:systabnames b
where ti_partnum=partnum and tabname=查询的表名 and ti_nkeys=0;

2) 估算表的索引数据量
清单5. 估算表的索引数据量

Select  ti_npdata  
    from   sysmaster:systabinfo a, 
    database_name: systabnames b , database_name:sysindexes  c
    where  ti_partnum=c.partnum  and b.tabname=表名  and  b.tabid=c.tabid;

将1),2)结果相加,可近似看作 onunload 卸载文件大小。实际 onunload 卸载文件大小比估计结果略大些。这里注意,需要访问两个数据库的内容: sysmaster,与查询表所在的数据库: database_name。

2.unload 工具卸载文本空间大小的估计

1) 统计表的记录数 recnum;
2) 统计每条记录占用磁盘空间大小;
将1),2)结果相乘,可近似看作 unload 卸载文件大小。

3. 查找 extents 个数较多的表
清单6. 查找 extents 个数较多的表

Select  tabname, count(*)  rec  
    from sysmaster:sysextents 
    group by 1 into temp tj with no log;
Select * from tj order by rec desc ;

发表回复