[pgsql-jp: 36295] Re: VACUUMの性能を向上させたい

[SAKATA Testuo]

おはようございます。坂田＠横須賀は快晴です。

Yumiko Izumi wrote:
> お世話になっております。Izumiです。
> 
> VACUUMの性能向上について追加質問です。
> 
> 取り急ぎ、postgresql.confを以下の通りチューニングして
> みました。

報告ありがとうございます。
効果が出ていないのは、残念ですね。

他の方からのコメントが複数出ていますよね。
少し整理してみると；

(1) そもそもディスクI/Oの性能が十分出ていないのではないか?
　　(koyama-7さんほか)
　　hdparmコマンドで確認
(2) ext3のジャーナリングがオーバヘッドになっているので、
　　ext2に替えてはどうか?
　　(加藤さん)
　　ext2に替えてテストしてみる
(3) テーブル構成とSQLの出し方を変えてはどうか?
　　(行川さん)

と言ったところですね。
ぼくが、この問題に取り組むのであれば、

(a) まず、所要I/Oの量を概算で割り出す
　　5GBのDBを(fullでないにせよ)vacuumするのであれば、
　　5GB分のファイルの読み+書きが(最悪の場合)発生する。
　　仮に、ディスクI/Oが10MB/sだとすると、(読み+書きで)
　　最悪の場合に1000秒かかるはず。
　　⇒これで条件を満足しないなら、ハードウェアの増強が必要(eへ)
(b) マシンの性能(特にディスクI/Oの性能を確認する)
　　ATAディスクの裸の状態での性能は、シーケンシャルアクセスの
　　場合であれば、10MB/s以上はある。
　　hdparm -t がどういう条件で測定しているかにも依存するが、
　　仮にシーケンシャルアクセスに近い条件で測定しているなら、
　　3MB/sという数値は悪すぎる
　　⇒OS周りの改善(バージョンアップ含む)を考える。(1)・(2)を参照。
(c) DMBS以外のI/O量を確認する
　　今回の前提条件では、DBMS以外に他のAPのI/Oも考慮する必要が
　　あるはずなので、それを上乗せする。
　　もし、DBMSとの並行アクセスが必須で、かつ、他のAPでも
　　性能条件が厳しいのであれば、ディスクドライブを分けるなどの
　　対処も考える。
(d) 現在の設定値の適否の確認
　　現在、かなりのディスクI/Oが出ているとのことですが、
　　仮想記憶のスワップなどは発生していないでしょうか？
　　発生している場合は、PostgreSQLの設定値を見直して、
　　メモリの所要量を削減する工夫が必要だと思います。
　　ぱっと見ですが、気になる点としては；
　　・shard_buffer の値はもう少し大きくのでは?
　　　(現時点でスワップが出ていれば、他の数値との兼ね合いになりますが)
　　・max_fsm_pages は約10GB分になりますが、これでOK?
　　　(ちょっと自信がありません)
(e) それでもダメなら、ハードウェアの増強
　　・ディスク台数を増やす(RAID0など)
　　・WALの格納先はファイルシステムを分ける
(f) アプリケーションの作りを見直す
　　・行川さんの記事(3)を参照。
　　・table 内の全ての行を削除するのであれば、truncate コマンド使うのが
　　　性能上は有利(本コマンドは、8.0以降で性能改善が図られているようです)

といったところでしょうか。


> ＜チューニング内容＞
> # デフォルトから変更した個所だけ書きます。
> # なお、shared_buffersの値は現在のカーネルの設定の限界です。
> shared_buffers = 3072
> max_fsm_relations = 4000
> max_fsm_pages = 1310720
> max_locks_per_transaction = 512
> vacuum_mem = 524288
> checkpoint_segments = 32
> 
> 
> 結果としては、ほとんど効果が得らませんでした。
> 
> ＜現状＞
> ・前述のDBはサイズが5GBを超えている
> ・VACUUM ANALYZEが90分以上かかる
> 　（VACUUM FULL ANALYZEではありません）
> ・VACUUM ANALYZE中にvmstatを見ていると、wa（ディスクI/O待ち）
> 　がずっと90%を超えていて、PostgreSQLでの処理は勿論、他の
> 　ディスク書き込みもほとんど出来なくなっている
> 
> 
> そこで追加で伺いたいのですが、このような大容量かつデータを
> 頻繁に更新するDBでは、VACUUMはこのように時間がかかるもの
> なのでしょうか？
> また、大量のディスクI/O待ちが発生するのもこのようなDBでは
> やむをえないことなのでしょうか？
> 
> 7.4.xや8.0.xでVACUUMも改良されているようなので、こうした現象
> が画期的に解消されるならバージョンアップも検討したいと考えて
> います。このあたりをご存知の方、どうか教えてください。
> 
> よろしくお願いします。
> 
> 
> 
> 


-- 
坂田 哲夫＠NTT サイバースペース研究所
sakata.tetsuo _at_ lab.ntt.co.jp
SAKATA, Tetsuo. Yokosuka JAPAN.