Agrupamiento de Conexiones PgBouncer con PostgreSQL

Introducción

En este laboratorio, aprenderá cómo configurar y usar PgBouncer para la agrupación de conexiones (connection pooling) con PostgreSQL. PgBouncer actúa como un agrupador de conexiones ligero (lightweight connection pooler), que se sitúa entre su aplicación y la base de datos PostgreSQL para administrar las conexiones de manera eficiente. Esto reduce la sobrecarga de crear nuevas conexiones para cada solicitud, mejorando significativamente el rendimiento, especialmente para aplicaciones con conexiones de base de datos frecuentes y de corta duración (short-lived).

El laboratorio le guía a través de la configuración de PgBouncer, incluyendo la creación de un archivo de configuración con los detalles de conexión de la base de datos y la configuración del pool. Luego, se conectará a la base de datos a través de PgBouncer, simulará múltiples conexiones de cliente para observar la agrupación en acción y supervisará el rendimiento de PgBouncer para comprender los beneficios de la agrupación de conexiones.

Skills Graph

%%%%{init: {'theme':'neutral'}}%%%% flowchart RL postgresql(("PostgreSQL")) -.-> postgresql/PostgreSQLGroup(["PostgreSQL"]) postgresql/PostgreSQLGroup -.-> postgresql/db_access("Connect To Database") postgresql/PostgreSQLGroup -.-> postgresql/sys_version("Get Server Version") subgraph Lab Skills postgresql/db_access -.-> lab-550957{{"Agrupamiento de Conexiones PgBouncer con PostgreSQL"}} postgresql/sys_version -.-> lab-550957{{"Agrupamiento de Conexiones PgBouncer con PostgreSQL"}} end

Configurar PgBouncer y Conectarse a PostgreSQL

En este paso, configurará PgBouncer para la agrupación de conexiones (connection pooling) y se conectará a su base de datos PostgreSQL a través de él.

Crear un archivo de configuración de PgBouncer:

Primero, cree un directorio para almacenar el archivo de configuración de PgBouncer:
```
mkdir -p ~/project/pgbouncer
cd ~/project/pgbouncer
```
Luego, cree el archivo de configuración pgbouncer.ini usando nano:
```
nano pgbouncer.ini
```
Pegue la siguiente configuración en el archivo 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] define la conexión a la base de datos. mydb es un alias.
- [pgbouncer] configura PgBouncer. listen_addr y listen_port son la dirección y el puerto en los que PgBouncer escucha. pool_mode se establece en transaction.
Presione Ctrl+O para guardar el archivo, luego Ctrl+X para salir de nano.
Crear un archivo de lista de usuarios:

PgBouncer necesita un archivo de lista de usuarios (user list file) para la autenticación. Cree userlist.txt:
```
nano userlist.txt
```
Agregue la siguiente línea, reemplazando labex_password con la contraseña real para el usuario postgres (puede establecerla usando sudo -u postgres psql -c "ALTER USER postgres WITH PASSWORD 'labex_password';") :
```
"postgres" "labex_password"
```
Presione Ctrl+O para guardar el archivo, luego Ctrl+X para salir de nano.
Iniciar PgBouncer:

Inicie PgBouncer usando el archivo de configuración:
```
pgbouncer pgbouncer.ini
```
Mantenga esta terminal abierta. Abra una nueva terminal para los siguientes pasos.
Conectarse a PostgreSQL a través de PgBouncer:

En la nueva terminal, conéctese a PostgreSQL a través de PgBouncer usando psql:
```
psql -h 127.0.0.1 -p 6432 -U postgres -d postgres
```
Ingrese la contraseña que configuró en userlist.txt. Si tiene éxito, verá el prompt de psql.
```
psql (13.3, server 1.18.0)
Type "help" for help.

postgres=#
```

Crear una Tabla de Ejemplo y Datos

Ahora que está conectado a PostgreSQL a través de PgBouncer, vamos a crear una tabla de ejemplo e insertar algunos datos.

Crear una tabla:

Ejecute el siguiente comando SQL para crear una tabla llamada users:
```
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255),
    email VARCHAR(255)
);
```
Debería ver la siguiente salida:
```
CREATE TABLE
```

Insertar datos en la tabla:

Inserte algunos datos de ejemplo en la tabla users:

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

Debería ver la siguiente salida:

INSERT 0 3

Verificar los datos:

Verifique que los datos se hayan insertado correctamente consultando la tabla:

SELECT * FROM users;

Debería ver los datos insertados:

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

Simular Múltiples Conexiones de Cliente

En este paso, simulará múltiples conexiones de cliente a la base de datos PostgreSQL a través de PgBouncer.

Crear un script para conectar y consultar:

Cree un script llamado connect_and_query.sh en su directorio ~/project:
```
nano ~/project/connect_and_query.sh
```
Pegue el siguiente script en el archivo:
```
#!/bin/bash

psql -h 127.0.0.1 -p 6432 -U postgres -d postgres -c "SELECT COUNT(*) FROM users;"
```
Este script se conecta a la base de datos a través de PgBouncer y ejecuta una consulta para contar el número de usuarios.

Haga que el script sea ejecutable:
```
chmod +x ~/project/connect_and_query.sh
```
Simular conexiones concurrentes:

Use xargs para ejecutar el script varias veces concurrentemente:
```
seq 5 | xargs -P 5 -I {} ~/project/connect_and_query.sh
```
Ingrese la contraseña cuando se le solicite. Debería ver el recuento de usuarios impreso varias veces.

Monitorear el Rendimiento del Agrupamiento (Pooling)

En este paso, monitoreará el rendimiento del agrupamiento (pooling) de PgBouncer.

Conectarse a la base de datos de administración de PgBouncer:

Conéctese a la base de datos pgbouncer:
```
psql -h 127.0.0.1 -p 6432 -U postgres -d pgbouncer
```
Ingrese la contraseña cuando se le solicite.
Mostrar las estadísticas de PgBouncer:

Ejecute el siguiente comando SQL para mostrar las estadísticas:
```
SHOW STATS;
```
Esto mostrará estadísticas sobre la actividad de PgBouncer, incluyendo el total de solicitudes, bytes recibidos y bytes enviados.
Mostrar las estadísticas del pool (grupo):

Ejecute el siguiente comando SQL para mostrar las estadísticas del pool (grupo de conexiones):
```
SHOW POOLS;
```
Esto mostrará estadísticas sobre el connection pool (grupo de conexiones), incluyendo las conexiones de cliente activas, las conexiones de cliente en espera, las conexiones de servidor activas y las conexiones de servidor inactivas.

Al examinar estas estadísticas, puede comprender cómo PgBouncer está administrando las conexiones e identificar posibles cuellos de botella (bottlenecks).
Salir de psql:
```
\q
```

Resumen

En este laboratorio, configuró PgBouncer para el connection pooling (agrupamiento de conexiones), creó una tabla de muestra y datos, simuló múltiples conexiones de cliente y monitoreó el rendimiento de PgBouncer. Aprendió cómo PgBouncer administra eficientemente las conexiones de la base de datos, reduciendo la sobrecarga (overhead) y mejorando el rendimiento para aplicaciones con conexiones frecuentes y de corta duración (short-lived connections). Al monitorear las estadísticas de PgBouncer, puede optimizar su configuración y garantizar un connection pooling (agrupamiento de conexiones) eficiente.