コネクションプールを大きくすると損をする理由 — 待ち行列をどこに立てるか

はじめに — プールサイズを増やしたのになぜもっと遅くなったのか
コネクションはなぜ高価な資源なのか
プールを大きくするほど遅くなる原理 — 待ち行列はデータベースの外に立てよ
よく引用される公式の根拠と限界
インスタンス数かけるプールサイズ — マイクロサービスの典型的な事故
PgBouncerの三つのモードと、トランザクションモードで使えないもの
コネクションリークの診断とサーバーレスの特殊性
おわりに — 公式ではなく測定、そして待ち行列の位置

はじめに — プールサイズを増やしたのになぜもっと遅くなったのか

負荷テストで応答時間が跳ね、ログにコネクション取得のタイムアウトが記録されます。自然な対応はプールサイズを増やすことです。20から50へ、それでも駄目なら100へ。

ところがスループットはそのままか、むしろ落ち、p99の遅延はさらに悪化します。データベースのCPU使用率は100パーセントに張り付いているのに、秒あたりの処理件数は増えません。

この現象はチューニングの失敗ではなく、予定された結果です。コネクションプールは性能を増やす装置ではなく、並行性を制限する装置だからです。プールを大きくするとは制限を緩めることであり、制限を緩めれば待ち行列がプールからデータベースの中へ移っていきます。待ち行列の位置が変わるだけで仕事の総量はそのままなのに、データベースの中の待ち行列ははるかに高価です。

この記事ではその理由を説明し、サイズを決める実際の手順とよく引用される公式の限界、そしてPgBouncerとサーバーレス環境で変わるものを整理します。

コネクションはなぜ高価な資源なのか

PostgreSQLは接続ごとにオペレーティングシステムのプロセスを一つずつ作ります。スレッドではなくプロセスです。この設計は安定性の面で利点がありますが、コストも明白です。

-- コネクション一つが実際にプロセス一つなのかを確認
SELECT pid, backend_type, application_name, state
FROM pg_stat_activity
WHERE backend_type = 'client backend'
LIMIT 5;

# 同じpidがOSのプロセス一覧にそのまま存在する
ps -o pid,rss,command -p 24188
#   PID    RSS COMMAND
# 24188  11284 postgres: api shop 10.0.3.21(52144) idle

コストは三つの層に分かれます。

第一に、生成コストです。新しい接続のたびにfork、認証、カタログキャッシュの初期化が起きます。ローカルでも数ミリ秒、ネットワークとTLSハンドシェイクを含めれば数十ミリ秒です。プールを使う一番目の理由がこれです。

第二に、メモリです。アイドル状態のバックエンドもカタログキャッシュと実行計画キャッシュのために数メガバイトを占め、長く生きてさまざまなクエリを処理したバックエンドはもっと大きくなります。ここにwork_memが掛かります。work_memはコネクション単位ではなくクエリ内のソートやハッシュ演算1つあたりに割り当てられるので、コネクション200個にwork_memが64MBで、クエリごとにソートが二回なら理論上25GBまで行きえます。

第三に、コネクション数に比例する内部コストです。スナップショットを作るときに実行中のトランザクション一覧を巡回しなければならず、ロック管理のデータ構造も大きくなります。PostgreSQL 14でスナップショット取得の経路が大きく改善され、アイドルコネクションの負担はかなり減りましたが、アクティブなコネクション数に応じたコストは依然として残っています。ここにOSのコンテキストスイッチが加わります。実行可能なプロセスがコア数を大きく上回ると、CPUは仕事の代わりに切り替えに時間を使います。

# コンテキストスイッチが急増しているか観察する
vmstat 1 5
# procs -----------memory---------- ---system-- ------cpu-----
#  r  b   swpd   free   buff  cache   in     cs  us sy id wa st
# 68  0      0 2104928  88420 9821004 42118 318442 71 26  3  0  0

r列がコア数を大きく超え、csが数十万なら、データベースは仕事ではなくスケジューリングをしている状態です。

プールを大きくするほど遅くなる原理 — 待ち行列はデータベースの外に立てよ

原理は待ち行列理論一つで説明できます。リトルの法則によれば、平均の同時処理件数はスループットと平均応答時間の積です。

同時処理件数(L) = スループット(X) × 応答時間(W)

データベースの物理的な処理能力はコア数とディスク帯域幅で決まっています。この限界に達したあとで同時要求を増やすと、スループットXはそれ以上増えず、代わりに応答時間Wが比例して伸びます。つまり同じ仕事をしながら、すべての要求の遅延だけが大きくなります。

これに加えて、並行性が高くなると実際にはスループットが減少し始めます。

コンテキストスイッチが有効なCPU時間を蝕みます。
同じ行やインデックスページをめぐるロック競合が増えます。競合は並行性の二乗に近い勢いで増加します。
バッファキャッシュを奪い合ってキャッシュヒット率が下がります。
デッドロックと直列化失敗が増え、再試行の負荷が乗ります。

ですから待ち行列はどこかに必ずでき、問題は位置です。プールで待てば何の資源も消費せず順番どおりに処理され、待ち時間が指標として表に出ます。データベースの中で待てば、プロセスとメモリとロックを握ったまま待ち、互いを遅くします。

核心を一文にまとめるとこうです。プールサイズはデータベースが同時にうまく処理できる件数であるべきで、アプリケーションが送りたい件数であってはなりません。

実際の観察方法は簡単です。プールサイズを固定して負荷を上げながら、二つの指標を一緒に見ます。

-- データベースが実際に同時に何をしているのか
SELECT state, wait_event_type, count(*)
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY 1, 2
ORDER BY 3 DESC;

 state  | wait_event_type | count
--------+-----------------+-------
 active | LWLock          |    41
 active |                 |     8
 active | Lock            |    22
 idle   | Client          |    64

activeなのにLWLockやLockを待っているバックエンドが、実際に働いているバックエンドよりはるかに多いなら、プールはすでに大きすぎます。ここでプールをさらに大きくすると、この比率はもっと悪くなります。

よく引用される公式の根拠と限界

最も広く引用される式はこれです。

プールサイズ = (コア数 × 2) + 有効なディスク軸(spindle)数

根拠は明確です。コア数の分は実際に計算を行い、その二倍を取るのは、ディスクやネットワークの待ちでコアが遊ぶときに別の要求が埋められるようにする余裕分です。最後の項は並列ディスク入出力の能力を反映します。8コアのサーバーならおおむね20前後になります。

この式が与えるメッセージは数字そのものではなく桁です。答えは数百ではなく数十だということ。8コアのデータベースにプール200個をつないでいるなら、その設定はほぼ確実に間違っています。

ただし公式をそのまま使えない条件がいくつかあります。

第一に、アプリケーションとデータベースの間の往復遅延です。同じアベイラビリティゾーンなら0.2ms程度ですが、ゾーンをまたぐと1msを超えます。トランザクション一つに文が十個なら往復だけで10msになり、その間コネクションは占有されているのにデータベースは遊んでいます。こうしたワークロードは公式より大きなプールが必要です。正確にはプールを大きくするのではなく往復の数を減らすべきです。

第二に、トランザクションの中でアプリケーションがすることです。外部API呼び出しやファイル処理がトランザクションの途中に入ると、コネクションの占有時間が爆発します。この場合、必要なプールサイズは公式と無関係になり、解法もプールサイズの調整ではありません。

第三に、ワークロードの混在です。OLTPと分析クエリが一つのプールを使うと、重いクエリ数個がプール全体を塞ぎます。このときはサイズ調整ではなくプールの分離が答えです。

# 用途別にプールを分ける
pools:
  web:      { size: 12, timeout: 3s }    # ユーザー応答の経路
  batch:    { size: 4,  timeout: 60s }   # 夜間バッチ
  readonly: { size: 8,  timeout: 10s }   # レプリカ検索

ですから実際の手順は公式で始めて測定で終えなければなりません。

公式で初期値を決めます。8コアなら20前後です。
目標負荷をかけて、プール待ち時間のp99と全体スループットを記録します。
プールサイズを半分に減らしてみます。スループットが維持されるなら、元の値が大きすぎたという意味です。
スループットが増えなくなる地点を見つけるまで増やしてみます。スループットが平らになり始めた値より少し小さいところが適正です。
その値でプール待ち時間が依然として大きいなら、プールではなくクエリを直すべきです。

5番が特に重要です。コネクション待ちが長いということはコネクションの占有時間が長いということであり、それはたいてい遅いクエリか長いトランザクションです。プールサイズはその症状をしばらく覆い隠すだけです。

インスタンス数かけるプールサイズ — マイクロサービスの典型的な事故

単一のアプリケーションではうまく合わせた設定が、サービスが増えるにつれて静かに崩れます。

サービスA: インスタンス12個 × プール20 = 240
サービスB: インスタンス 8個 × プール15 = 120
サービスC: インスタンス 6個 × プール10 =  60
バッチワーカー: 4個 × プール5          =  20
                             合計      = 440

max_connections = 200

各サービスの設定はどれも合理的に見えます。ところが合計が最大コネクション数の二倍を超えます。平常時はすべてのプールが満杯にならないので問題が表に出ず、トラフィックが集中したりローリングデプロイでインスタンスが一時的に二倍になった瞬間に破裂します。

FATAL:  sorry, too many clients already
FATAL:  remaining connection slots are reserved for non-replication superuser connections

もっと悪いのは、このエラーがヘルスチェックや監視エージェントまで塞いでしまう点です。障害の状況で観測手段が一緒に消えます。

予算として管理しなければなりません。計算から漏らしやすい項目があります。

SHOW max_connections;                    -- 200
SHOW superuser_reserved_connections;     -- 3
SHOW max_wal_senders;                    -- 10 (レプリカ。max_connectionsとは別枠)

-- いま誰がどれだけ使っているのか
SELECT application_name,
       count(*)                                  AS total,
       count(*) FILTER (WHERE state = 'active')  AS active,
       count(*) FILTER (WHERE state = 'idle')    AS idle,
       count(*) FILTER (WHERE state = 'idle in transaction') AS idle_in_tx
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY 1
ORDER BY 2 DESC;

 application_name | total | active | idle | idle_in_tx
------------------+-------+--------+------+------------
 order-service    |    88 |      6 |   79 |          3
 user-service     |    42 |      3 |   39 |          0
 datadog-agent    |     6 |      1 |    5 |          0
 flyway           |     2 |      0 |    2 |          0

この出力が典型的な状態です。88個を握っていますが、実際に働いているのは6個です。残りの79個はメモリとプロセススロットだけを占めています。

予算配分の原則はこうです。

ユーザー経路のサービスに先に配分し、バッチと管理ツールは最小に抑えます。
ローリングデプロイ中にインスタンスが一時的に増えることを考慮して余裕を持たせます。最大インスタンス数を基準に計算しなければなりません。
マイグレーションツール、監視エージェント、管理者接続用にいくつか残しておきます。
サービスごとにデータベースユーザーを分け、ユーザー単位で上限をかけます。

-- ユーザー単位のコネクション上限。一つのサービスが全体を食い尽くすのを構造的に防ぐ。
ALTER ROLE batch_worker CONNECTION LIMIT 10;
ALTER ROLE order_service CONNECTION LIMIT 60;

プールサイズを減らしてもサービス数が増え続けるなら、そのときがコネクションプーラーを導入する時点です。

PgBouncerの三つのモードと、トランザクションモードで使えないもの

PgBouncerはアプリケーションとPostgreSQLの間に立ち、多数のクライアント接続を少数のサーバー接続に多重化します。モードが三つあり、違いはサーバーコネクションをいつ回収するかです。

モード	サーバーコネクションの返却時点	多重化の効率	使用できない機能
session	クライアント接続の終了	低い	なし。接続生成コストだけが節約される
transaction	トランザクションの終了	高い	セッション変数、セッションレベルのadvisory lock、LISTENとNOTIFY、WITH HOLDカーソル、一時テーブル
statement	文の終了	最も高い	複数の文からなるトランザクションそのもの

実務で意味のある選択肢は事実上transactionモードです。クライアント1000個をサーバーコネクション20個で受け止められます。

[databases]
shop = host=10.0.1.10 port=5432 dbname=shop

[pgbouncer]
pool_mode = transaction
max_client_conn = 2000
default_pool_size = 20
reserve_pool_size = 5
reserve_pool_timeout = 3
server_idle_timeout = 60
max_prepared_statements = 200

その代償として諦めなければならないものは明確です。トランザクションが終わった瞬間に別のクライアントがそのサーバーコネクションを使うので、トランザクションの境界を越えて維持されるセッション状態はすべて壊れます。

第一に、セッション変数です。SET search_pathやSET timezoneをトランザクションの外で実行すると、次の要求には残っていません。マルチテナンシーをsearch_pathで実装していたなら、トランザクションモードでは静かに別テナントのスキーマを読んでしまう可能性があります。

-- トランザクションモードで安全なやり方
BEGIN;
SET LOCAL search_path = tenant_42, public;
SELECT ...;
COMMIT;

SET LOCALはトランザクション終了時に自動で戻るので安全です。行レベルセキュリティに使うSET LOCAL app.current_user_idのようなパターンも同じです。

第二に、advisory lockです。pg_advisory_lockはセッション単位なのでトランザクションが終わっても解除されず、そのコネクションが別のクライアントに渡ると永遠に解けません。

-- 危険: セッション単位のロック
SELECT pg_advisory_lock(12345);

-- 安全: トランザクション終了時に自動解除
SELECT pg_advisory_xact_lock(12345);

第三に、prepared statementです。長らくトランザクションモードの最大の制約であり、JDBCやasyncpgのようにデフォルトで準備された文を使うドライバでエラーが出ていました。PgBouncer 1.21からmax_prepared_statements設定でプロトコルレベルのprepared statementをサポートするので、最新バージョンならこの制約はなくなりました。古いバージョンを使っているならドライバ側で無効化する必要があります。

# JDBC
prepareThreshold=0

# asyncpg
statement_cache_size=0

第四に、LISTENとNOTIFYです。購読はセッションに紐づいているので、トランザクションモードでは動作しません。通知を使うには、その用途だけsessionモードのプールを別に用意するか、PgBouncerを迂回する必要があります。

もう一つ。PgBouncerを入れたからといってアプリケーション側のプールをなくしてはいけません。二層を併用しつつ、アプリケーションのプールは小さく取り、PgBouncerが多重化を担当するのが一般的な構成です。

コネクションリークの診断とサーバーレスの特殊性

コネクションリークは二つの形で現れます。

第一に、返却されなかったコネクションです。例外の経路でcloseを漏らしたコードが原因で、プール使用量が時間とともに単調増加するグラフとして表に出ます。

第二に、より危険なidle in transactionです。トランザクションを開いたままアプリケーションが別のことをしている状態です。コネクションを占有するだけにとどまらず、そのトランザクションのスナップショットのせいでVACUUMが不要になったタプルを片付けられません。テーブルが膨らみ、全体の性能が徐々に悪くなります。

-- 古いidle in transactionを探す
SELECT pid,
       application_name,
       state,
       now() - xact_start   AS tx_age,
       now() - state_change AS idle_age,
       left(query, 60)      AS last_query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND now() - state_change > interval '30 seconds'
ORDER BY xact_start;

  pid  | application_name |        state        |    tx_age    |   idle_age   |          last_query
-------+------------------+---------------------+--------------+--------------+-------------------------------
 24193 | order-service    | idle in transaction | 00:18:44.221 | 00:18:42.008 | SELECT * FROM orders WHERE ...

18分間も開いています。last_queryが原因のコードを指しているので、ここからすぐ追跡できます。そして安全網をかけておきます。

ALTER SYSTEM SET idle_in_transaction_session_timeout = '30s';
ALTER SYSTEM SET idle_session_timeout = '10min';   -- PostgreSQL 14以上
SELECT pg_reload_conf();

idle_session_timeoutはコネクションプーラーの後ろでは注意して使う必要があります。プールが維持しようとしているアイドルコネクションをサーバーが切ると、プールが予期しない切断に出会うことになるので、プールのアイドル検証周期より長く取らなければなりません。

最も古いトランザクションがVACUUMをどれだけ止めているかも一緒に見るべきです。

SELECT max(age(backend_xmin)) AS oldest_xmin_age
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL;

サーバーレス環境には、ここに構造的な問題がもう一つあります。関数インスタンスは要求ごとに作られて消えるので、各インスタンスが自分のプールを維持するモデルが成り立ちません。同時実行インスタンスが500個ならコネクション要求も500個になります。さらにインスタンスが終了するときに接続を片付ける保証がないので、サーバー側に幽霊コネクションが残ります。

対応は三つです。

外部プーラーを必須にします。PgBouncerやRDS Proxyのようなマネージドプロキシが多重化を担当します。サーバーレスにおいてこれは選択肢ではありません。
関数の中ではプールサイズを1に取ります。インスタンス一つが同時に処理する要求が一つなら、コネクションも一つで十分です。
コールドスタートの外で接続を作り、インスタンスの再利用時に維持されるようハンドラの外側のスコープにクライアントを置きます。

HTTPベースのドライバを使う選択肢もあります。TCP接続とセッションを維持せず、要求ごとにHTTPでクエリを送る方式で、コネクション管理の問題そのものをなくします。代わりにトランザクションとセッション機能に制約が生じます。

おわりに — 公式ではなく測定、そして待ち行列の位置

覚えておくことは三つです。

第一に、コネクションプールの目的は並行性を増やすことではなく制限することです。データベースが同時にうまく処理できる件数を超えて送ると、スループットは増えず遅延だけが大きくなります。待ち行列はどのみちできるので、何の資源も握らずに待てるプール側に立てるほうが常に良いのです。

第二に、コア数ベースの公式は出発点であって正解ではありません。その式が教えてくれる本当の情報は、正解の桁が数百ではなく数十だという事実です。実際の値は目標負荷でプールサイズを変えながらスループットと待ち時間を測定して決めるべきです。そして待ち時間が長いなら、たいていはプールが小さいのではなくトランザクションが長いのです。

第三に、インスタンス数とプールサイズの積を常に計算してください。各サービスの設定がすべて合理的でも、総和がmax_connectionsを超えればデプロイ中に障害が起きます。ユーザー単位のCONNECTION LIMITで上限をかけ、サービスが増え続けるならPgBouncerのtransactionモードを導入しつつ、セッション状態に依存するコードを先に整理してください。