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

PgBouncer を用いた高トラフィックアプリケーションの PostgreSQL 接続プール構成

PostgreSQL の接続プールに PgBouncer を使用すると、数千の同時接続を処理し、リソースのオーバーヘッドを削減し、アプリケーションのパフォーマンスを劇的に向上させることができます

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

PostgreSQLデータベースが大量の接続に直面すると、パフォーマンスは急速に低下する可能性があります。各クライアント接続はPostgreSQLのバックエンドプロセスにマッピングされるため、ビジーなWebアプリケーションはセッションを開いたままにするだけでメモリとCPUを過剰に消費する可能性があります。PgBouncer接続プーリングは、多数のクライアント接続が少数のサーバー接続を再利用できるようにすることで、その負荷を軽減します。

接続プーリングが重要な理由

接続の問題

  • リソースオーバーヘッド: すべてのPostgreSQL接続にはバックエンドプロセスとメモリオーバーヘッドがあります。
  • 接続制限: max_connectionsは有限であり、これを高く設定しすぎると、データベースが高速になるどころか不安定になる可能性があります。
  • 起動コスト: 新しいデータベース接続を作成するとレイテンシが増加します。
  • コンテキストスイッチ: アクティブなバックエンドプロセスが多すぎると、CPUを浪費する可能性があります。

PgBouncerの利点

  • 多数のアプリケーションクライアントが少数のPostgreSQLサーバー接続を共有できるようにします。
  • プールがビジーな場合、データベースを圧倒する代わりにクライアントをキューに入れます。
  • セッション、トランザクション、ステートメントプーリングモードをサポートします。
  • SHOW POOLS、SHOW CLIENTS、RELOADなどの運用コマンドを提供します。

インストールと基本設定

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

パッケージのデフォルトはディストリビューションによって異なるため、インストール後にサービスファイルと設定パスを確認してください。多くのLinuxパッケージでは、/etc/pgbouncer/pgbouncer.iniがメインファイルです。

プーリングモードの理解

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

  • 動作: クライアントにセッション全体で接続が割り当てられます。
  • ユースケース: 一時テーブルやプリペアドステートメントを使用するアプリケーション。
  • 効率: 低い（1:1の接続比率）。
pool_mode = session

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

  • 動作: 各トランザクション後に接続がプールに戻されます。
  • ユースケース: 短いトランザクションを持つほとんどのWebアプリケーション。
  • 効率: 高い（アプリがセッション状態に依存しない場合）。
pool_mode = transaction
default_pool_size = 25
max_client_conn = 1000

トランザクションプーリングは、リクエスト/レスポンス型のWebアプリケーションには強力なデフォルトですが、セッションレベルの機能に関する前提を破る可能性があります。一時テーブル、セッション変数、トランザクション外で保持されるアドバイザリロック、LISTEN/NOTIFY、ドライバーレベルのプリペアドステートメントには注意が必要です。これらを使用する場合は、PgBouncerのバージョンと設定でテストしてください。

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

  • 動作: 各ステートメント後に接続が返されます。
  • ユースケース: トランザクションのない単純な読み取り専用クエリ。
  • 効率: 最大（ただし非常に制限的）。
pool_mode = statement
; 注意して使用 - マルチステートメントトランザクションを壊します

認証設定

userlist.txtの作成

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

userlist.txtの例:

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

PostgreSQLスタイルのMD5パスワードの場合、値はmd5にpassword + usernameのMD5ハッシュを連結したものです。本番環境に偽のハッシュを貼り付けないでください。実際のユーザー名とパスワードからエントリを生成するか、環境でサポートされているより安全な認証方法を使用してください。

PostgreSQL auth_queryの使用（高度）

PgBouncerは、auth_fileにユーザーが見つからない場合にPostgreSQLにユーザー資格情報を照会できますが、これにはPgBouncerがログインできるauth_userが必要です。最小限の例は次のようになります。

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

認証ユーザーの権限を制限し、PostgreSQLバージョンのガイダンスに従ってください。多くのチームは、カタログパスワードデータへの直接アクセスを許可する代わりに、SECURITY DEFINER関数を使用しています。

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

接続プールのサイジング

万能のプールサイズの公式はありません。データベースが適切に実行できるアクティブなクエリの数に基づいて控えめな値から始め、実際のメトリクスから調整してください。

典型的なWebアプリケーションの場合、ここから始めて調整できます：

default_pool_size = 25
reserve_pool_size = 5
max_client_conn = 1000

PostgreSQLのCPU、クエリレイテンシ、ロック待機、およびPgBouncerのcl_waitingカウントを監視してください。データベースがアイドル状態でクライアントが待機している場合、プールが小さすぎる可能性があります。データベースが飽和状態の場合、プールを増やすと状況が悪化する可能性があります。

完全な本番環境設定

[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

max_prepared_statements = 0は、PgBouncerのプリペアドステートメント追跡を無効にします。最近のPgBouncerバージョンでは、これを0より大きく設定すると、トランザクションプーリングでプロトコルレベルのプリペアドステートメントをサポートできますが、有効にする前にドライバーとワークロードをテストする必要があります。

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

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"

これは、PgBouncerがクライアント接続を拒否しているか、PostgreSQLがサーバー接続を拒否している可能性があります。エラーが表示される場所を確認してください。

PgBouncer側で可能な変更：

max_client_conn = 5000
default_pool_size = 50

両方を増やす前に、OSのファイルディスクリプタ制限とPostgreSQLのmax_connectionsが新しい合計をサポートできることを確認してください。PgBouncer自体も、クライアントおよびサーバーソケットに十分なファイルディスクリプタが必要です。

問題2: cl_waitingカウントが高い

解決策:

  1. プールサイズを増やす
  2. 低速クエリを最適化する
  3. リザーブプールを追加する

問題3: プリペアドステートメントエラー

アプリケーションまたはドライバーがプリペアドステートメントを使用しており、トランザクションプーリングモードの場合、後続のステートメントが別のサーバー接続に割り当てられるとエラーが発生する可能性があります。オプションは次のとおりです：

  1. ドライバー側のプリペアドステートメントを無効にする。
  2. そのワークロードにセッションプーリングを使用する。
  3. 最近のPgBouncerバージョンで、max_prepared_statementsに正の値を設定してPgBouncerのプリペアドステートメントサポートをテストする。

控えめな設定：

max_prepared_statements = 0

実践的なデプロイ例

アプリケーションサーバーが数百の同時HTTPリクエストを開くことができるが、データベースは数十のアクティブなクエリで最適に動作するとします。アプリケーションをPgBouncerのポート6432に向け、max_client_connをクライアントのバーストに対応できる十分な高さに設定し、default_pool_sizeをそのデータベース/ユーザーペアに対して実際にアクティブにしたいデータベース接続数に近づけます。

次に、以下で検証します：

SHOW POOLS;
SHOW STATS;

通常のトラフィック中にcl_waitingがゼロより大きいままの場合は、単にプールサイズを増やす前に低速クエリを調査してください。PgBouncerはPostgreSQLを接続ストームから保護しますが、低速なSQLを高速化するわけではありません。

実践的なポイント

ステートレスなWebワークロードにはトランザクションプーリングから始め、プールサイズを意図的に小さく保ち、PgBouncerとPostgreSQLのメトリクスから調整してください。アプリケーションがセッション動作やプリペアドステートメントに依存している場合は、本番トラフィックの前にPgBouncerを配置する前にそれらのパスをテストしてください。