Skip to content

Latest commit

 

History

History
184 lines (149 loc) · 7.31 KB

007cc5265ab5cd6213ac.md

File metadata and controls

184 lines (149 loc) · 7.31 KB
title tags author slide
PostgreSQLのバルクインサート
PostgreSQL Docker Linux
dameyodamedame
false

序

今までOracleが多かったのもあり、バルクインサートの構文はほとんど使っていませんでした。

この手の処理は、典型的には初期構築や大量投入処理時に行われるので、システムを止めてインデックスや制約、必要ならロギングも外してローダーなどを使って大量投入した後、外したものをまた付けて統計情報更新などして整合を確認後にシステムを再開する、みたいな感覚なのです(そうしないとメチャクチャ時間かかるので)。

この感覚でいうと、バルクインサートには出番がありません。

しかし昨今は(個人的にはまだ十分とは思ってないけど)ORMなどが発展してきており、RDBMSの差異は徐々に小さくなりつつあり、RDBMS固有のローダー機能に頼るのはその阻害要因になる可能性があり、またRDBMSの構成も複雑になってきていて、個々のDBインスタンスに対して直接作用するローダーは扱いにくいかもしれません。

そこで遅ればせながら、ローダーの代わりとなる(?)バルクインサートの門戸を叩き、単独のインサートと比較してどの程度の効果があるのか調べてみました。

データベースの選択

今回はOracleに代わるデータベースの選択肢として、(ある程度)PL/SQLが使え、RETURNING句を持つPostgreSQLを使用します。MySQLや他のRDBMSでもバルクインサート自体は広く使えると思いますが、速度測定をしてみたかったのがこれだっただけです。

テーブル構造

10列で主キーあり。主キーのみvarchar(100)とし、残りの9列はbigintとし、2列ほどインデックスを付けます。

create table test(
    id varchar(100) primary key,
    value1 bigint not null,
    value2 bigint not null,
    value3 bigint not null,
    value4 bigint not null,
    value5 bigint not null,
    value6 bigint not null,
    value7 bigint not null,
    value8 bigint not null,
    value9 bigint not null
);
create index idx_test_value1 on test(value1);
create index idx_test_value2 on test(value2);

データ定義

全行で100万行とし、1行内の全ての列は単一の整数値、もしくはそれを文字列型にしたもの、とします。各行は0から採番して999999までの値になります。

トランザクション

10万行単位でコミットします。

計測

テーブルの中身が空の状態から、インデックスを付けたまま10万行ずつインサートし、100万行になるまで、各トランザクションの時間を計測していきます。1行ずつインサートする場合と、10万行分バルクインサートする場合の2種類の計測を行います。

なお、HDD環境のため、DBデータ含むファイルシステムをRAMディスク内に置いています。

https://qiita.com/dameyodamedame/items/b6e356c783a529cb7187

実装方法

コードはシェルスクリプトでSQLを出力し、これをdocker上で動作するpostgresqlに食わせる形です。

コード(クリックすると表示) 
set -eu
cat >docker-compose.yml <<EOF
services:
    db:
        build: postgres
        restart: always
        volumes:
            - ./data:/var/lib/postgresql/data
            - ./:/home/user
        ports: 
            - "5432:5432"
        environment:
          POSTGRES_PASSWORD: example
EOF
cat >setup.sql <<EOF
create database test;
EOF
cat >ddl.sql <<EOF
create table test(
    id varchar(100) primary key,
    value1 bigint not null,
    value2 bigint not null,
    value3 bigint not null,
    value4 bigint not null,
    value5 bigint not null,
    value6 bigint not null,
    value7 bigint not null,
    value8 bigint not null,
    value9 bigint not null
);
create index idx_test_value1 on test(value1);
create index idx_test_value2 on test(value2);
EOF
docker compose up -d
docker compose exec -T db bash -eux <<EOF
while ! psql 'postgresql://postgres:example@localhost' </dev/null 2>/dev/null; do 
    sleep 1
done
EOF
docker compose exec -T db psql 'postgresql://postgres:example@localhost' <setup.sql
docker compose exec -T db psql 'postgresql://postgres:example@localhost/test' <ddl.sql
docker compose exec -T db psql 'postgresql://postgres:example@localhost/test' <<EOF
truncate table test;
EOF
for i in `seq 0 9`; do
    echo "begin;" >insert.sql
    start=`expr $i \* 100000` || true
    end=`expr \( $i + 1 \) \* 100000 - 1`
    for v in `seq $start $end`;do
        echo "insert into test values('$v',$v,$v,$v,$v,$v,$v,$v,$v,$v);"
    done >>insert.sql
    echo "commit;" >>insert.sql
    /usr/bin/time -f %e docker compose exec -T db psql -q -v ON_ERROR_STOP=1 'postgresql://postgres:example@localhost/test' <insert.sql
done 
docker compose exec -T db psql 'postgresql://postgres:example@localhost/test' <<EOF
truncate table test;
EOF
for i in `seq 0 9`; do
    echo "begin;" >insert.sql
    echo "insert into test values" >>insert.sql
    start=`expr $i \* 100000` || true
    end=`expr \( $i + 1 \) \* 100000 - 2`
    for v in `seq $start $end`;do
        echo "('$v',$v,$v,$v,$v,$v,$v,$v,$v,$v),"
    done >>insert.sql
    v=`expr $end + 1`
    echo "('$v',$v,$v,$v,$v,$v,$v,$v,$v,$v);" >>insert.sql
    echo "commit;" >>insert.sql
    /usr/bin/time -f %e docker compose exec -T db psql -q -v ON_ERROR_STOP=1 'postgresql://postgres:example@localhost/test' <insert.sql
done
docker compose exec -T db psql -v ON_ERROR_STOP=1 'postgresql://postgres:example@localhost/test' <<EOF
\\copy test to '/home/user/test.csv' with csv header;
truncate table test;
EOF
/usr/bin/time -f %e docker compose exec -T db psql -q -v ON_ERROR_STOP=1 'postgresql://postgres:example@localhost/test' <<EOF
\\copy test from '/home/user/test.csv' with csv header;
EOF
docker compose down

※今回グラフはデータをコピペしてofficeで描いています

結果

数値
start simple bulk
0 11.49 3.22
100000 11.48 3.42
200000 11.47 3.24
300000 11.67 3.26
400000 11.65 3.38
500000 11.46 3.27
600000 11.6 3.19
700000 11.5 3.21
800000 11.58 3.25
900000 11.45 3.23

image.png

ついでに測った\COPYコマンド(ローダー)の速度は100万件で8.28秒でした(計測方法はコード参照)。

100万行程度では既存の行数に対して時間の変化は見られません(行数が足りないかもしれないということ)。今回の計測ではバルクインサートの方が3倍以上速いようです。ただし、ローダーの方がさらにその3倍速い結果になったということです。

考察

この結果から、今回のデータであれば、ローダーの使用を検討した上で、使用しない判断の場合はバルクインサートで実施すべきということが言えそうです。

まとめ

(今回のケースでは)

  • 通常インサートよりはバルクインサートの方が速い
  • バルクインサートよりローダーの方が速い