PostgreSQL 実践講座 #2 コネクションプール — max_connections の誤解と PgBouncer

サービスが成長して最初にぶつかる PostgreSQL の運用問題は、たいていクエリではなくコネクションです。症状は決まっています。FATAL: sorry, too many clients already、あるいはコネクション数には余裕があるのに応答が遅くなる現象です。今回はこの問題の構造と、標準の解法であるコネクションプールを扱います。

PostgreSQL のコネクションは高くつきます #

前提になる事実を 1 つ。PostgreSQL はコネクションごとにサーバープロセスを 1 つずつ作ります。 スレッドではなくプロセスです。接続のたびに fork のコストと数 MB のメモリがかかり、コネクション数が増えるほどプロセス間の調整コストが大きくなります。ここから帰結が 2 つ出ます。1 つ目、リクエストごとに接続して切るパターンは災害です(接続コストがクエリコストを圧倒します)。2 つ目、max_connections を 5000 に上げるのは答えではありません。

2 つ目は直感に反します。ベンチマークで繰り返し確認されている事実は、同時にアクティブなコネクションが CPU コア数の数倍を超えると、総スループットがむしろ下がるということです。コア 16 個のサーバーが本当に同時に実行できるクエリは結局十数個で、それを超えるコネクションはコンテキストスイッチとロック競合を足すだけです。コネクション 1000 個が必要に見えるサービスの本当の要求は「1000 個のクライアントが十数個の実行スロットを行儀よく分け合うこと」で、その分け合いをやってくれるのがプールです。

1 層目: アプリケーションのプール #

最初の層はアプリのフレームワークのプールです(HikariCP、SQLAlchemy のプール、Go の database/sql など)。サーバー起動時にコネクションを N 個作っておいてリクエストが借りて使う構造で、「リクエストごとに接続」をなくしてくれます。SQLAlchemy 講座で扱ったあのプールです。アプリのインスタンスが数個なら、これで十分です。

問題は水平スケーリングです。インスタンスあたりプール 20 個 × インスタンス 50 個 = コネクション 1000 個になり、それぞれは行儀のいいプールなのに、合計が DB を圧迫します。サーバーレス(Lambda など)ならインスタンス数自体が制御不能なので、さらに深刻です。このとき必要になるのが 2 層目です。

2 層目: PgBouncer のような外部プーラー #

PgBouncer はアプリと DB の間に立つ軽量プロキシです。アプリたちにはコネクションをたっぷり渡しつつ、DB へは少数の実コネクションだけを維持します。核心の設定はプールモードです。

実務の既定値は transaction モードです。Web アプリのコネクションの大半は「借りたまま遊んでいる」時間なので、トランザクション単位でだけ実コネクションを占有させれば、数千のクライアントを数十の実コネクションで受けられます。代価は明確です。トランザクションが終わると、次は別の実コネクションを受け取るかもしれないので、セッションに状態を残す機能が使えません。 セッションレベルのプリペアドステートメント、SET で変えたセッション変数、セッション単位のアドバイザリロック、LISTEN などがここに引っかかります(ドライバや PgBouncer の最新バージョンでプリペアドステートメント対応は改善されてきましたが、「セッション状態は使えない」を基本感覚にしておくのが安全です)。

ちなみにマネージド環境では、同じ役割の商品が別にあります。AWS なら RDS Proxy がその席です。

適正サイズの感覚 #

では実コネクション(プールサイズ)はいくつが良いのか。出発点として広く使われる目安はコア数 × 2〜4 近くから始めて実測で調整することです(ディスク待ちの多いワークロードほど大きめ)。重要なのは方向です。性能が物足りないとき、プールを減らして改善する場合が実際に多いです。判断材料は次回扱う観測です。pg_stat_activity でアクティブ/アイドルのコネクション比率を見て、大半が idle ならコネクションは余っているのであって、足りないのではありません。

SELECT state, count(*) FROM pg_stat_activity GROUP BY state;

まとめ #

• PostgreSQL のコネクションはプロセス 1 つです。リクエストごとの接続は禁物で、max_connections の増設は解法ではありません。
• 同時アクティブなコネクションがコア数の数倍を超えるとスループットはむしろ下がります。必要なのはコネクションではなく、行儀のいい共有です。
• 1 層目はアプリのプールです。インスタンスが増えて合計が大きくなったら 2 層目、PgBouncer のような外部プーラーを立てます。
• PgBouncer は transaction モードが実務の標準です。代わりにセッション状態の機能(SET、セッションのプリペアドなど)を諦めます。
• プールサイズはコア × 2〜4 から始めて実測で調整します。pg_stat_activity の idle 比率が判断材料で、この観測ルーチンが次回の主題です。