徹底比較!! Oracle & PostgreSQL 第8回

テスト其の伍〜 VACUUMの影響

ではPostgreSQLでのVACUUM実行の有無が、パフォーマンス上どのような影響となって現われるかを検証してみます。テスト方法は、以下のようなパターンで行いました。

1. 表を作成し、100万件ローディング: インデックスは社員番号のユニークインデックスをロード後に作成します。update操作のためで、全件検索には影響しません
2. 全件検索（1回目）: 「テスト其の弐〜データの検索」で使用したのと同じ「給与」列の集計を求めるSQL文を使用（下記参照）。キャッシュの影響を排除するために、全件検索実行前にDBを再起動します
3. データの半分50万件をupdate: 「テスト其の参〜データの更新」で使用したのと同じ、社員番号の奇数の行を対象に「その他」列を書換えるSQL文を使用（下記参照）
4. 全件検索（2回目）
5. さらに50万件をupdate: 3と同じスクリプトを実行。対象データも同じ
6. 全件検索（3回目）
7. VACUUM実行: 社員表を対象にしたコンカレントVACUUMと全DBを対象にしたVACUUM FULLをそれぞれ実行します
8. 全件検索（4回目）

2で実行したSQL文

select sum(給与) from 社員表;

3で実行したSQL文

update 社員表 set その他="ダミーの文字列70byte"

	where 社員番号=[任意の番号];

   データ中の半分をupdateする事によって、データファイル中で更新された行が（削除マークを付けられたうえで）新たな行へと置き換わるために、表全体の占める領域が徐々に拡大します。

   表を構成するファイルサイズの拡大と、この操作の合間に実行する全件検索の検索の実行時間がどう推移するかを確認してみます。そして全件検索の3回目が終了したところで、VACUUMもしくはVACUUM FULLを実行して、その効果の有無や大きさを4回目の計測で確認しようというのが今回のテストの趣旨です。

   OracleにはVACUUMと同様の操作は存在しませんが、それ以外の部分で同じデータの操作を行った場合のパフォーマンスへの影響を合わせて確認してみます。

図1：PostgreSQLのデータサイズ増分と全件検索時のレスポンス（VACUUMとVACUUM FULLの違い

updateを2回目に実行した後のデータファイルのサイズは、元のサイズのちょうど倍程度になりました。それにつれて、全件検索実行時のレスポンスタイムもデータファイルのサイズのようにデータ量に比例してという訳ではありませんでしたが、徐々にですが悪化していく様子がお分かりいただけると思います。

ここでグラフ中「3回目」と「4回目」の間で、実行したVACUUMについてのおさらいです

VACUUM: コンカレントVACUUMと呼ばれ、削除・更新され不要とマークされた行を再利用可能にするのみ。排他ロックは発生しないが、ファイル縮小は行われない
VACUUM FULL: 本来のVACUUM。排他ロックが発生する

   詳細については過去の記事を参照していただきたいと思います。

   両者の大きな違いとしては、排他ロックの有無と不要な行の扱いがあります。図1の4回目の部分を見てください。コンカレントVACUUMの方はデータファイルのサイズには変化がありませんでしたが、VACUUM FULLではupdate実行前のデータローディング終了直後よりも若干ですがサイズが小さくなりました。

   そして肝心のレスポンスタイムは両者ともデータローディング終了直後(1回目)と同じレベルまで改善されました。このテストではその差は小さいものでしたが、VACUUM FULL実行後のほうが若干高速です。微妙な差ではありますが、データファイルの大きさの差が影響により、発生したものであると考えられます。

図2：VACUUMの実行時間

   次に実行にかかった時間ですが、図2のような結果でした。VACUUM FULLは対象が全DBとなる事、ファイルの縮小も行うなどその作業量の大きさから実行時間もコンカレントVACUUMに比べて大きくなります。

   テスト環境ではVACUUM FULLはコンカレントVACUUMの倍程度の時間が必要でしたが、この実行時間の絶対値は重要ではありません。VACUUMの実行時間はその処理から想像されるように、行データ・データファイルのサイズや不要データの割合などで大きく変わります。

   重要なのはシステムのデータ状態（更新・削除などの処理量）を把握したうえで実行間隔を見積もり、定期的に実行する運用設計を事前に行う事です。特に更新データが多い場合には、排他ロックが許されるタイミングを見つけ（作り）だしVACUUM FULLを実行するようにすべきでしょう。