Y-110's Wiki

_ postgresql.conf チューニング

PostgreSQL 基本的なチューニングは, 修正する項目と値がある程度決まっています。
チューニングに関しては数値はこんなもんだ, と割り切って設定しましょう。

ただ, 項目毎の意味を理解することは, PostgreSQL の動作を理解する上で重要ですので, 数字だけではなく, 意味もしっかりと把握して下さい。
その上で, サーバの状態を確認しながら適切なチューニングを行えるようになりましょう。

PostgreSQL 完全機能リファレンスは, 設定ファイルの各項目の解説の他, PostgreSQL内部の動作等が詳しく載っていますので, PostgreSQL を深く理解するにはオススメの書籍です。

_ shared_buffers

7系 では, 性能のピークは 8000〜10000(約80M) の範囲にあります。
shared_buffers を多く取りすぎると, バッファ管理のオーバーヘッドが生じて, 逆に性能が低下してしまいます。

8系では shared_buffers の性能が改善され, 150000程度までは性能が低下しないようです。
性能のピークは 100000(約800M)付近にあるようです。

8系ではメモリをたくさん積んで shared_buffers を多めに確保したほうが良いでしょう。

ちなみに, PostgreSQL が使用している共有メモリは ipcsコマンド で確認することができます。 bytes が共有メモリサイズ, nattch は共有メモリにアクセスしているプロセス数で, 接続数とほぼ等しくなります。

# ipcs -m
------ Shared Memory Segments --------
key        shmid      owner      perms      bytes      nattch     status
0x0052e2c1 0          postgres  600        88162304   2

shared_buffers を増やすと大抵の場合, ログに下記のようなエラーが出ます。

FATAL:  could not create shared memory segment: Invalid argument

これは１つのプロセスが確保できる共有メモリの最大サイズを超えている場合に起こる典型的エラーです。
Linux の場合は /proc/sys/kernel/shmmax に shared_buffers で使用する共有メモリサイズより大きい値を指定しましょう。*1
例えば共有メモリを 160M 確保したい場合は, 以下のようにバイトで設定します。

# echo  167772160 > /proc/sys/kernel/shmmax
(167772160 = 160 * 1024 * 1024)

設定値を確認するには, ipcs -ml で確認します。

# ipcs -ml
------ Shared Memory Limits --------
max number of segments = 4096
max seg size (kbytes) = 163840
max total shared memory (kbytes) = 0
min seg size (bytes) = 1

postmaster を不正に kill したりすると, 共有メモリに PostgreSQL のゴミが残る場合があります。
その場合は, ipcrmコマンドを使って手動で共有メモリ領域のゴミを削除します。

# ipcrm -m 32768

_ max_connections

7系では 256 あたりが性能のピークのようです。*2
最大でも 512位にとどめておいたほうが良いのではないかと。

8系では 1000程度でもそれほど性能は落ちないようです。
特に 8.1 ではバッファマネージャの根本的な改良を行っており, 同時接続時の処理性能が大幅に向上しています。

サーバに積んでいるメモリを考慮した上で, max_connections はスワップしない程度に抑えてください。
接続時に消費する最大メモリの見積もりは, 以下の式で算出できます。

postmaster のサイズ × max_connections

_ deadlock_timeout

デッドロック検出処理は比較的重い処理です。
SQL がロック待ちに入り, deadlock_timeout(ms) 経過した段階でデッドロック検出処理が走ります。
デフォルトの 1000ms はかなり早めに設定されていて, ちょっと重いトランザクションが走ってしまうとデッドロックではないのにデッドロック検出処理が走る可能性があります。

目安として 1000(ms) * 最大同時セッション数 を設定しておくとよいでしょう。

そもそもデッドロックを起こすようなアプリケーションを書くべきではないので, 適当に長めに設定してもいいかも知れません。

_ effective_cache_size

カーネルや PostgeSQL の共有バッファなど, PostgreSQL が使用するバッファ領域の大きさの推定値です。
effective_cache_size * 8192 がバイトに換算した値になります（デフォルト値は8MB）。
参照するテーブルが effective_cache_size の範囲内に収まっていると, オプティマイザは積極的にインデックスを使うようになります。

数G のメモリを積んだマシンでは, メモリ総量の 1/4〜1/2程度に設定しておきましょう。

_ wal_buffers

WALデータ用の共有メモリ内のバッファ数です。
WALデータはトランザクションコミット時かバッファが溢れるとディスクに書き出されるため, トランザクションが同時に多数走る場合や, 一つのトランザクションが大きい場合 は, 大きめに設定したほうが書き込み効率がよくなります。
デフォルトは 8(* 8kb = 64kb) ですが, バッファを大きめに取っても大してメモリを圧迫しませんので 32〜64 くらいに設定しておけばよいのではないでしょうか。

_ wal_sync_method

WAL更新のディスクへの書き込み方法を指定します。
プラットフォームによっていくつかの選択肢(fsync, fdatasync, open_sync, open_datasync等)がありますが, どれが最適かは実際にベンチマークをとってみないとわからないようです。

同じ条件で pgbench を行い, 一番早い方法を選択しましょう。
ちなみに fsync が無効になっている場合は, この項目は無視されます。

_ commit_delay

通常はトランザクションがコミットされるタイミングで WAL にコミットレコードが書き込まれますが, 同時に実行されるトランザクション数が多い場合は書き込み処理が頻繁に行われ, 効率が良くありません。
commit_delay を設定すると, コミットしてから commit_delay(ms) だけ待って WAL に書き込み処理が行われます。

同時トランザクション数が多いサイトの場合, 0以上の値を設定すると書き込み効率が上がります。
ただし, アクティブなトランザクションが commit_siblings よりも少ない場合は遅延処理は行われず, すぐにログに書き込まれます。

_ random_page_count

データベースのテーブルの１ページへのアクセス時間を基準として, インデックスを利用したアクセスの場合にはどのくらい時間がかかるか, という目安の数値です。
数G のメモリを積んだマシンでは 2〜3 程度が適当で, デフォルトの 4 では大きすぎるようです。*3

値を小さくすると, インデックススキャンのコストが下がるため, オプティマイザがインデックスを使いやすくなります。
関連する項目として, １つのインデックスタプルの処理コストは cpu_index_tuple_cost で指定可能です。

_ max_fsm_pages

PostgreSQL は更新・削除が行われても, 古い行はそのまま残る追記型の RDBMS です。
この古くなった不要領域は, VACUUM を実行するによって共有メモリ上の FSM(Free Space Map) に記録されます。
更新・削除を行う場合は, FSM を検索して不要領域を再利用します。

FSM のサイズは max_fsm_pages で指定します。
FSM が小さすぎると再利用できない領域が増えていき, データベースクラスタの容量がどんどん膨れ上がります。
また, FSM が大きすぎても検索オーバーヘッドが大きくなり, パフォーマンスが下がります。*4
マシンスペックが厳しい場合, max_fsm_pages は 10000程に抑えておき, 不要領域をきちんと回収できるように VACUUM の回数を増やして対応しましょう。

全ての不要領域を回収するために必要な FSM のサイズは, vacuumdb のログを確認しましょう。
8.1 の場合は下記の様に表示されます。

$ vacuumdb -a -z -v
...

DETAIL:  A total of 3136 page slots are in use (including overhead).
3136 page slots are required to track all free space.
Current limits are:  200000 page slots, 1000 relations, using 1237 KB.
VACUUM

この結果, 全ての不要領域を回収するために必要なページ数は 3136 ページであることがわかります。

max_fsm_pages より必要なページ数の方が多い場合は,

• max_fsm_pages を必要なページ数より多く設定する
• VACUUM FULL を行って不要領域を完全に削除した上で, VACUUM の頻度を上げる

といった方法があります。

運用の段階で必要な FSM のサイズは変わっていく可能性がありますので, VACUUM を行う際に定期的にログを確認して FSM が十分かどうかを確認することが大事です。

関連する項目として max_fsm_relations があります。
こちらはデータベース(群)の最大テーブル数を指定します。
データベースが複数ある場合は, それぞれのデータベースに属するテーブルを全て合計した値を指定します。*5
デフォルトは 1000 ですので, テーブル数が多い場合は適宜修正して下さい。

実際にどのくらいのテーブルがあるのかは, VACUUM のログにも出力されますので, こちらも参考に。

INFO:  free space map contains 18471 pages in 143 relations