Calender
Sun Mon Tue Wed Thu Fri Sat
     12
3456789
10111213141516
17181920212223
2425262728  
<< February 2019 >>
広告
SEARCH

SELECTED ENTRIES
RECENT COMMENTS
RECENT TRACKBACK
CATEGORIES
ARCHIVES
LINKS
PROFILE
OTHERS
SKYPE
PC: skype.jojo.jp
chat
iPad: iphone.jojo.jp
chat call
THANKS



本日:
昨日:
多言語
広告
 ▼▲ 作業日報 ▼△
    What's under the hood?
<< 巨大なファイルのdiff(diff memory exhausted) | main | 公開鍵が利用できないときの一般的な対応方法 >>
postgresql の FSM(FreeSpaceMap)値 調査
運用しているPostgreSQLのファイルサイズがものすごい勢いで増えているとの連絡を受けて調査したところ、fsm値が適切でなかったため、無用なデータ増長を招いているようでした。

まとめるのが面倒だったので、以下そのときの調査のメールを挙げておきます

すいません、返答おそくなりました。
DBを何度もリストア/vacuumしてたので時間がかかりました。
自分の見解が入っているところもあると思います、情報元はリンクさせていただきました

fsm系のチューニングにつきまして、長くなりましたので結論から書きます
-------------------------------------------------------
max_fsm_relations は テーブル数の2500に修正する必要があります。

(1)vacuum --full を前提としない場合は
  max_fsm_pagesの値を適切に設定する必要があります。
  算出方法は
  その時点で1日ぐらい運用した時点で(削除処理が十分走った時点)
  #sudo -u postgres vacuumdb -d [HOGE_DB] -z -v として
  max_fsm_pages値を算出します
  ※本当はautovacuum時に -v オプションを入れておきたい

(2)vacuum --full を前提とする場合は
  毎朝 vacuumdb -d [HOGE_DB] -z -v できるようにしておく
  
上記のチューニングでDBサイズの肥大化は抑えられると思われます
・・・
vacuum --fullの結果ですが、

容量ですが、

41.6GBだったものが
28.4GBとなり 容量的には約13.2G(32%)
の削除となりました(時間的には昨日20時から今朝5時10分までかかりました
(途中バックアップ処理が動いていましたが)

又、vacuum --full でもINDEXについては削除されないようなので 
reindex コマンドを実行したところ「reindex database」  ....約1時間

最終的に 20.5Gとなりました(50%の削減)

この際に報告にあるように以下のようにエラーが出ます。
---------------------------
NOTICE:  max_fsm_relations(1000) equals the number of relations checked
HINT:  You have at least 1000 relations.  Consider increasing the 
       configuration parameter "max_fsm_relations".
---------------------------
設定を確認しますと  max_fsm_relations に対するメッセージですが、
デフォルトで1000となっています
------PostgreSQL.conf-------
# - Free Space Map -
max_fsm_pages = 204800          # min max_fsm_relations*16, 6 bytes each
                                # (change requires restart)
#max_fsm_relations = 1000       # min 100, ~70 bytes each <-●
                                # (change requires restart)
------PostgreSQL.conf-------
現状でのテーブル総数は 2464となっていて確かに1000じゃ足りていませんません。
(select count(*) from  pg_stat_user_tables で確認できます)
これは2500程度にする必要がありそうです、

又、
【max_fsm_pages】についてですがmax_fsm_relationから算出すると 
2464*16=39424(最低)なので現状の値で問題なさそうですが、この値については
以下のサイトで説明されている通り最適な値を求める必要がありそうです。
▽---------------------------------------▽
Y-110's Wiki
Postgresql.confのチューニング
(※01)
max_fsm_pagesは大きすぎても小さすぎてもよくないらしい、 スペックが厳しい場合は 10000 程度に収めておき、 適切なFSMのサイズ(2005/06/06) http://www.thinkit.co.jp/cert/marugoto/2/1/13/2.htm △---------------------------------------△ そもそもFSMとは何か?とう言う点ですが FSM(Free Space Map)とはDBファイル中の(削除などで)利用可能な 領域を覚えておくメモリ空間で、次の追加の際に、テーブル単位で使っていな いファイル領域がある場合はそこに書き込みましょうということらしいです (この図が分かりやすい) http://www.thinkit.co.jp/free/marugoto/2/1/13/1.html (Vacuumを使いこなす05/06/06) この領域がなくなる(いっぱいになる)と追加データは削除された空白エリアを再利用せず 新規にファイルに書き出しにいくようです。 そうなると、ファァイルに追加されデータの増長が起こります。 vacuum --fullされると削除されたデータは実際に抹消されるので、fsm情報は必要なくなります。 <<実際にテーブル作成し、データを入れレコード削除とfsmの利用状況をテストしてみました>> (--snip--) 現状でのDBのFSMの利用状況ですが(vacuumdb -v や pg_maintenance -v) --------------------------- #sudo -u postgres vacuumdb -d postgres -t test -v (・・テーブル単位で利用ページ数が報告される・・) DETAIL: A total of 2320 page slots are in use (including overhead). <--● 2320 page slots are required to track all free space. Current limits are: 204800 page slots, 1000 relations, using 1305 kB. --------------------------- となり必要な量は2320ページとなっていました。。 vacuum後だからかな。。と思い元のDBをリストア直後に検査してみましたがやはり --------------------------- #sudo -u postgres vacuumdb -d postgres -t test -v (・・テーブル単位で利用ページ数が報告される・・) DETAIL: A total of 2416 page slots are in use (including overhead).<--● 2416 page slots are required to track all free space. Current limits are: 204800 page slots, 1000 relations, using 1305 kB. --------------------------- という感じで設定値204800に対し2416ページしか利用されていません。 ■これはあまりに少なすぎる気がします■ ひょっとすると元々max_fsm_relationsが足りていないためにファイルに新規に書き出している為、 fsm自体を利用できていないのかもしれません。 ただ FSMを効かすと  ファイルの増長は抑えられ(リアルタイムに増長はしない)ますが、  空き部分にレコードが随時挿入されることになりシーケンシャルにレコードを読みにいこうとした場合  ヘッダの移動が発生すると思われます。  そうなると元々5時間おきに--alalyzeは走っていますしデフラグ(データ圧縮)の意味を  こめてある程度のタイミングでやはり vacuum--fullをしたほうがいいような気がします。  (ただし主キーに従いデータが並べ変わる訳ではありません それはcluster文で行えます) それで以下のサイトも下のように結論ずけています ----------------------------- ここで解決のパターンは2つあると思います * max_fsm_pages を必要なページ数より多く設定する * VACUUM FULL を行って不要領域を完全に削除した上で, VACUUM の頻度を上げる。 http://php.y-110.net/wiki/index.php?PostgreSQL%A1%A7%A5%C1%A5%E5%A1%BC%A5%CB%A5%F3%A5%B0%B4%AA%BD%EA -----------------------------  1番目 →元々削除の列が再利用されるのでDB容量は増えない。。  2番目 →vacuum full の頻度が上がるので増える->圧縮->増える->圧縮という増加 ●autovacuumについて そもそもautovacuumが動いているにもかかわらずどうして vacuum --full をする必要があるか? autovaluumは何をしているのでしょうか?8.3のドキュメント公式以下に書いてありました 「23.1.4. 自動バキュームデーモン」 autovacuum http://www.postgresql.jp/document/pg833doc/html/routine-vacuuming.html#AUTOVACUUM で確認すると 「PostgreSQL 8.1から、自動バキュームという機能があります。これはVACUUMとANALYZEコマンドの 実行を自動化することを目的としたものです。有効にすると、自動バキュームは大量のタプルの挿入、 更新、削除があったテーブルを検査します。 ・・・、ワーカプロセスはデータベース内の各テーブルを検査し、必要に応じてVACUUMまたは ANALYZEコマンドを発行します。 」 (・・・) 8.3からは複数プロセス(ワーカ)により、それぞれテーブル単位でvacuumが必要かどうかを判断します、 vacuumはテーブルやインデックスの不要データを再利用できるように印をつけます、 2つ目の形式はvacuum fullです、不要な領域の回収を行います」 とあり 「容量の復旧のための定常的なバキューム処理には、VACUUM FULLではなく、普通のVACUUMを使用してください。 」 http://www.postgresql.jp/document/pg833doc/html/routine-vacuuming.html#VACUUM-FOR-SPACE-RECOVERY とありますが、 恐らくこれは適切に fsm が設定されている場合にはそういえるのかもしれません。 vacuumコマンドだけではDBが縮小することはありませんでした。

変更結果報告:
HDDを高速にし設定を変更しもう一度 vacuum -full を実行してみました

・postgresql.confを以下のように修正し
---------------------------
autovacuum=off            (on->off)
checkpoint_segments=32             (3->32)
max_fsm_relations=2500               (1000->2500)   <--多分これが効いたかな
----------------------------- 

9時間かかっていた処理が40分になりました。。。

ただHDDはRAID0で通常の2.5倍ぐらい高速です。
--------------------------------------------
eva:/home/db# hdparm -tT /dev/md1
/dev/md1:
 Timing cached reads:   2846 MB in  2.00 seconds = 1424.02 MB/sec
 Timing buffered disk reads:  718 MB in  3.00 seconds = 239.20 MB/sec
--------------------------------------------
・vacuum -fullを実行したところ(時間を出すようにして)
--------------------------------------------
eva:/home/db# date ;sudo -u postgres vacuumdb -d [HOGE_DB] --full --analyze ;date
2009年 10月 21日 水曜日 15:22:32 JST

NOTICE:  number of page slots needed (368544) exceeds max_fsm_pages (204800)
HINT:  Consider increasing the configuration parameter "max_fsm_pages" to a value over 368544.
2009年 10月 21日 水曜日 16:01:36 JST 。。。。vacuum --full に 40分かかっていません

多分「max_fsm_relations=2500」が効いたのだと思います、そちらのエラーは出なくなりました。

又、 number of page slots needed (368544) exceeds max_fsm_pages (204800) と
fsm値もそれらしい(368544)値をだしてきています

インデックスの最適化「reindex database [HOGE_DB]」 も行うと(こちらは約15分でした)

21.7Gになりました。

気になる点としては、pg_dumpでvacuum前後でデータを確認したところ、
#pg_dump -U sa -a -Fp -f ~text/[HOGEDB]_2.dump [HOGEDB]

データサイズ、行数は全く一致しましたが
diffをかけると順番が変わっていることが分かりました。
vacuumをかけた時点でデータをずらすのではなく、削除データ部に
後の方にあるレコードが移動したような形となっています。

なので、過去データを削除し、新規データを挿入するようなレコード操作
がある場合、新規データは過去データの削除レコード位置に配置されちき
シーケンシャルに読み出す利用が多い場合はヘッダの移動がきになります。

やはりあまり薦められていない方法ですが定期的に過去データがFIXした時点で
多くのテーブルは時系列にデータを並べ替える(cluster 処理を行ったほうが)
スループットがあがるように思います。

要はFSM利用時における新規データ挿入時のHDDに対する分散具合です。
参考資料:
http://lets.postgresql.jp/documents/tutorial/introduction
autovacuum を有効にしない場合、もしくは有効にしていても夜間等にシステムの負荷が下 がることが予想できている場合には、vacuumdb 等で明示的に VACUUM を実行すると安心です。 夜中に VACUUM しておけば、次の日の昼間に autovacuum が動作する可能性も減らせます
「VACUUM FULLの使用するタイミングを教えてください」 (教えてgoo!)
空DISK容量が必要になったらvacuum --fullを行うというスタンス
** max_fsm_pages ***
[pgsql-jp: 35621] max_fsm_pagesの設定値について
「トレンドマイクロサポートページ:UNIX:データベースのVACUUM実行時に、max_fsm_pages や max_fsm_relations に関するメッセージが出力されます」
** max_fsm_relations ***
PostgreSQL8.3.3付属文書:SQL:VACUUM
vacuum
pg_maintenance (英語)

| サーバー関連 | 12:01 | comments(0) | trackbacks(0) |









http://blog.jojo.jp/trackback/1310785