Agrupamiento de Conexiones PgBouncer para PostgreSQL

Introducción

En este laboratorio, aprenderá a configurar y utilizar PgBouncer, un agrupador de conexiones ligero para PostgreSQL. La agrupación de conexiones es una técnica fundamental para mejorar el rendimiento de la base de datos, especialmente para aplicaciones que manejan muchas conexiones de corta duración. Al gestionar un grupo de conexiones de base de datos reutilizables, PgBouncer reduce la sobrecarga de establecer nuevas conexiones para cada solicitud del cliente.

Comenzará instalando y configurando PgBouncer, creando los archivos de configuración y autenticación de usuarios necesarios. A continuación, iniciará el servicio PgBouncer y probará la conexión a su base de datos PostgreSQL a través del agrupador. Luego, utilizará pgbench, una herramienta estándar de benchmarking de PostgreSQL, para simular una carga de clientes y observar cómo PgBouncer gestiona las conexiones. Finalmente, aprenderá a conectarse a la consola de administración de PgBouncer para monitorizar su rendimiento y ver estadísticas de conexión.

Configurar PgBouncer

En este paso, creará los archivos de configuración necesarios para PgBouncer. Esto implica definir la cadena de conexión a la base de datos y configurar un archivo de autenticación para los usuarios.

Primero, cree un directorio dedicado para sus archivos de configuración de PgBouncer dentro de la carpeta de su proyecto.

mkdir -p /home/labex/project/pgbouncer
cd /home/labex/project/pgbouncer

A continuación, cree el archivo de configuración principal, pgbouncer.ini, utilizando el editor nano.

nano pgbouncer.ini

Pegue la siguiente configuración en el editor. Esta configuración indica a PgBouncer cómo conectarse a su base de datos PostgreSQL y en qué puerto escuchar las conexiones de los clientes.

[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

Revisemos los parámetros clave:

• [databases]: Define la cadena de conexión para la base de datos de destino.
• listen_port: El puerto en el que PgBouncer escuchará (6432). Su aplicación se conectará a este puerto.
• auth_type: Especifica el método de autenticación. md5 es una opción común.
• auth_file: La ruta al archivo que contiene los nombres de usuario y las contraseñas.
• admin_users: Una lista separada por comas de los usuarios autorizados a conectarse a la consola de administración de PgBouncer.
• pool_mode: Establecido en session, lo que significa que una conexión de servidor se asigna a un cliente durante toda la sesión.

Presione Ctrl+O para guardar el archivo, luego Enter, y Ctrl+X para salir de nano.

Ahora, cree el archivo de autenticación userlist.txt al que hizo referencia en la configuración.

nano userlist.txt

Añada la siguiente línea al archivo. La contraseña labex_password se estableció para el usuario postgres durante la fase de configuración del laboratorio.

"postgres" "labex_password"

Guarde y salga del editor presionando Ctrl+O, Enter, y Ctrl+X.

Ahora ha configurado correctamente PgBouncer para gestionar las conexiones de su base de datos PostgreSQL.

Iniciar PgBouncer y Probar la Conexión

Con la configuración lista, el siguiente paso es iniciar el servicio PgBouncer y verificar que puede conectarse a la base de datos PostgreSQL a través de él.

En su terminal, inicie PgBouncer como un demonio (opción -d) utilizando el archivo de configuración que creó.

pgbouncer -d /home/labex/project/pgbouncer/pgbouncer.ini

Puede verificar que el proceso PgBouncer se está ejecutando con el siguiente comando. Debería ver el proceso pgbouncer listado.

ps aux | grep pgbouncer

La salida será similar a esta:

labex      1234  0.0  0.0  12345   678 ?        Ss   12:00   0:00 pgbouncer -d /home/labex/project/pgbouncer/pgbouncer.ini

Ahora, pruebe la conexión utilizando psql para conectarse al puerto donde PgBouncer está escuchando (6432), no al puerto predeterminado de PostgreSQL (5432).

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

Se le pedirá la contraseña. Introduzca labex_password. Si la conexión es exitosa, verá el prompt de psql.

Para confirmar que está conectado a la base de datos, ejecute una consulta simple para verificar la versión de PostgreSQL.

SELECT version();

La salida mostrará la versión de su servidor PostgreSQL, confirmando que PgBouncer ha reenviado correctamente su conexión.

                                                 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)

Finalmente, salga del shell de psql escribiendo \q y presionando Enter.

\q

Simular Carga de Clientes con pgbench

Para observar el funcionamiento del agrupamiento de conexiones (connection pooling), utilizará pgbench, una herramienta de benchmarking estándar incluida con PostgreSQL. pgbench puede simular múltiples clientes accediendo a la base de datos de forma concurrente.

Primero, necesita inicializar el entorno de pgbench. Esto crea algunas tablas y las puebla con datos de ejemplo. Ejecute el siguiente comando, asegurándose de conectarse a través del puerto de PgBouncer (6432).

pgbench -i -h 127.0.0.1 -p 6432 -U postgres postgres

Se le pedirá la contraseña (labex_password). La opción -i inicializa la base de datos para el benchmarking. Debería ver una salida que indique que las tablas se han creado y poblado.

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).

Ahora, ejecute el benchmark para simular una carga. El siguiente comando simula 10 clientes concurrentes (-c 10), con cada cliente ejecutando 300 transacciones (-t 300).

pgbench -c 10 -t 300 -h 127.0.0.1 -p 6432 -U postgres postgres

Nuevamente, introduzca la contraseña cuando se le solicite. El benchmark se ejecutará durante unos segundos y luego mostrará un resumen de los resultados, incluyendo el número de transacciones por segundo (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)

Esta prueba generó un tráfico significativo a través de PgBouncer, que inspeccionaremos en el siguiente paso.

Monitorizar Estadísticas de PgBouncer

PgBouncer proporciona una base de datos administrativa especial que le permite monitorizar su actividad y ver estadísticas sobre los pools de conexiones.

Conéctese a la consola administrativa de PgBouncer. Tenga en cuenta que el nombre de la base de datos es pgbouncer.

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

Introduzca la contraseña labex_password cuando se le solicite.

Una vez conectado, puede ejecutar comandos especiales SHOW. Primero, vea las estadísticas generales de todas las bases de datos.

SHOW STATS;

Este comando muestra datos acumulados desde que PgBouncer se inició por última vez. Verá columnas como total_xact_count (transacciones totales) y total_query_count, que ahora deberían tener valores altos del test 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)

A continuación, para ver el estado de los pools de conexiones en tiempo real, utilice el comando SHOW POOLS.

SHOW POOLS;

Este comando proporciona una instantánea de los pools de conexiones, mostrando las conexiones activas e inactivas tanto para clientes como para servidores.

• cl_active: Conexiones de cliente que están activamente vinculadas a una conexión de servidor.
• sv_active: Conexiones de servidor que se están utilizando actualmente.
• sv_idle: Conexiones de servidor que están inactivas y listas para ser utilizadas por un nuevo cliente.

 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)

Al examinar estas estadísticas, puede comprender con qué eficacia PgBouncer está gestionando las conexiones y reutilizándolas para atender las solicitudes de los clientes.

Salga del shell de psql cuando haya terminado.

\q

Resumen

En este laboratorio, ha configurado y utilizado con éxito PgBouncer para el agrupamiento de conexiones de PostgreSQL. Aprendió a crear el archivo de configuración pgbouncer.ini y los archivos de autenticación userlist.txt. Inició el servicio PgBouncer y verificó que podía actuar como proxy para las conexiones a su base de datos PostgreSQL. Utilizando pgbench, simuló una carga de clientes realista y luego monitorizó los resultados utilizando la consola administrativa de PgBouncer. Esto proporcionó una visión práctica de cómo funciona el agrupamiento de conexiones y cómo se puede monitorizar para garantizar un rendimiento eficiente de la base de datos.