このエントリーをはてなブックマークに追加

PostgreSQLの設定 チューニング

PostgreSQL when it is not your job - Christophe Pettus経由で DjangoCon Europe 2012 で発表されたこのスライドを知ったので、メモ代わりに残してみる。件のblogは抜粋なので、ここでは可能な限り全部載せます。

最初に

気をつける設定はこれだけ。

  • ログ
  • メモリ
  • チェックポイント
  • プランナー

ログ

ログに対して寛容になること。システムへの影響は少ない。

ログは性能問題を解決するための一番いい情報。

どこにログを出す?

  • syslog:
    • すでにsyslogを使うシステムを構築してるならこれがいいでしょう
  • 標準形式のファイル:
    • 標準形式のファイルを使うツールをすでに使っていればこれ。
  • そうでなければ
    • CSVフォーマット

あとの設定はこれ。

log_destination = 'csvlog'
log_directory = 'pg_log'
logging_collector = on
log_filename = 'postgres-%Y-%m-%d_%H%M%S'
log_rotation_age = 1d
log_rotation_size = 1GB
log_min_duration_statement = 250ms
log_checkpoints = on
log_connections = on
log_disconnections = on
log_lock_waits = on
log_temp_files = 0

メモリ

  • shared_buffers
    • 2GB以下: 全メモリの20%
    • 32GB以下: 全メモリの25%
    • 32GB以上: 8GB
  • work_mem
    • 32〜64MBから始める
    • logの中で'temporary file' という行があるか眺める
    • もしあれば、2、3倍ずつしていく
    • ちゃんと設定すればめちゃくちゃ早くなる
    • でも、プランナーノードごとにこの量のメモリを使うので注意して
  • maintenance_work_mem
    • システムメモリの10%。最大1GB
    • もしVACUUMの問題があればもっと大きく
  • effective_cache_size
    • 利用可能なファイルシステムキャッシュと同じ量
    • 分からなければ、全システムメモリの50%に

チェックポイント

チェックポイントはバッファにあるデータをディスクに書き出す処理。大量のI/Oを引き起こす可能性がある。

以下の二つの条件のうちどちらかが発生した時にチェックポイントが発生する。

  • 適切な量のWALセグメントがかかれたとき
  • タイムアウトが発生したとき

で、設定。

wal_buffers = 16MB
checkpoint_completion_target = 0.9
checkpoint_timeout = 10m-30m # 再起動時間に依存
checkpoint_segments = 32 # この値から始める

こうしておいて、ログ中にcheckpointというエントリがあるか見る。

checkpoint_timeoutよりも多く発生しているなら、checkpoint_segmentsを調整する。これにより、WALセグメントが埋まるよりもタイムアウトによってcheckpointが発生するようになる。

ちなみに、以下の点に注意

  • WALは3 x 16MB x checkpoint_segments 分だけディスク領域を消費する。
  • PostgreSQLの再起動は最大checkpoint_timeoutかかる。(でも普通もっと短い)

プランナー

  • effective_io_concurrency

    I/Oチャンネルの数に合わせる。でなければ無視

  • random_page_cost

    • 典型的なRAID 10: 3.0
    • SAN: 2.0
    • Amazon EBS: 1.1

ここまでが設定の話なので、とりあえずいったんここまで。