Help us understand the problem. What is going on with this article?

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

参考:

awakia
検索とか推薦とかやってきたエンジニア。早稲田の山名研出身。大学院の頃、論文を書こうとしない僕を見かねた教授に、北京のMSRAに追放されるが3ヶ月後無事帰還。 大学を卒業後、エンジニアのブラックホールとの別名を持つGoogleに吸収されそうになるが1年2ヶ月後無事生還。 現在は、Wantedly(https://www.wantedly.com/ )の4番目のエージェントとして救出活動に専念。
http://awakia-n.hatenablog.com/
wantedly
「シゴトでココロオドル」ためのビジネスSNS「Wantedly」の開発・運営をしています。
https://wantedlyinc.com/ja/presentations
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away