[解決済み】PostgreSQLで挿入のパフォーマンスを高速化する方法

質問

Postgresの挿入パフォーマンスをテストしています。データ型が数値のカラムを1つ持つテーブルがあります。このテーブルには、インデックスもあります。このクエリを使ってデータベースを満杯にしました。

insert into aNumber (id) values (564),(43536),(34560) ...

上記のクエリで400万行を一度に1万行ずつ非常に高速に挿入しました。データベースが600万行に達した後、パフォーマンスは15分毎に100万行に激減しました。挿入パフォーマンスを向上させるコツはありますか？このプロジェクトでは、最適な挿入パフォーマンスが必要なのです。

5GBのRAMを搭載したマシンでWindows 7 Proを使用しています。

解決方法は？

参照 データベースを作成する は、PostgreSQLのマニュアルに記載されています。 depeszさんの秀逸な例文 というトピックと このSOの質問 .

(この回答は、既存のDBにデータを一括ロードする、または新規にDBを作成する場合についてであることに注意してください。でのDBリストアパフォーマンスに興味があるのであれば pg_restore または psql の実行 pg_dump を出力する場合、この多くは適用されません。 pg_dump と pg_restore は、スキーマ＋データのリストア終了後にトリガーやインデックスの作成などをすでに行っています) .

やることはたくさんあるんです。理想的な解決策は、インポートを UNLOGGED テーブルをインデックスなしで使用し、それをログに記録するように変更してインデックスを追加します。残念ながら、PostgreSQL 9.4では、テーブルをログ付きに変更するためのサポートはありません。 UNLOGGED をログに残すことができます。9.5では ALTER TABLE ... SET LOGGED を使えば、このようなことが可能になります。

一括インポートのためにデータベースをオフラインにできる場合は pg_bulkload .

それ以外の場合

• テーブル上のすべてのトリガーを無効にする

• インポート開始前にインデックスを削除し、インポート後にインデックスを再作成してください。(その際 だいぶ 一度にインデックスを作成する方が、同じデータを徐々に追加していくよりも時間がかからず、出来上がったインデックスもずっとコンパクトになります)。

• 単一のトランザクション内でインポートを行う場合、コミットする前に外部キー制約を削除し、インポートを行い、制約を再作成することは安全です。インポートが複数のトランザクションにまたがっている場合は、無効なデータを取り込む可能性があるため、この操作を行わないでください。

• 可能であれば COPY の代わりに INSERT s

• を使用できない場合 COPY を使用することを検討してください。 INSERT を使用することができます。もうやっているようですね。を並べようとしないでください。 も 多くの値を1つの VALUES しかし、これらの値はメモリに数回分収まらなければならないので、1ステートメントあたり数百個に抑えてください。

• 明示的なトランザクションに挿入をバッチ化し、1トランザクションあたり数十万から数百万回の挿入を行います。AFAIKでは、実用的な制限はありませんが、バッチ処理を行うことで、入力データに各バッチの開始をマークすることにより、エラーから回復することができます。繰り返しになりますが、あなたはすでにこれを実行しているようです。

• 使用方法 synchronous_commit=off と、巨大な commit_delay を使用して、fsync() のコストを削減します。しかし、これは大きなトランザクションに作業を分割している場合にはあまり役に立ちません。

• INSERT または COPY を複数の接続から並列に接続します。接続数はハードウェアのディスクサブシステムに依存します。経験則では、直接接続型ストレージを使用する場合、物理的なハードドライブごとに1つの接続が必要です。

• を高く設定します。 max_wal_size 値( checkpoint_segments を有効にしてください。 log_checkpoints . PostgreSQLのログを見て、チェックポイントがあまりにも頻繁に発生することに不満を抱いていないことを確認してください。

• インポート中にシステムがクラッシュした場合、PostgreSQLクラスタ全体（あなたのデータベースと同じクラスタ上の他のデータベース）を壊滅的な破壊で失っても構わない場合のみ、Pgを停止させることができます。 fsync=off を設定し、Pg を起動し、インポートを行い、その後（極めて重要なことですが）Pg を停止して fsync=on をもう一度。参照 WAL設定 . PostgreSQLのインストール先のデータベースに気になるデータが既にある場合は、この操作を行わないでください。 を設定した場合 fsync=off を設定することができます。 full_page_writes=off ただし、データベースの破損やデータの損失を防ぐため、インポート後はこの機能をオンに戻してください。参照 非耐久性設定 のマニュアルを参照してください。

また、システムのチューニングも視野に入れておく必要があります。

• 使用する 良品 ストレージはなるべくSSDを。信頼性が高く、電源保護されたライトバックキャッシュを備えた優れたSSDは、コミットレートを信じられないほど高速化します。上記のアドバイスに従えば、ディスクのフラッシュ回数や、ディスクの交換回数が減るので、あまりメリットはありません。 fsync() しかし、それでも大きな助けになることは間違いありません。データの保存を気にしないのでなければ、適切な電源障害対策のない安価なSSDを使用しないでください。

• ダイレクトアタッチドストレージにRAID 5またはRAID 6を使用している場合、今すぐ停止してください。データをバックアップし、RAIDアレイをRAID 10に再構築して、もう一度試してみてください。RAID 5/6 は大量書き込みのパフォーマンスには絶望的です - 大きなキャッシュを持つ優れた RAID コントローラが役立ちますが。

• もし、バッテリバックアップされた大きなライトバックキャッシュを持つハードウェアRAIDコントローラを使用するオプションがあれば、多くのコミットを行うワークロードの書き込みパフォーマンスを本当に向上させることができます。コミット遅延のある非同期コミットや、バルクロード中の大きなトランザクションが少ない場合は、あまり役に立ちません。

• 可能であれば、WALを保存する( pg_wal または pg_xlog を別のディスク/ディスクアレイで使用することができます。同じディスクで別のファイルシステムを使用する意味はほとんどない。人々はしばしばWALにRAID1ペアを使用することを選択します。繰り返しますが、これはコミット率の高いシステムでより効果を発揮します。また、データロードのターゲットとしてログのないテーブルを使用する場合は、ほとんど効果がありません。

こちらもご覧ください 高速テストのためのPostgreSQLの最適化 .