くだらないことばかり書いているので、自分の知識の整理もかねて真面目なことをかいてみようと思う。
PostgreSQLは、セットアップ時のデフォルトの設定は、最近の高性能なハードウエアに合わせた設定になっていないのでハードウエアの性能に合わせて適切な値にしないとハードウエアの性能を引き出すことができない。
[max_connections]
PostgreSQLがどれだけコネクションを待ち受けるかを指定するパラメーター、基本的にフロントエンドのWebサーバーの接続数と同じ数だけ用意する必要がある。
数値を増やすとOSのShared MemoryとPostgreSQLのWork_memの消費量が増えるので必要以上に大きくしない方が良い。
また1000コネクション同時接続とか大量のコネクションをさばく必要があるのならpgpoolを使用してコネクションプールをすることを考慮に入れる。
また、コネクションの接続が大量にあると、OS側のファイルディスクリプタとプロセス数の上限に引っかかる可能性があるので、次の方法で調整すると良いかも。
[Shared_buffer]
DB専用のサーバーとして使用するなら搭載メモリーの25%ぐらいを目安に設定をする。DB専用でないサーバーならば10～15%の間で設定をする。
古いバージョンのPostgreSQLでは800MBぐらいに性能のピークがあるらしい。
またPostgreSQL8.4以降では、読んだ本によると8GBぐらいに性能のピークがあるようです。まあメモリに余裕があるならデータベースがすべてメモリに乗るように設定をすればいいと思うよ。
[Work_mem]
ソートの処理やテーブル結合の際のハッシュ生成処理などを行う際に使用されるメモリ、設定より大きなデータを処理しようとするとハードディスクに一時ファイルを作って処理が行われてしまう。
当然だがメモリとディスクの速度差からものすごく性能が落ちる。
PostgreSQL8.3以降では、postgresql.conf中のlog_temp_filesパラメーターで指定したサイズ以上の一時ファイルが作成された場合ログに記録することができる。
例えば1MB以上の一時ファイルが作成された場合のみログを残す場合次のように設定する（数値はkB単位で設定する）。
log_temp_files = 1024
最大で接続数×Work_memぐらいのメモリを消耗すると考えてよいが、１つの接続の中で複数のソートなどの処理が実行されることもあるので、最悪の場合には、接続数×Work_mem×ソートなど処理の数分だけメモリを消耗する可能性がある。
クエリを実行する際に使用されるメモリは決まるので指定された値を必ず消費するわけではない。
メモリの搭載量が十分にない状態で増やしすぎるとメモリを使い尽くしてしまう可能性があるので注意する。
Work_memの消費量は、正直どれぐらい使うか予想をすることが難しい。読んだ本によると、下記のような設定を目安にして、あとはtopコマンドなどでどれぐらいメモリの空きがあるかを調べながら、上限値と安全値の間ぐらいで適正値を探るべきらしい。
この値ぐらいをとりあえずの上限にして最適値を探る。アプリケーションにもよるけれど実際には大量のメモリを使うクエリなんて頻繁に発生しないと思うのでもう少し多めにしても良いかもしれない。
work_mem = 搭載メモリ / max_connections / 4
安全を見た設定値、かなりメモリの割り当てが少なくなる。
work_mem = 搭載メモリ / max_connections / 16
[effective_cache_size]
搭載メモリ量の半分ぐらいの値を設定する。このパラメーターは、実際にメモリを確保するわけではなく、あくまでもPostgreSQLがクエリーを実行するときのスキャン方式などの決定のヒントとして使われる。
[Wal_buffers]
PostgreSQLのトランザクションログのバッファー
OSの共有メモリを設定値だけ消耗する以外に何もデメリットはないので、メモリをたくさん積んでいるならば、何も考えないで設定上の最大値である16MBを設定する。
なぜ最大値が、16MBとかというとpg_xlogディレクトリの中にあるトランザクションログの１ファイルの大きさが16MBだから。