mysqlworkbench导入sql文件_数据库使用SQL*Loader导入的并行误区,如何巧妙规避?
发布日期:2021-10-30 18:55:24 浏览次数:6 分类:技术文章

本文共 2618 字,大约阅读时间需要 8 分钟。

de1d7aa065ac6499481135be9387d118.gif

作者介绍

蒋健,云趣网络科技联合创始人,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 参考下图:

d4bff291bee82a6646f4c4c45e9e5df8.png

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

三、总结

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

0143f929eb7b4156760ec297c95b6e93.png

转载地址:https://blog.csdn.net/weixin_39849239/article/details/111278914 如侵犯您的版权,请留言回复原文章的地址,我们会给您删除此文章,给您带来不便请您谅解!

上一篇:华为摄像头搜索软件_涨知识!原来华为手机摄像头还有这些高级玩法,学会轻松提高工作效率...
下一篇:sql 外键_数据库的外键,到底该不该用?为什么

发表评论

最新留言

很好
[***.229.124.182]2024年03月01日 13时19分09秒

关于作者

    喝酒易醉,品茶养心,人生如梦,品茶悟道,何以解忧?唯有杜康!
-- 愿君每日到此一游!

推荐文章

仪表盘故障图像识别_仪表显示的图像识别算法研究 2019-04-21
c#背单词小程序视频_C#用timer实现背单词小程序 2019-04-21
24v开关电源维修技巧_【电视技术】液晶电视电源板十个维修经验分享 2019-04-21
laravel comment显示到页面最上面了_使用 Laravel 快速开发API接口,新手必读 2019-04-21
echart实现3d地图_orbslam_2生成稀疏点云地图的保存与加载的实现 邹鹏程 2019.9.15... 2019-04-21
bash 不是内部或外部命令_python学习笔记6-pip命令不是内部命令问题 2019-04-21
管道的另一端上无任何进程。_别被忽悠入坑!信号贴贴上就能信号满格?对手机信号无任何改善... 2019-04-21
mysql无法写数据库_求助,为何我的数据不能写入数据库 2019-04-21
ssh 两个mysql数据库_ssh连接两个数据库(转) 2019-04-21
mysql 双向链表_23张图!万字详解「链表」,从小白到大佬! 2019-04-21
mysql 常量命名规则_详解Java编程规约(命名风格、常量定义、代码格式) 2019-04-21
pomelo mysql_全文索引 - Pomelo.EFCore.MySql 2019-04-21
如何打开git命令窗口_win10系统如何将右键菜单中“在此处打开powershell窗口”调整为“在此处打开命令窗口”?... 2019-04-21
rtsp 华为_华为多实例生成树RSTP配置详解 2019-04-21
ewb交通灯报告和文件_基于ewb平台的交通灯电路设计.doc 2019-04-21
mysql中$使用_在MySQL中使用序列的简单教程 2019-04-21
mysql alter auto increment_将MySQL列更改为AUTO_INCREMENT 2019-04-21
mysql+err+1067_MySQL 5.7 Invalid default value for 'CREATE_TIME'报错的解决方法 2019-04-21
程序中mysql添加用户_MySQL添加用户的两种方法 2019-04-21
简述mysql安装过程_mysql安装的过程 2019-04-21