写点什么

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

  • 2019-08-02
  • 本文字数:2186 字

    阅读完需:约 7 分钟

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

本文由 dbaplus 社群授权转载


本文中的 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。当然如果没硬性限制的话,很多场景可以考虑直接用外部表。


作者介绍


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


原文链接


https://mp.weixin.qq.com/s?__biz=MzI4NTA1MDEwNg==&mid=2650779518&idx=2&sn=08c456ecb524111099bee96f28756db6&chksm=f3f91aebc48e93fd8c40705cf2b218ef9592e5ec9c0f65eefeeebc12d2a54ab81c135a7a4704&scene=27#wechat_redirect


2019-08-02 08:002697
用户头像
dbaplus社群 数据连接未来

发布了 175 篇内容, 共 82.0 次阅读, 收获喜欢 620 次。

关注

评论

发布
暂无评论
发现更多内容

高情商程序员:5种类型的bug沟通有诀窍!

养心进行时

bug bug修复 bug管理 bug报告

你的拖延,该不会是“约拿情结”吧?程序员的5个时间管理技巧

养心进行时

时间管理 拖延症

AIGC时代IT人的迷茫有解(2):从“产品规划十步法”到“职业规划十步法”

养心进行时

职业规划 产品规划

安全是最大的豪华!星纪元ET成功挑战31.9米空中坠落试验

极客天地

芯盾时代IAM,三种权限管理模型按需选择

芯盾时代

权限管理 iam 统一身份认证 统一身份管理平台 最小权限管理

新一代智慧音视频平台,企业必备新基建

中关村科金

音视频 企业服务 大模型

程序员离不开的10款开发软件(必备)

伤感汤姆布利柏

当「软件研发」遇上 AI 大模型

阿里巴巴云原生

阿里云 云原生 通义灵码

竞品调研- 19条小技巧快速收集竞品信息

养心进行时

竞品分析 竞品调研

需求做不完?7种“问句”用了没?

养心进行时

需求 需求排序 需求变更 需求澄清 需求评审

AIGC时代IT人的迷茫有解(3):从“用户画像”到“个人职业画像”

养心进行时

职业规划 用户画像 #职业发展

Apache RocketMQ ACL 2.0 全新升级

阿里巴巴云原生

阿里云 云原生 Apache RocketMQ

即时通讯技术文集(第37期):IM代码入门实践(Part1) [共16篇]

JackJiang

即时通讯;IM;网络编程

高情商程序员是如何沟通需求的?

养心进行时

需求 需求排序 需求变更 需求澄清 需求评审

锁定“场景”,告别“需求不明确”

养心进行时

需求 场景 场景化 场景化思维 需求澄清

星纪元ET首次上演空中“自由落体”,极致安全实力无惧硬核极限挑战

极客天地

2024-05-08:用go语言,给定一个由正整数组成的数组 nums, 找出数组中频率最高的元素, 然后计算该元素在数组中出现的总次数。 输入:nums = [1,2,2,3,1,4]。 输出:4。

福大大架构师每日一题

福大大架构师每日一题

理论+实践,带你了解分布式训练

华为云开发者联盟

机器学习 华为云 华为云开发者联盟 大语言模型 企业号2024年5月PK榜

从代码到洞察:使用API接口深入分析商品详情数据

Noah

AIGC时代IT人的迷茫有解(1):从“商业画布”到“个人画布”

养心进行时

职业规划 商业画布 个人画布

锁定“用户人群”,远离“ 需求取舍难”

养心进行时

需求 用户 用户画像 需求澄清 需求评审

将“安全”进行到底!星纪元ET挑战TOP Safety空中坠落试验现场大公开

极客天地

天上掉下个“星纪元ET”!首次挑战空中坠落试验高调秀肌肉

极客天地

一键自动化博客发布工具,用过的人都说好(oschina篇)

不在线第一只蜗牛

运维 自动化

当「软件研发」遇上 AI 大模型

阿里云云效

阿里云 云原生 通义灵码

数据库使用SQL*Loader导入的并行误区,如何巧妙规避?_数据库_dbaplus社群_InfoQ精选文章