PostgresSQLチューニング

簡単なチューニングを考えてみる。

以下の方法が考えられる。

shared_buffersを増やす

 postmaster(postgresのマスタープロセス)が確保するメモリ。いわば作業机で、検索するテーブルが小さくここに収まりきる場合、必要なければいちいち検索のたびに再計算やロードをせずこのメモリ内のものが使われる。よって必要なだけの最低限の値が望ましい。

 だが、システムが許容するbufferサイズを越えると致命的なエラーを引き起こしデータベース接続ができなくなる可能性があるので注意が必要である。

 shared_buffers=4096の場合の bufferサイズは 40406400 bytes になるので、これを以下のように設定する。

echo "40406400"  > /proc/sys/kernel/shmmax

postgresqlサーバの起動前、sysctl.confに仕掛けるのがよいが、一時的に試すだけなら /etc/init.d/postgresに仕込んでしまってもよいだろう。テスト鯖では後者を選んだ。

max_connectionsを増やす

とりあえず倍にしてみよう。

設定前(システムデフォルト)
max_connections = 64
設定後
max_connections = 128

sort_mem の増加

これはsortだけでなくテーブルの結合などでも使われる。だから大きくしておくと性能向上が期待される。

sort_mem = 4096

テスト

以上を全て設定した状態で試験サーバでpostgresqlを再起動。shared_mamoryまわりのエラーが出ていないことを確認してから pgbench を走らせてみた。テストにはPostgresql付属の pgbench を使用した。試験は三回行った。

変更前

postgres@blog:~$ /usr/lib/postgresql/bin/pgbench -t 2000
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 1
number of clients: 1
number of transactions per client: 2000
number of transactions actually processed: 2000/2000
tps = 231.094639 (including connections establishing)
tps = 231.174961 (excluding connections establishing)

postgres@blog:~$ /usr/lib/postgresql/bin/pgbench -t 2000
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 1
number of clients: 1
number of transactions per client: 2000
number of transactions actually processed: 2000/2000
tps = 181.769732 (including connections establishing)
tps = 181.820331 (excluding connections establishing)

postgres@blog:~$ /usr/lib/postgresql/bin/pgbench -t 2000
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 1
number of clients: 1
number of transactions per client: 2000
number of transactions actually processed: 2000/2000
tps = 197.326833 (including connections establishing)
tps = 197.386874 (excluding connections establishing)

変更後

postgres@blog:~$ /usr/lib/postgresql/bin/pgbench -t 2000
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 1
number of clients: 1
number of transactions per client: 2000
number of transactions actually processed: 2000/2000
tps = 345.175678 (including connections establishing)
tps = 345.419325 (excluding connections establishing)

postgres@blog:~$ /usr/lib/postgresql/bin/pgbench -t 2000
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 1
number of clients: 1
number of transactions per client: 2000
number of transactions actually processed: 2000/2000
tps = 358.247218 (including connections establishing)
tps = 358.500871 (excluding connections establishing)

postgres@blog:~$ /usr/lib/postgresql/bin/pgbench -t 2000
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 1
number of clients: 1
number of transactions per client: 2000
number of transactions actually processed: 2000/2000
tps = 360.575016 (including connections establishing)
tps = 360.835167 (excluding connections establishing)

参考

http://h2np.net/tips/tips12.htm
単純明解かつちょっと乱暴だが筋は通っている。
http://journal.mycom.co.jp/articles/2004/09/22/postgres8/001.html
7.4以降でのパフォーマンスの改善について。(Fedora core 2は7.4.2、 Debian sargeは7.4.7なので対象内と思われる)
http://www.asahi-net.or.jp/~aa4t-nngk/pgsql5.html
チューニングまとめ。ただし貧弱なメモリ環境は対象にしていないらしい。1GB以上推奨
http://osb.sra.co.jp/PostgreSQL/Manual/PostgreSQL-7.1-ja/performance-tips.html
バージョン別マニュアルの入口
http://sonic64.com/cat_postgres.html
いろいろなバージョン、それに他所のdbシステムの話も混じっているので注意されたし。