pg_bulkload工具的使用

原创
2017/03/06 16:50
阅读数 3.9K

    目前在做一个数据加载工具的项目,近期研究了pg_bulkload这款数据加载工具,在此,把学习pg_bulkload的东西分享出来。

 一、pg_bulkload数据加载工具

    pg_bulkload是批量导入数据的工具,当你一次插入的数据量非常大的时候,这种通过外部文件加载数据的方式要比multi-insert性能高很多,本文也会介绍insert语句的执行过程,通过分析insert的执行过程,可以发现pg_bulkload性能为什么如此之高。PostgreSQL(下文都简称pg)内部也支持从外部加载数据到数据库表,在pg里这款工具叫做copy,pg_bulkload与copy在实现上有差别,本文会介绍pg_bulkload与copy的性能对比。

    pg_bulkload现在稳定的版本是3.1,支持的pg版本是8.3到9.4,目前只支持linux系统。pg_bulkload的使用与pg的contrib目录下的extension一样,安装前pg已经完成了安装并且初始化,我的pg版本是9.4,安装目录是:/Users/wanggang/pg9.4。pg_bulkload安装需要pg_config命令,这个命令在bin目录下,所以需要把bin目录导入PATH中,安装跟其他软件安装一样,make & make install。

安装完后在pg_bulkload的bin目录下有个pg_bulkload的命令,这就是数据加载的命令,该目录下还有一个命令postgresql,下面详细介绍这两个命令的使用。

1.1 命令

postgresql 脚本

    postgresql脚本是pg_ctl的封装,启动、停止数据库,postgresql脚本在内部调用pg_ctl,为什么有了pg_ctl还需要一个postgresql脚本?这主要跟pg_bulkload的实现机制有关,为了追求性能,pg_bulkload不写日志,因此pg_bulkload自己提供了一个恢复的函数,postgresql脚本会在启动时检查是否需要调用恢复函数。如果使用pg_bulkload,建议使用postgresql脚本启动数据库。

pg_bulkload 

    这个程序用来加载数据,它在内部调用自定义函数pg_bulkload(),此命令的使用包括以下三步:

  1. 编辑一个控制文件,比如"simple_csv.ctl",这个控制文件里包括了加载数据的一些设置,比如输入文件的位置,输出文件,文件格式等,也可以使用命令行参数,路径需要绝对路径。
  2. 执行的过程中会产出一个状态记录文件。
  3. 如果使用控制文件,可以使用相对路径。

1.2 参数

下面介绍pg_bulkload主要的参数选项,主要有一下选项:

加载选项

-i INPUT

--input=INPUT

--infile=INPUT

    数据源的位置,与控制文件里的"INPUT"类似。

-O OUTPUT

--output=OUTPUT

    数据的输出,比如数据库的某个表,与控制文件的"OUTPUT"类似。

-l LOGFILE

--logfile=LOGFILE

    保存加载结果日志文件的路径,与控制文件的"LOGFILE"类似。

-o "key=value"

    其他可用的选项,比如“TYPE=CSV","WRITER=PARALLEL",可以指定多个选项。

连接选项

-d dbname

--dbname dbname

    指定需要连接的数据库,如果没有没有指定,默认使用环境变量PGDATABASE,如果PGDATABASE没有设定,默认使用用户名作为数据库名。

-h host

--host host

    指定需要连接的主机地址。

-p port

--port port

    指定端口,pg默认端口是5432。

    上面这三个参数通常都可以从环境变量得到,建议设置环境变量,因为pg_bulkload命令内部使用libpq,libpq需要这些环境变量。

通用选项

--help

    显示帮助信息。

--version

    显示版本号。

控制文件

    除了在命令行上指定参数外,还可以在控制文件中指定参数,下面介绍控制文件里的参数。

TYPE=CSV|BINARY|FIXED|FUNCTION

        输入数据的类型,默认是CSV。

  • CSV:从CSV格式的文本文件里加载数据。
  • BINARY|FIXED:从二进制文件里加载数据。
  • FUNCTION:从函数输出中加载数据。       

INPUT|INFILE=path|stdin|function_name

    数据源,必须指定,类型不同,它的值也不一样:

  • 文件:如果是文件,此处就是路径,可以是相对路径,pg服务器必须有读文件的权限。
  • stdin:pg_bulkload将从标准输入读取数据。
  • SQL FUNCTION:指定SQL函数,用这个函数返回插入数据,可以是内建的函数,也可以是用户自定义的函数。如下:

          

WRITER=DIRECT|PARALLEL|BUFFERED|BINARY

    加载数据的方式,默认是DIRECT

  • DIRECT:直接把数据写入表中,绕过了共享内存并且不写日志,需要提供恢复函数。
  • BUFFERED:把数据写入共享内存,写日志,利用pg的恢复机制。
  • PARALLEL:并行处理模式,速度比DIRECT更快。
  • BINARY:把输入数据转换成二进制数据,然后加载。

OUTPUT|TABLE=table_name|outfile

    输出源,即把数据导到哪里。

  • 表:把数据导入到数据库的表里。
  • 文件:指定文件的路径,把数据导入到文件里。 

LOGFILE=path

    日志文件的路径 ,执行过程中会记录状态。

MULTI_PROCESS=YES|NO

    若设置了此值,会开启多线程模式,并行处理数据导入。若没设置,单线程模式,默认模式是单线程模式。VERBOSE=YES|NO

    若设置了YES,坏的元组将写入服务器日志,默认是NO。

SKIP|OFFSET=n

    跳过的行数,默认是0,不能跟"TYPE=FUNCTION"同时设置。

LIMIT|LOAD

    限制加载的行数,默认是INFINITE,即加载所有数据,这个选项可以与"TYPE=FUNCTION"同时设置。                           

1.3 pg_bulkload的架构

    pg_bulkload主要包括两个模块:reader和writer。reader负责读取文件、解析tuple,writer负责把解析出的tuple写入输出源中。pg_bulkload最初的版本功能很简单,只是加载数据。3.1版本增加了数据过滤的功能。

二、insert在pg内的实现

    本节介绍insert语句在pg内部的处理过程,insert的一般处理步骤如下:

  1. 开启事务
  2. 词法语法解析
  3. 优化、重写语法树
  4. 执行器执行
  5. 关闭事务

    insert处理过程中函数调用关系:

    图中红色虚线框里的函数才是跟插入相关的操作,heap_insert把数据写入共享内存,XLogInsert写日志。insert默认是autocommit,用户没有指定begin....end,默认一条insert就开启一个事务,在批量导入数据时这是很大的开销。因为insert需要做上图中所有的工作,所以批量插入数据性能都很低。PostgreSQL内部支持copy批量加载数据。copy的操作在一个事务内完成,copy不用经过parser、rewriter、planner过程,性能自然优于insert。

三、pg_bulkload与copy的性能对比

    pg_bulkload与copy在实现上是有区别的,pg_bulkload默认的处理方式是绕过了共享内存,不写日志,copy默认是把数据写入共享内存同时写日志,下面对比二者的性能差异。测试的数据集3.6G,macbook pro,I5处理器,8G内存,256SSD。创建了一个test的表,表结构如下:

insert into test select generate_series(1,50000000),md5(random()::text),clock_timestamp();

可以用上面这条SQL语句产生数据,然后利用COPY TO命令把数据导出到文本文件中,

copy test to '/Users/wanggang/test.sql';

测试结果如下:

    在命令行里用到了pg的环境变量,建议使用环境变量,因为pg_bulkload命令通过libpq与pg服务器通信,libpq需要使用这些环境变量,使用前指定这些环境变量。数据结果显示一共加载了5千万行数据,没有解析错误的行,详细的输出信息在test.log文件里,通过参数l指定,如下图。

    上图是pg_bulkload的日志记录的测试数据,图中的参数就是上文中介绍的参数选项。INPUT表示数据源的路径,LOGFILE是运行结果日志的路径,TYPE是数据源的文件格式,WRITER是处理方式,默认DIRECT,最后一行显示了运行时间,95.5秒。

    上图是copy处理同样的数据的执行结果,执行时间是172秒,copy是需要写日志的,刷写日志缓冲区会增加磁盘I/O,性能自然会比pg_bulkload低。

四、总结

    这里重点介绍了pg_bulkload的使用,简单介绍了其内部的实现原理,并与copy做了性能对比,下一阶段可以分析一下这类数据加载工具的代码实现。

 

 

展开阅读全文
加载中

作者的其它热门文章

打赏
0
1 收藏
分享
打赏
0 评论
1 收藏
0
分享
返回顶部
顶部