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做了性能对比，下一阶段可以分析一下这类数据加载工具的代码实现。