DevOpsツールとベストプラクティスの習得 - DevOpsナレッジハブ DevOpsツールとベストプラクティスの習得 - DevOpsナレッジハブ

PostgreSQLのスケーラビリティ向上：PgBouncer接続プーリングの実装

PgBouncerを使用してPostgreSQLの接続オーバーヘッドを削減し、プーリングモードを選択し、プールのサイズを調整し、クライアントの負荷を監視します。

PostgreSQLのスケーラビリティ向上：PgBouncer接続プーリングの実装

PostgreSQLは、クライアント接続ごとに1つのバックエンドプロセスを使用します。このモデルは信頼性がありますが、Webアプリケーションが数百または数千のほとんどアイドル状態の接続を開く場合、コストが高くなります。

PgBouncerはアプリケーションとPostgreSQLの間に位置し、少数のサーバー接続プールを保持し、多くのクライアントがそれらを再利用できるようにします。その結果、接続オーバーヘッドが低減され、データベースのメモリ使用量がより予測可能になります。

ボトルネック：ネイティブPostgreSQL接続オーバーヘッド

PostgreSQLは、接続ごとに専用プロセスを使用するモデルを採用しています。非常に安定しており、分離性を確保しますが、このアーキテクチャは負荷がかかると大きなオーバーヘッドを引き起こします。

  1. リソース消費： 新しい接続ごとに、サーバーは新しいバックエンドプロセスをフォークする必要があり、メモリとCPUリソースを消費します。数百または数千のアイドル接続がRAMを不必要に占有します。
  2. 接続確立の遅さ： 新しい接続の確立には、ネットワークハンドシェイク、認証、プロセス初期化が含まれ、特に頻繁に接続を開閉するアプリケーションリクエストに測定可能なレイテンシを追加します。
  3. スケーリングの限界： これらのリソース要求により、PostgreSQLサーバーがパフォーマンスが低下する前に現実的に処理できる同時接続数に実質的な上限が課せられます。

PgBouncerの紹介：軽量プロキシ

PgBouncerは、クライアントアプリケーションとPostgreSQLデータベースサーバーの間に配置される軽量プロキシサーバーとして機能します。その中核機能は、PostgreSQLバックエンドへの永続的で固定数のオープン接続を維持し、これらの接続を一時的なアプリケーションクライアントリクエストのためにプールして再利用することです。

このアプローチにより、2つの重要な利点が得られます。

  1. オーバーヘッドの削減： PostgreSQLサーバーは、PgBouncerによって維持される固定プールの接続のみを認識し、受信クライアントリクエストに対するコストのかかるプロセスごとのフォークサイクルを排除します。
  2. スループットの向上： 確立された接続を再利用することで、PgBouncerは認証と接続初期化時間を最小限に抑え、アプリケーションのスループットを大幅に向上させ、レイテンシを低減します。

PgBouncerプーリングモードの理解

PgBouncerの効率は、選択されたプーリングモードに大きく依存します。PgBouncerは3つの基本モードを提供し、それぞれ異なるアプリケーションアーキテクチャと並行性要件に適しています。

1. セッションプーリング（pool_mode = session）

セッションプーリングはデフォルトで最も安全なモードです。クライアントが接続すると、PgBouncerはそのクライアントが切断するまでプールされたサーバー接続を専有します。接続は、クライアントが明示的にセッションを閉じた場合にのみプールに戻されます。

  • ユースケース： セッション固有の機能（例：プリペアドステートメント、一時テーブル、カスタム変数のSETコマンド）に大きく依存するアプリケーション。
  • 長所： 最も安全で、すべてのPostgreSQL機能と完全に互換性があります。
  • 短所： クライアントがアイドル状態の間も接続が保持されるため、最も効率の悪いプーリングです。

2. トランザクションプーリング（pool_mode = transaction）

トランザクションプーリングは、特にステートレスAPIを使用する高トラフィックのWebアプリケーションに一般的に推奨されます。サーバー接続は、単一トランザクション（BEGINからCOMMIT/ROLLBACK）の間のみクライアントに専有されます。トランザクションが完了するとすぐに、接続はプールに戻され、他の待機中のクライアントが再利用できるようになります。

  • ユースケース： OLTPシステムやマイクロサービスで一般的な、短く頻繁なトランザクション。
  • 長所： サーバーリソースの非常に効率的な利用。
  • 短所： アプリケーションがトランザクションを注意深く管理する必要があります。セッションレベルの状態変更（例：SET extra_float_digits = 3）は、トランザクション間で失われるか、他のクライアントに漏洩する可能性があります。

トランザクションプーリングの注意

トランザクションプーリングでは、一時テーブル、セッションレベルのSET変更、セッションアドバイザリロック、長時間実行されるプリペアドステートメントなどのセッション状態を避けてください。PgBouncerはクライアント間でサーバー接続をリセットしますが、トランザクションプーリングにはアプリケーションの互換性テストが必要です。

3. ステートメントプーリング（pool_mode = statement）

ステートメントプーリングは最も積極的なモードです。サーバー接続は、単一のステートメント実行ごとにプールに戻されます。このモードは、複数ステートメントのトランザクションの使用を事実上防止し、非常に制限が厳しくなります。

  • ユースケース： トランザクションが明示的に禁止されているか不要な、高度に特化された読み取り専用の負荷。
  • 長所： 接続の再利用を最大化します。
  • 短所： すべてのトランザクションを壊します。 トランザクションが使用されないことが保証されている環境にのみ適しています。

PgBouncerのセットアップと初期設定

1. インストール

PgBouncerは、標準のディストリビューションリポジトリで利用できることがよくあります。

# Debian/Ubuntuの場合
sudo apt update && sudo apt install pgbouncer

# RHEL/CentOSの場合
sudo dnf install pgbouncer

2. 設定ファイル

PgBouncerは主に2つの設定ファイルに依存しており、通常は/etc/pgbouncer/にあります。

  • pgbouncer.ini：メイン設定。データベース、プール制限、動作モードを定義します。
  • userlist.txt：PgBouncerがPostgreSQLサーバーへの認証に使用するユーザーとパスワードを定義します。

3. ユーザーの定義（userlist.txt）

セキュリティ上の理由から、PgBouncerはPostgreSQLのpg_authidテーブルを直接読み取りません。認証できるユーザーを手動で定義する必要があります。このファイルは安全に保護してください（例：pgbouncerユーザーが所有し、権限を制限する）。

"app_user" "md5<md5-hash>"
"admin_user" "another_hash"

一部の認証設定では平文パスワードも可能ですが、PgBouncerとPostgreSQLのバージョンがサポートしている場合は、ハッシュ化された認証またはより強力な認証を優先してください。レガシーMD5認証の場合、保存される値はmd5にパスワードとユーザー名のMD5ハッシュを加えたものであり、パスワードだけではありません。

4. pgbouncer.iniの設定

pgbouncer.iniファイルはプーラーの動作を定義します。以下は、トランザクションプーリングを使用する一般的なWebアプリケーション設定に合わせた例です。

[databases]
# クライアント接続文字列の定義：
# <データベース名> = host=<pg_server_ip> port=<pg_port> dbname=<db_name> user=<pgbouncer_auth_user>
myappdb = host=10.0.0.5 port=5432 dbname=productiondb user=pgbouncer_service

[pgbouncer]

; リスニング設定
listen_addr = *
listen_port = 6432

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

; プーリングモード（アプリケーションのニーズに基づいて設定）
pool_mode = transaction
; DISCARD ALLはセッションプーリングのデフォルトのリセットクエリです。
; トランザクションプーリングでは、セッション状態に依存する前に注意深くテストしてください。
server_reset_query = DISCARD ALL

; 接続制限とサイズ
; PgBouncerへの最大クライアント接続数
max_client_conn = 1000

; PgBouncerがデータベースごとに保持する最大接続数（プールのサイズ）
default_pool_size = 20

; すべてのデータベースにわたってプール全体で許可する最大接続数
max_db_connections = 100

; プールが枯渇した場合、この数のスロットを予約
reserve_pool_size = 5

; ロギングと管理
admin_users = postgres, admin_user
stats_users = postgres

監視と管理

PgBouncerは、pgbouncerという疑似データベースを公開しており、管理者はプーラーのステータス、統計、接続をリアルタイムで監視できます。定義されたadmin_usersのいずれかを使用して、PgBouncerリスナーポート（例：6432）に接続します。

psql -p 6432 -U admin_user pgbouncer

主要な管理コマンド：

コマンド 説明 使用上の注意
SHOW STATS; 接続統計（リクエスト、バイト、合計期間）を表示します。 パフォーマンス分析に役立ちます。
SHOW POOLS; 設定されたすべてのデータベースのプールの状態を表示します。 cl_active、sv_active、sv_idleを監視します。
SHOW CLIENTS; PgBouncerに接続されているすべてのクライアント接続を一覧表示します。
RELOAD; 接続を中断せずに設定の再読み込みを試みます。
PAUSE; 新しいクエリの受け入れを停止し、現在のトランザクションが終了するのを待ちます。 メンテナンスやPgBouncerのアップグレード前に使用します。

スケーリングのヒント

  1. 配置： PgBouncerをアプリケーションと同じサーバー、またはネットワーク最適化された専用マシンにインストールして、アプリケーションとプーラー間のレイテンシを最小限に抑えます。
  2. プールサイズ設定： default_pool_sizeは妥当な数値（多くの場合10〜50）に設定する必要があります。これは通常、PostgreSQLサーバー自体で許可される接続数よりもはるかに低くなります。過度に大きなプールサイズはプーリングの目的を無効にします。
  3. クライアント制限： max_client_connを使用して、接続の急増がPgBouncer自体を圧倒するのを防ぎます。これは堅牢なフロントエンドのスロットルとして機能します。

まとめ

PgBouncerは、アプリケーションに多数の短命またはアイドル状態の接続がある場合に最も役立ちます。アプリケーションが許容できる最も控えめなプーリングモードを選択し、データベースを保護するためにPostgreSQLサーバープールを十分に小さく保ち、ユーザーが速度低下を感じる前にSHOW POOLS;で待機中のクライアントを監視してください。