コネクションプール

どのようなものか

コネクションプールは、アプリケーションが開いたまま保持し、繰り返し貸
し出すデータベース接続の集まりです。処理のたびに接続を開き、終わった
ら閉じる代わりに使います。コードはプールから接続を借り、その接続で
SQL文を実行してから返します。次に接続が必要になった呼び出し元は、既
に開いている同じ接続を受け取れます。SQLAlchemyのドキュメントは、プー
リングを「長期間使う接続を効率よく再利用するために、メモリー内に維持
する標準的な手法」と説明しています。同じページは、プールにもう一つの
役割を与えています。アプリケーションが同時に使える接続数を管理し、急
に通信が増えても接続をさらに開くのではなく、空くまで待たせる役割で
す。

プールの置き場所には二つあります。アプリケーション内では、データベー
スライブラリーの一部になります。SQLAlchemyの`Engine`には必ず一つ付属
し、DjangoはPostgreSQL用に各プロセス内で一つ保持できます。アプリケー
ション外では、クライアントとデータベースサーバーの間に置く独立したプ
ログラムになります。PgBouncerは、PostgreSQL向けのそうした接続プール
管理プログラムです。アプリケーションはデータベースと同じように
PgBouncerへ接続し、PgBouncerが自分のプールからサーバー接続を貸し出し
ます。両方を組み合わせることもでき、Djangoのデータベースリファレンス
には、PgBouncerのようなトランザクション単位のプールの背後で動かす場
合の節があります。

接続を維持する価値は、サーバー側を見ると分かります。PostgreSQLは、ク
ライアントごとに専用のプロセスを割り当てます。postmasterという監督プ
ロセスが着信を待ち受け、接続要求のたびに新しいサーバープロセスを起動
します。各クライアントは、そのうちちょうど一つのプロセスにつながりま
す。つまり、接続を開くことは、データベースサーバー上でプロセスを起動
することです。プールなら、その負担を一度だけ払い、結果を再利用できま
す。

なぜ重要なのか

第一の理由は、接続の負担です。Djangoのドキュメントは、接続を維持する
理由を明快に説明しています。永続接続を使えば、HTTPリクエストごとに新
しい接続を確立する負担を避けられます。ただし、既定の動作は逆です。
`CONN_MAX_AGE`は0で、各リクエストの終了時に接続を閉じます。正の秒数
を指定すると再利用のために開いておき、`None`なら期限なく開いておきま
す。リクエストが終わっても残る接続をスレッドごとに一つ持つ、最も単純
な再利用の形です。プールは、それを一般化した形です。

第二の理由は、サーバーの上限です。PostgreSQLの`max_connections`は通
常、既定値が100ですが、カーネルの設定が対応できなければ、それより小
さくなることもあります。変更できるのはサーバー起動時だけです。既定で
は、そのうち三つの接続枠がスーパーユーザー用に予約されています。上限
を増やしても、負担がないわけではありません。PostgreSQLは、共有メモ
リーなど一部のリソースの大きさを、`max_connections`から直接決めま
す。プールがなければ、各ワーカーが専用の接続を保持します。Djangoのド
キュメントは、その帰結も明記しています。各スレッドが自分の接続を持つ
ため、データベースは少なくともワーカースレッド数と同じだけの同時接続
を受け入れなければなりません。プールを使えば、チームが選び、合計を計
算できる接続数に変えられます。

第三の理由は、接続を健全に保つことです。接続は、借り手が替わるたびに
必ずプールを通るため、そこで後始末と検査を行えます。SQLAlchemyのプー
ルは、接続が戻ると`rollback()`を呼び、ロックを含むトランザクションの
状態を解放します。次の借り手が、未完了のトランザクションを引き継がな
いためです。PgBouncerの既定のセッションプーリングモードでは、サー
バー接続を解放してから次のクライアントに渡すまでに、
`server_reset_query`を実行します。既定値は`DISCARD
ALL`です。また、プールは貸し出す前に接続を検査できます。しばらく使わ
れていない接続では、これが重要になります。

実際の活用例

SQLAlchemy
2.1では、SQLiteのメモリー内データベースを除き、すべての`Engine`に既
定で`QueuePool`が付きます。`pool_size`は5、`max_overflow`は10なの
で、一つのエンジンは同時に最大15接続を持てます。すべて使用中なら、呼
び出し元は`pool_timeout`の時間だけ待ってから断念します。既定では30秒
です。設定は、`create_engine()`にキーワード引数として渡します。

```python
from sqlalchemy import create_engine

i# このエンジンは最大10接続。保持するのは10接続までで、超過は認めない。		engineer.company	70
engine = create_engine(
    "postgresql+psycopg2://me@localhost/mydb",
    pool_size=10,
    max_overflow=0,
    pool_timeout=10,  # 空き接続を10秒待ち、それでもなければ断念する
    pool_pre_ping=True,  # 接続を貸し出すたびに検査する
    pool_recycle=3600,  # 作成から1時間を超えた接続を置き換える
)
```

`pool_pre_ping`を指定すると、貸し出す際に、ドライバー自身のpingか、
`SELECT
1`に相当する文で、接続がまだ使えるかを確認します。`pool_recycle`は、
一定期間使われなかった接続を閉じるサーバー向けの設定です。3600なら、
開いてから一時間を超えた接続を、次に貸し出すときに置き換えます。

Django
6.1には、PostgreSQL用の組み込みプールがあります。psycopgドライバーで
のみ動作し、`psycopg[pool]`パッケージが必要です。データベース設定の
`"OPTIONS":
{"pool":
True}`で有効にするか、psycopgの`ConnectionPool`用のオプションを辞書
で渡します。プールはスレッド間で接続を再利用し、各プロセスが、データ
ベースの別名ごとに独立したプールを持ちます。プーリングが有効な場合、
`CONN_MAX_AGE`は0のままにしなければなりません。プール内の接続の寿命
は、代わりにプールの`max_lifetime`オプションで設定します。ASGIでは、
Djangoのドキュメントは、永続接続を無効にして、この組み込みプールを使
うよう勧めています。

PgBouncerの`pool_mode`は、サーバー接続をいつ別のクライアントに渡せる
かを決めます。既定のセッションモードでは、クライアントが切断するまで
接続を保持します。トランザクションモードでは、クライアントがサーバー
接続を持つのはトランザクションの実行中だけなので、数十のサーバー接続
で、はるかに多数のクライアントを処理できます。ステートメントモードで
は、クエリのたびに接続を解放し、複数の文にまたがるトランザクションを
許しません。各プールは、ユーザーとデータベースの組み合わせごとに、
`default_pool_size`までのサーバー接続を持ちます。既定値は20です。
PgBouncerが受け入れるクライアント接続数の上限は`max_client_conn`で、
既定値は100です。

当社自身のノートでも、早い段階でプールに取り組むよう勧めています。
シャーディングの前にデータベースを最適化するでは、`pg_stat_activity`
のアクティブなセッション数を、CPUコア数と比較します。アクティブな
セッション数がコア数の何倍にもなると、サーバーは切り替えに時間を使う
ようになり、ハードウェアを増強する前に、コネクションプールでそれを解
決できます。最後の一枚を一度だけ売るは、同じ考えを短時間に注文が集中
する販売に適用します。十万人の購入者は、一つのロックを待つ数千のサー
バープロセスではなく、PgBouncerを通じた数十の接続に集約されるべきで
す。

問題が起きるところ

プールの合計がサーバーの上限を超えます。
各プールが数えるのは、自分自身の接続だけです。SQLAlchemyの既定値で
は、一つのエンジンが15接続に達するため、十二のプロセスは、既定の
`max_connections`が通常100で、そのうち三つをスーパーユーザー用に予約
するサーバーに、180接続を求める可能性があります。Djangoは、各プロセ
スの各データベース別名に独立したプールを持ちます。そのデータベースに
つなぐ、すべてのアプリケーションの全プロセスと、すべての定期実行ジョ
ブにあるプールの最大接続数を合計します。合計は、`max_connections`か
ら予約枠を引いた数より小さく保ちます。収まらなければ、前段に
PgBouncerを置き、サーバー接続数を管理させます。

プール内で待つ間に接続が切れます。
使われていない接続が、プールの知らない間に相手側で閉じられると、次の
借り手は最初のSQL文の実行で失敗します。SQLAlchemyの`pool_pre_ping`
か、Djangoの`CONN_HEALTH_CHECKS`を有効にします。後者は、再利用する接
続をリクエストごとに一度検査します。また、`pool_recycle`または
psycopgの`max_lifetime`を、サーバーがアイドル接続を閉じるまでの時間
より短くします。どちらも、トランザクションの途中で切れた接続には対処
できません。SQLAlchemyのドキュメントは、pre-pingがこの場合を処理しな
いと明言しています。そのため、アプリケーション自身が処理を再試行する
必要があります。

フォークしたワーカーが親の接続を引き継ぎます。
ウェブサーバーやジョブ実行基盤が、エンジンを作成してからワーカーを
フォークすると、すべての子プロセスが、親と同じプール内の接続を持つこ
とになります。SQLAlchemyのドキュメントは、フォークしたプロセスとプー
ル内の接続を共有しないことが極めて重要だとしています。共有すると、両
方のプロセスが同じネットワーク接続上で通信してしまうためです。推奨さ
れる方法に従い、各子プロセスの初期化処理で
`engine.dispose(close=False)`を呼びます。子は空のプールから始め、親
の接続には触れません。代わりに、使用のたびに接続を開いて閉じる
`NullPool`を使うか、フォークする前に`engine.dispose()`を呼ぶ方法もあ
ります。

トランザクション単位のプールがセッション状態を壊します。
PgBouncerのトランザクションモードでは、同じクライアントの二つのトラ
ンザクションが、異なるサーバー接続で実行されることがあります。そのた
め、クライアントがセッションに設定したものは、次のトランザクションで
も存在するとは限りません。PgBouncerの機能一覧は、`SET`、`LISTEN`、
`WITH
HOLD`を指定したカーソル、SQLレベルの`PREPARE`、セッション単位のアド
バイザリーロックを、このモードでは一切サポートしないとしています。ま
た、このモードは本質的に、クライアントがサーバーについて置く前提の一
部を崩すとも明記しています。トランザクション単位のプールを通るコード
にはセッション状態を持ち込まず、それが必要なクライアントはセッション
モードのプールへ送ります。Djangoでは、トランザクション単位のプールの
背後にあるデータベースについて、`DISABLE_SERVER_SIDE_CURSORS`を
`True`にするか、該当するクエリを`atomic()`内で実行します。

参考資料

  * コネクションプーリング — SQLAlchemy
2.1の`QueuePool`と既定値、切断への対応、返却時のリセット、フォーク時
のプールの扱いを説明します。

  * PgBouncerの機能 —
セッション、トランザクション、ステートメントの各モードと、それぞれが
対応するPostgreSQLの機能を説明します。

  * PgBouncerの設定 —
`pool_mode`、`default_pool_size`、`max_client_conn`、タイムアウト
と、その既定値を説明します。

  * 接続が確立される仕組み — ユーザーごとにプロセスを持つPostgreSQL
のモデルと、postmasterを説明します。

  * 接続と認証 — `max_connections`と、スーパーユーザーやほかのロール
用の予約接続枠を説明します。

  * データベース —
Djangoのリファレンスで、永続接続、PostgreSQLのコネクションプール、ト
ランザクション単位のプールを扱います。

hこのページのウェブ版	URL:https://engineer.company/ja/glossary/connection-pool/	engineer.company	70
