16
16

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

PostgreSQL - 高速でINSERTしたい君へ

Last updated at Posted at 2019-12-29

大量インサートの性能をあげよう

PostgresSQLに大量インサートする場合には、
複数レコードまとめてインサート用のユーティリティを使用しましょう。
あるいは、commit実行頻度を見直して、不要な処理コストを減らしましょう

複数パターンで実測して性能を比較します。
PythonでPostgreSQLにアクセスするライブラリのひとつpsycopg2では
複数件まとめてインサート用のユーティリティとして
execute_valuesという関数を使用します。
また、commitの実行単位を変更してcommitの実行間隔を変えて実測します。

確認準備

テスト用テーブル

テスト用DDL
CREATE TABLE test01
(key serial PRIMARY KEY,
 text_val text);

テスト用プログラム

簡単のため、実行件数やcommit回数は対話的な入力が行えるようにします。
できるだけ同じ範囲の時間で比較できるようにパラメータを作る部分を含めます。
実行前に都度、テーブルリセットするため、TRUNCATEとSEQUENCEオブジェクトの値初期化を行います。

execute_valuesは、デフォルト100行まで。それ以上を入力するときは、引数のpage_sizeに入力数以上の値を渡す必要があります。

テスト用プログラム
import psycopg2
from psycopg2.extras import execute_values
from time import time

_text = 'test' * 300

conn = psycopg2.connect('dbname=dbname host=localhost user=username password=password')
cur = conn.cursor()

# テーブルリセット
reset_query_tbl = "TRUNCATE TABLE test01;"
cur.execute(reset_query_tbl)
reset_query_seq = "SELECT setval ('test01_key_seq', 1, false);"
cur.execute(reset_query_seq)
count_query = "SELECT count(key) FROM test01;"
cur.execute(count_query)
(count,) = cur.fetchone()
conn.commit()
print('■実行前状態')
print(' データ件数: ', count)
print()

# テスト設定
print('■INSERT方法')
print('INSERT方法を以下から選択してください(0/1/2/3)')
print(' 0: 1件ずつINSERT&commit.')
print(' 1: 1件ずつINSERT&複数まとめてcommit.')
print(' 2: 1件ずつINSERT&全件まとめてcommit.')
print(' 3: まとめてINSERT&commit.')
test_type = int(input(' INSERT方法 >'))
test_cnt  = int(input(' INSERT件数 >'))
if test_type == 1:
    commit_cnt  = int(input(' COMMIT間隔 >'))

# テスト実行&結果
print()
print('■実行結果')
if test_type==0:

    query = "INSERT INTO test01 (text_val) VALUES ('%s');" % _text
    st = time()
    for _ in range(test_cnt):
        cur.execute(query)
        conn.commit()

    elapsed_time = time() - st
    print(' 経過時間(s): ', elapsed_time)

if test_type==1:
    query = "INSERT INTO test01 (text_val) VALUES ('%s');" % _text
    st = time()
    for i in range(test_cnt):
        cur.execute(query)
        if (i + 1) % commit_cnt == 0:
            conn.commit()

    conn.commit()

    elapsed_time = time() - st
    print(' 経過時間(s): ', elapsed_time)

if test_type==2:

    query = "INSERT INTO test01 (text_val) VALUES ('%s');" % _text
    st = time()
    for _ in range(test_cnt):
        cur.execute(query)

    conn.commit()

    elapsed_time = time() - st
    print(' 経過時間(s): ', elapsed_time)

if test_type==3:

    query = "INSERT INTO test01 (text_val) VALUES %s;"
    st = time()

    params = []
    for _ in range(test_cnt):
        params += [(_text,)]

    ret = execute_values(cur, query, params, page_size=test_cnt)
    conn.commit()
    elapsed_time = time() - st
    print(' 経過時間(s): ', elapsed_time)

cur.execute(count_query)
(count,) = cur.fetchone()
print(' データ件数: ', count)

cur.close()
conn.close()

性能結果

結果は参考まで。当然、環境、マシンスペックに依存します。

  • Windows10 64bit/論理プロセッサ数4/メモリ8GB
  • PostgreSQL11
  • PostgreSQLのコンフィグはデフォルト値。
INSERT方法 INSERT件数 COMMIT回数 処理時間(S)
1件ずつINSERT(execute) & 1件ずつCOMMIT. 100,000 100,000 40.9
1件ずつINSERT(execute) & 10件ずつCOMMIT. 100,000 10,000 25.4
1件ずつINSERT(execute) & 100件ずつCOMMIT. 100,000 1,000 24.1
1件ずつINSERT(execute) & 1,000件ずつCOMMIT. 100,000 100 27.2
1件ずつINSERT(execute) & 10,000ずつCOMMIT. 100,000 10 25.9
1件ずつINSERT(execute) & 全件まとめてCOMMIT. 100,000 1 24.4
まとめてINSERT(execute_values) & 全件まとめてCOMMIT. 100,000 1 8.8

結論

execute_values、速い。
できれば使いましょう。

16
16
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
16
16

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?