pg_bulkload
pg_bulkloadは大量データの高速ロードするツールです。
大量データをロードするには他にCOPYコマンドを使用する方法もありますが、pg_bulkloadを使えばCOPYコマンドよりも高速にデータをロードすることができます。
pg_bulkloadは、詳細なドキュメントが以下に準備されており、特にここで追加して説明する必要はないかなと思います。
今回はpg_bulkloadをインストールして、COPYコマンドとの性能比較を実施したいと思います。
COPYコマンドとの性能比較は以下のページにも結果が記載されていますが、PostgreSQL9.6で実行されているので、ここではPostgreSQL12で実行しています。
環境
以下の環境で試しています。
- CentOS 7.5(CPU 2core, MEM 8GB)
- PostgreSQL 12
- pg_bulkload 3.1.16
pg_bulkloadのインストール
今回はCentOSなので、以下からRPMファイル（pg_bulkload-3.1.16-1.pg12.rhel7.x86_64.rpm）をダウンロードしインストールします。
JITを有効にしてPostgreSQL 11、12を使用している場合は、pg_bulkload-llvmjitも一緒にインストールするといいらしいのですが、今回は使用していません。
rpmコマンドでインストールします。
# rpm -ivh pg_bulkload-3.1.16-1.pg12.rhel7.x86_64.rpm
データベースに接続して、CREATE EXTENSIONコマンドで拡張をインストールします。
# CREATE EXTENSION pg_bulkload;
テストデータの作成
インポート用のCSVファイルは一旦テーブルにデータを作成し、COPYコマンドで作成しました。
DBT-2のcustomerテーブルを利用しています。
最初にcustomerテーブルを作成し、2000万件のデータを作成します。
-- drop table customer;
create table customer (
c_id INTEGER,
c_d_id INTEGER,
c_w_id INTEGER,
c_first VARCHAR(16),
c_middle char(2),
c_last VARCHAR(16),
c_street_1 VARCHAR(20),
c_street_2 VARCHAR(20),
c_city VARCHAR(20),
c_state char(2),
c_zip char(9),
c_phone char(16),
c_since TIMESTAMP,
c_credit char(2),
c_credit_lim NUMERIC(24, 12),
c_discount REAL,
c_balance NUMERIC(24, 12),
c_ytd_payment NUMERIC(24, 12),
c_payment_cnt REAL,
c_delivery_cnt REAL,
c_data VARCHAR(500));
insert into customer(c_id, c_d_id, c_w_id, c_first, c_middle, c_last, c_street_1, c_street_2, c_city, c_state, c_zip, c_phone, c_since, c_credit, c_credit_lim, c_discount, c_balance, c_ytd_payment, c_payment_cnt, c_delivery_cnt, c_data)
select
i,
i,
i,
'first name',
'mr',
'last name',
'street name 1',
'street name 2',
'city name 1',
'99',
'999999999',
'9999999999999999',
clock_timestamp(),
'99', -- credit
123456,
123456,
123456,
123456,
123456,
123456,
'test data 1234567890123456789012345678901234567890'
from
generate_series(1, 20000000) as i -- 2000万レコード
;
-- drop index i_customer;
-- alter table customer drop constraint pk_customer;
alter table customer
add constraint pk_customer
primary key(c_w_id, c_d_id, c_id);
create index i_customer
on customer(c_w_id, c_d_id, c_last, c_first, c_id);
データ投入後、pg_total_relation_size関数でテーブル＋インデックスのサイズをはかると6,455MBでした。
select pg_total_relation_size('customer');
pg_total_relation_size
------------------------
6769287168
(1 row)
最後にCOPYコマンドでCSVファイルに出力します。出力されたCSVファイルは5,363MBでした。
postgres=# COPY customer TO '/var/lib/pgsql/customer.csv' WITH CSV DELIMITER ',';
COPY 20000000
計測方法
計測はcustomerテーブルに2000万行のCSVファイルをロードして実施します。
以下の4ケースで計測しています。
- COPYコマンドでロード（インデックスあり）
- COPYコマンドでロード（インデックスは後から作成）
- pg_bulkloadでロード（ノンパラレル）
- pg_bulkloadでロード（パラレル）
COPYコマンドの実行方法
COPYコマンドの実行は、実行時間の出力のため以下の無名関数で実行しています。
DO $$
DECLARE
ttime timestamp;
BEGIN
select clock_timestamp() into ttime;
RAISE NOTICE '%', ttime;
COPY customer_imp FROM '/var/lib/pgsql/customer.csv' WITH CSV DELIMITER ',';
select clock_timestamp() into ttime;
RAISE NOTICE '%', ttime;
END
$$
;
実行の出力結果は以下のようになります。
NOTICE: 2020-03-21 03:30:26.20532
NOTICE: 2020-03-21 03:33:33.425405
DO
インデックスの作成時間は以下のように計測しました。
DO $$
DECLARE
ttime timestamp;
BEGIN
select clock_timestamp() into ttime;
RAISE NOTICE '%', ttime;
alter table customer_imp
add constraint pk_customer
primary key (c_w_id, c_d_id, c_id);
create index i_customer
on customer_imp (c_w_id, c_d_id, c_last, c_first, c_id);
select clock_timestamp() into ttime;
RAISE NOTICE '%', ttime;
END
$$
;
pg_bulkloadの実行方法
pg_bulkloadコマンドを実行するには、ロードの設定等について記載された制御ファイルを準備します。
以下が制御ファイルの例です。各行のコメントの":"以降はデフォルト値を示しています。
OUTPUT = customer_imp # 出力先のテーブル
TYPE = CSV # 入力データのタイプ: CSV
INPUT = /var/lib/pgsql/customer.csv # 入力データソース
LOADER = DIRECT # ロード方式: DIRECT
SKIP = 0 # 先頭からスキップする行数を指定: 0
LIMIT = INFINITE # ロード行数：INFINITE（全件）
CHECK_CONSTRAINTS = NO # ロード時に CHECK 制約を検査するかどうかを指定: NO
PARSE_ERRORS = 0 # パース処理等の許容件数を指定: 0
# エラーの件数がこの値を超えた場合は、その時点でコミットして残りの入力データのロードは行わない。-1だと無制限。
DUPLICATE_ERRORS = 0 # 一意制約違反の許容件数を指定: 0
# エラーの件数がこの値を超えた場合は、ロールバックする。-1だと無制限。
ON_DUPLICATE_KEEP = NEW # 一意制約違反のレコードが存在した場合の挙動。: NEW
# NEWだと新しくロードしたデータ、OLDは元々のデータを残す。
LOGFILE = ./pg_bulkload.log # ログファイルのパス
PARSE_BADFILE = ./PARSE_BADFILE.csv # BADファイルのパスを指定
DUPLICATE_BADFILE = ./DUPLICATE_BADFILE.csv # 一意制約違反の不良レコードを記録するBADファイルのパスを指定
TRUNCATE = NO # YESの場合はデータロード前にテーブルから全ての行を削除: NO
MULTI_PROCESS = NO # YESの場合は並行処理を行う: NO
DELIMITER = "," # デリミタを指定: ,
QUOTE = "\"" # 引用符を指定: "
ESCAPE = \ # エスケープ文字を指定: "
なお、制約については公式ドキュメントに以下の記述があります。
デフォルトでは、ロード時のデータの整合性は、一意制約と非NULL制約のみ適用します。 CHECK 制約を適用する場合は "CHECK_CONSTRAINTS=YES" を指定してください。 外部キー制約は適用しません。
制御ファイルを引数に指定してpg_bulkloadコマンドを実行します。
ログファイルはデフォルトで$PGDATA/pg_bulkloadに保存されます。
$ pg_bulkload -d postgres -h 127.0.0.1 -U postgres sample_csv.ctl
NOTICE: BULK LOAD START
NOTICE: BULK LOAD END
0 Rows skipped.
20000000 Rows successfully loaded.
0 Rows not loaded due to parse errors.
0 Rows not loaded due to duplicate errors.
0 Rows replaced with new rows.
実行時間は以下のようにログへ出力されます。
$ cat pg_bulkload.log
0 Rows skipped.
20000000 Rows successfully loaded.
0 Rows not loaded due to parse errors.
0 Rows not loaded due to duplicate errors.
0 Rows replaced with new rows.
Run began on 2020-03-21 03:36:05.284753+01
Run ended on 2020-03-21 03:38:20.920093+01
CPU 5.41s/125.25u sec elapsed 135.64 sec ★この数値を記録する
計測結果
計測結果は以下のようになりました。
| ケース | 実行時間 |
|---|---|
| COPYコマンドでロード（インデックスあり） | 405秒 |
| COPYコマンドでロード（インデックスは後から作成） | 259秒（インデックス作成72秒を含む） |
| pg_bulkloadでロード（ノンパラレル） | 215秒 |
| pg_bulkloadでロード（パラレル） | 191秒 |
COPYコマンドでロードする場合、インデックスは後から作成した方が高速です。
pg_bulkloadはそれよりも高速でパラレル実行すると更に高速になります。
pg_bulkloadはノンパラレルとパラレルであまり時間が変わりませんでしたが、コア数が多ければ（今回は2コア）もっと差がついたかもしれません。
pg_bulkloadはmaintenance_work_memパラメータにも影響を受けるということで、maintenance_work_memを変更した場合も計測しました。
maintenance_work_memをデフォルトの64MBから1GB変更した場合、pg_bulkloadでロード（ノンパラレル）のケースで215秒から193秒と、10%程度速くなりました。