環境
PostgreSQL12.4
Ubuntu20.4
はじめに
PostgreSQLの運用を開始すれば、日々運用管理を行う必要が出てきます。特に重要なのが、VACUUMとANALYZEです。公式情報によると、自動バキュームは強く推奨されているもようです。
VACUUM
テーブルの更新/削除が何回も繰り返されると、タプルと呼ばれる空になった無駄領域が残り続けることになります。PostgreSQLが無駄に所有してしまったディスク領域をクリアにして、ディスク容量を圧迫させないようにする機能がVACCUMです。PostgreSQLは多版同時性制御（MVCC）と呼ばれるアーキテクチャを採用しており、構造上どうしてもタプルが発生してしまいます。
テーブルの更新/削除が多ければ多いほど、タプルの容量は雪だるま式に増えていきます。タプルの容量が大きくなるとデータベースへのRead/Writeの速度に影響が出てきてしまいます。
どこかのタイミングで定期的にVACUUMを実行して、肥大化したデータベースの容量を、実際の容量に戻してあげる必要が出てきます。機能としてはMS-Accessのデータベースの最適化に似ています。
VACCUMを実行してもテーブルに対して排他ロックはかからないようになっています。ユーザーのアクセスに影響がないように、深夜にこっそりと実行しなくてはいけないといったことはありません。日中に堂々と実行してOKです。
vaccumコマンドに似たコマンドに、vaccum fullコマンドがあります。vaccumコマンドでは排他ロックはかからないが、vaccum fullコマンドでは排他ロックがかかることになります。手動でvaccum fullコマンドを実行すれば、ユーザーのアクセスに影響が出てしまうため、業務時間を避けて実行しないといけないことになります。自動バキュームでは、vaccum fullコマンドではなく、vaccumコマンドの方が流れるようになっています。ご安心を。vaccum fullコマンドは手動でしか流すことはできません。
ANALYZE
各テーブルが持っている統計情報を最新の状態に更新するのがANALYZEです。テーブル参照のレスポンスを早くするために、インデックスが付けられますが、統計情報が古いと折角インデックスを付けても、それに見合ったパフォーマンスが得られなくなることになります。
テーブルの更新/削除が頻繁に行われれば行われる程、統計情報の状態は、実体とどんどんとかけ離れていくことになります。統計情報を最新にすることで、Select文のレスポンスを早くすることができるようになります。「最近、レスポンス遅くね？」と思ったら、ANALYZEを実行するべきです。
ANALYZEが実行されると、テーブルに対して一時的に読み取りロックがかかるが、排他ロックはかかりません。ANALYZEが実行されることでユーザーのレスポンスが低下することはないのため、日中に堂々と実行してOKです。
設定
VACUUMとANAZYZEはコマンドから手動で実行することはできますが、PostgreSQL側で自動化することもできます。自動化させるには、postgresql.confでつぎの２行のパラメータを設定すればOKです。デフォルトでは２行ともコメントアウトされているため、外せばいいだけです。
autovaccume = on
track_counts = on
自動バキュームを有効化すると、ANALYZEも自動で実行されるようになります。自動バキュームのデーモンが統計情報を参照するようになっており、必然的にANALYZEも流れるためです。
自動バキュームデーモンを有効にすれば、デーモンが常駐するようになります。そして、タプスの閾値が超えれば、VACCUMEが自動で実行されるようになります。
パラメータ
公式マニュアルによると、VACCUMが実行されると、ディスクに対し割と相当数のI/Oが走るようです。ディスク領域に余裕があるのであれば、VACCUMが走る間隔は、極力少なくしておいた方がレスポンスへの影響を少なくすることができます。自動バキュームを有効化するにあたり、考慮しておくべきパラメーターについて、いくつかピックアップします。
autovacuumの実行チェックが行われる間隔です。デフォルトでは1分です。使い方にもよりますが、1分は流石に短すぎだと思ったら、ここの設定はもっと延ばしてください。
autovacuum_naptime = 1min
VACCUMが実行される閾値の設定です。閾値は次の式で計算されます。
「autovacuum_vacuum_threshold ＋ autovacuum_vacuum_scale_factor × レコード数」
テーブルにデータが100件あった場合、デフォルト値の設定であれば
50 ＋ 0.2 × 100 ＝ 70となります。
データが70件、更新/削除されればVACCUMが流れることになります。
テーブルにデータが1000件あった場合、デフォルト値の設定であれば
50 ＋ 0.2 × 1000 ＝ 250となります。
データが250件、更新/削除されればVACCUMが流れることになります。
ここの設定はデフォルト値で妥当だろうと個人的には思います。しかし、状況によってはカスタマイズしてくださいということになります。
autovacuum_vacuum_threshold = 50
autovacuum_vacuum_scale_factor = 0.2
参考情報