PostgreSQL PgBouncer 连接池化

介绍

在这个实验中，你将学习如何配置和使用 PgBouncer 进行 PostgreSQL 的连接池管理。PgBouncer 作为一个轻量级的连接池工具，位于你的应用程序和 PostgreSQL 数据库之间，有效地管理连接。这减少了为每个请求创建新连接的开销，从而显著提高性能，特别是对于具有频繁、短生命周期数据库连接的应用程序。

本实验将指导你完成 PgBouncer 的配置，包括创建包含数据库连接详细信息和连接池设置的配置文件。然后，你将通过 PgBouncer 连接到数据库，模拟多个客户端连接以观察连接池的实际效果，并监控 PgBouncer 的性能，从而了解连接池的优势。

Skills Graph

配置 PgBouncer 并连接到 PostgreSQL

在此步骤中，你将配置 PgBouncer 以进行连接池管理，并通过它连接到你的 PostgreSQL 数据库。

1. 创建 PgBouncer 配置文件：

   首先，创建一个目录来存储 PgBouncer 配置文件：

   mkdir -p ~/project/pgbouncer
   cd ~/project/pgbouncer

   然后，使用 nano 创建配置文件 pgbouncer.ini：

   nano pgbouncer.ini

   将以下配置粘贴到 pgbouncer.ini 文件中：

   [databases]
   mydb = host=127.0.0.1 port=5432 dbname=postgres user=postgres
   
   [pgbouncer]
   listen_addr = 127.0.0.1
   listen_port = 6432
   user = postgres
   pool_mode = transaction
   server_reset_query = DISCARD ALL
   default_pool_size = 20
   max_client_conn = 100

   • [databases] 定义数据库连接。mydb 是一个别名。
   • [pgbouncer] 配置 PgBouncer。listen_addr 和 listen_port 是 PgBouncer 监听的地址和端口。pool_mode 设置为 transaction（事务模式）。

   按 Ctrl+O 保存文件，然后按 Ctrl+X 退出 nano。

2. 创建用户列表文件：

   PgBouncer 需要一个用户列表文件来进行身份验证。创建 userlist.txt：

   nano userlist.txt

   添加以下行，将 labex_password 替换为 postgres 用户的实际密码（你可以使用 sudo -u postgres psql -c "ALTER USER postgres WITH PASSWORD 'labex_password';" 设置它）：

   "postgres" "labex_password"

   按 Ctrl+O 保存文件，然后按 Ctrl+X 退出 nano。

3. 启动 PgBouncer：

   使用配置文件启动 PgBouncer：

   pgbouncer pgbouncer.ini

   保持此终端打开。打开一个新的终端进行下一步操作。

4. 通过 PgBouncer 连接到 PostgreSQL：

   在新终端中，使用 psql 通过 PgBouncer 连接到 PostgreSQL：

   psql -h 127.0.0.1 -p 6432 -U postgres -d postgres

   输入你在 userlist.txt 中设置的密码。如果成功，你将看到 psql 提示符。

   psql (13.3, server 1.18.0)
   Type "help" for help.
   
   postgres=#

创建示例表和数据

现在你已经通过 PgBouncer 连接到 PostgreSQL，让我们创建一个示例表并插入一些数据。

1. 创建表：

   执行以下 SQL 命令来创建一个名为 users 的表：

   CREATE TABLE users (
       id SERIAL PRIMARY KEY,
       name VARCHAR(255),
       email VARCHAR(255)
   );

   你应该看到以下输出：

   CREATE TABLE

2. 将数据插入到表中：

   将一些示例数据插入到 users 表中：

   INSERT INTO users (name, email) VALUES
   ('Alice', '[email protected]'),
   ('Bob', '[email protected]'),
   ('Charlie', '[email protected]');

   你应该看到以下输出：

   INSERT 0 3

3. 验证数据：

   通过查询表来验证数据是否已正确插入：

   SELECT * FROM users;

   你应该看到插入的数据：

    id |  name   |       email
   ----+---------+-----------------------
     1 | Alice   | [email protected]
     2 | Bob     | [email protected]
     3 | Charlie | [email protected]
   (3 rows)

模拟多个客户端连接

在此步骤中，你将模拟通过 PgBouncer 连接到 PostgreSQL 数据库的多个客户端连接。

1. 创建一个连接和查询的脚本：

   在你的 ~/project 目录中创建一个名为 connect_and_query.sh 的脚本：

   nano ~/project/connect_and_query.sh

   将以下脚本粘贴到文件中：

   #!/bin/bash
   
   psql -h 127.0.0.1 -p 6432 -U postgres -d postgres -c "SELECT COUNT(*) FROM users;"

   这个脚本通过 PgBouncer 连接到数据库，并执行一个查询来统计用户的数量。

   使脚本可执行：

   chmod +x ~/project/connect_and_query.sh

2. 模拟并发连接：

   使用 xargs 并发运行该脚本多次：

   seq 5 | xargs -P 5 -I {} ~/project/connect_and_query.sh

   出现提示时输入密码。你应该看到用户数量被打印多次。

监控连接池性能

在此步骤中，你将监控 PgBouncer 的连接池性能（pooling performance）。

1. 连接到 PgBouncer 管理数据库：

   连接到 pgbouncer 数据库：

   psql -h 127.0.0.1 -p 6432 -U postgres -d pgbouncer

   出现提示时输入密码。

2. 显示 PgBouncer 统计信息：

   执行以下 SQL 命令以显示统计信息：

   SHOW STATS;

   这将显示有关 PgBouncer 活动的统计信息，包括总请求数（total requests）、接收的字节数（received bytes）和发送的字节数（sent bytes）。

3. 显示连接池统计信息：

   执行以下 SQL 命令以显示连接池统计信息：

   SHOW POOLS;

   这将显示有关连接池的统计信息，包括活动客户端连接数（active client connections）、等待客户端连接数（waiting client connections）、活动服务端连接数（active server connections）和空闲服务端连接数（idle server connections）。

   通过检查这些统计信息，你可以了解 PgBouncer 如何管理连接并识别潜在的瓶颈。

4. 退出 psql：

   \q

总结

在这个实验（lab）中，你配置了 PgBouncer 用于连接池化（connection pooling），创建了一个示例表和数据，模拟了多个客户端连接，并监控了 PgBouncer 的性能。你学习了 PgBouncer 如何有效地管理数据库连接，从而减少开销并提高具有频繁、短生命周期连接的应用程序的性能。通过监控 PgBouncer 的统计信息，你可以优化其配置并确保高效的连接池化。