[解決済み】高速テストのためのPostgreSQLの最適化

質問

典型的なRailsアプリケーションで、SQLiteからPostgreSQLに切り替えているところです。

問題は、PGでスペックの実行が遅くなったことです。

SQLite では ~34 秒かかりましたが、PG では ~76 秒となり、これは 2倍以上遅い .

そこで今度は、あるテクニックを応用して 仕様のパフォーマンスをSQLiteと同等にする。 理想的には接続オプションの設定だけですが、おそらく不可能でしょう）。

私の頭の中にある明らかなことは、2つあります。

• RAMディスク（OSXのRSpecでうまく設定できると良いのですが）
• ログアウトしたテーブル (データベース全体に適用できるので、すべてのスクリプトを変更する必要がない？)

お分かりかと思いますが、私は信頼性やその他は気にしません（DBはここではただの捨て駒です）。

PGの性能を最大限に引き出して <強い 可能な限り高速に .

ベストアンサー を記述するのが理想的でしょう。 トリック そのためのセットアップと欠点。

UPDATE fsync = off + full_page_writes = off のみで、時間は〜65秒（〜16秒）に短縮されました。良いスタートですが、目標の34秒には程遠いです。

UPDATE 2: I RAMディスクを使用してみた が、性能向上は誤差の範囲でした。だから、その価値はなさそうだ。

UPDATE 3:* 最大のボトルネックを見つけたので、私のスペックでもSQLiteのものと同等の速度で動くようになりました。

を行うデータベースクリーンアップが問題でした。 切り捨て . どうやらSQLiteはそこそこ速すぎるようです。

これを修正するために、私は トランザクション 各テストの前にロールバックし、最後にロールバックする。

700回のテストでの数字です。

• 切り捨てです。SQLite - 34s, PG - 76s.
• トランザクション SQLite - 17s、PG - 18s。

SQLiteで2倍の速度向上。 PG で 4 倍の速度向上。

解決方法は？

まず、常に最新バージョンのPostgreSQLを使用することです。性能向上は常に行われているので、古いバージョンをチューニングしているのは時間の無駄でしょう。例えば PostgreSQL 9.2では TRUNCATE そしてもちろん、インデックスのみのスキャンも追加されています。マイナーリリースであっても、常にフォローする必要があります。 バージョンポリシー .

注意事項

行う NOT RAMディスクなどの耐久性のないストレージにテーブルスペースを置く。 .

表領域を失うと、データベース全体が損傷し、かなりの作業を行わないと使用できなくなる可能性があります。を使うだけの場合と比較して、ほとんど利点がありません。 UNLOGGED テーブルがあり、キャッシュ用に多くのRAMがあります。

本当にラムディスクベースのシステムを望むのであれば。 initdb によって、ラムディスク上に全く新しいクラスタを構築することができます。 initdb 新しい PostgreSQL インスタンスを ramdisk 上に作成し、完全に使い捨ての PostgreSQL インスタンスを手に入れることができます。

PostgreSQLサーバーの設定

テスト時には、サーバーの設定を 耐久性はないが高速に動作する .

の唯一の許容される使い方のひとつです。 fsync=off の設定は、PostgreSQLでは この設定は、PostgreSQLに対して、順序付き書き込みやその他の厄介なデータ整合性保護やクラッシュ安全に関することを気にしないように指示し、停電やOSクラッシュが発生した場合にデータを完全に廃棄する許可を与えるものです。

言うまでもないことですが fsync=off を他の場所から再生成できるデータの一時的なデータベースとして使用している場合を除き、実稼働環境で使用することができます。fsync をオフにするために行っている場合のみ、Pg を他の場所から再作成できるデータのための一時的なデータベースとして使用することもできます。 full_page_writes をオフにすると、もはや何の効果もありません。注意することは fsync=off と full_page_writes で適用されます。 クラスタ レベルに影響するため すべて を使用することで、PostgreSQLインスタンスのデータベースを変更することができます。

製品版で使用する場合は synchronous_commit=off を設定し commit_delay と同じような効果を得ることができます。 fsync=off 巨大なデータ破損のリスクを伴わずに。非同期コミットを有効にした場合、最近のデータが失われる可能性が少しありますが、それだけです。

DDLを少し変更するオプションがある場合は、DDLを変更する前に UNLOGGED テーブルを使用することで、WALログを完全に回避し、サーバーがクラッシュした場合にテーブルが消去される代償として、実質的なスピードアップを得ることができます。全てのテーブルをログに残さないようにするための設定オプションはありません。 CREATE TABLE . テストに適しているだけでなく、生成されたデータや重要でないデータでいっぱいのテーブルがある場合、それ以外は安全である必要があるものを含むデータベースで便利です。

ログをチェックして、チェックポイントが多すぎるという警告が出ていないかどうか確認してください。もしそうなら チェックポイントセグメント . また、書き込みをスムーズにするために checkpoint_completion_target を調整するのもよいでしょう。

チューニング shared_buffers を使用して、ワークロードに適合させます。これはOSに依存し、あなたのマシンで他に何が起こっているかに依存し、いくつかの試行錯誤が必要です。デフォルトは非常に保守的です。を増やした場合、OS の最大共有メモリ制限を増やす必要があるかもしれません。 shared_buffers 9.3以降では、これを避けるために共有メモリの使用方法を変更しました。

多くの作業を行うコネクションをいくつか使用している場合は、接続を増やすために work_mem を使用して、ソートなどに使用するRAMを増やしてください。あまりに高い work_mem の設定は、接続ごとではなくソートごとなので、ひとつのクエリが多くのネストされたソートを持つことができるため、メモリ不足の問題を引き起こす可能性があります。あなたは 本当に を増やす必要があります。 work_mem でディスクにこぼれるソートが見える場合は、そのソートを削除してください。 EXPLAIN でログを取ったり log_temp_files 設定 (推奨)ですが、より高い値を設定すると、Pg がよりスマートなプランを選択できるようになる場合があります。

他の投稿者が言っているように、可能であればxlogとメインテーブル/インデックスを別々のHDDに置くことが賢明です。パーティションを分けるのはかなり無意味で、本当に別々のドライブが必要です。この分離は、もしあなたが fsync=off を使用している場合は、ほぼゼロです。 UNLOGGED テーブルを使用します。

最後に、クエリのチューニングを行います。このとき random_page_cost と seq_page_cost はシステムの性能を反映するものであることを確認し effective_cache_size が正しいかどうかなど。使用する EXPLAIN (BUFFERS, ANALYZE) を使用して個々のクエリプランを調査し、その結果に基づいて auto_explain

UNLOGGED