[pgsql-jp: 34389] Re: パフォーマンスチューニング

[北村 英志]

おはようございます。北村です。


> リストアで解決するのであれば、ブロック内に空きが多く
> 余計なブロックを多く読んでいるのではないでしょうか。
> たとえば、
> ・大量のデータをロードした。その後で削除したが、VACUUM FULLを
> 　しなかったため、ファイルサイズは大きいまま。
> ・更新や削除が多いトランザクション構成だが、VACUUMがたまにしか行われていないため、
> 　ブロック内に不要なデータが残っていて、無駄にブロック数が増えている。
> などです。

更新や削除は頻繁に行われていますが、VACUUMは3時間おきに行われているよう
です。


> > ただ、フルバキュームを行うとその間、データベースがロックしてしまうという
> > 認識なのですが、間違いないでしょうか？
> 
> テーブル単位のロックです。
> （VACUUMコマンドでテーブル名を指定しないと、全てのテーブルを処理するので、
> データベースがロックされていると思ったほうが運用上は安全という意味で使われたのだと
> 思います）

夕べ実際にVACUUM FULL VERBOSEを試してみました。1時間半程度かかったのです
が、確かにロックはテーブル単位でした。
問題はその結果なのですが、、、

EXPLAIN ANALYZEの結果です。見づらいと思いますが、ご容赦ください

EXPLAIN ANALYZE SELECT DISTINCT as.id, as.able_id, as.mem_id, as.feed_id, as.ldata_id, as.ts_fetch, as.ts_filter, as.ts_trigger, al.type FROM ablesub as, able al, fdata fd WHERE al.status='OKAY' AND al.type='MLST' AND al.id=as.able_id AND as.feed_id=fd.feed_id AND ((as.ldata_id ISNULL AND fd.ts_inst>=as.ts_fetch) OR fd.id>as.ldata_id) LIMIT 20;
                                                                                    QUERY PLAN

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=79183.50..79191.28 rows=20 width=60) (actual time=14995.380..14995.439 rows=1 loops=1)
   ->  Unique  (cost=79183.50..82886.48 rows=9520 width=60) (actual time=14995.376..14995.434 rows=1 loops=1)
         ->  Sort  (cost=79183.50..79553.80 rows=148119 width=60) (actual time=14995.373..14995.390 rows=18 loops=1)
               Sort Key: as.id, as.able_id, as.mem_id, as.feed_id, as.ldata_id, as.ts_fetch, as.ts_filter, as.ts_trigger, al."type"
               ->  Merge Join  (cost=54204.13..60867.75 rows=148119 width=60) (actual time=12468.169..14995.216 rows=18 loops=1)
                     Merge Cond: ("outer".feed_id = "inner".feed_id)
                     Join Filter: ((("inner".ldata_id IS NULL) OR ("outer".id > "inner".ldata_id)) AND (("outer".ts_inst >= "inner".ts_fetch) OR ("outer".id > "inner".ldata_id)))
                     ->  Sort  (cost=52162.18..52731.77 rows=227837 width=20) (actual time=6055.064..6347.393 rows=228106 loops=1)
                           Sort Key: fd.feed_id
                           ->  Seq Scan on fdata fd  (cost=0.00..27767.37 rows=227837 width=20) (actual time=0.086..2576.670 rows=228106 loops=1)
                     ->  Sort  (cost=2041.95..2049.89 rows=3174 width=60) (actual time=268.398..1865.506 rows=2356968 loops=1)
                           Sort Key: as.feed_id
                           ->  Hash Join  (cost=3.83..1857.35 rows=3174 width=60) (actual time=0.502..183.213 rows=10557 loops=1)
                                 Hash Cond: ("outer".able_id = "inner".id)
                                 ->  Seq Scan on ablesub as  (cost=0.00..1547.52 rows=54852 width=52) (actual time=0.008..68.188 rows=33530 loops=1)
                                 ->  Hash  (cost=3.81..3.81 rows=7 width=12) (actual time=0.399..0.399 rows=0 loops=1)
                                       ->  Seq Scan on able al  (cost=0.00..3.81 rows=7 width=12) (actual time=0.109..0.379 rows=1 loops=1)
                                             Filter: ((status = 'OKAY'::bpchar) AND ("type" = 'MLST'::bpchar))
 Total runtime: 15014.291 ms

これは何度か試したうちの、早いほうの結果です。
長いとやはり、トータルで30秒弱かかるようです。
お馬鹿なことに、リストア直後の結果はANALYZEしていなかったので、分かりま
せん・・・
主観になってしまいますが、全体的なパフォーマンスとしては、若干早くなった
気もしますが、リストアしたときほどの効果が得られているとは思えません。
EXPLAINの結果であるコストがむしろ上がっていることが気にかかります。

前回お送りしたEXPLAIN結果と並べて見てみると、Merge Joinが使われていた
りと、かなりやり方が変わっているようです。
詳細に解析中です・・・


> また、これは興味本位の質問なのですが、autovacuumの結果の確認はどのように
> なさっていますか？
> 私の近所のチームで、確認方法がわからないケースがあったので(^_^;)
> 教えていただければ幸いです。

ログは取っていますが、詳細は記入されていません。
バキュームの効果測定のやり方は皆さんどうされているのでしょうか？
僕の場合は、単純にEXPLAINやANALYZEの結果を比較しているだけなのですが。
みなさんには常識的な部分も見落としている可能性がありますので、ご教示頂け
れば幸いです。