PostgreSQL Vacuum について

とあるシステムの障害に巻き込まれてしまって、 いろいろ対応したので、メモします。

簡単にいうと、PostgreSQLのデータ空き容量の確保についてのメモです。

今回の環境

PostgreSQLのオブジェクトファイルの場所

デフォルトでは、 /var/lib/pgsql/data/base 配下にあるようです。 PostgreSQLのディスク使用量は、以下のコマンドで取得しました。

# cd /var/lib/pgsql/
# du -h --max-depth=1

実際のオブジェクトファイル名は、オブジェクトID（数字）のため、 ファイル名とテーブル名等の対応がわからないと、 どのテーブルがどれだけディスクを消費しているのかわかりません。

オブジェクトファイルサイズの確認

以下のpsqlコマンドから、SQLを使ってオブジェクトファイルのサイズを取得しました。 $DBUSER, $DBNAME, $FILENAME は適宜置き換えてください。

psql -U $DBUSER $DBNAME -c 'SHOW block_size;'
psql -U $DBUSER $DBNAME -c 'SELECT relname, relfilenode, ROUND(relpages*8/1024.0,2) AS MB FROM pg_class WHERE relpages > 1 ORDER BY relpages DESC;' >> $FILENAME

SHOW block_size でまずは、ブロックサイズの確認です。 デフォルトは8キロバイト(8192)です。

pg_class カタログには、テーブルの他にもインデックスやビューなどを目録にしているそうです。 relnameがテーブル等の名前、relfilenodeがディスク上のファイル名、 “relpages * ブロックサイズ"がディスク上のサイズとなります。 MB での表記にして、おおよその容量を取得しています。 （※ 8192/(1024*1024) = 8/1024 となります。） ちなみに、relpages自体は、プランナで使用される単なる推測値のようです。

vacuum と reindex

今回は、容量のほとんどをpostgresqlが使っていたため、 以下のことをして、対処しました。

  1. 巨大なindexを一旦dropして空き容量確保。
  2. 不要なデータの削除。
  3. テーブルに対して、vacuum fullの実行。
  4. vacuumするとindexが肥大化するので、reindexを実行してindexの再構築。
  5. dropしたindexを再作成。

最初に巨大なindexをdropして、空き容量を確保しました。 容量の大きい順で10個drop。主キーのindexはdrop対象から除いています。 空き容量を確保したことでサーバが応答し始めました。まずは、応急処置としての対応となります。

応急処置だけでは、応答し始めたとしても応答自体の速度はよくありません。 index削除しただけですから。 そこで、不要なデータを削除して、vacuum fullを試みました。

postgresql 8.1だったので、vacuum fullは、かなりの時間がかかります。 本当はclusterを使いたかったのですが、容量の関係等もあって断念。 テーブル毎に時間をかけながらvacuum fullです。 vacuum fullすると、indexのサイズが大きくなるので、 併せてreindexも実行します。

全てのテーブルとインデックスに対処したら、 最初にdropしたindexを再作成して完了です。

定期的なvacuum

さて、このままだと、月日が経てばまた同様の症状が発生することが目に見えていました。

こんなことは、構築時に担当者なりが事前にチェックするものですが、 どこかの時期もしくはどこかのタイミングで止まってしまったのかもしれません。

vacuum fullを毎日実行するとかは非現実的ですので、 まずは、vacuumがうまく機能させるためにFSMの適正化や実行タイミングを調査して設定。 そして、週末の負荷があまりない時間帯にclusterを定期実行するように設定しました。

vacuum fullの補足

vacuum fullは9.0から方式が変更になっている。 詳細はこちらをご確認いただきたい。

9.0からはCLUSTERに似た実装になっているため、 9.0以降のvaccum fullは実行速度が速くなったかわりに、十分な空き容量が必要になります。 つまり、容量不足の場合は、事前にindexの削除等で十分な空き容量を空ける必要があります。

参考

pg_class
VACUUM FULL/ja - PostgreSQL wiki
PostgreSQLのvacuumとvacuum fullの違い - chulip.org

comments powered by Disqus
#日記 #PostgreSQL #DB