基本的にはDBの利用のされ方やサーバのスペックによって最適なパラメータは変化します。
ただし、ここだけは、ほとんどの場合にデフォルトから変更したほうがパフォーマンスが上がると思われるパラメータを紹介します。
共有メモリバッファ（shared_buffers）
テーブルやインデックスのデータをキャッシュしておく領域です。
DB全体の性能に関わります。
データアクセス時にデータをメモリ上に展開しキャッシュすることで、繰り返しアクセス時の処理性能が向上します。
また、データの更新処理時にも更新内容は一時的にメモリ上に反映され、実際のディスク上のデータファイルに反映されるまではタイムラグがあります。（後述のチェックポイント処理）
デフォルト値は8MBしかありません。専用サーバーの場合はシステムメモリーの25%から30%程度を割り当てます。
また、単純に量を増やせばよいわけではなく40%以上を割り当てても逆に他の処理に利用可能なメモリが減ってしまい、逆効果となる場合があります。
設定変更にはDBの再起動が必要となるのでサーバーを本格稼働させる前に必ず設定変更しておきましょう。
# 例)16GBメモリを搭載したサーバーなら4GB
shared_buffers = 4GB
チェックポイント関連
上記、富士通様の記事が非常にわかりやすくチェックポイントの仕組みを解説されています。
更新系のSQLを実行すると、まずはメモリ上に書き込まれ、その後WALファイルと呼ばれる一時ファイルに書込されます。
その後、一定時間の経過、もしくはWALファイルが一定容量たまると実際のデータファイルにデータが書込される仕組みとなっています。
WALファイルから実際のデータファイルへの書込をチェックポイントと呼んでいます。
チェックポイント処理は負荷の高い処理なので、チェックポイント処理の頻度を低くして、処理の実施時もなるべく時間をかけて書込をすることで負荷を分散させる必要があります。
おもに以下の３つを変更します。いずれも設定ファイルの再読み込みで反映可能でDBの再起動は不要です。
- チェックポイント間隔(
checkpoint_timeout) - WALファイルの最大サイズ(
max_wal_size) - チェックポイント処理の負荷分散度合い(
checkpoint_completion_target)
チェックポイント間隔(checkpoint_timeout)
デフォルトで５分ですが、一般的には３０分くらいにしておけば良いです。
長くしすぎるとチェックポイントまでにたまるWALファイルサイズが大きくなって障害発生時の復旧に必要な時間が長くなります。
checkpoint_timeout = 30min
WALファイルの最大サイズ(max_wal_size)
デフォルトは1GBです。
共有メモリバッファやチェックポイント間隔を大きくしても、ここが小さいとボトルネックになるので基本はセットで設定変更しましょう。
チェックポイント間隔のあいだにどれだけWALファイルに書込されるかで設定値を試算します。
max_wal_sizeの1/3～1/2を超えるとチェックポイントが強制実行されるので、チェックポイント間隔のあいだ増えたWALファイル量の３倍程度が目安となります。
チェックポイントが強制実行されると、前のチェックポイントでの書込処理が終わる前に次のチェックポイントが実行されるわけで、計画が狂っちゃうのでファイルサイズ契機でのチェックポイントは発生しないようにしなくてはいけません。
基本的には上記のチェックポイント間隔ごとにデータファイルに書き込みされるように、ディスクの空き容量が多くあるのであれば十分に大きな値にしておいてもよいと思っています。
max_wal_size = 30GB
チェックポイント処理の負荷分散度合い(checkpoint_completion_target)
デフォルトは0.5(50%)です。次のチェックポイントまでの時間の半分の時間をかけてWALファイルからデータファイルに書込します。
逆に残りの50%の時間は何も処理していないことになります。
大体90%に設定すればよいです。
checkpoint_completion_target = 0.9
設定値の確認
pg_settingsビューで各種設定値の確認が可能です。
- 現在セッションでの値(
setting) - デフォルト値(
boot_val) - 設定値(
reset_val) - デフォルトから変更されている場合の設定ファイルと設定箇所(
sourcefile,sourceline) - 設定変更の反映方法(
context) - など
contextの意味
| contextの値 | 意味 |
|---|---|
| internal | 基本変更不可、一部はDB作成時に設定できる |
| postmaster | DBサーバの再起動が必要。pg_ctl restart
|
| sighup | 再読み込みによる反映が可能。pg_ctl reload
|
| superuser | スーパーユーザ－経由のSETコマンドで変更可能。ALTER SYSTEM SET xxxx=xxxx
|
| user | 一般ユーザー経由のSETコマンドで変更可能。ALTER SYSTEM SET xxxx=xxxx
|
最後に
何事にもそうですが
必ず公式のマニュアルを参照し、実際に検証したうえで設定値を決めることが重要です。
記事は参考程度でお願いします。