Edited at

PostgreSQLのチューニング事例

More than 5 years have passed since last update.


事前情報

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分半で残存プロセスを除去


参考: