LoginSignup
201

More than 5 years have passed since last update.

PostgreSQLのチューニング事例

Last updated at Posted at 2013-02-27

事前情報

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を超えることはないため、大きすぎる場合は設定しなおしたほうが良い。

ログ書き出しの設定

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

参考:

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
201