PgBouncerを使うことでPostgreSQLサーバーの負荷を減らし、パフォーマンスを向上する
PostgreSQLデータベースサーバーは、クライアントアプリケーションからのリクエストを受け付けて処理し、結果を返します。アプリケーションのコードはデータベースとやり取りします。データベースはデータを検索、保存、操作し、クライアントに応答します。PostgreSQLはクライアント接続とどのように連携しているのでしょうか？
PostgreSQLは、各クライアント接続ごとにその接続を担当する個別のプロセスを作成します。例えば、10個のクライアント接続がある場合、PostgreSQLは10個のプロセスを作成します。100個のクライアント接続があれば、100個のPostgreSQLサーバープロセスがそれぞれのクライアント接続に対応します。
このようなアーキテクチャは、迅速かつ効率的に動作するでしょうか？答えはノーです。このようなアーキテクチャは効率的でも、速くも、スケーラブルでもありません。
やるべきことは、リクエストごとに別個のデータベース接続を作成しないようにすることです。
これをどうするか？これは、コネクションプーラーと呼ばれる特別なユーティリティを使用して実現できます。PostgreSQLデータベースサーバーの場合、よく使用されるコネクションプーラーの一つにPgBouncerがあります。
PgBouncerは非常に多くの接続を処理し、ごく少数の実際のデータベース接続にリダイレクトできます。クライアントアプリケーションからデータベースに対して何百ものリクエストがある場合、それらはPgBouncerを通過し、データベースへのいくつかの接続に分配され、必要に応じてキューを作成し、リダイレクトし、全てがうまく機能します。
このアプローチの利点は何でしょうか？まず第一に、アプリケーションはリクエストの数が急激に増加しても引き続き機能し続けるため、各リクエストがデータベースへの個別のプロセスを作成しないからです。代わりに、PgBouncerにリクエストが送られ、データベースへの少数の接続に変換されます。二つ目の利点は、リクエストごとに専用のプロセスをデータベースで作成しないために時間が節約されるため、アプリケーションがより速く動作することです。
PgBouncerのインストールと設定方法は？
PostgreSQLサーバーがあるとしましょう。
dmi@dmi-VirtualBox:~$ psql -h 127.0.0.1 -p 5432 -U postgres -d postgres
Password for user postgres:
psql (15.1 (Ubuntu 15.1-1.pgdg22.04+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.
postgres=# select version();
version
-----------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 15.1 (Ubuntu 15.1-1.pgdg22.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.3.0-1ubuntu1~22.04) 11.3.0, 64-bit
(1 row)
PgBouncerのインストールを行いましょう。
sudo apt-get install pgbouncer
次にPgBouncerを設定します。
sudo vi /etc/pgbouncer/pgbouncer.ini
[databases]セクションに下記のエントリを追加します。
* = host=localhost port=5432
"Pooler personality questions"セクションで、pool_mode=transactionと定義します。
...
;;;
;;; Pooler personality questions
;;;
;; When server connection is released back to pool:
;; session - after client disconnects (default)
;; transaction - after transaction finishes
;; statement - after statement finishes
pool_mode = transaction
...
"Connection limits"セクションでは、接続可能なクライアントの総数を高い値に設定します：max_client_conn=5000。
...
;;;
;;; Connection limits
;;;
;; Total number of clients that can connect
max_client_conn = 5000
...
"Authentication settings"セクションでは、auth_type = md5に設定して、パスワードでユーザーを認証します。データベースのログインとパスワードが格納されるファイルは/etc/pgbouncer/userlist.txtにあります。
...
;;;
;;; Authentication settings
;;;
;; any, trust, plain, md5, cert, hba, pam
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
...
"Users allowed into database 'pgbouncer'"セクションでは、admin_usersパラメータを設定します。これは、pgbouncerの設定をデータベースで行う権限を持つデータベースユーザーです。
...
;;;
;;; Users allowed into database 'pgbouncer'
;;;
;; comma-separated list of users who are allowed to change settings
;admin_users = user2, someadmin, otheradmin
admin_users = my_db_user
...
これで/etc/pgbouncer/userlist.txtにユーザーファイルを開きます。
PostgreSQLの13バージョンまでを使用している場合、デフォルトのpassword_encryption方法はmd5です。
ここにユーザー名を二重引用符で囲み、md5パスワードのハッシュ値を1行で配置します。
"my_db_user" "md5badc318d987f61146c6ad8e15d84a111"
md5パスワードハッシュを決定するためには、次の方法が使えます。
echo "md5"$(echo -n 'YourdbpasswordYourdbusername' | md5sum | awk ' { print $1 } ')
それから、pgbouncerをリロードしましょう。
sudo service pgbouncer restart
それから、pgbouncerを使ってポート6432（pgbouncerのデフォルトポート）を使ってデータベースに接続できるようになります。
PostgreSQLの14バージョンからは、デフォルトのpassword_encryption方法はscram-sha-256です。
ここでユーザー名を二重引用符で囲み、scram-sha-256パスワードのハッシュ値を1行で配置します。
"my_db_user" "SCRAM-SHA-256$4096:lLN4+i05+kpeffD4s3rRiw==$Oq62iUGamAaF5cpB+agWV4u3xfc5cZCRtvMhmA+Zm3E=:hHkCesEi0p0wLWk1uUEeTtJTYLXHKDLdy2te3VAOe8s="
scram-sha-256パスワードハッシュを決定する方法は以下の通りです。
psql -h <db_host> -p <db_port> -Atq -U postgres -d postgres -c "SELECT concat('\"', usename, '\" \"', passwd, '\"') FROM pg_shadow"
アプリケーションがデータベースに接続する際にpgbouncerを使用するようにするためには、ポート番号を5432から6432に変更するだけです。
pgbenchユーティリティを使って、pgbouncerを使用するかしないかでPostgreSQLへの接続パフォーマンスを比較するテストを実行しましょう。
私のブログからpgbenchユーティリティについて学ぶことができます。
私のデータベースの最大接続数は100に設定されています。
postgres=# show max_connections;
max_connections
-----------------
100
(1 row)
pgbouncerを使用せずにPostgresデータベースサーバーに接続します。
このコマンドは、直接PostgreSQLデータベースに接続する1000の同時クライアントで60秒間のテストを開始します。
dmi@dmi-VirtualBox:~$ pgbench -c 1000 -T 60 my_benchmark_test_db -h 127.0.0.1 -p 5432 -U my_db_user
Password:
pgbench (15.1 (Ubuntu 15.1-1.pgdg22.04+1))
starting vacuum...end.
pgbench: error: connection to server at "127.0.0.1", port 5432 failed: FATAL: sorry, too many clients already
connection to server at "127.0.0.1", port 5432 failed: FATAL: sorry, too many clients already
pgbench: error: could not create connection for client 44
100のクライアントしか同時に接続できないデータベースで1000のクライアントの動作をシミュレートした結果、エラーとなります。
FATAL: sorry, too many clients already
pgbouncerを使用してPostgresデータベースサーバーに接続します：
pgbouncerを使用してデータベースに接続すると、問題なく動作します。
pgbench -c 1000 -T 60 my_benchmark_test_db -h 127.0.0.1 -p 6432 -U my_db_user
Password:
pgbench (15.1 (Ubuntu 15.1-1.pgdg22.04+1))
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 50
query mode: simple
number of clients: 1000
number of threads: 1
maximum number of tries: 1
duration: 60 s
number of transactions actually processed: 47370
number of failed transactions: 0 (0.000%)
latency average = 1106.280 ms
initial connection time = 8788.955 ms
tps = 903.930420 (without initial connection time)
dmi@dmi-VirtualBox:~$
アプリケーションがpgbouncerを使用してデータベースに接続する場合と、使用しない場合で、データベースが実行する1秒あたりのトランザクション数を比較しましょう。
dmi@dmi-VirtualBox:~$ cat mysql.sql
select 1;
pgbenchの-Cオプションは、pgbenchが各トランザクションごとに開いた接続を閉じて新しい接続を作成することを示しています。これは接続のオーバーヘッドを測定するのに有用です。
私のテストでは、select-onlyトランザクションを実施しています。その理由は、多数のトランザクションが他のトランザクションを待ってブロックされるアップデートコンテンションを測定から排除したいからです。
アプリケーションがpgbouncerを使用せずにデータベースに接続します：
dmi@dmi-VirtualBox:~$ pgbench -c 20 -t 100 -S my_benchmark_test_db -h 127.0.0.1 -p 5432 -U my_db_user -C -f mysql.sql
Password:
pgbench (15.1 (Ubuntu 15.1-1.pgdg22.04+1))
starting vacuum...end.
transaction type: multiple scripts
scaling factor: 50
query mode: simple
number of clients: 20
number of threads: 1
maximum number of tries: 1
number of transactions per client: 100
number of transactions actually processed: 2000/2000
number of failed transactions: 0 (0.000%)
latency average = 340.479 ms
average connection time = 16.910 ms
tps = 58.740729 (including reconnection times)
SQL script 1: <builtin: select only>
- weight: 1 (targets 50.0% of total)
- 979 transactions (49.0% of total, tps = 28.753587)
- number of failed transactions: 0 (0.000%)
- latency average = 158.504 ms
- latency stddev = 133.666 ms
SQL script 2: mysql.sql
- weight: 1 (targets 50.0% of total)
- 1021 transactions (51.0% of total, tps = 29.987142)
- number of failed transactions: 0 (0.000%)
- latency average = 162.888 ms
- latency stddev = 136.175 ms
アプリケーションがpgbouncerを通してデータベースに接続します：
dmi@dmi-VirtualBox:~$ pgbench -c 20 -t 100 -S my_benchmark_test_db -h 127.0.0.1 -p 6432 -U my_db_user -C -f mysql.sql
Password:
pgbench (15.1 (Ubuntu 15.1-1.pgdg22.04+1))
starting vacuum...end.
transaction type: multiple scripts
scaling factor: 50
query mode: simple
number of clients: 20
number of threads: 1
maximum number of tries: 1
number of transactions per client: 100
number of transactions actually processed: 2000/2000
number of failed transactions: 0 (0.000%)
latency average = 178.276 ms
average connection time = 8.867 ms
tps = 112.185757 (including reconnection times)
SQL script 1: <builtin: select only>
- weight: 1 (targets 50.0% of total)
- 1022 transactions (51.1% of total, tps = 57.326922)
- number of failed transactions: 0 (0.000%)
- latency average = 85.993 ms
- latency stddev = 50.377 ms
SQL script 2: mysql.sql
- weight: 1 (targets 50.0% of total)
- 978 transactions (48.9% of total, tps = 54.858835)
- number of failed transactions: 0 (0.000%)
- latency average = 84.039 ms
- latency stddev = 51.036 ms
dmi@dmi-VirtualBox:~$
平均レイテンシーとtpsは、pgbouncerを使ってデータベースに接続した場合の改善を示しています：
平均レイテンシー: 340.479 ms -> 178.276 ms --- 改善
tps: 58 -> 112 --- 改善
```<br><br>こちらの記事はdev.toの良い記事を日本人向けに翻訳しています。<br>[https://dev.to/dm8ry/pgbouncer-reduce-the-load-on-the-postgresql-server-and-improve-performance-37np](https://dev.to/dm8ry/pgbouncer-reduce-the-load-on-the-postgresql-server-and-improve-performance-37np)
