この実験（Lab）では、PostgreSQL 用の軽量コネクションプーラーである PgBouncer のセットアップと使用方法を学びます。コネクションプーリングは、データベースのパフォーマンスを向上させるための重要な技術であり、特に多くの短命な接続を処理するアプリケーションにとって重要です。PgBouncer は、再利用可能なデータベース接続のプールを管理することで、クライアントリクエストごとに新しい接続を確立するオーバーヘッドを削減します。
まず、PgBouncer のインストールと設定を行い、必要な設定ファイルとユーザー認証ファイルを作成します。次に、PgBouncer サービスを開始し、プーラーを介して PostgreSQL データベースへの接続をテストします。その後、標準的な PostgreSQL ベンチマークツールである pgbench を使用してクライアント負荷をシミュレートし、PgBouncer がどのように接続を管理するかを観察します。最後に、PgBouncer 管理コンソールに接続して、そのパフォーマンスを監視し、接続統計を表示する方法を学びます。
このステップでは、PgBouncer に必要な設定ファイルを作成します。これには、データベース接続文字列の定義と、ユーザー認証ファイルのセットアップが含まれます。
まず、プロジェクトフォルダ内に PgBouncer 設定ファイル専用のディレクトリを作成します。
mkdir -p /home/labex/project/pgbouncer
cd /home/labex/project/pgbouncer次に、nano エディタを使用してメイン設定ファイル pgbouncer.ini を作成します。
nano pgbouncer.ini以下の設定をエディタに貼り付けます。この設定は、PgBouncer が PostgreSQL データベースにどのように接続するか、およびクライアント接続のためにどのポートでリッスンするかを指示します。
[databases]
postgres = host=127.0.0.1 port=5432 dbname=postgres
[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = md5
auth_file = /home/labex/project/pgbouncer/userlist.txt
admin_users = postgres
pidfile = /home/labex/project/pgbouncer/pgbouncer.pid
logfile = /home/labex/project/pgbouncer/pgbouncer.log
pool_mode = session
default_pool_size = 20
max_client_conn = 100主要なパラメータを確認しましょう。
[databases]: 対象データベースの接続文字列を定義します。listen_port: PgBouncer がリッスンするポート（6432）です。アプリケーションはこのポートに接続します。auth_type: 認証方法を指定します。md5 は一般的な選択肢です。auth_file: ユーザー名とパスワードを含むファイルのパスです。admin_users: PgBouncer 管理コンソールへの接続が許可されるユーザーのカンマ区切りリストです。pool_mode: session に設定されています。これは、サーバー接続がセッション全体でクライアントに割り当てられることを意味します。Ctrl+O を押してファイルを保存し、Enter を押し、Ctrl+X を押して nano を終了します。
次に、設定で参照した認証ファイル userlist.txt を作成します。
nano userlist.txtファイルに以下の行を追加します。パスワード labex_password は、実験（Lab）のセットアップフェーズ中に postgres ユーザーに設定されたものです。
"postgres" "labex_password"Ctrl+O、Enter、Ctrl+X を押してエディタを保存して終了します。
これで、PostgreSQL データベースの接続を管理するために PgBouncer を正常に設定しました。
設定が完了したら、次に PgBouncer サービスを起動し、それを通じて PostgreSQL データベースに接続できることを確認します。
ターミナルで、作成した設定ファイルを使用して、デーモン（-d フラグ）として PgBouncer を起動します。
pgbouncer -d /home/labex/project/pgbouncer/pgbouncer.ini以下のコマンドで、PgBouncer プロセスが実行されていることを確認できます。pgbouncer プロセスがリストに表示されるはずです。
ps aux | grep pgbouncer出力は以下のようになります。
labex 1234 0.0 0.0 12345 678 ? Ss 12:00 0:00 pgbouncer -d /home/labex/project/pgbouncer/pgbouncer.ini次に、psql を使用して、デフォルトの PostgreSQL ポート（5432）ではなく、PgBouncer がリッスンしているポート（6432）に接続してテストします。
psql -h 127.0.0.1 -p 6432 -U postgres -d postgresパスワードの入力を求められます。labex_password を入力してください。接続が成功すると、psql プロンプトが表示されます。
データベースに接続されていることを確認するために、PostgreSQL のバージョンを確認する簡単なクエリを実行します。
SELECT version();出力には PostgreSQL サーバーのバージョンが表示され、PgBouncer が接続を正常にプロキシしたことが確認できます。
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 14.18 (Ubuntu 14.18-0ubuntu0.22.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0...
(1 row)最後に、\q と入力して Enter を押して psql シェルを終了します。
\qコネクションプーリングの動作を観察するために、PostgreSQL に含まれる標準的なベンチマークツールである pgbench を使用します。pgbench は、複数のクライアントが同時にデータベースにアクセスするのをシミュレートできます。
まず、pgbench 環境を初期化する必要があります。これにより、いくつかのテーブルが作成され、サンプルデータが投入されます。以下のコマンドを実行し、PgBouncer ポート（6432）経由で接続していることを確認してください。
pgbench -i -h 127.0.0.1 -p 6432 -U postgres postgresパスワード（labex_password）の入力を求められます。-i フラグは、データベースをベンチマーク用に初期化します。テーブルが作成され、データが投入されたことを示す出力が表示されるはずです。
dropping old tables...
NOTICE: table "pgbench_accounts" does not exist, skipping
NOTICE: table "pgbench_branches" does not exist, skipping
NOTICE: table "pgbench_history" does not exist, skipping
NOTICE: table "pgbench_tellers" does not exist, skipping
creating tables...
generating data (client-side)...
100000 of 100000 tuples (100%) done (elapsed 0.01 s, remaining 0.00 s)
vacuuming...
creating primary keys...
done in 0.13 s (drop tables 0.00 s, create tables 0.00 s, client-side generate 0.06 s, vacuum 0.04 s, primary keys 0.02 s).次に、負荷をシミュレートするためにベンチマークを実行します。以下のコマンドは、10 の同時クライアント（-c 10）と、各クライアントが 300 のトランザクションを実行する（-t 300）ことをシミュレートします。
pgbench -c 10 -t 300 -h 127.0.0.1 -p 6432 -U postgres postgres再度、パスワードの入力を求められたら入力してください。ベンチマークは数秒間実行され、トランザクション/秒（tps）を含む結果の概要が表示されます。
pgbench (14.18 (Ubuntu 14.18-0ubuntu0.22.04.1))
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1
query mode: simple
number of clients: 10
number of threads: 1
number of transactions per client: 300
number of transactions actually processed: 3000/3000
latency average = 5.935 ms
initial connection time = 1.342 ms
tps = 1685.027854 (without initial connection time)このテストにより、PgBouncer を介してかなりのトラフィックが生成されました。これは次のステップで確認します。
PgBouncer は、そのアクティビティを監視し、コネクションプールの統計情報を表示できる特別な管理データベースを提供します。
PgBouncer の管理コンソールに接続します。データベース名は pgbouncer です。
psql -h 127.0.0.1 -p 6432 -U postgres -d pgbouncerパスワード labex_password を求められたら入力してください。
接続したら、特別な SHOW コマンドを実行できます。まず、すべてのデータベースの全体的な統計情報を表示します。
SHOW STATS;このコマンドは、PgBouncer が最後に起動されてからの累積データを表示します。total_xact_count（総トランザクション数）や total_query_count のような列が表示され、これらは pgbench テストによって高い値になっているはずです。
pgbouncer=## SHOW STATS;
database | total_xact_count | total_query_count | total_received | total_sent | total_xact_time | total_query_time | total_wait_time | avg_xact_count | avg_query_count | avg_recv | avg_sent | avg_xact_time | avg_query_time | avg_wait_time
-----------+------------------+-------------------+----------------+------------+-----------------+------------------+-----------------+----------------+-----------------+----------+----------+---------------+----------------+---------------
pgbouncer | 1 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
postgres | 3019 | 21033 | 2860191 | 561691 | 17758077 | 16667957 | 277707 | 36 | 250 | 34117 | 6700 | 5882 | 792 | 3312
(2 rows)次に、コネクションプールの状態をリアルタイムで確認するには、SHOW POOLS コマンドを使用します。
SHOW POOLS;このコマンドは、コネクションプールのスナップショットを提供し、クライアントとサーバーの両方のアクティブな接続とアイドル状態の接続を表示します。
cl_active: サーバー接続にアクティブにリンクされているクライアント接続。sv_active: 現在使用中のサーバー接続。sv_idle: アイドル状態で、新しいクライアントが使用できるサーバー接続。 database | user | cl_active | cl_waiting | cl_cancel_req | sv_active | sv_idle | sv_used | sv_tested | sv_login | maxwait | maxwait_us | pool_mode
-----------+-----------+-----------+------------+---------------+-----------+---------+---------+-----------+----------+---------+------------+-----------
pgbouncer | pgbouncer | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | statement
postgres | postgres | 0 | 0 | 0 | 0 | 10 | 0 | 0 | 0 | 0 | 0 | session
(2 rows)これらの統計情報を調べることで、PgBouncer がどのように効率的に接続を管理し、クライアント要求に応答するために再利用しているかを理解できます。
終了したら、psql シェルを終了します。
\qこの実験では、PostgreSQL のコネクションプーリングのために PgBouncer を正常に設定し、使用しました。pgbouncer.ini 設定ファイルと userlist.txt 認証ファイルの作成方法を学びました。PgBouncer サービスを開始し、PostgreSQL データベースへの接続をプロキシできることを確認しました。pgbench を使用して現実的なクライアント負荷をシミュレートし、その後 PgBouncer の管理コンソールを使用して結果を監視しました。これにより、コネクションプーリングがどのように機能し、効率的なデータベースパフォーマンスを確保するためにどのように監視できるかについての実践的な洞察が得られました。
