介绍
在本实验中，你将学习如何设置和使用 PgBouncer，一个轻量级的 PostgreSQL 连接池。连接池是提高数据库性能的关键技术，尤其适用于处理大量短暂连接的应用程序。通过管理可重用的数据库连接池，PgBouncer 减少了为每个客户端请求建立新连接的开销。
你将首先安装和配置 PgBouncer，创建必要的配置文件和用户认证文件。接下来，你将启动 PgBouncer 服务，并通过连接池测试与 PostgreSQL 数据库的连接。然后，你将使用 pgbench，一个标准的 PostgreSQL 性能测试工具，来模拟客户端负载并观察 PgBouncer 如何管理连接。最后，你将学习如何连接到 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 是在实验设置阶段为 postgres 用户设置的。
"postgres" "labex_password"
通过按 Ctrl+O、Enter 和 Ctrl+X 保存并退出编辑器。
你现在已经成功配置了 PgBouncer 来管理你的 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 连接到 PgBouncer 正在监听的端口（6432），而不是默认的 PostgreSQL 端口（5432），来测试连接。
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 shell。
\q
使用 pgbench 模拟客户端负载
为了观察连接池的实际运行情况，你将使用 pgbench，这是 PostgreSQL 自带的标准基准测试工具。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 管理控制台。请注意，数据库名称是 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 shell。
\q
总结
在本实验中，你已成功配置并使用了 PgBouncer 进行 PostgreSQL 连接池管理。你学习了如何创建 pgbouncer.ini 配置文件和 userlist.txt 认证文件。你启动了 PgBouncer 服务，并验证了它可以代理连接到你的 PostgreSQL 数据库。通过使用 pgbench，你模拟了真实的客户端负载，然后使用 PgBouncer 的管理控制台监控了结果。这让你对连接池的工作原理及其如何被监控以确保高效的数据库性能有了实际的了解。
