【この記事の所要時間 ： 約 7 分】
PostgreSQL 7系 と 8系 では 8系の方が性能的に大きく改善されている。8.0からは、VACUUM処理の遅延機能、8.1からは、自動VACUUM機能が実装されVACUUM処理の欠点が大きく解消された。そして8.0からバックグランドライター機能が実装されCHECKPOINT実行による性能低下を避けることが可能になった。それに伴い、設定するパラメータにもバージョンの違いによる変化がある。
以下は、Postgresql 7系の時に利用したパラメータである。
PostgreSQL 7系のパラメータチューニング
１．実装メモリ
= 1GB×4 = 4 GB
２．shmall（システムの許す共有メモリの総量）
/proc/sys/kernel/shmall = 8 GB
３．shmmax （カーネルの共有メモリ量）
/proc/sys/kernel/shmmax = 476472934 (401MB)
/etc/sysctl.conf → 476472934
４．postgresql.conf
・shared_buffers = 10000 (80MB)
・sort_mem = 4096
・wal_ buffers = 96
・checkpoint_segments = 16
・max_fsm_pages = 100000
・max_connections = 256
・effective_cache_size = 131072 (1GB)
・random_page_count = 2
PostgreSQL 8系のパラメータは、上記の 7 系のものと少し異なる。PostgreSQLのバージョンが上がって性能が改善されているため性能のピークとなるパラメータが異なるのがその理由である。
PostgreSQL：チューニング勘所 – Y-110’s Wiki
１．実装メモリ
= 1GB×4 = 4 GB
２．shmall（システムの許す共有メモリの総量）
/proc/sys/kernel/shmall = 8 GB
３．shmmax （カーネルの共有メモリ量）
/proc/sys/kernel/shmmax = 1890432000 (1.8GB)
/etc/sysctl.conf → 1890432000
４．postgresql.conf
・shared_buffers = 100000 (800MB)
・work_mem = 4096
・wal_ buffers = 96
・checkpoint_segments = 16
・max_fsm_pages = 100000
・max_connections = 1000
・effective_cache_size = 262144(2GB)
・random_page_count = 2
これ以外に、8系 で新たに採用されたパラメータについても考える必要がある。重要なのが、バックグランドライター機能に影響するパラメータである。なぜバックグランドライター機能が必要なのかは、ダーティーページとハードディスクへの書込みタイミングについて書いてある以下のサイトを読めばわかる。
ThinkIT – 性能か安定か？ライタープロセスのチューニング ①
ダーティページは更新が行われるデータベースサーバであれば常に存在し、徐々に増えていくものです。ですがダーディページはいつか必ずハードディスクに書き込まれます。
このバックグランドライター機能に影響するパラメータは3つある。
ThinkIT – 性能か安定か？ライタープロセスのチューニング ②
* bgwriter_delay
* bgwriter_percent
* bgwriter_maxpages
ダーティページは更新が行われると発生する。更新が行われない参照系DBの場合にレスポンスタイムの改善をはかる方法が書かれていた。とても参考になる。
レスポンスタイムの改善を行いたい場合には、共有バッファ内の次に捨てられる可能性の高いダーティページだけを書き出せば良いわけですから、一度に書き出すページ数は少なくて大丈夫です。つまり bgwriter_maxpages をグッと減らすと良い結果が得られます。この設定によりライタープロセス自体の処理が軽くなり、サーバ全体のパフォーマンスを底上げできます。ただし共有バッファ中の全ダーティページを減らす効果は薄いので、チェックポイント時の性能劣化を防ぐ力は弱いです。ダーティページがあまり発生しない参照系のデータベースサーバに向いた設定といえます。
逆に、更新系DBの場合はどのようにすればいいのか？参照系DBとは逆に増え続けていく共有バッファ中のダーティページをできるだけ減らして、チェックポイント時の性能劣化を抑えればよい。つまり、参照系DBとは逆に、bgwriter_maxpages を必要に応じて大きな値にしてやればよい。パラメータ値の目安の計算方法が以下のページに示されている。
ThinkIT – 性能か安定か？ライタープロセスのチューニング ③
チェックポイントの発生間隔はデフォルトで5分ですから、ライタープロセスが200msに1回実行されるとすると、チェックポイントと次のチェックポイントの間にはライタープロセスが1500回実行されることになります。1500回ですべての共有バッファをチェックして書き出せるようにと考えると、bgwriter_maxpagesは少なくとも「共有バッファサイズ÷1500」ページ以上は必要ということになります。たとえば共有バッファが16000ページだとすると、bgwriter_maxpagesには10 ページ程度を指定すればいいことになり、これを基準値として増減すればいいでしょう。
bgwriter_delay をデフォルトのまま 200ミリ秒と考えると、共有バッファ（shared_buffers）が 100000ページなので、100000÷1500=約67ページが基準値となる。ちなみに bgwriter_maxpages のデフォルトは、100 である。実際に有効となる上限値はbgwriter_maxpagesとbgwriter_percentとで指定されたページ数のうち小さい方であるので、以下のような設定値となる。
bgwriter_delay = 200
bgwriter_percent = 1
bgwriter_maxpages = 67
結局、デフォルトとあまり変わらない結果となったので、デフォルトのままでも性能的にはあまり変わらないのかもしれないが、中身を知っているのと知らないのでは今後困ったときに影響するので知っておいて損はないと思う。
ちなみに、PostgreSQL 8.0 の場合だけだが、 bgwriter_percent を有効にするとサーバが非常に重くなってしまうという欠点が知られている。これは全ダーティページ数を調べるために共有バッファを全スキャンするという実装になっていたためのよう。よってもしバージョン 8.0 を利用する場合は、必ず、’0’を設定して無効にするように！
オライリー・ジャパン
売り上げランキング: 831,039
