PostgreSQLのチューニング事例

  • 160
    いいね
  • 0
    コメント
この記事は最終更新日から1年以上が経過しています。

事前情報

m3.xlargeインスタンス

15GB メモリ
13 ECU = 3.25ECU(2.6GHz) * 仮想4コア

DBサイズ

3.38 GB

設定値

/etc/postgresql/9.1/main/postgresql.conf
max_connections = 100
shared_buffers = 3GB # 実メモリの20%

# ここらへんのはとりあえずこのくらいで。小さすぎる分にはlog_temp_filesの設定で書き出されるので後で気づいて調整できる。
temp_buffers = 32MB
work_mem = 32MB
maintenance_work_mem = 128MB

wal_buffers = 16MB  # 迷いどころもなく16MB

checkpoint_segments = 16  # HDへのフラッシュ回数を減らす
effective_cache_size = 7GB  # planerのため。実メモリの半分ぐらいがいいらしい
/etc/sysctl.conf
# 7GB max shared mamory
kernel.shmall=1835008
kernel.shmmax=7516192768

してsudo sysctl -p, sudo /etc/init.d/postgresql restart

メモ

  • shared_buffersの経験則は実メモリの20%-30%、もしくは頻繁にアクセスするテーブルのデータが載る程度
  • shared_bufferを50%以上にするとカーネルのキャッシュが溢れスワップする
  • work_memの最大は(実メモリ-shared_buffers)/max_connections。そうでないと確実にスワップする
  • work_memの経験は実メモリ/max_connections/[4-16]
  • log_temp_filesの設定により、一時ファイルを使った場合Logに書き出すことが出来る
  • wal_buffersは-1でshared_bufferの1/32になる。
    • 通常のWALセグメントの大きさである16MBを超えることはないため、大きすぎる場合は設定しなおしたほうが良い。

ログ書き出しの設定

http://www.postgresql.jp/document/9.2/html/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHERE

/etc/postgresql/9.1/main/postgresql.conf
#log_destination = 'stderr'
logging_collector = on
log_rotation_size = 1GB
#log_min_messages = warning
log_min_error_statement = warning  # log_min_messages似合わせる
log_line_prefix='[%t][%p][%c-%l][%x][%e]%q (%u, %d, %r, %a) ' #とりあえず全部の情報を出しておけば良いのではという発想 
log_min_duration_statement = 500  # 0.5秒以上かかった問い合わせを記録
log_temp_files = 1MB  # 1MB以上の一時ファイルを作ったらログに書き出す

残存プロセスの除去

AP側が落ちたり、APとのNWが断絶された際、APからの応答をずっと待ってしまうDB側のプロセスが残ってしまう。こうなるとConnection数がMaxに達したり、ロングトランザクション化したりする問題が起こる。タイムアウトを短めにしてこれを防ぐ。

tcp_keepalives_idle = 60  # keep alive パケット送出までの待機時間(デフォルト7200秒)
tcp_keepalives_interval = 5  # KeepAliveパケット送出に応答がなかった場合の再送間隔(デフォルト75秒)
tcp_keepalives_count = 6  # KeepAliveパケット送出回数の上限(デフォルト9)

この設定では1分半で残存プロセスを除去

参考: