北村聡士のブログ

  1. ホーム
  2. プログラミング
  3. データベース

PostgreSQLのパフォーマンスチューニング

PHPもPostgreSQLもやったことないのに、そんなシステムを作り直すことになり、運用し始めたら、パフォーマンスがどんどん低下していくというワナにはまったので、ちゃんとメモしておく。PostgreSQLのチューニングには、postgres.confで設定値を変更する方法や、定期的にVACUUMする方法などがあるらしい。今回作ったシステムは、参照よりも更新レコード数が多い場合なので、一日一回VACUUM、REINDEXするという方法が効果的だった。これでも遅くなるようならダンプ&リストアしかないかもしれない。

チューニング方法

  1. 測定
  2. postgres.conf
  3. トランザクション
  4. VACUUM
  5. REINDEX
  6. ダンプ&リストア

システム概要

  • PHPスクリプトをcronで定期実行
  • 一日の更新レコード数は100万件～150万件程度
  • PostgreSQL 7.3（既存DBを使う条件なので、新しいバージョンは使えず）
  • 深夜はスクリプトを実行しないので、その間にメンテナンス可能
  • 決してDELETE文は使わない

まずは測定から

何が遅くて、どこを速くしたいかをまず把握すること。参照系で遅いSELECT文があるなら、該当するSQLをチューニングすればいいし、あるいはINDEXを作ってみるなど対策ができる。今回はSELECT文もUPDATE文も遅かったが、PHP側で出力しているログを確認すると、更新レコード数が圧倒的に多く、時間がかかっていたので、いかにして更新を速くするかを調べてみた。

トランザクション

DBの話というか、プログラムの書き方というか。登録・更新対象となるデータをあらかじめまとめておいて、処理の最後にまとめてINSERT・UPDATEするようなスクリプトを書いていたら、io waitが99%になっていた。トランザクションにまとめて、最後にコミットすれば、かなり負荷が減った。

postgres.confの設定

postgres.confというのが、PostgreSQLの設定ファイルのひとつ。この設定ファイルには、パラメータを設定することができ、その設定値によってパフォーマンスが変わってくる。設定したパラメータは以下の通りだが、設定方法は参照先にまとまっている。ありがたや。

max_connections

最大接続数。必要最小限にすること。

max_fsm_pages

FSMの最大ページ数。FSMはfree space mapという再利用可能領域のこと。VACUUMによってゴミ掃除が終わったあとにできる未使用領域を、FSMという領域で管理している。この設定値が少ないと、再利用可能領域があるにもかかわらず、新規に行が追加されてしまい、データベースの肥大をまねくことになる。更新するレコードが多い場合は、大きくしておいた方が良いが、あまり大きくすると、FSMの検索に時間がかかるようになるので、やりすぎは禁物。

sort_mem

ソート処理に使用するメモリ量 (KB) 。

shared_buffer

カーネルの共有メモリのうち、postmasterが占有するメモリ量をブロックサイズで指定するもの。

wal_buffers

PostgreSQLには、トランザクションログをバッファファイルにためておく仕組みがあるそうだ。これをWAL(write-ahead logging)といい、バッファファイルのことをWALログというらしい。トランザクションログをバッファリングするメモリ量を設定する。

checkpoint_segments

WALログの書き出し頻度を制御する。セグメントは16MB単位。

VACUUM

PostgreSQLでのUPDATEは、一度DELETEしてから(削除フラグを立ててから)INSERTする追記型アーキテクチャなので、UPDATE文が大量に発行されるとゴミがたまるそうだ。それを取り除いたりするのがVACUUMというものらしい。

vacuumdb -f -a -z

を一日一回実行することにした。

REINDEX

VACUUMだけだと、やっぱりだんだん処理速度が遅くなってしまったので、さらに調べてみたら、INDEXにもゴミがたまることが分かった。これはVACUUMでは取り除けないらしい。そこで実行するのがREINDEX。

psql -d dbname -c "REINDEX TABLE hoge;" username

こいつも一日一回実行することにした。

ダンプ&リストア

これでも遅くなるようだったら、最後はこれ。レコードを全部ダンプして、リストアすると、元の処理速度に戻る。定期的にメンテナンス時間があるとか、システムが動いていない時間があるようだったら、これを実行するのが、一番手っ取り早いメンテナンス方法なのかもしれない。もちろん、根本的な解決にはなっていないので、遅くなる原因を追究することは必要だと思う。

参考