大量インサートの性能をあげよう
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、速い。
できれば使いましょう。