Pooling de Conexões PgBouncer para PostgreSQL

Introdução

Neste laboratório, você aprenderá como configurar e usar o PgBouncer, um pooler de conexões leve para PostgreSQL. O pooling de conexões é uma técnica crítica para melhorar o desempenho do banco de dados, especialmente para aplicações que lidam com muitas conexões de curta duração. Ao gerenciar um pool de conexões de banco de dados reutilizáveis, o PgBouncer reduz a sobrecarga de estabelecer novas conexões para cada solicitação do cliente.

Você começará instalando e configurando o PgBouncer, criando os arquivos de configuração e autenticação de usuário necessários. Em seguida, você iniciará o serviço PgBouncer e testará a conexão com seu banco de dados PostgreSQL através do pooler. Você então usará o pgbench, uma ferramenta padrão de benchmarking do PostgreSQL, para simular uma carga de clientes e observar como o PgBouncer gerencia as conexões. Finalmente, você aprenderá como se conectar ao console de administração do PgBouncer para monitorar seu desempenho e visualizar estatísticas de conexão.

Configurar o PgBouncer

Nesta etapa, você criará os arquivos de configuração necessários para o PgBouncer. Isso envolve definir a string de conexão do banco de dados e configurar um arquivo de autenticação para os usuários.

Primeiro, crie um diretório dedicado para seus arquivos de configuração do PgBouncer dentro da pasta do seu projeto.

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

Em seguida, crie o arquivo de configuração principal, pgbouncer.ini, usando o editor nano.

nano pgbouncer.ini

Cole a seguinte configuração no editor. Esta configuração informa ao PgBouncer como se conectar ao seu banco de dados PostgreSQL e em qual porta ele deve escutar as conexões dos 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

Vamos revisar os parâmetros principais:

• [databases]: Define a string de conexão para o banco de dados de destino.
• listen_port: A porta em que o PgBouncer escutará (6432). Sua aplicação se conectará a esta porta.
• auth_type: Especifica o método de autenticação. md5 é uma escolha comum.
• auth_file: O caminho para o arquivo que contém nomes de usuário e senhas.
• admin_users: Uma lista separada por vírgulas de usuários permitidos a se conectar ao console de administração do PgBouncer.
• pool_mode: Definido como session, o que significa que uma conexão de servidor é atribuída a um cliente durante toda a sessão.

Pressione Ctrl+O para salvar o arquivo, depois Enter, e Ctrl+X para sair do nano.

Agora, crie o arquivo de autenticação userlist.txt que você referenciou na configuração.

nano userlist.txt

Adicione a seguinte linha ao arquivo. A senha labex_password foi definida para o usuário postgres durante a fase de configuração do laboratório.

"postgres" "labex_password"

Salve e saia do editor pressionando Ctrl+O, Enter, e Ctrl+X.

Você agora configurou com sucesso o PgBouncer para gerenciar conexões para o seu banco de dados PostgreSQL.

Iniciar o PgBouncer e Testar a Conexão

Com a configuração pronta, o próximo passo é iniciar o serviço PgBouncer e verificar se você consegue se conectar ao banco de dados PostgreSQL através dele.

No seu terminal, inicie o PgBouncer como um daemon (flag -d) usando o arquivo de configuração que você criou.

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

Você pode verificar se o processo PgBouncer está em execução com o seguinte comando. Você deverá ver o processo pgbouncer listado.

ps aux | grep pgbouncer

A saída será semelhante a esta:

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

Agora, teste a conexão usando psql para se conectar à porta em que o PgBouncer está escutando (6432), e não à porta padrão do PostgreSQL (5432).

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

Você será solicitado a digitar a senha. Digite labex_password. Se a conexão for bem-sucedida, você verá o prompt do psql.

Para confirmar que você está conectado ao banco de dados, execute uma consulta simples para verificar a versão do PostgreSQL.

SELECT version();

A saída exibirá a versão do seu servidor PostgreSQL, confirmando que o PgBouncer intermediou com sucesso sua conexão.

                                                 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, saia do shell psql digitando \q e pressionando Enter.

\q

Simular Carga de Clientes com pgbench

Para observar o pooling de conexões em ação, você usará o pgbench, uma ferramenta de benchmarking padrão incluída com o PostgreSQL. O pgbench pode simular múltiplos clientes acessando o banco de dados simultaneamente.

Primeiro, você precisa inicializar o ambiente do pgbench. Isso cria algumas tabelas e as popula com dados de exemplo. Execute o seguinte comando, certificando-se de se conectar através da porta do PgBouncer (6432).

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

Você será solicitado a digitar a senha (labex_password). A flag -i inicializa o banco de dados para benchmarking. Você deverá ver uma saída indicando que as tabelas foram criadas e populadas.

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

Agora, execute o benchmark para simular uma carga. O seguinte comando simula 10 clientes concorrentes (-c 10), com cada cliente executando 300 transações (-t 300).

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

Novamente, digite a senha quando solicitado. O benchmark será executado por alguns segundos e, em seguida, exibirá um resumo dos resultados, incluindo o número de transações 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)

Este teste gerou tráfego significativo através do PgBouncer, que inspecionaremos na próxima etapa.

Monitorar Estatísticas do PgBouncer

O PgBouncer fornece um banco de dados administrativo especial que permite monitorar sua atividade e visualizar estatísticas sobre os pools de conexões.

Conecte-se ao console administrativo do PgBouncer. Observe que o nome do banco de dados é pgbouncer.

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

Digite a senha labex_password quando solicitado.

Uma vez conectado, você pode executar comandos especiais SHOW. Primeiro, visualize as estatísticas gerais para todos os bancos de dados.

SHOW STATS;

Este comando exibe dados cumulativos desde que o PgBouncer foi iniciado pela última vez. Você verá colunas como total_xact_count (total de transações) e total_query_count, que agora devem ter valores altos do teste 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)

Em seguida, para ver o estado dos pools de conexões em tempo real, use o comando SHOW POOLS.

SHOW POOLS;

Este comando fornece um instantâneo dos pools de conexões, mostrando conexões ativas e ociosas tanto para clientes quanto para servidores.

• cl_active: Conexões de clientes que estão ativamente vinculadas a uma conexão de servidor.
• sv_active: Conexões de servidor que estão atualmente em uso.
• sv_idle: Conexões de servidor que estão ociosas e prontas para serem usadas por um novo 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)

Ao examinar essas estatísticas, você pode entender com que eficácia o PgBouncer está gerenciando conexões e reutilizando-as para atender às solicitações dos clientes.

Saia do shell psql quando terminar.

\q

Resumo

Neste laboratório, você configurou e utilizou com sucesso o PgBouncer para pooling de conexões do PostgreSQL. Você aprendeu a criar o arquivo de configuração pgbouncer.ini e os arquivos de autenticação userlist.txt. Você iniciou o serviço PgBouncer e verificou que ele poderia fazer proxy de conexões para seu banco de dados PostgreSQL. Ao usar o pgbench, você simulou uma carga de cliente realista e, em seguida, monitorou os resultados usando o console administrativo do PgBouncer. Isso forneceu uma visão prática de como o pooling de conexões funciona e como ele pode ser monitorado para garantir um desempenho eficiente do banco de dados.