PostgreSQLをチューニング

PostgreSQLをチューニングする機会があったので
その時に調べたチューニング項目を備忘録として残しておきます。
バージョンの違いやサーバの規模などによっても
効果は変わってくると思うのであくまで参考程度のものですが。

・shared_buffers
　7系では8000〜10000程度まで引き上げる
　8系では150000程度まで引き上げることが可能、100000程度が性能のピーク
　これに多く割り当てるよりOSのバッファ領域として使う方が性能が向上する
　テーブルサイズを割り出して設定するのがベスト
　簡単に設定するなら搭載メモリ量の1/4、搭載メモリが多ければ1/2ぐらいでも可
・max_connections
　7系では256程度、8系では1000程度が性能のピーク
・work_mem(sort_mem)
　適切なサイズに調整する、2048〜4096程度
　プロセス毎に領域が割り当てられるので多すぎるとスワップする可能性も
　ソートメモリはORDER BYだけではなくMerge Join、CREATE INDEX時でも使用される
・deadlock_timeout
　長めに設定する
　1000ms×同時セッション数が理想
・effective_cache_size
　数GBメモリ積んだマシンなら総メモリの1/4〜1/2を設定
・wal_buffers
　トランザクションが多い、大きい場合に多く取っておくと書き込み効率が良くなる。32〜64程度。
・commit_delay
　同時トランザクション数が多い場合、0以上を設定すると書き込み効率が上がる
・random_page_count
　余程巨大なテーブルが無い限り数GBのメモリのマシンなら2〜3が適当
・max_fsm_pages
　小さすぎるとクラスタ容量が増えすぎる、大きすぎると検索オーバーヘッドが大きくなる、vacuum -vの情報を見て調節
・bgwriter_maxpages
　参照系DBなら値を小さく、トランザクションが多いDBなら大きく設定する
・checkpoint_segments
　大きく設定するとハードディスクへの書き込み頻度が減り性能が向上する

・VACUUM FULLをしてもインデックスの領域は切り詰められないのでREINDEXを行い無駄な領域を削除する
・複数のハードディスク上のテーブルスペースにテーブルを作ることによりIOが分散し効率が良くなる
・walログも別ハードディスクに移すと結構IO効率が上がる
・fstabのマウント情報にnoatimeを指定し、atimeの更新を抑えると若干性能アップ