Posted inデータベース

高トラフィック下におけるPgBouncerコネクションプーリングを活用したPostgreSQLのスケーリング

Database tutorial - IT technology blog
Database tutorial - IT technology blog

クイックスタート：深夜2時の接続スパイクを食い止める

深夜2時、監視システムからアラートが発報されます。APIのp99レイテンシが45msから一気に12,000msへと跳ね上がりました。ログを確認すると、悪夢のようなエラーメッセージが出力されています：FATAL: remaining connection slots are reserved for non-replication superuser connections。

PostgreSQLインスタンスがmax_connectionsの上限に達してしまいました。各バックエンドワーカープロセスはベースラインとして5MB〜10MBのRAMを消費するため、サーバーでディスクへのページング（スワップ）が発生しています。あらゆるサービスにわたってクエリが停止状態に陥っています。

安易にpostgresql.confのmax_connectionsを2倍に引き上げたい衝動は抑えてください。100から1,000へ引き上げると、高負荷時にLinuxのOut-Of-Memory（OOM）キラーが作動し、データベースプロセスを強制終了させる危険があります。PostgreSQLの前面にPgBouncerを配置すれば安全弁として機能し、わずか数分でメモリ消費とレイテンシを安定化させることができます。

1. PgBouncerのインストール

# Debian / Ubuntu
sudo apt-get update && sudo apt-get install -y pgbouncer

# RHEL / Rocky Linux
sudo dnf install -y pgbouncer

2. 本番環境向け最小構成

/etc/pgbouncer/pgbouncer.iniを開き、軽量プロキシを設定します：

[databases]
# アプリケーションからの受信トラフィックをローカルホストのPostgreSQLにルーティング
app_production = host=127.0.0.1 port=5432 dbname=app_production

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
admin_users = postgres, pgbouncer_admin

# プーリングパラメータ
pool_mode = transaction
max_client_conn = 5000
default_pool_size = 25
reserve_pool_size = 5
reserve_pool_timeout = 5

3. 認証情報の設定とサービスの起動

PgBouncerはローカルの参照ファイルをもとにユーザー認証情報を検証します。PostgreSQLから既存のユーザーレコードを直接抽出できます：

sudo -u postgres psql -d app_production -Atc \
  "SELECT concat('"', usename, '" "', passwd, '"') FROM pg_shadow WHERE usename = 'app_user';" \
  | sudo tee -a /etc/pgbouncer/userlist.txt

ファイルのパーミッションを制限し、デーモンを起動・有効化します：

sudo chown -R postgres:postgres /etc/pgbouncer
sudo chmod 0600 /etc/pgbouncer/userlist.txt
sudo systemctl restart pgbouncer

アプリケーションのデータベース接続ポートを5432から6432に変更します。これで、アプリケーションからの5,000件の接続要求は25個の安定したバックエンドワーカープールへと適切にキューイングされるようになります。レイテンシも即座に正常値へと戻ります。

徹底解説：高並行処理下でPostgreSQLが破綻する理由

PostgreSQLはスレッドではなく「接続ごとに1プロセス」を割り当てるプロセスモデルを採用しています。クライアントのソケットごとに、OSが専用のpostgresプロセスをforkします。

これにより、大規模環境では主に2つのボトルネックが発生します：

  • メモリの肥大化： 600個のクライアント接続が存在する場合、ソケットバッファやプロセスごとのカタログキャッシュを維持するだけで約4GB〜6GBのRAMが消費されます。このメモリは、クエリがハッシュ結合やソートのためにwork_memを割り当てる前に失われてしまいます。
  • CPUコンテキストスイッチ： 何百ものプロセスが8個または16個のCPUコアを奪い合うため、LinuxカーネルはSQLを実行する時間よりも、プロセス状態の切り替え（コンテキストスイッチ）により多くのCPUサイクルを費やすことになります。

適切なプールモードの選択

PgBouncerはアプリケーションとPostgresの間に位置し、インテリジェントな接続マルチプレクサとして機能します。プーリングには以下の3つのモードがあります：

  • セッションプーリング（Session Pooling）： クライアントがログインした瞬間にサーバー接続を紐付け、切断されるまで保持します。一時テーブルやセッションロックなど、Postgresのすべての機能を100%サポートします。しかし、数千のクライアントが同時にアイドル状態になるようなケースでは、並行処理能力の向上はほとんど期待できません。
  • トランザクションプーリング（Transaction Pooling、推奨）： トランザクション（BEGIN ... COMMIT）の実行中のみデータベース接続をクライアントに割り当てます。トランザクションが終了した瞬間、PgBouncerはそのバックエンドワーカーを待機列の次のクライアントへと引き渡します。これにより、数千のWebクライアントであっても、25〜50個のアクティブなPostgreSQLプロセスを効率よく共有できます。注意：セッションレベルの状態変更（SET timezoneなど）はクエリ間で保持されません。
  • ステートメントプーリング（Statement Pooling）： 個々のクエリが完了するたびに接続がプールへと返却されます。複数ステートメントにわたるトランザクションは使用できません。単純なKey-Value参照や自動コミット（auto-commit）の分析フィードでのみ使用してください。

クエリパイプラインの動作フロー

トランザクションプーリング環境では、クライアントのリクエストは次の4つのステップで処理されます：

  1. クライアントがポート6432でPgBouncerとの長期持続TCPソケットを開きます。
  2. 25個のPostgreSQL接続がすべてビジー状態の場合、PgBouncerはクライアントをインメモリキューで待機させます。
  3. バックエンド接続でトランザクションが完了した瞬間、PgBouncerはその接続を待機中のクライアントに割り当てます。
  4. Postgresがクエリを実行して結果セットを返し、PgBouncerは即座にその接続を回収します。

マルチテナントおよび本番ワークロード向けのチューニング

行儀の悪いバッチジョブやインデックスのないクエリが1つあるだけで、プール内の全接続を独占してしまうことがあります。接続の枯渇（コネクションスターベーション）を防ぐには、きめ細かなルーティングが必要です。

専用プールによるワークロードの分離

/etc/pgbouncer/pgbouncer.ini内で仮想データベースを定義し、レイテンシに敏感なユーザー向けAPI呼び出しをバッチワーカーから分離します：

[databases]
# 高優先度のREST APIトラフィック
app_live = host=127.0.0.1 port=5432 dbname=app_production pool_mode=transaction pool_size=40

# セッションプーリングを使用する長時間実行バックグラウンドジョブ
app_analytics = host=127.0.0.1 port=5432 dbname=app_production pool_mode=session pool_size=5

[users]
# 自動レポートスクリプトの接続数を制限
report_bot = pool_mode=transaction max_user_connections=5

PgBouncer 1.21以降におけるプリペアドステートメントの処理

トランザクションプーリングではクエリごとにサーバー接続が切り替わるため、従来はprepared statement "s1" does not existのようなエラーが発生していました。Prisma、ActiveRecord、SQLAlchemyなどの最新ORMの多くは、デフォルトでプリペアドステートメントを有効にしています。

PgBouncer 1.21以降では、プロトコルレベルの透過的なプリペアドステートメントを有効化できます：

[pgbouncer]
max_prepared_statements = 100

古いバージョンのPgBouncerを実行している場合は、ORMの設定でプリペアドステートメントを無効化してください（例：Prismaの接続文字列でpgbouncer=trueを設定する、ActiveRecordでprepare_threshold: nilを指定するなど）。

リアルタイムなプールメトリクスの確認

PgBouncerにはpsqlからアクセスできる内部管理コンソールが用意されています：

psql -p 6432 -U pgbouncer_admin -d pgbouncer

トラフィック急増時に実行すべき主要な診断コマンド：

-- クライアントのキュー待機数とアイドル状態のサーバー数を監視
SHOW POOLS;

-- 接続中のクライアントと現在のトランザクション状態を確認
SHOW CLIENTS;

-- クエリ数、データ転送量、待機時間を追跡
SHOW STATS;

SHOW POOLSのcl_waitingカラムを注視してください。sv_idleが0のままcl_waitingが長時間0を超えている場合は、default_pool_sizeを5〜10刻みで増やしてください。

本番運用の実践Tips

1. CPUコア数に応じたプールサイズの設定

8コアのマシンでdefault_pool_size = 500に設定すると、深刻なロック競合が発生します。接続数を増やしてもスループットが向上することはほとんどありません。アクティブなバックエンドプロセス数を算出する実績あるヒューリスティックは以下の通りです：

Max Active Backends = ((CPU_cores * 2) + effective_spindle_count)

高速なNVMeストレージを備えた専用の16コアデータベースサーバーの場合、メモリやL3キャッシュラインを奪い合う400個の無秩序な接続よりも、35〜50個のアクティブなPostgreSQLプロセスのほうが低いp99レイテンシを実現できます。

2. インポートするペイロードの安全なサニタイズ

ホットフィックスや緊急のデータベースパッチ適用では、大量のデータアップロードやペイロードの監査が必要になることがよくあります。ピンポイントのバッチ更新を実行する前にCSVダンプをJSONに変換する場合は、toolcraft.app/ja/tools/data/csv-to-jsonのようなブラウザ内ユーティリティを活用してください。パース処理はローカルのWebAssembly上で完全に実行されるため、機密性の高い顧客の個人情報（PII）がブラウザ外に送信されることがなく、手動マイグレーション時のデータ漏洩を防止できます。

3. 必須となるタイムアウトとファイルディスクリプタの上限設定

孤立したソケットがOSリソースをひそかに消費し尽くすのを防ぎます：

  • server_idle_timeout = 600： 10分間アイドル状態だったサーバー接続を切断し、メモリをOSに返却します。
  • query_timeout = 30： 30秒以上ハングしたクエリを中止し、暴走したシーケンシャルスキャンからバックエンドワーカーを保護します。
  • ファイルディスクリプタの上限（File descriptor limits）： PgBouncerは数千もの同時ソケットを管理します。/etc/security/limits.confに以下の行を追加してください：
postgres soft nofile 65536
postgres hard nofile 65536

sysctl -pで変更を適用し、デーモンがOSのファイルディスクリプタ枯渇に陥ることなくトラフィックの急増を処理できるようにします。

Share:
Tags: