はじめに
これは、PostgreSQL Advent Calendar 2024の1日目の記事となります。
この記事は 2024/04/30 に投稿したのですが、12/01時点で、いいね 0 ストック 0
1766 views とさびしい限りです。
有用な記事だと思っているので、トップバッターを務めさせて頂きます。
以前サーバーリプレース作業(Windows Server 2008R2 + Oracle 11g → Windows Server 2016 + PostgreSQL 9.6)に移行しました。
その際 Shared_buffersの設定値としてサーバーの搭載メモリ(16MB)の25％程度が目安のなか、Windows版に関しては公式ドキュメントに従い、最大の512MBに設定しました。
Windowsでもshared_buffersに対し大きな値を設定することはあまり有効でありません。 設定値を比較的小さく保ち、代わりにオペレーティングシステムのキャッシュを使用することが、より良い結果になるでしょう。 Windowsシステムでの有効なshared_buffersの範囲は一般的に64MBから512MBです。
https://www.postgresql.jp/document/9.6/html/runtime-config-resource.html
その後に PostgreSQL 9.6 から PostgreSQL 12 にアップグレードした際にも、そのまま設定を引き継ぎました。
問題
現象
先日、更新SQLを実行した際に、「Exception while reading from stream」エラーが発生し、トランザクションによりロールバックされ更新されない問題が発生しました。
これ自体は、接続文字列にCommandTimeout=600 (10分)が指定されていたことが原因でした。1800 (30分)に設定を変更したことで、最後まで更新されることが出来ました。
社内DBでは数分以内に終わる更新処理が、ユーザーのDB上では20分以上かかってしまうことが問題であり、ユーザーのDBを社内に構築してインデックスや設定見直し等のチューニングの調査を同僚に依頼しました。
調査
ユーザーのDB上は複数のスキーマが構築されています。問題となったスキーマ自体のデータ量は少なく、他1つのスキーマが巨大になっている状態です。
同僚の報告結果によるとインデックスの追加も少しは効果がありましたが、一番効果が大きかったのは、Shared_buffersの設定値を大きくしたことでした。なんと 20分 以上かかっていた更新処理が 2分 になったとのこと。
他にも統計情報など何かが影響している可能性があることは否定できません。
筆者は、Shared_buffersの設定値が 512MB が最大と公式資料の値を信じていたので、これは意外な報告でした。
本題
Windowsでもshared_buffersに対し大きな値を設定することはあまり有効でありません。 設定値を比較的小さく保ち、代わりにオペレーティングシステムのキャッシュを使用することが、より良い結果になるでしょう。 Windowsシステムでの有効なshared_buffersの範囲は一般的に64MBから512MBです。
https://www.postgresql.jp/document/9.6/html/runtime-config-resource.html
調べてみると、この文言は PostgreSQL 10以降の公式ドキュメントからは削除されていたのです。しかし、「EDB Postgres ドキュメントのshared_buffers」ページには最新版であっても文言が残ったままです。
この文言が消えた理由の根拠があればと思って、調べてみるとPostgreSQLの性能関連パラメータの推奨値を提示してくれる「PGTune」というツールの discussions に辿り着きました。
OS として Windows を選択した場合、Shared_buffers の値は常に 512Mb になります。
PG のバージョン 9.6 以降、このルールは無効になり (PG の専門家に確認済み)、RAM の 1/4 のルールが適用されるべきだと思います。
https://github.com/le0pard/pgtune/discussions/50
どうやら下記のPostgreSQLのメーリングリストのやり取りの中で、PostgreSQL 10以降のドキュメントから文言が削除されるに至ったようです。
2016-09-20 富士通株式会社 綱川 貴之さんが発信
pgbench の読み取り専用モードと読み取り/書き込みモードを実行しましたが、shared_buffers を増やしても明らかなパフォーマンスの低下は見られませんでした。
推奨値
現在では、PGTuneのWindows版 Shared_buffers の推奨値は、RAM の 1/4 のルールが適用されるように修正されています。
| 搭載メモリ | 推奨値 |
|---|---|
| 2GB | 512MB |
| 4GB | 1GB |
| 8GB | 2GB |
| 16GB | 4GB |
Shared_buffers が 12.5 % から 25% に増加すると、読み取り/書き込みパフォーマンスが低下するようです。というメーリングリストのやり取りもありますが、それは別のトピックであり、他の OS にも当てはまります。
最後に
今回、トラブルがなかったら気が付かなったし、もし筆者がトラブル担当をしていたら、Shared_buffers 512MB制限のままで設定を変更することもしなかった可能性が高いです。
この記事を見て、Windows版の Shared_buffers の設定を見直してみてはいかがでしょうか。