今日は、Postgres データベースにデータをプッシュする Python スクリプトのパフォーマンスを改善することに一日を費やしました。以前は、次のようにレコードを挿入していました。
query = "INSERT INTO my_table (a,b,c ... ) VALUES (%s, %s, %s ...)";
for d in data:
cursor.execute(query, d)
次に、Postgres のコマンドで使用されるメモリ内ファイルを作成するようにスクリプトを書き直しました。COPYこれにより、ファイルからテーブルにデータをコピーできるようになります。
f = StringIO(my_tsv_string)
cursor.copy_expert("COPY my_table FROM STDIN WITH CSV DELIMITER AS E'\t' ENCODING 'utf-8' QUOTE E'\b' NULL ''", f)
そのCOPY方法は驚くほど速い。
METHOD | TIME (secs) | # RECORDS
=======================================
COPY_FROM | 92.998 | 48339
INSERT | 1011.931 | 48377
しかし、その理由に関する情報が見つかりません。マルチラインと比べてどのように動作が異なりINSERT、大幅に高速化されるのでしょうか?
見るこのベンチマーク同じように：
# original
0.008857011795043945: query_builder_insert
0.0029380321502685547: copy_from_insert
# 10 records
0.00867605209350586: query_builder_insert
0.003248929977416992: copy_from_insert
# 10k records
0.041108131408691406: query_builder_insert
0.010066032409667969: copy_from_insert
# 1M records
3.464181900024414: query_builder_insert
0.47070908546447754: copy_from_insert
# 10M records
38.96936798095703: query_builder_insert
5.955034017562866: copy_from_insert
ベストアンサー1
ここではいくつかの要因が働いています:
- ネットワーク遅延と往復遅延
- PostgreSQL のステートメントごとのオーバーヘッド
- コンテキストスイッチとスケジューラの遅延
COMMIT挿入ごとに1つのコミットを実行する場合のコスト（あなたはそうではありません）COPY一括読み込みに特化した最適化
ネットワーク遅延
サーバーがリモートの場合、ステートメントごとに固定時間「料金」、たとえば50ms（1/20秒）を支払うことになるかもしれません。クラウドホストのDBの場合はもっと高額になることもあります。最後の挿入が正常に完了するまで次の挿入を開始できないため、最大挿入速度は、1 秒あたり 1000/往復遅延 (ミリ秒) 行です。遅延が 50 ミリ秒 (「ping 時間」) の場合、1 秒あたり 20 行になります。ローカル サーバーでも、この遅延はゼロではありません。一方、COPYTCP 送受信ウィンドウを埋め、DB が書き込み、ネットワークが転送できる速度で行をストリームします。遅延の影響はそれほど受けず、同じネットワーク リンクで 1 秒あたり数千行を挿入している可能性があります。
PostgreSQL のステートメントごとのコスト
PostgreSQL では、ステートメントの解析、計画、実行にもコストがかかります。ロックをかけたり、リレーション ファイルを開いたり、インデックスを検索したりする必要があります。COPYこれらすべてを最初に 1 回実行し、その後は行をできるだけ早くロードすることに集中します。
タスク/コンテキスト切り替えコスト
オペレーティング システムは、アプリケーションが行を準備して送信する間に postgres が行を待機し、その後、postgres が行を処理する間にアプリケーションが postgres の応答を待機する間を切り替える必要があるため、さらに時間コストがかかります。一方から他方に切り替えるたびに、少し時間が無駄になります。プロセスが待機状態に入ったり出たりすると、さまざまな低レベルのカーネル状態が一時停止および再開されるため、さらに時間が無駄になる可能性があります。
COPY最適化を見逃す
それに加えて、 にはCOPY、いくつかの種類のロードに使用できる最適化機能があります。たとえば、生成されたキーがなく、デフォルト値が定数である場合、それらを事前に計算してエグゼキュータを完全にバイパスし、PostgreSQL の通常の作業の一部を完全にスキップする低レベルでデータをテーブルに高速にロードできます。 またはCREATE TABLE同じTRUNCATEトランザクションで を使用する場合COPY、マルチクライアント データベースで必要な通常のトランザクション記録をバイパスすることで、ロードを高速化するためのさらに多くのトリックを実行できます。
それにもかかわらず、PostgreSQL は、COPYまだやり方がわかっていないものの、処理速度を上げるためにさらに多くのことを行うことができます。テーブルの一定の割合以上を変更する場合は、インデックスの更新を自動的にスキップして、インデックスを再構築することができます。インデックスの更新をバッチで実行することもできます。他にもたくさんあります。
コミットコスト
最後に考慮すべきことは、コミットコストです。psycopg2デフォルトではトランザクションが開かれ、指示があるまでコミットされないため、おそらく問題にはなりません。自動コミットを使用するように指示しない限りは。しかし、多くの DB ドライバでは自動コミットがデフォルトです。そのような場合、1 回ごとにコミットを実行することになりますINSERT。つまり、1 回のディスク フラッシュで、サーバーはメモリ内のすべてのデータをディスクに書き出し、ディスクに独自のキャッシュを永続ストレージに書き出すように指示します。これには、長さ時間はかかり、ハードウェアによって大きく異なります。私の SSD ベースの NVMe BTRFS ラップトップは、1 秒あたり 200 回の fsync しか実行できませんが、1 秒あたり 300,000 回の非同期書き込みを実行できます。したがって、1 秒あたり 200 行しかロードされません。サーバーによっては、1 秒あたり 50 回の fsync しか実行できないものもあれば、20,000 回の fsync を実行できるものもあります。したがって、定期的にコミットする必要がある場合は、バッチでロードしてコミットし、複数行の挿入などを実行するようにしてください。COPY最後に 1 つのコミットしか実行されないため、コミット コストはごくわずかです。ただし、これはCOPYデータの途中でエラーが発生した場合に回復できないことも意味します。一括ロード全体が元に戻ってしまいます。