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

[mochizuki]

 こんにちは。望月です。

御返事ありがとう御座います。大変参考になりました。

> ただ、強いて理由がありそうだとすると、
> ・実質的には「無効だがまだ削除できないタプル数」を含んだタプル数を
> 　スキャンしなければならない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
>>
>> ここで質問ですが、(★)にて「無効だがまだ削除できないタプル数」を
>> 推定に使用しているのは何故なのでしょうか？
>>
>> 素直に考えれば、「現在有効なタプル数」のみで
>> 推定した方が高精度な推定が出来そうな気がしています。
>>
>> ------------------------------------------------------------------------
>>
>> 素朴な疑問をだらだと書いてしまい恐縮ですが、
>> 御見識のある方がいらっしゃいまいたら、御教示頂けますと幸いです。
>>
>> --
>> 望月