PgBouncerを使用したPostgreSQL接続プーリングの高トラフィックアプリケーション向け設定

はじめに

PostgreSQLデータベースが高い接続数に直面すると、パフォーマンスが急速に低下する可能性があります。各データベース接続はシステムリソースを消費し、PostgreSQLには同時接続数に実用的な制限があります。軽量な接続プーラーであるPgBouncerは、データベース接続のプールを維持し、それらをクライアントアプリケーションに効率的に配布することでこの問題を解決します。

接続プーリングの重要性

接続の問題

リソースオーバーヘッド: 各PostgreSQLバックエンドプロセスは5-10MBのメモリを消費
接続制限: デフォルトのmax_connectionsは通常100-200
起動コスト: 新しい接続の作成には1-5msかかる
コンテキストスイッチング: プロセスが多すぎるとCPUスラッシングが発生

PgBouncerの利点

データベース接続数を10-100倍削減
最小限のオーバーヘッドで数千のクライアント接続を可能にする
ピーク時の接続キューイングを提供
さまざまなユースケースに対応する複数のプーリングモードをサポート

インストールと基本設定

PgBouncerのインストール

Ubuntu/Debianの場合:

sudo apt update
sudo apt install pgbouncer

CentOS/RHELの場合:

sudo yum install pgbouncer

macOSの場合:

brew install pgbouncer

ディレクトリ構造

/etc/pgbouncer/
  ├── pgbouncer.ini        # メイン設定
  └── userlist.txt         # 認証資格情報

設定ファイルのセットアップ

基本的なpgbouncer.ini設定

[databases]
; database_name = host=hostname port=5432 dbname=actual_db
myapp = host=localhost port=5432 dbname=production_db

[pgbouncer]
; 接続プーリングモード
pool_mode = transaction

; 最大接続数
max_client_conn = 1000
default_pool_size = 25
reserve_pool_size = 5
reserve_pool_timeout = 3

; ネットワーキング
listen_addr = 0.0.0.0
listen_port = 6432

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

; ログ
log_connections = 1
log_disconnections = 1
log_pooler_errors = 1

; パフォーマンス
max_prepared_statements = 0

プーリングモードの理解

1. セッションプーリング (pool_mode = session)

動作: クライアントにセッション全体にわたって接続を割り当て
ユースケース: 一時テーブルや準備済みステートメントを使用するアプリケーション
効率: 低い (1:1の接続比率)

pool_mode = session

2. トランザクションプーリング (pool_mode = transaction) - 推奨

動作: 各トランザクション後に接続をプールに戻す
ユースケース: 短いトランザクションを持つほとんどのウェブアプリケーション
効率: 高い (10-100倍の削減)

pool_mode = transaction
default_pool_size = 25
max_client_conn = 1000

3. ステートメントプーリング (pool_mode = statement)

動作: 各ステートメント後に接続を戻す
ユースケース: トランザクションなしの単純な読み取り専用クエリ
効率: 最大 (ただし非常に制限的)

pool_mode = statement
; 注意して使用 - マルチステートメントトランザクションを壊す

認証設定

userlist.txtの作成

PgBouncerには別の認証ファイルが必要です。MD5ハッシュを生成し、userlist.txtに追加します。

userlist.txtの例:

"app_user" "md5d8578edf8458ce06fbc5bb76a58c5ca4"
"readonly_user" "md5a3c7f5e89d24e7c8b1f9d2e4a6c8b0d2"

PostgreSQL auth_queryの使用 (高度)

認証のためにPostgreSQLを直接クエリします:

auth_type = md5
auth_query = SELECT usename, passwd FROM pg_shadow WHERE usename=$1

高トラフィック向けの最適設定

接続プールのサイズ設定

プールサイズの公式:

default_pool_size = (num_cores × 2) + effective_spindle_count

4コアサーバーでSSDの場合:

default_pool_size = 20
reserve_pool_size = 5
max_client_conn = 1000

完全な本番設定

[databases]
production = host=db.example.com port=5432 dbname=prod_db pool_size=30
analytics = host=db-replica.example.com port=5432 dbname=prod_db pool_size=15

[pgbouncer]
pool_mode = transaction

; 接続制限
max_client_conn = 2000
default_pool_size = 25
min_pool_size = 10
reserve_pool_size = 8
reserve_pool_timeout = 3
server_lifetime = 3600
server_idle_timeout = 600

; ネットワーキング
listen_addr = 0.0.0.0
listen_port = 6432
so_reuseport = 1
pkt_buf = 8192

; セキュリティ
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
ignore_startup_parameters = extra_float_digits,options

; ログ
log_connections = 1
log_disconnections = 1
log_pooler_errors = 1
stats_period = 60

; パフォーマンス
max_prepared_statements = 0
query_timeout = 30
query_wait_timeout = 120

アプリケーション接続文字列

PgBouncer前

# 直接PostgreSQL接続
DATABASE_URL = "postgresql://user:[email protected]:5432/mydb"

PgBouncer後

# PgBouncer経由で接続
DATABASE_URL = "postgresql://user:[email protected]:6432/mydb"

モニタリングと管理

管理コンソールコマンド

PgBouncer管理コンソールに接続:

psql -h localhost -p 6432 -U pgbouncer pgbouncer

必須コマンド:

-- プール統計を表示
SHOW POOLS;

-- アクティブ接続を表示
SHOW CLIENTS;
SHOW SERVERS;

-- 設定を表示
SHOW CONFIG;

-- 設定をリロード
RELOAD;

一般的な問題のトラブルシューティング

問題1: "no more connections allowed"

解決策:

max_client_conn = 5000
default_pool_size = 50

問題2: 高いcl_waitingカウント

解決策:

プールサイズを増やす
遅いクエリを最適化
リザーブプールを追加

問題3: 準備済みステートメントエラー

解決策:

max_prepared_statements = 0

パフォーマンスへの影響例

PgBouncer前

500同時リクエスト → 500PostgreSQL接続
データベース負荷: 95% CPU、8GB RAM
応答時間: 平均250ms

PgBouncer後

500同時リクエスト → 25PostgreSQL接続
データベース負荷: 35% CPU、1GB RAM
応答時間: 平均80ms
結果: 3倍高速、70%リソース使用量削減

結論

PgBouncerはPostgreSQLアプリケーションのスケーリングに不可欠です。接続オーバーヘッドを90%以上削減し、10-100倍のクライアントをサポートし、応答時間を劇的に改善します。トランザクションプーリングモードから始めて、モニタリングに基づいて調整してください。