Cloud SQL for PostgreSQL のリソース管理のベストプラクティス関連ドキュメントを読む の実践編。

課題

• Cloud SQL で週数回のペースでメモリ使用率 70% を超えたり、コネクション数が 500 程度に達したりしており、なんとかしたい
• サーバーレスで50を超える Cloud Run サービスで別々にコネクションプールを持っていてコネクション管理が煩雑になっている

アプローチ

1. コネクション数を増やしている原因を取り除く
   • リクエスト過多か？スロークエリか？
2. 1. ですぐ改善しない場合は Cloud SQL が OOM kill されないようになんとかする
   • メモリ管理ちゃんとやる

今コネクション何個までなら耐えられるのか？

https://katsusand.dev/posts/postgresql-memory/ を参考に考える。
total = work_mem x max_connections + shared_buffers ではない。

work_mem = (RAM - shared_buffers) / ((max_connections * 3) / max_parallel_workers_per_gather) らしい。
つまり RAM = shared_buffers + work_mem * max_connections * 3 / max_parallel_workers_per_gather

max_parallel_workers_per_gather はなんで割ってるのかあんまわかってないのと、*3 もしっくりきてないけど時間がないので一旦信じる。

max_connections に掛けられている 3 の係数ですが、これは上述した「1 クエリでの並び替えや JOIN, GROUP BY のハッシュ操作の数」であり、これも自分が運用するシステムによってある程度決めるべきでしょう。大概は 3 程度あれば十分なように思います。
max_parallel_workers_per_gather は、CPU コア数の 1/2 とするのがベストプラクティスのようです。

SHOW flag_name; で設定値を確認できる
今は、

• shared_buffers = 2650MB
• work_mem = 4MB
• max_parallel_workers_per_gather = 2
• max_connections = 1000
  なので、RAM = 2650MB + 4MB * 1000 * 3 / 2 = 8650MB
  今メモリが 8GB なのでややオーバーしていた。

結論、一旦 max_connections を 800 に下げることにする。(7450MBになる)

PGTune で CPU2, 8GB で max_connections 800 計算すると work_mem が1.7MBで出てしまった。
が、effective_cache_size とかの値が小さめに設定されてたから一旦耐えとかないかな。

# DB Version: 15
# OS Type: linux
# DB Type: web
# Total Memory (RAM): 8 GB
# CPUs num: 2
# Connections num: 800
# Data Storage: ssd

max_connections = 800
shared_buffers = 2GB
effective_cache_size = 6GB
maintenance_work_mem = 512MB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 1310kB
huge_pages = off
min_wal_size = 1GB
max_wal_size = 4GB