[pgsql-jp: 41215] Re: Autovacuumが効果的に動作していないと思われる事象について

[kasaharatt ＠ nttdata.co.jp]

笠原と申します。

Autovacuum のログをもう少し詳細に確認することは可能ですか？
また、pg_stat_activity ビューなどで古くから存在しているトランザクションが
いないかを確認してみてください。

もし autovacuum のログメッセージ中にある
 tuples: NNNN removed, MMMM remain
というautovacuumで掃除された行(NNNN)と掃除せずに残された行(MMMM)の情報において
MMMM が実際のレコード数を超えて増加していた場合は、ロングトランザクションが
存在し、それが autovacuum の活動を阻害している可能性があります(※)。

また、autovacuum とバッティングするようなロックを伴う処理(ALTER TABLEや
LOCK TABLEなど)が実施される場合、autovacuum は自身の処理をキャンセルします。
このような処理が頻繁に実施されてしまう場合も、やはりautovacuum が十分に
働けないことになります。
ログに autovacuum cancel のメッセージが出ていないかも併せて確認してみて下さい。

(※)
PostgreSQL では、あるトランザクション A が実行中の場合、そのトランザクション A が
開始された以降に発生したガベージ(別のトランザクションによる更新処理で発生したものを
含む)は、そのトランザクションAが終わるまで回収できません。


> -----Original Message-----
> From: pgsql-jp-bounces ＠ ml.postgresql.jp
> [mailto:pgsql-jp-bounces ＠ ml.postgresql.jp] On Behalf Of 両國　執
> Sent: Tuesday, September 18, 2012 8:07 PM
> To: pgsql-jp ＠ ml.postgresql.jp
> Subject: [pgsql-jp: 41214] Autovacuumが効果的に動作していないと思われる
> 事象について
> 
> こんにちは、両國と申します。
> 
> 環境
> DB:PostgreSQL9.0.2
> OS:CentOS6.3
> 
> Autovacuumが正常に動作していないと思われる事象が発生しています。
> 
> 以下、postgresql.conf内のautovacuumに関するパラメータは、
> 
> autovacuum = on
> log_autovacuum_min_duration = 10000
> autovacuum_max_workers = 10
> autovacuum_naptime = 1min
> autovacuum_vacuum_threshold = 500
> autovacuum_analyze_threshold = 50
> autovacuum_vacuum_scale_factor = 0.2
> autovacuum_analyze_scale_factor = 0.1
> autovacuum_freeze_max_age = 200000000
> autovacuum_vacuum_cost_delay = -1
> autovacuum_vacuum_cost_limit = -1
> 
> で運用しております。show all; で確認しても、全て反映されていることは確
> 認
> 済みです。
> 
> Disk上のDBサイズの肥大化が進んでおり、DeadTupleがきちんと掃除されてい
> る
> かを確認した際、
> autovacuum_vacuum_scale_factor = 0.2 であるにも関わらず、LiveTupleに対
> し
> て、殆どのテーブルで20%を
> 超えてしまっていて、掃除される気配がありません。
> ワーカーの数が追いついていないかと思い、autovacuum_max_workers = 10 に
> 増
> やしたりと試してみましたが、
> 特に変化がありません。
> ログを出してみたところ、Autovacuum自体は数秒間隔で動作しているようなの
> で
> すが、このような事象に対する
> 知見や対策といったものをご教授いただきたいと思います。
> (AutovacuumでDisk上のDBファイルのサイズが小さくなるのではなく、リサイ
> ク
> ル用にマークされる、という認識は
> ありますが、手動でvacuumをかけて DeadTupleを削除した場合でも、その後サ
> ー
> ビスインさせた後に、再び20%の
> 閾値を超えて増え続けてしまう状態です)
> 
> 何か、これ以外に必要なパラメータ等ございますでしょうか？
> 
> 何卒よろしくお願い致します。