20 de setembro de 2022 Wilian Kitagaki 7392 Views

Entenda o Pool de Conexões no PostgreSQL e Melhore a Performance do seu Banco de Dados

Você que trabalha com o PostgreSQL há um determinado tempo já deve ter visto o seguinte erro “FATAL: sorry, too many clients already”, ou então visto o parâmetro max_connections e se pensado o motivo do limite de conexões. Nesse post vamos abordar como funciona as conexões no nosso querido SGBD e como funciona a técnica de pool de conexões e explicar algumas das ferramentas que podem ser utilizadas para realização da mesma.

Para começarmos vamos explicar como funciona as conexões no postgresql. O postgresql assim que iniciado possui um processo principal, postmaster, que roda em background. Sempre que a requisição de um nova conexão chega, é realizado um fork desse processo para atender aquela conexão em específico, quando essa conexão é terminada o processo é encerrado junto. Esse gerenciamento de conexões no postgresql custa CPU e RAM, quanto mais conexões são abertas mais o processo acaba consumindo de recursos.

Entretanto com uma estratégia de pooling, isso se torna diferente.
Imagine o pool como se fosse um conjunto de conexões disponíveis(por tempo determinado) para uso, onde você retira as conexões quando pede uma requisição de abrir conexão e retorna elas para o conjunto de disponíveis sempre que encerra a conexão.

Pool

Quando o pool de conexões recebe requisição para o mesmo database e mesmo usuário, ele não irá criar uma nova conexão, e sim utilizar uma conexão já aberta anteriormente.

O PostgreSQL não possuí nenhum pool nativo em compensação temos dois desenvolvidos pela comunidade, sendo eles o pgpool e o pgbouncer. Apesar dos dois serem pools de conexão eles tem grandes diferenças.

Neste tópico vai ser dado uma introdução sobre ambos os pools, porém o pgbouncer será o que vai ser mais aprofundado junto com uma instalação simples.

Pgpool-II

Assim como os demais pool é um middleware que funciona entre a aplicação cliente e o banco de dados, porém tem mais funções do que um pool convencional como consequência mais complexo.
As funções do pgpool são:

Pool de conexão
Replicação
Load Balance
Cache em memória
Alta disponibilidade

PgBouncer

Ele é o contraponto do pgpool, ele funciona apenas como pooling de conexões e a configuração dele é bem simples.
Ele possui 3 tipos de pool sendo:

Session: Esse é o modo menos agressivo, sendo transparante para a aplicação, ou seja, para a aplicação é como se estivesse conectado direto no banco de dados
Transaction: Esse é o modo intermediario, onde quando uma transação é encerrada a conexão se torna disponível para reutilização, necessita de alteração na connection string/parametro de conexão.
Statement: O modo agressivo, porém o que mais implica o maior cuidado, pois uma transação realizando vários comandos não é permitido nesse modo. Nesse modo após o termino do comando, a conexão já é devolvida.

Abaixo iremos ensinar como fazer uma configuração simples do pgbouncer utilizando o modo session

Instalação e Configuração

A principal vantagem do pgbouncer é por ser uma ferramenta simples para uma configuração out-of-the-box.

Tem duas formas de instalar o pgbouncer que é compilando o código fonte, ou da forma que vamos fazer aqui que é pelos pacotes da PGDG.

Para a configuração do pacote do PGDG, pode ser utilizado os seguintes links de acordo com a distro utilizada, para esse post irei me basear no Debian, porém irei colocar os demais links aqui para consulta.

Redhat e variantes: https://www.postgresql.org/download/linux/redhat/
Ubuntu: https://www.postgresql.org/download/linux/ubuntu/
Debian: https://www.postgresql.org/download/linux/debian/

Após a configuração do pacote, iremos para a instalação do pgbouncer.

apt-get install pgbouncer -y

Para a configuração irei me basear que o postgresql está na mesma máquina (com as configurações padrões) em que o pgbouncer

No debian por padrão quando você instala o pgbouncer, será criado uma pasta no seguinte diretório

/etc/pgbouncer

Essa pasta conterá dois arquivos, esses arquivos são de extrema importância. Os arquivos são:

pgbouncer.ini: Possui toda a configuração do pgbouncer.
userlist.txt: Esse é o arquivo utilizado para realização das autenticações no banco de dados.

Iremos criar um diretório de log para o pgbouncer

mkdir /var/log/pgbouncer

Como iremos fazer uma configuração simples, vamos criar um arquivo pgbouncer.ini do zero, mas iremos deixar o antigo como uma forma de backup para caso um dia queiram ver algumas das configurações dele

cd /etc/pgbouncer
mv pgbouncer.ini pgbouncer.bkp

Iremos executar o seguinte comando para criação do novo pgbouncer.ini

cat << EOF > /etc/pgbouncer/pgbouncer.ini
[databases]
* = host=127.0.0.1 port=5432

[pgbouncer]
listen_addr = *
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
logfile = /var/log/pgbouncer/pgbouncer.log
admin_users = postgres
pool_mode = session
default_pool_size=90
max_client_conn=3000
EOF

O arquivo de configuração do pgbouncer é definido por seções que estão caracterizadas por []. Vamos explicar as duas seções utilizadas:

[database]: define o nome dos databases que os clientes podem se conectar e como as conexões serão resolvidas. No exemplo acima significa que toda conexão que chegar no pgbouncer independente do nome do database, será resolvido para o host local e na porta 5432.
[pgbouncer]: define as configurações gerais do pgbouncer

Vamos explicar o que é cada um dos parâmetros?

listen_addr: igual ao parametro do postgresql, sendo utilizado para marcar quais endereços de rede pode se conectar via TCP
auth_type: é a forma de encriptação utilizada, iguale com o parametro password_encryption do postgresql
logfile: é onde serão salvos os logs do pgbouncer
admin_users: usuários que é possível acessar o console de administração do pgbouncer
pool_mode: define qual a forma de pooling padrão
default_pool_size: quantas conexões são permitidas por par de database/usuário
max_client_conn: número maximo de conexões permitido no pgbouncer para todos os pares de database/usuário.

Antes de continuarmos iremos mudar o usuário e grupo proprietários dos diretórios de configuração e de log (iremos utilizar o usuário postgresql para a execução do pgbouncer nesse exemplo)

chown postgres.postgres -R /etc/pgbouncer /var/log/pgbouncer

Vamos acessar o usuário postgres do S.O. para criar um usuário no postgres para testar o pgbouncer?

su - postgres 
psql -c "CREATE USER aluno SUPERUSER PASSWORD '4linux';"

Ainda com o usuário postgres vamos preencher o userlist.txt com o nome de usuário criado e a senha

echo "\"aluno\" \"```(psql -Atqc "SELECT rolpassword FROM pg_authid WHERE rolname = 'aluno'";)```\"" > /etc/pgbouncer/userlist.txt

Vamos subir o pgbouncer?

Para isso vamos criar o serviço do pgbouncer através do usuário root

cat << EOF > /etc/systemd/system/pgbouncer.service
[Unit]
Description=connection pooler for PostgreSQL
Documentation=man:pgbouncer(1)
Documentation=https://www.pgbouncer.org/
After=network.target

[Service]
Type=notify
User=pgbouncer
ExecStart=/usr/local/pgbouncer/bin/pgbouncer /etc/pgbouncer/pgbouncer.ini
ExecReload=/bin/kill -HUP \${MAINPID}
KillSignal=SIGINT

[Install]
WantedBy=multi-user.target

EOF

Iremos executar o seguinte comando, para que o serviço do pgbouncer se inicialize agora e sempre que a máquina for inicializada

systemctl enable --now pgbouncer

Vamos testar o pgbouncer?

Com o usuário postgres vamos acessar e criar um database


su - postgres
psql -c "CREATE DATABASE pgbench;"

Iremos usar utilitário para benchmark para simular várias conexões simultâneas no banco de dados, o primeiro comando irá preparar a base para execução desse utilitário

pgbench -U aluno -i pgbench

Vamos conferir o número máximo de conexões, o valor default é 100.

psql -Aqtc "SHOW MAX_CONNECTIONS;"

Iremos testar utilizar o pgbench com 200 conexões e 10 transações cada, mas conectando direto no banco de dados

pgbench -U aluno -c 200 -t 10 pgbench

Iremos perceber que ocorreu um errro

FATAL: sorry, too many clients alterady

Agora vamos fazer o mesmo teste, mas através do pgbouncer

pgbench -U aluno -c 200 -t 10 pgbench -p 6432

E podemos perceber que ocorreu tudo certo.

Conclusão

Apesar de ter sido mais aprofundado o pgbouncer do que o pgpool, as duas ferramentas são muito importantes, porém cada uma com seu uso.
Ambas as ferramentas nos permitem diminuir a quantidade de max_connections do banco de dados, sendo assim diminuindo a quantidade de forks necessários e permitindo uma performance melhor tanto em alocação de recursos para conexões por exemplo o work_mem.