PostgreSQL pg_bulkload 性能
1 背景知识
本文主要对比 copy 和 pg_bulkload 性能差异和测试结果。
1.1 测试的基准表
测试的基准表是使用 TPC-C 中的 DBT-2 数据子集。
1.2 索引
测试数据集会创建两个索引。
第一个是主键，有一个升序整数数列。
第二个是非唯一索引，包含一个随机整数列。
1.3 测试的场景
场景一： 将 4GB 初始数据加载到空表中。
场景二：将 1GB 数据作为附加数据。
场景三： 调整 maintenance_work_mem 和 FILTER 功能。
2 初始数据加载
在 WRITER=PARALLEL 模式下，pg_bulkload 加载数据的时间几乎是 copy 的一半。
在 PARALLEL 模式下，多核CPU 的性能将会提高不少，因为读取的数据文件和写入数据的表是两个进程同时完成的。
| Item | Duration | Duration comparison |
|---|---|---|
| COPY with indexes | 500 sec | - |
| COPY without indexes + CREATE INDEX |
333 sec (229 sec + 51 sec+ 53 sec) |
66.7 % |
| pg_bulkload (DIRECT) with indexes |
334 sec | 66.8 % |
| pg_bulkload (PARALLEL) with indexes |
221 sec | 44.2 % |
3 附加数据加载
在 WRITER = PARALLEL 模式下，pg_bulkload 加载的数据时间几乎是 copy 的一半。
Appended Load (1GB)
| Item | Duration | Duration comparison |
|---|---|---|
| COPY with indexes | 140 sec | - |
| COPY without indexes + CREATE INDEX |
187 sec (62 sec + 60 sec + 65 sec) |
133.6 % |
| pg_bulkload (DIRECT) with indexes |
93 sec | 66.4 % |
| pg_bulkload (PARALLEL) with indexes |
70 sec | 50.0 % |
4 参数和特性的调优
maintenance_work_mem 会影响 pg_bulkload 的性能。如果此参数从 64MB 改为 1GB ，所耗费的时间缩短 15%。
Filter 功能可以转换输入的数据，但会增加导入时间。实际测量显示，SQL 函数的加载时间增加了 240%，而 C 语言 的函数加载时间增加了 140%。
| MWM 值 | 加载方法 | Initital（4GB） | Appedned(1GB) |
|---|---|---|---|
| MWM = 64MB | DIRECT | 397 sec | 109 sec |
| MWM = 1GB | DIRECT | 334 sec | 93 sec |
| MWM = 1GB | DIRECT with SQL FILTER | 801 sec | 216 sec |
| MWM = 1GB | DIRECT with C FILTER | 456 sec | 126 sec |
5 服务器环境
| Item | Value |
|---|---|
| Server | Dell PowerEdge R410 |
| CPU | Intel Xeon E5645 (2.4GHz) 12 core * 2 |
| Memory | 32GB |
| Disks | SAS 10000rpm 2TB * 4 |
| OS | CentOS 6.2 (64bit) |
| PostgreSQL version | 9.3.4 |
| pg_bulkload version | 3.1.6 |
| shared_buffers | 3210MB |
| checkpoint_segments | 300 |
| checkpoint_timeout | 15min |
| work_mem | 1MB |
| maintenance_work_mem | 1GB |
| Table definition | DBT-2 customer table |
| Indexed columns | c_id (PRIMARY KEY) |
| c_d_id (non-unique B-Tree) | |
| Constraints | NOT NULL for all columns |
| Input file format | CSV |