私たちはPostgresql 9.1.4DBサーバーとして使っています。テストスイートを高速化しようとしていたので、何が起こっているのかを正確に知るためにDBのプロファイリングを少し始めました。私たちは使っていますデータベースクリーナーテストの最後にテーブルを切り捨てます。はい、トランザクションの方が高速であることはわかっていますが、特定の状況では使用できないため、それについては気にしていません。
私が懸念しているのは、TRUNCATION になぜそれほど時間がかかるのか (DELETE を使用するよりも長い)、そして CI サーバーではなぜさらに長い時間がかかるのかということです。
現在、ローカル（Macbook Air 上）では、完全なテスト スイートを実行するのに 28 分かかります。ログを tail すると、テーブルが切り捨てられるたびに、次のようになります。
TRUNCATE TABLE table1, table2 -- ... etc
切り捨てを実行するには 1 秒以上かかります。CI サーバー (Ubuntu 10.04 LTS) のログを tail すると、テーブルの切り捨てに 8 秒かかり、ビルドには 84 分かかります。
この戦略に切り替えたところ:deletion、ローカルビルドには20分かかりましたが、CIサーバーでは44分に短縮されました。これは重要な違いが分かり、なぜそうなるのか本当に驚きました。調整された のCI サーバー上の DB には、16 GB のシステム RAM、4 GB の共有バッファ、そして SSD が搭載されています。すべて優れたものです。なぜそれが可能なのでしょうか。
a.2GBのRAMを搭載したMacBook Airよりもずっと遅い
b.TRUNCATIONはDELETEよりも非常に遅いため、postgresql ドキュメント 明示的に述べるもっと速くなるはずだ。
何かご意見は？
ベストアンサー1
これは最近、SO と PostgreSQL メーリング リストの両方で何度か話題になっています。
の要約最後の2点について:
(a) shared_buffers が大きいことが、CI サーバーで TRUNCATE が遅くなる原因である可能性があります。fsync 構成が異なるか、SSD の代わりに回転メディアを使用していることも原因である可能性があります。
(b)TRUNCATEは固定コストですが、 よりも必ずしも遅いわけではなくDELETE、より多くの作業を行います。以下の詳細な説明を参照してください。
アップデート：あpgsql-performance に関する重要な議論この投稿から生まれました。このスレッド。
更新2:9.2beta3ではこの問題を解決する改善が加えられました。この郵便受け。
TRUNCATEvsの詳細な説明DELETE FROM:
このトピックの専門家ではありませんが、私の理解では、 はTRUNCATEテーブルごとにほぼ固定のコストがかかりますが、DELETEn 行の場合は少なくとも O(n) です。削除するテーブルを参照する外部キーがある場合はさらに悪くなります。
私は常に、 の固定コストはほぼ空のテーブルでのTRUNCATEのコストよりも低いと想定していましたDELETEが、これはまったく真実ではありません。
TRUNCATE table;以上ですDELETE FROM table;
後のデータベースの状態は、TRUNCATE table次のように実行した場合とほぼ同じです。
DELETE FROM table;VACCUUM (FULL, ANALYZE) table;(9.0 以降のみ、脚注を参照)
...もちろん、 とではTRUNCATE実際にその効果は得られません。DELETEVACUUM
重要なのは、DELETEと がTRUNCATE異なることを実行するため、単に同じ結果を持つ 2 つのコマンドを比較しているわけではないということです。
A ではDELETE FROM table;、不要な行と膨張が残り、インデックスに不要なエントリが保持され、クエリ プランナーによって使用されるテーブル統計が更新されないなどの問題が発生します。
は、 を実行しTRUNCATEたばかりの場合と同じように、完全に新しいテーブルとインデックスを作成しますCREATE。すべてのレコードを削除し、テーブルのインデックスを再作成して を実行したのと同じですVACUUM FULL。
テーブルに汚れが残っていても、後で再度埋めるつもりなので気にしない場合は、 を使用する方がよいでしょうDELETE FROM table;。
実行していないため、VACUUM不要な行とインデックス エントリが蓄積して膨張し、スキャンしてから無視する必要があります。これにより、すべてのクエリが遅くなります。テストで実際に大量のデータを作成および削除しない場合は、気付かなかったり気にしなかったりするかもしれませんが、その場合VACUUMは、テスト実行の途中で 1 回または 2 回実行できます。自動バキュームを積極的に設定して、自動バキュームがバックグラウンドで実行するようにすると、さらに効果的です。
TRUNCATEテーブルは、全体テスト スイートを実行して、複数回の実行で影響が蓄積されないことを確認します。9.0 以降では、VACUUM (FULL, ANALYZE);グローバルにテーブルを実行すると、少なくとも同等かそれ以上の効果が得られ、はるかに簡単になります。
私の記憶が正しければ、PG にはいくつかの最適化が施されており、テーブルを参照できるのが自分のトランザクションだけである場合にそれを検知して、とにかくブロックをすぐに空きとしてマークする可能性があります。テストでは、ブロートを作成したい場合、それを実行するには複数の同時接続が必要でした。ただし、これに頼るつもりはありません。
DELETE FROM table;f/k参照のない小さなテーブルでは非常に安価です
DELETE外部キー参照のないテーブルのすべてのレコードに対して、Pg は順次テーブル スキャンを実行し、xmax検出されたタプルの を設定する必要があります。これは非常に安価な操作で、基本的には線形読み取りと半線形書き込みです。私の知る限り、インデックスに触れる必要はありません。インデックスは、VACUUMデッド タプルのみを含むテーブル内のブロックを空きとしてマークする によってクリーンアップされるまで、デッド タプルを指し続けます。
DELETE高価になるのはたくさんレコードの数が多い場合、チェックする必要がある外部キー参照が多数ある場合、またはの効果VACUUM (FULL, ANALYZE) table;を一致させるために必要な後続をTRUNCATEのコスト内にカウントする場合DELETE。
ここでの私のテストでは、 は0.5 ミリ秒に対して 2 ミリ秒DELETE FROM table;で、通常 4 倍高速でしたTRUNCATE。これは SSD 上のテスト DB で、fsync=offこのデータをすべて失ってもかまわないので で実行しています。もちろん、DELETE FROM table;は同じ作業をすべて行っているわけではなく、 をフォローアップするとVACUUM (FULL, ANALYZE) table;、はるかにコストのかかる 21 ミリ秒になります。そのためDELETE、 が勝るのは、実際にテーブルをきれいな状態に保つ必要がない場合のみです。
TRUNCATE table;固定費の作業や家事の負担がはるかに大きいDELETE
対照的に、 はTRUNCATE多くの作業を行う必要があります。 テーブル、TOAST テーブル (存在する場合)、およびテーブルにあるすべてのインデックスに新しいファイルを割り当てる必要があります。 ヘッダーをそれらのファイルに書き込む必要があり、システム カタログも更新する必要がある可能性があります (この点については不明です。確認していません)。 次に、古いファイルを新しいファイルに置き換えるか、古いファイルを削除し、同期操作 (fsync() など) を使用してファイル システムが変更に追いついたことを確認する必要があります。同期操作では、通常、すべてのバッファーがディスクにフラッシュされます。 (データ消費) オプションで実行している場合、同期がスキップされるかどうかはわかりませんfsync=off。
最近、古いテーブルに関連する PostgreSQL のバッファもすべてフラッシュする必要があることがわかりましたTRUNCATE。これは、大きなサイズの場合、かなりの時間がかかることがありますshared_buffers。これが、CI サーバーで速度が遅くなる理由だと思います。
バランス
TRUNCATEいずれにしても、関連付けられた TOAST テーブル (ほとんどの場合に存在します) と複数のインデックスを持つテーブルの には、数分かかることがわかります。長くはありませんが、DELETEほぼ空のテーブルからの よりも長くなります。
したがって、 を実行する方がよいかもしれませんDELETE FROM table;。
--
注: 9.0 より前の DB では、CLUSTER table_id_seq ON table; ANALYZE table;またはVACUUM FULL ANALYZE table; REINDEX table;は に近いものになりますTRUNCATE。VACUUM FULL実装は 9.0 ではるかに優れたものに変更されました。