PostgreSQL VACUUM で年末大掃除

これは TECHSCORE Advent Calendar 2018 の18日目の記事です。

今回はPostgreSQLを運用する上で絶対に無視できない「VACUUM」について、その機能と役割を確認していきたいと思います。

VACUUMとは

VACUUMは、テーブルの実体となるファイルの中から、不要領域を探索し、再利用可能な状態にしていくものです。VACUUMを全く実行しない場合、ファイルサイズが増え続け、パフォーマンスの低下、ディスクスペースの圧迫へとつながります。

AUTO VACUUM機能

PostgreSQLには「AUTO VACUUM」機能が搭載されており、自動で随時VACUUMが実行されるため、多くの場合問題となりません。しかし、AUTO VACUUMも万能ではありません。テーブルによって追加・更新・削除の頻度、規模は様々であるため、AUTO VACUUM機能によるVACUUM実行のタイミングが適切でないケースがでてきます。
(AUTO VACUUMによるVACUUM実行のタイミングは設定により調整が可能です。autovacuum_vacuum_threshold、autovacuum_vacuum_scale_factor等)

なぜVACUUMが必要か

PostgreSQLは追記型アーキテクチャを採用しているためです。PostgreSQLが行の追加・更新・削除をどのように行うか、おおざっぱにですが確認していきましょう。

PostgreSQLはデータを8KB単位で管理します。空のテーブルに1行INSERTする場合、8KBの空き領域を準備し、そこにデータを格納します。
さらにINSERTを続け、8KBの空き領域が不足した場合、追加で8KBの領域を確保します。このタイミングでファイルサイズが大きくなることになります。

次にDELETEですが、DELETEは対象の行に「不要」という印付けを行うのみで、実際に消えることはありません。印付けられた行はそれ以降のトランザクションから見えなくなり、結果として削除されたこととなります。

次にUPDATEですが、DELETE + INSERTのような動作となります。UPDATE対象の行に「不要」という印付けを行い、更新内容を新しい行として追加します。

そのため、INSERT/UPDATE/DELETEを続けるとファイルサイズが大きくなり続けます。そこで、VACUUMです。
VACUUMでは「不要」と印付けされた領域を「再利用可能」として印付けます。再利用可能な領域は、以降のINSERT/UPDATE時に利用されます。

実際に試してみる(VACUUM)

テーブルに大量データを入れ、VACUUMの挙動を実際に確認してみましょう。
(ここでの検証にはPostgreSQLバージョン11.1を使用しました。)

テスト用のテーブルを準備します。

テーブルの実ファイルを確認します。
実ファイルはデフォルトで$PGDATA以下のbaseディレクトリ以下にデータベースごとにディレクトリが切られ、テーブルのrelfilenodeがファイル名となって配置されます。

データベースのIDと、テーブルのrelfilenodeを確認しましょう。

以上から、テーブルのファイルは $PGDATA/base/16384/16418 にあることがわかります。確認しましょう。

ファイルサイズがゼロですね。

適当に1行INSERTしてみます。

ファイルサイズが8KBとなりました。

先ほど投入したidが1のものをSELECTしてみます。

1件しかデータがないので 1ms もかからないですね。

ではデータを一気に1000万件INSERTしてみます。

最初にINSERTしたデータとあわせて10000001件です。

データの内容は以下のような感じです。

ファイルを確認すると一気に約650MBとなりました。

idが1のデータをSELECTしてみます。

さすがに約1000万件もデータがあるので遅いです。約250msかかるようになりました。
パラレルシーケンシャルスキャンが実装されていなかった時代だともっと気が遠くなるほど遅かったことでしょう。/(ツ)\

ここからが今回の本題です。
投入したデータをidが10000000のもの1件だけを残して削除してみます。

データが1件だけになりました。ファイルサイズを確認してみます。

ファイルサイズが小さくなることはありません。かなしいですね。

idが1のデータをSELECTしてみます。

先ほどよりもかなりマシではありますが、最初に1件だけINSERTしたときに比べるとかなり遅いですね。

ここまででDELETEをしてもファイルサイズが小さくなることはなく、パフォーマンスも低下することを確認できました。

もう一度1000万件のデータを投入します。

ファイルサイズを確認してみます。

1279MBとなり、約2倍になっています。
(PostgreSQLはデフォルト設定で1GB毎にファイルを分割して管理します。)
VACUUMで不要領域を回収していなかったため、すべて新規に追記されてしまったようです。

SELECT性能をみてみましょう。先ほど約1000万件のデータがあるときは約 250ms でした。
今回は。。。

約420msとなり、さらにパフォーマンスが低下してしまいました。

再度、idが10000000以外のものを削除します。

残っているデータは2件だけです。SELECT性能はどうでしょうか。

データが2件しかないにもかかわらず約200msもかかってしまっています。これはひどい。

VACUUMの効果を試してみましょう。
これまでにDELETEした約2000万件の不要データの領域を再利用可能にします。

ファイルサイズを確認します。

再利用可能にするだけなので、変化はありません。

もう一度1000万件のデータを投入します。

ファイルサイズを確認します。

再利用可能な領域に1000万件のデータが格納されたので変化はありません。
まだ再利用可能な領域が残っているはずなので、さらに1000万件行きましょう。

ファイルサイズを確認します。

サイズは変わっていません。再利用可能な領域に収まりきったようです。

VACUUMの効果が出ていますね。
定期的にVACUUMを実行していればファイルサイズの肥大化を抑制できることを確認できました。

しかし、VACUUMでは一度肥大化してしまったテーブルを小さくすることはできませんし、低下してしまったパフォーマンスも改善しません。あくまでも抑制にしかならないのです。
ここで VACUUM FULL の出番です。

実際に試してみる(VACUUM FULL)

もう一度idが10000000のものを残して削除します。

残ったデータは4件のみです。
VACUUMをしてみますが、当然、ファイルサイズは変わりません。

パフォーマンスも悪いままです。

データ4件のみで約350ms。。。

それではいよいよVACUUM FULLを実行します。

VACUUM FULLでは新しいファイルにデータを整理して入れなおされるので、再度ファイル名を確認します。

ファイルを確認します。

データが4件しかないので1ブロックに収まり、ファイルサイズが8KBとなりました!

SELECT性能も完全に復活です!

VACUUMの例外ケース

VACUUMではファイルサイズが小さくならないことを確認しましたが、VACUUMでも小さくなる場合があります。それは、ファイル末尾から連続して不要領域が存在する場合です。このとき、PostgreSQLはファイルを切り詰めます。
これまでの動作確認で DELETE FROM foobar WHERE id <= 9999999; としていたのはそのためです。WHERE句を id > 1; とするとファイルが切り詰められ、小さくなります。確認してみましょう。

上の状態から、idが1のデータ(ファイルの先頭領域に格納されていると想定される)のみを残して削除します。

VACUUMを実行し、ファイルサイズを確認します。

先頭領域のみを残してすべて消えてしまいましたね。

※この挙動は公式ドキュメントに記載がないため、変更される可能性があります。

最後に

VACUUM FULLはテーブルに排他ロックを必要とするうえ、処理に非常に長い時間を要します。そのため、VACUUM FULLの実行がサービス障害につながることもありえます。
VACUUM FULLが必要とならないよう、普段から適切にVACUUMが実行されるようにAUTO VACUUMのパラメータ調整や、各テーブルのVACUUM実行状況の監視が大切です。

年末に大掃除しなくていいように普段からこまめに掃除することが大切ですねo(ツ)9

Comments are closed, but you can leave a trackback: Trackback URL.