更新処理が多いシステムでのチューニング
PostgreSQL8系を利用して更新処理の多いシステムを作成するときのパラメータチューニングについて書きます。
参考：http://www.thinkit.co.jp/free/tech/10/2/1.html
[システム情報]
・単一テーブルに数百万レコードが格納されている
・更新処理頻度(INSERT/UPDATE/DELETE)が高い
[アプリケーション側の処理]
１．SELECTによりデータを取得
２．１の結果を元にUpdate/Insertを実行
３．Commit間隔は500件
[改善したい点]
アプリケーション処理能力をもっと高めたい。
[検証]
１．SELECT性能
Explainの結果、Seq Scanではあったが遅くとも１０秒程度でレスポンスがある。
また２回目以降はSELECT結果がキャッシュされるのでSELECT性能には大きな問題はない。
ただし１点懸念があった。
強制的にインデックスを使用させると、コスト上は高いが実測では早い。
つまりプランナとデータに乖離がある。
プランナの解析情報をより細かく設定してみたが、変わらなかったのでこの部分についは未解決である。
・強制的にインデックスを使用する
SET enable_seqscan TO off; # シーケンシャルスキャンをしない
http://www.postgresql.jp/document/pg732doc/user/indexes-examine.html
・プランナの情報をきめ細かくするにする
参考：http://ml.postgresql.jp/pipermail/pgsql-jp/2003-March/013076.html
１．SQL
ALTER TABLE table1 ALTER COLUMN t1_key_p1 SET STATISTICS 20;
２．postgresql.conf
cd $PGDATA vi postgresql.conf default_statistics_target = 100 # range 1-1000 pg_ctl restart
default_statistics_targetは、デフォルト１０．
サイズを大きくするほどプランナの解析する情報がきめ細かくなる。
トレードオフとして、(Vacuum) Analyzeに時間が掛かるようになる。
プランナの情報を確認するには↓
SELECT relname, relkind, reltuples, relpages FROM pg_class WHERE relname LIKE '%テーブル名%';
参考：http://www.flatz.jp/var/manual/postgres/planner-stats.html
ここが本題、SELECT性能には問題なとして、UPDATE性能について調べたところ↓があった。
参考：http://www.thinkit.co.jp/free/tech/10/2/1.html
トランザクションログ(wal_buffers)と共有バッファ(shared_buffers)が大きく関わるとのこと
[変更前]
shared_buffers = 32768 # 256M wal_buffers = 4096 # 32M
[変更後]
shared_buffers = 65536 # 512M wal_buffers = 4096 # 32M
処理データサイズからいって、wal_buffersのサイズは問題ないだろうと見積もった。
具体的にはcommit時の最大データサイズを考えると32Mも使わないから、もっと少なくても良いかも？
結果として、shared_buffersを256M -> 512Mに変更することで更新処理性能が向上した。
PostgreSQLでは、SELECT/INSERT/UPDATE/DELETE データを共有バッファに格納するが、
運用しているＤＢサイズ、システムにとっては共有バッファが足りずに、ページングが発生していたのだと思われる。
[まとめ]
今後データ量が増加した場合は、shared_buffersを大きくしてみるとよいでしょう。
ただし、大きくとりすぎるとメモリ利用時のオーバーヘッドが大きくなってしまうので、
高性能なマシンなら1/2。
廉価版なら1/4程度を最大サイズと見積もるのが良いでしょう。
