数据库使用SQL*Loader导入的并行误区,如何巧妙规避?

蒋健 2019-07-30 10:01:05
作者介绍

蒋健,云趣网络科技联合创始人,11g OCM,多年Oracle设计、管理及实施经验,精通数据库优化,Oracle CBO及并行原理。云趣鹰眼监控核心设计和开发者,资深Python Web开发者。

 

本文中的SQL*Loader案例源于几年前数据库一体机PK测试的场景,场景比较特殊,在疯狂的PK中,方案不断迭代升级,使得案例有一定的趣味性。近期又碰到客户在弄SQL*Loader的导入,老案例整理下分享一波。

 

一、大数据量超宽表导入

 

该SQL*Loader测试场景如下:

 

将一个包含约数亿行数据的txt文件(不可切分文件),使用SQL*Loader导入到数据库中,表约有200+列,不能改数据库层面配置,统计上机操作到导入完成的时间计算成绩,数据量不对该场景直接计0分。

 

当时的最新款exadata x5测试该场景计时约40分钟(直接路径,Parallel,BINDSIZE等该优化的都优化了),这个场景我测出来约10min,远超所有竞争对手的成绩,那究竟是什么操作能有如此大性能提升呢?

 

1、并行误区
 

 

当时由于甲方测试方案中限制不能拆分文件,导致大家测试时没有开启并行,那么SQL*Loader是否一定要拆分文件后才能进行并行呢?当然不是,这个场景中出了第1版本的方案:

 

  • wc -l统计数据行数;

  • 配合skip + load 生成多条命令逻辑上 进行切分文件;

  • 批量并行执行命令导入数据。

 

生成SQL*Loader的命令可以使用以下脚本,其中total line number取wc -l的结果,DOP为自定义并行度:

 

    set serveroutput on

    set linesize 1000

    set pages 0

    declare

    total_line_number number;

    dop  number;

    skip  number;

    load  number;

    tail_of_mod  number;

    command varchar2(4000);

    directory varchar2(4000);

    begin

    total_line_number := 348104868;

    directory := '/home/oracle/adam';

    dop := 20;

    skip := 0;

    load := 0;

    tail_of_mod := mod(total_line_number,dop);

    load := trunc(total_line_number/dop);

    for i in 1..dop loop

    if i = dop then 

    load := load + tail_of_mod;

    end if;

    command := 'nohup sqlldr tester/tester control='||directory||'/load.ctl log='||directory||'/test'||i||'.log READSIZE=20000000 BINDSIZE=20000000 direct=true parallel=true  errors=99999 silent=errors,discards skip='||skip||'   load='||load ||' &' ;

    dbms_output.put_line(command);

    skip := skip+load;

    end loop;

    end;

    /

 

当时测试SQL*Loader场景时,故意最后一个测试,方案报上去,甲方就补充了一条规则,数据量不对该场景直接0分。

 

经历过后面的场景后发现,当时确实是运气还不错,改进版方案应对的坑当时都没踩到。这个场景中,表的列特别多,其实导入过程中瓶颈并不是IO,而是CPU,这也才使得逻辑切分的方法非常适合使用。

 

那么如果是IO为瓶颈呢?可能这种方式并不太合适。对于有高性能存储的环境,测试发现单进程压测可以达到峰值60%的IOPS,也就是多进程能带来的IO上的提升很有限,同时skip操作,其实会产生无用的读操作,同时也消耗IO资源,综上IO为瓶颈的导入采用这种方式可能大打折扣。

 

二、超大数据量导入

 

这个场景有趣的地方就在于,如果你没看上面的那波操作,导入会一帆风顺......场景描述起来很简单:SQL*Loader单表导入6T的文本文件,条件也一样不能拆分文件。

 

只是这次稍微有点不一样的就是,有环境测试,气氛没那么紧张。但按照老的方案上来,第一步就坑了。之前的场景中,wc统计那步大约3分钟就完成了。而这次wc搞了两个小时还没弄完,不得不感叹这个厕所上的时间有点长啊。

 

很想抽根烟,可惜我不会,只能老实的cancel掉再来改进方案。很快我发现其实可以这样。

 

1、改进方案
 

 

  • 在wc统计总行数的过慢的时候,可采用估值方式。head -n 50000 xxxx.file > 1.txt;

  • 大文件的字节/小文件的字节数 * 采样样数,可以近似估算出一个总数;

  • 最后一个Job不用写load数,即为全部加载。

 

看上去这个方案还是不错,执行下来也还ok。

 

2、重大bug
 

 

只是跟甲方汇报的时候,发现了一个大问题,数据对不上!我反思了一下方案,没找到逻辑上的毛病,查了日志,也没问题,只能重新再导入一次,还是4294967295这个数值,比甲方提供的数据少几个亿。

 

作为Oracle的DBA,一个常识就是当你遇到不合理,解释不通的问题时候,MOS上的bug列表永远不会让你失望,印象中拿着4294967295这个幸运数字马上定位到了文档id(1161183.1) SQL*Loader Fails To Load More Than 4294967295 (2^32 - 1) rows From An External Data File 参考下图:

 

 

简单来说就是:每命令只能load约42亿数据,加上skip约65亿。12c后才修改这个bug,当时主流版本时11.2.0.4所以这个任务不拆分文件SQL*Loader应该是搞不定的。

 

三、总结

 

对于SQL*Loader导入场景中,skip + load实现并行的导入的方式对于CPU消耗大的导入(涉及很多的单行拆分)还是有适用场景的,但有对于IO密集型的导入,可能需要测试以及规避bug。当然如果没硬性限制的话,很多场景可以考虑直接用外部表。

活动预告