[pgsql-jp: 41309] Re: autovacuum時のreltuples推定の内部挙動について

2012年 12月 18日 (火) 01:30:25 JST

こんにちは。望月です。

> # ただ、VACUUMの場合 visibilitymap により部分的にしかページをスキャンしないため
> 　単純に有効な行のみをカウントしてしまうと、不要レコードが大量に蓄積した状況では
> 　vac_estimate_reltuples() にて、トータルで不当に低い行数として見積もってしまうかも？
> 　とも思います。

なるほどです。
一応 vac_estimate_reltuples()にて重み付け平均を取っているとはいえ、
VACUUMによる偏ったサンプリングが続くと、
確かに不当に低い行数として見積もってしまう可能性がありそうですね。

具体的には同一テーブル内に「更新されにくい大量のデータ」と「更新頻度が激しい少量のデータ」の2群があり、
かつANALYZEによるランダムサンプリングが間に挟まっていない時、などでしょうか。

そういった可能性を加味して尺度を変えているのかもしれませんね。

ここのあたりの当時の議論なども、もう少し探してみるようにします。

> もし対策を打つとなると、
> ・FILLFACTOR を 90 - 80 程度まで落としてみる。
> ・autovacuum の実施頻度をあげるため、autovacuum_vacuum_scale_factorと
> 　autovacuum_vacuum_thresholdを下げてみる。
> あたりでしょうか。

対策案をありがとう御座います。
autovacuum_vacuum_scale_factor と autovacuum_vacuum_threshold は
本件にて色々弄ってみていたのですが、FILLFACTORはノータッチでした。
こちらも色々試して見ます。


2012年12月16日 23:15 Kasahara Tatsuhito <kasahara.tatsuhito ＠ gmail.com>:
> 笠原です。
>
>> 今回話題になった、vacuumlazy.c の lazy_scan_heap()と
>> analyze.c のacquire_sample_rows()ではカウントする対象が大幅に違うようですね。
>> (ex. 前者はHOTで未回収のタプルをreltuplesにカウントするが、後者はしない)
>>
>> このため、どうも真意が見えずらいな、と感じています。
> 端的に見ると矛盾している気もしますね。
> # ただ、VACUUMの場合 visibilitymap により部分的にしかページをスキャンしないため
> 　単純に有効な行のみをカウントしてしまうと、不要レコードが大量に蓄積した状況では
> 　vac_estimate_reltuples() にて、トータルで不当に低い行数として見積もってしまうかも？
> 　とも思います。
>
>> 対象の表は「少量のデータしかないが、更新のみ大量にかかる」というものです。
>> 今の使い方のままでは、ある程度はどうしようもないのかなと、若干諦め気味になっています・・・
> もし対策を打つとなると、
> ・FILLFACTOR を 90 - 80 程度まで落としてみる。
> ・autovacuum の実施頻度をあげるため、autovacuum_vacuum_scale_factorと
> 　autovacuum_vacuum_thresholdを下げてみる。
> あたりでしょうか。
>
> 少量のデータしかないのであれば、HOTをある程度効果的に作用させるため、
> 効果の薄いインデックスをあえて外してみるのも手だてです。
> # 程度によりますが、シーケンシャルスキャンでも問題ない量のテーブルであれば・・
>
> 手助けになれば幸いです。
>
> 2012年12月15日 6:54 mochizuki <duvote ＠ gmail.com>:
>>  こんにちは。望月です。
>>
>> 御返事ありがとう御座います。大変参考になりました。
>>
>>> ただ、強いて理由がありそうだとすると、
>>> ・実質的には「無効だがまだ削除できないタプル数」を含んだタプル数を
>>>　 スキャンしなければならないSQL(全件検索とか)もある。
>>> もしくは
>>> ・「無効だがまだ削除できないタプル数」が相当数蓄積されてしまうケースを
>>>   例外的なものとし、考慮していない。
>>> といったところでしょうか・・
>>
>> なるほどです。
>> ケースによっては実際の有効な行数を使わないほうが
>> 厳密なコスト計算が出来るケースもあるのですね。
>>
>> また各種ブログなどを見る限り、今回のように
>> 「主キーで検索しているはずなのに、検索推定行数が100行近くある！」
>> みたいな悲鳴はあまりないようです。
>> 確かに私が被ったデメリットの方が例外的なのかもしれません。
>>
>>>どちらかと言えば、
>>>> 一時的にpg_class.reltuplesの値が非常に大きな値になる事を確認しました。
>>>> (データ量が少ないテーブルだと実レコード数の～数10倍の誤差)
>>>という状況になってしまうことを防ぐか、定期的に明示的なANALYZEを
>>>かけるようにすべきかもしれません。
>>># ANALYZE はVACUUM と異なり、ANALYZEから見える有効行のみカウントする
>>>ため、VACUUM で膨れ上がったreltuples を最新の状態に近づけることができます。
>>
>> 私の環境だと、ほとんどのケースでautovacuumの直後にautoanalyzeが走っています。
>> その際に、仰るとおりreltuplesが現在の有効行数に戻るようです。
>> # そのために、今回のreltuplesの件になかなか気が付きませんでした。
>>
>> 今回話題になった、vacuumlazy.c の lazy_scan_heap()と
>> analyze.c のacquire_sample_rows()ではカウントする対象が大幅に違うようですね。
>> (ex. 前者はHOTで未回収のタプルをreltuplesにカウントするが、後者はしない)
>>
>> このため、どうも真意が見えずらいな、と感じています。
>>
>> また、推定時に経由した関数によって、
>> 尺度の違う推定値間で(重み付きの)平均を取る事について、
>> 人によって賛否が分かれるかもしれないな、と思いました。
>>
>>> 本件の場合、例えばロングトランザクションがいたりするため発生している
>>> 気がしますので、そこの対処が重要かなと思います。
>>
>> 手元にある情報を確認する限り、実行計画が狂ったと思しきSQLを除くと、
>> そこまで不自然なトランザクションはありませんでした。
>>
>> autovacuumのログによると、わずかずつvacuumが進んでいるようなので、
>> 少なくともある特定トランザクションに露骨に全タプルのバキュームが妨害されている、
>> というわけではなさそうです。
>>
>>> # テーブルサイズもあるべきサイズより膨らんでいませんか？その場合
>>>   テーブル肥大化により、実行計画はもとよりI/O的にも無駄が発生しますので。
>>
>> はい、仰るとおり過度に膨らんでいます。
>> 対象の表は「少量のデータしかないが、更新のみ大量にかかる」というものです。
>> 今の使い方のままでは、ある程度はどうしようもないのかなと、若干諦め気味になっています・・・
>>
>> 2012年12月13日 19:31  <kasaharatt ＠ nttdata.co.jp>:
>>> 笠原と申します。
>>>
>>>> ここで質問ですが、(★)にて「無効だがまだ削除できないタプル数」を
>>>> 推定に使用しているのは何故なのでしょうか？
>>> 私的にも「無効だがまだ削除できないタプル数」を除いたタプル数での
>>> 推定を行うように改善できそうな気がしました。
>>>
>>> ただ、強いて理由がありそうだとすると、
>>> ・実質的には「無効だがまだ削除できないタプル数」を含んだタプル数を
>>>　 スキャンしなければならないSQL(全件検索とか)もある。
>>> もしくは
>>> ・「無効だがまだ削除できないタプル数」が相当数蓄積されてしまうケースを
>>>   例外的なものとし、考慮していない。
>>> といったところでしょうか・・
>>>
>>> そのため、掃除できなかったタプルも含めて、総数をそのまま reltuples にセット
>>> しているのかなと思います。
>>>
>>> どちらかと言えば、
>>>> 一時的にpg_class.reltuplesの値が非常に大きな値になる事を確認しました。
>>>> (データ量が少ないテーブルだと実レコード数の～数10倍の誤差)
>>> という状況になってしまうことを防ぐか、定期的に明示的なANALYZEを
>>> かけるようにすべきかもしれません。
>>> # ANALYZE はVACUUM と異なり、ANALYZEから見える有効行のみカウントする
>>>   ため、VACUUM で膨れ上がったreltuples を最新の状態に近づけることができます。
>>>
>>> 本件の場合、例えばロングトランザクションがいたりするため発生している
>>> 気がしますので、そこの対処が重要かなと思います。
>>> # テーブルサイズもあるべきサイズより膨らんでいませんか？その場合
>>>   テーブル肥大化により、実行計画はもとよりI/O的にも無駄が発生しますので。
>>>
>>>> -----Original Message-----
>>>> From: pgsql-jp-bounces ＠ ml.postgresql.jp [mailto:pgsql-jp-bounces ＠ ml.postgresql.jp] On Behalf Of mochizuki
>>>> Sent: Thursday, December 13, 2012 6:36 AM
>>>> To: pgsql-jp ＠ ml.postgresql.jp
>>>> Subject: [pgsql-jp: 41305] autovacuum時のreltuples推定の内部挙動について
>>>>
>>>> こんにちは。望月と申します。
>>>>
>>>> autovacuum時のreltuples推定の内部挙動でよく分からない点が
>>>> ありましたので、申し訳ありませんが質問させて下さい。
>>>>
>>>> ------------------------------------------------------------------------
>>>> ○背景
>>>>
>>>> 現在以下の環境でPostgreSQLを動かしています。
>>>>
>>>> バージョン：PostgreSQL 9.1.6
>>>> OS：Red Hat Enterprise Linux Server release 5.6 (x86_64)
>>>>
>>>> 最近autovacuum実施後のタイミングに、不適切な実行計画が選択され、
>>>> 性能が大きく劣化する問題が発生することに気が付きました。
>>>>
>>>> そこでautovacuumのタイミングで統計情報を確認すると、
>>>> 一時的にpg_class.reltuplesの値が非常に大きな値になる事を確認しました。
>>>> (データ量が少ないテーブルだと実レコード数の～数10倍の誤差)
>>>>
>>>> ------------------------------------------------------------------------
>>>> ○質問
>>>>
>>>> ソースコードで該当しそうな箇所を探すと、
>>>> 以下のようにしてreltuplesを推定しているように見えました。
>>>>
>>>> スキャンしたページのうち、
>>>> (「現在有効なタプル数」+「無効だがまだ削除できないタプル数」) / (「スキャンしたページ数」)(★)
>>>>
>>>> の比率と以前の情報から、「全ページで有効なタプル数」を推定
>>>>
>>>> ※該当箇所
>>>> vacuumlazy.c の lazy_scan_heap() ～ vacuum.c の vac_estimate_reltuples()
>>>>
>>>> http://doxygen.postgresql.org/vacuumlazy_8c_source.html#l00361
>>>> http://doxygen.postgresql.org/vacuum_8c_source.html#l00482
>>>>
>>>> ここで質問ですが、(★)にて「無効だがまだ削除できないタプル数」を
>>>> 推定に使用しているのは何故なのでしょうか？
>>>>
>>>> 素直に考えれば、「現在有効なタプル数」のみで
>>>> 推定した方が高精度な推定が出来そうな気がしています。
>>>>
>>>> ------------------------------------------------------------------------
>>>>
>>>> 素朴な疑問をだらだと書いてしまい恐縮ですが、
>>>> 御見識のある方がいらっしゃいまいたら、御教示頂けますと幸いです。
>>>>
>>>> --
>>>> 望月
>
>
>
> --
> Tatsuhito Kasahara
> kasahara.tatsuhito _at_ gmail.com