使用 load 工具进行批量装载时,如果数据库有日志,效率很低;尤其数据量较大时,还可能出现锁溢出、长事务等导致装载失败;对于失败的装载为保证数据一致性, IDS 自动回滚。回滚很耗时,尤其当长事务超出排它高水准线( LTXEHWM )时,其他用户的进程将被挂起,性能进一步恶化。这在联机事务处理环境中,势必影响其他业务的正常运行。为此,对有日志的数据库进行大量数据装载时,尽量去掉数据库日志,提高效率。
对于不能去掉日志的数据库环境,如果 Informix 版本为V9系列,可采用表级日志更改功能,即在装载时停止装载表的日志,装载结束后恢复表日志。
实施步骤:
1) 删除装载表的参照性约束及索引,只能对没有参照性约束及索引的表改变表级日志,所以在装载前先删除表索引及参照性约束,在装载完毕后重新创建。
2) 取掉目的表级日志
ALTER TABLE TABNAME TYPE(RAW)
3) 采用 LOAD 工具装载,创建索引及参照性约束
4) 增加目的表表级日志
ALTER TABLE TABNAME TYPE(STANDARD)
目前大多数用户数据库服务器配置较高,具有多个 CPU ,物理内存也较大,如果采用单进程卸装海量表,一方面运行速度缓慢,而另一方面大量系统资源闲置。为此,建议采用多进程,加速卸载速度。针对不同的实际情况,可采用不同方法启用多进程:
- 将需要装卸的多张表分配到不同的进程中并发装卸,可减少整体装卸载时间;
- 将海量表分配到多个进程中并发卸装,可减少海量表的卸载时间
多进程与单进程卸装速度的见测试6。
需要说明的是,多进程适合运行在资源充分、负载较轻、具有多个 CPU 的主机上,而对资源较少,或负载已重的主机,启动多进程反而会降低性能,甚至影响其他应用程序的执行。一般来说,在多 CPU 机器上可启动少于物理 CPU 个进程,同时将分割的多个输入输出文件分布在不同设备上。
采用多进程装载海量表,如果装载的数据库有日志,情况较为复杂,需要特别注意锁溢出与长事务的出现,为此采用以下方法:
- 采用小事务,即每个进程每次装载的数据量适当小。
- 多进程并发装载同一表,防止锁溢出,表锁设置为页级锁,而非行锁。
- 去掉表的索引,加速每个进程事务的提交。
索引的根本目的是提高查询效率,但在插入操作时,索引的存在却严重地影响效率。当插入数据量非常大时,索引页重建量也很大,索引结点的分裂也相当频繁, I/O 操作显著增大。同时索引页的分裂,导致需要更多的内存空间来存放分裂前相同的数据量,从而降低缓存效率。
下列情况索引的存在甚至导致装载失败:采用多进程向有日志数据库装载数据时,如果表有索引,锁模式为页锁,则会由于锁碰撞导致装载失败。这是因为每个进程对其插入的索引页加锁,当其他进程插入的记录需要在加锁的索引页上重建的索引时,势必引起索引页上锁碰撞,导致装载失败。这种情况可将表的锁模式改为行锁得到解决,但对于海量数据而言,采用行锁模式不仅锁管理开销庞大,性能低下,而且很可能由于锁溢出而装载失败。所以去掉索引,保证装载的成功。
调整 IDS 与性能相关的参数,可加速索引创建:
- 数据表分片,为数据并行扫描提供可能。
- 设置 MAX_PDQPRIORITY 及 PDQPRIORITY 参数,启用 PDQ 功能。需要说明,启动 PDQ 功能,将会给操作系统带来很大的负载, PDQ 将占用更多的内存、 CPU VP 、扫描线程、磁盘 I/O 资源。应充分考虑在 OLTP 环境对应用程序的影响。运用 PDQ 技术应该选择多 CPU 的机器。
- 配置多个临时数据库空间,提高排序速度。
使用 unload 工具迁移,对静态表而言,卸载的文本与源表保持一致;对动态表而言,由于表的更新随时发生,从而造成卸载的文本与源表不一致性。为保证数据的一致性,可考虑将表加锁卸载。加锁会降低并发,需要权衡两方面的因素。装载时如果表有并发追加操作,同样存在上述问题。为此可对表更名装载,核对正确后,再删除原表,更名新表为目的表。onunload/onload 工具对卸装表加锁,故不存在上述问题。
1.onunload/onload 与 unload/load 的装卸载速度测试
主 机: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 需要多次分配磁盘空间;也可能有并发进程在同一数据库空间有写操作。
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 并不清理记录已删除的空间。
版 本: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 可加速索引的创建。
- 单进程与多进程装载效率比较
- 索引表与无索引表装载效率比较
- 日志与无日志装载效率比较
测试环境
版 本:Informix Dynamic Server Version 9.30
主 机:HP V2600 小型机,配有 4 个CPU,8G 内存,操作系统为 HP UNIX
装载文件:行数 rows=8500000,列宽 rowsize=580。
序号 | 日志方式 | 装载时索引状况 | 多进程 | 测试目的 | 结果 |
1 | 数据库及表有日志 | 无索引 | 单进程 | 长事务回滚 | 在耗时 15 分钟、装载 800 多万时出现长事务,回滚耗时 5 小时 |
2 | 数据库及表有日志 | 无索引 | 6 个进程 | 小事务多进程 | 12 分钟 |
3 | 数据库无日志 | 无索引 | 1 个进程 | 无索引非日志单进程装载 | 18 分钟 |
4 | 数据库无日志 | 无索引 | 6 个进程 | 无索引非日志多进程装载 | 4 分钟 |
5 | 数据库无日志 | 有索引 | 6 个进程 | 有索引非日志多进程装载 | 18 分钟 |
结论:去掉索引及数据库日志、采用多进程装载时,可显著提高装载效率。
1) 估算表的数据量
Select ti_npdata, ti_nrows from sysmaster:systabinfo a, sysmaster:systabnames b where ti_partnum=partnum and tabname=查询的表名 and ti_nkeys=0; |
2) 估算表的索引数据量
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。
1) 统计表的记录数 recnum;
2) 统计每条记录占用磁盘空间大小;
将1),2)结果相乘,可近似看作 unload 卸载文件大小。
Select tabname, count(*) rec from sysmaster:sysextents group by 1 into temp tj with no log; Select * from tj order by rec desc ; |