Posted inDatabase

PgBouncer で PostgreSQL パフォーマンスを最適化する：実践コネクションプーリング

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

背景：PostgreSQL が「息切れ」し始めたとき

100GBのデータベースをMySQLからPostgreSQLに移行した後 — 計画に3日、実行に1日かかった — システムは約2週間安定して動いていたが、突然おかしな症状が現れ始めた。アプリインスタンスをデプロイするたびに同時接続数が増え続け、ピーク時のAPIレスポンスタイムが800ms〜1sまで跳ね上がった。時にはアプリが直接 too many connections エラーを返すこともあった。

pg_stat_activity を確認すると、数百ものコネクションがidle状態 — 何もせず、ただ新しいクエリを待ち続けていた。問題の本質はここにある：PostgreSQLへの各コネクションは独立したOSプロセスであり、実際にRAMとCPUを消費する。180のアイドルコネクションは、何もしていない180のプロセスが動いていることを意味する。

スレッドベースモデルを採用するMySQLとは異なり、PostgreSQLはコネクションごとに独立したOSプロセスを生成する — これを process-per-connection と呼ぶ。アプリインスタンスをスケールアウトするたびに、各インスタンスが独自のコネクションプールを持ち、合計コネクション数は誰も制御できないまま指数関数的に増加していく。

アプリケーション層でのコネクションプーリング（HikariCP、psycopg2 pool）は、単一プロセス内でしか機能しない。複数のインスタンスからのコネクションをまとめるには、アプリとデータベースの間にプロキシ層が必要だ。PgBouncer はまさにその役割を担う。

PgBouncer の仕組み

PgBouncer は軽量プロキシ — 消費RAMはわずか1〜2MB — アプリとPostgreSQLの間に位置する。複数のアプリインスタンスからのコネクションを受け取り、PostgreSQLサーバーへの少数の実コネクションを再利用する。アプリはPostgreSQLに直接接続していると思っているが、実際にはPgBouncer と通信している。

PgBouncer には3つのプールモードがある：

  • session mode：クライアントのセッション中ずっと1つのサーバーコネクションを保持 — 最も安全だが節約効果は最小
  • transaction mode：1トランザクションの実行中のみサーバーコネクションを保持 — 最も効率的で、現代的なアプリの大半に適している
  • statement mode：非常に積極的で、1ステートメント中のみ保持 — 複数ステートメントのトランザクションには使用不可

本番環境では transaction mode を使用している。PgBouncer への500クライアント接続に対して、PostgreSQL への実コネクションはわずか〜25で済む。これがコネクションプーリングの核心だ。

PgBouncer のインストール

Ubuntu/Debian の場合：

sudo apt update
sudo apt install -y pgbouncer

# バージョンを確認する
pgbouncer --version
# PgBouncer 1.21.0

インストール後、設定が必要なためPgBouncer はまだ起動できない。設定ファイルは /etc/pgbouncer/ にある。

詳細な設定

pgbouncer.ini ファイル

/etc/pgbouncer/pgbouncer.ini を編集する：

[databases]
; アプリが接続するエイリアス = PostgreSQL への実際の接続文字列
myapp_db = host=127.0.0.1 port=5432 dbname=myapp_production

[pgbouncer]
; PgBouncer のリッスンアドレスとポート
listen_port = 6432
listen_addr = 127.0.0.1

; 認証設定
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

; プールモード
pool_mode = transaction

; クライアント側：アプリが PgBouncer に接続できる最大数
max_client_conn = 1000

; サーバー側：PostgreSQL への実コネクション数（データベース-ユーザーペアごと）
default_pool_size = 25

; プールが満杯になったときのリザーブ
reserve_pool_size = 5
reserve_pool_timeout = 3

; アイドル状態のサーバーコネクションを10分後に閉じる
server_idle_timeout = 600
client_idle_timeout = 0

; ログ設定
logfile = /var/log/postgresql/pgbouncer.log
pidfile = /var/run/postgresql/pgbouncer.pid

; 管理者アクセス
admin_users = pgbouncer_admin
stats_users = pgbouncer_stats

理解しておくべき重要なパラメータ：

  • default_pool_size = 25：データベース-ユーザーペアごとのPostgreSQLへのサーバーコネクション合計数。この値はPostgreSQLの max_connections とプールするデータベース数に依存する。私の環境では max_connections = 100、2つのデータベースを使用しているため、各プール25が妥当だ。
  • max_client_conn = 1000：PgBouncer に接続するクライアント数 — PgBouncer は非常に軽量なので大きな値でも問題ない（各クライアントのコストは数KBのみ）。
  • server_idle_timeout = 600：不必要に長時間アイドル状態のサーバーコネクションを保持しないための設定。

userlist.txt ファイル

PostgreSQL からパスワードハッシュを取得して認証ファイルに記入する：

# ユーザーのパスワードハッシュを取得する
sudo -u postgres psql -c "SELECT usename, passwd FROM pg_shadow WHERE usename='myapp_user';"

/etc/pgbouncer/userlist.txt を作成する：

"myapp_user" "md5xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"
"pgbouncer_admin" "md5yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy"

PgBouncer が読み取れるよう適切な権限を設定する：

sudo chown postgres:postgres /etc/pgbouncer/userlist.txt
sudo chmod 640 /etc/pgbouncer/userlist.txt

起動と接続

sudo systemctl enable pgbouncer
sudo systemctl start pgbouncer
sudo systemctl status pgbouncer

アプリは今後ポート5432の代わりにポート 6432 に接続する。Python の場合：

import psycopg2

# 変更前：PostgreSQL ポート5432 に直接接続
# conn = psycopg2.connect("host=localhost port=5432 dbname=myapp_production user=myapp_user")

# 変更後：PgBouncer ポート6432 経由で接続
conn = psycopg2.connect(
    host="localhost",
    port=6432,           # PgBouncer のポート
    dbname="myapp_db",   # [databases] セクションのエイリアス
    user="myapp_user",
    password="your_password"
)

transaction mode 使用時の注意点

transaction mode は PostgreSQL のセッションレベルの一部機能をサポートしていない：

  • SET/RESET セッション変数
  • LISTEN/NOTIFY
  • セッションレベルのアドバイザリーロック
  • プリペアドステートメント（追加設定か server_reset_query_always = 1 の使用が必要）

通常のDjango/FastAPIアプリ？ほとんど問題は起きない。ただし、プリペアドステートメントを明示的に使用しているコードがある場合は、本番環境へ移行する前に十分にテストすること — または session mode への切り替えも検討しよう。

確認とモニタリング

PgBouncer 管理コンソール

管理コンソールに接続する：

psql -h 127.0.0.1 -p 6432 -U pgbouncer_admin pgbouncer

知っておくべきコマンド：

-- プールのステータスを表示する
SHOW POOLS;

-- リクエスト数/秒、レイテンシーの統計を表示する
SHOW STATS;

-- サーバーコネクション一覧（PostgreSQL への実コネクション）を表示する
SHOW SERVERS;

-- 再起動なしで設定をリロードする
RELOAD;

SHOW POOLS の出力例：

 database  | user       | cl_active | cl_waiting | sv_active | sv_idle | maxwait
-----------+------------+-----------+------------+-----------+---------+---------
 myapp_db  | myapp_user |        45 |          0 |        20 |       5 |       0

最も注目すべき列は cl_waiting だ。この値が頻繁に0より大きい場合、プールが過負荷になっており、クライアントが順番待ちしているサインだ。対策：default_pool_size を増やし、PostgreSQL の max_connections も相応に増やすこと。

PostgreSQL 側からの確認

-- PgBouncer から PostgreSQL への実コネクション数をカウントする
SELECT count(*), state
FROM pg_stat_activity
WHERE usename = 'myapp_user'
GROUP BY state;

-- 現在の max_connections を確認する
SHOW max_connections;

シンプルなアラートスクリプト

#!/bin/bash
# /opt/scripts/check-pgbouncer.sh
# 5分ごとにcronで実行する: */5 * * * * /opt/scripts/check-pgbouncer.sh

WAITING=$(psql -h 127.0.0.1 -p 6432 -U pgbouncer_stats pgbouncer \
  -t -c "SHOW POOLS;" 2>/dev/null | awk -F'|' '{sum += $4} END {print int(sum)}')

if [ "${WAITING:-0}" -gt 10 ]; then
  echo "ALERT: PgBouncer で $WAITING 件のクライアントがコネクション待ちです！" | \
    mail -s "[PgBouncer] Pool exhausted" [email protected]
fi

6ヶ月の実運用で得られた実際の結果

PgBouncer デプロイ後の具体的な数値：

  • PostgreSQL へのサーバーコネクションが〜180から安定した〜25へ減少
  • プロセス数の減少により PostgreSQL の RAM 使用量が約15%削減
  • ピーク時のAPIレスポンスタイムが300ms以下に安定（以前は800ms〜1s）
  • too many connections エラーが完全に解消

6ヶ月の本番運用で得た教訓：PostgreSQLは非常に強力だが、そのコネクションモデルはMySQLよりも積極的に管理する必要がある。PgBouncer は万能薬ではない — クエリが遅ければクエリ最適化は依然として必要だ。しかし、インフラ層のコネクションオーバーヘッドという問題に対しては、これが最も明確な解決策だ。

PostgreSQL のコネクション数が異常に増加している、またはアプリが頻繁にタイムアウトエラーを報告している場合 — サーバーのアップグレードを検討する前にまず PgBouncer を導入してみよう。ハードウェアに手を加えることなく問題が解決できることが多い。

Share:
Tags: