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

2012年 12月 16日 (日) 23:15:08 JST

笠原です。

> 今回話題になった、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