PostgreSQL で挿入パフォーマンスを高速化する方法質問する

Question

見るデータベースにデータを入力するPostgreSQLのマニュアルでは、depesz のいつも通り素晴らしい記事このトピックについて、そしてこのSOの質問。

(この回答は、既存の DB にデータを一括ロードするか、新しい DB を作成する方法に関するものであることに注意してください。DB の復元パフォーマンスやpg_restore出力psqlの実行に興味がある場合、スキーマ + データの復元が完了した後にトリガーやインデックスの作成などの操作が既に行われているため、pg_dumpこれの多くは当てはまりませんpg_dumppg_restore) 。

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

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

さもないと：

テーブル上のトリガーを無効にする
インポートを開始する前にインデックスを削除し、後で再作成します。(同じデータを段階的に追加するよりも、1 回のパスでインデックスを構築する方が時間がかかり、結果として得られるインデックスははるかにコンパクトになります)。
単一のトランザクション内でインポートを実行する場合は、外部キー制約を削除し、インポートを実行して、コミットする前に制約を再作成しても安全です。インポートが複数のトランザクションに分割されている場合は、無効なデータが導入される可能性があるため、これを行わないでください。
可能であれば、sCOPYの代わりに使用してくださいINSERT
使用できない場合は、可能であれば、COPY複数の値を持つの使用を検討してください。すでにこれを実行しているようです。ただし、 1 つのステートメントにあまり多くの値をリストしないでください。それらの値はメモリに数回にわたって収まる必要があるため、ステートメントごとに数百に抑えてください。INSERTVALUES
挿入を明示的なトランザクションにバッチ処理し、トランザクションごとに数十万または数百万の挿入を実行します。私の知る限り、実質的な制限はありませんが、バッチ処理により、入力データ内の各バッチの開始をマークすることでエラーから回復できます。繰り返しますが、これはすでに実行されているようです。
synchronous_commit=offfsync() のコストを削減するには、 huge を使用しますcommit_delay。ただし、作業を大きなトランザクションにバッチ処理している場合は、あまり役に立ちません。
INSERTまたは、COPY複数の接続から並列に接続できます。接続数はハードウェアのディスクサブシステムによって異なります。目安としては、直接接続ストレージを使用する場合は、物理ハードドライブごとに 1 つの接続が必要です。
高いmax_wal_size値 (checkpoint_segments古いバージョンでは ) を設定して有効にしますlog_checkpoints。PostgreSQL ログを調べて、チェックポイントが頻繁に発生していることについてエラーが出ていないことを確認します。
インポート中にシステムがクラッシュした場合に、PostgreSQL クラスタ全体 (データベースと、同じクラスタ上の他のすべてのデータベース) が壊滅的な破損で失われても構わない場合に限り、Pg を停止してを設定し、fsync=offPg を起動してインポートを実行し、その後 (必ず) Pg を停止して再度を設定することができますfsync=on。WAL 構成PostgreSQL インストールのデータベースに既に必要なデータがある場合は、これを実行しないでください。を設定すると、fsync=offも設定できますfull_page_writes=off。ただし、インポート後にデータベースの破損やデータ損失を防ぐために、必ずをオンに戻すようにしてください。非永続的な設定Pg マニュアルに記載されています。

システムのチューニングも検討する必要があります。

ストレージには、できるだけ高品質のSSDを使用してください。信頼性が高く、電源保護されたライトバックキャッシュを備えた高品質の SSD を使用すると、コミットレートが驚くほど高速になります。上記のアドバイスに従うと、ディスクフラッシュやfsync()s の数を減らすことができるため、メリットは少なくなりますが、それでも大きな助けになります。データを保持する必要がない場合は、適切な電源障害保護のない安価な SSD を使用しないでください。
直接接続ストレージに RAID 5 または RAID 6 を使用している場合は、今すぐ中止してください。データをバックアップし、RAID アレイを RAID 10 に再構築して、もう一度試してください。RAID 5/6 は一括書き込みのパフォーマンスには望みがありませんが、大容量キャッシュを備えた優れた RAID コントローラは役立ちます。
大容量のバッテリバックアップ式ライトバックキャッシュを備えたハードウェア RAID コントローラを使用するオプションがある場合、コミットの多いワークロードの書き込みパフォーマンスが大幅に向上します。commit_delay で非同期コミットを使用している場合や、一括読み込み中に大きなトランザクションをあまり実行していない場合は、それほど効果がありません。
可能であれば、WAL ( pg_wal、またはpg_xlog古いバージョン) を別のディスク/ディスクアレイに保存します。同じディスクで別のファイルシステムを使用する意味はあまりありません。多くの場合、WAL には RAID1 ペアが使用されます。繰り返しますが、これはコミットレートが高いシステムではより効果があり、ログに記録されないテーブルをデータロードターゲットとして使用している場合はほとんど効果がありません。

あなたも興味があるかもしれませんPostgreSQLを最適化して高速テストを実現。

Answer 1