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

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.

 

Líder em Treinamento e serviços de Consultoria, Suporte e Implantação para o mundo open source. Conheça nossas soluções:

CURSOSCONSULTORIA

Anterior Tuning de Banco de Dados: Melhorando a Performance do SGBD PostgreSQL
Próxima Guia definitivo para instalação e configuração do Graylog, MongoDB e Elasticsearch

About author

Você pode gostar também

Banco de Dados

Entenda o Log Binário do MySQL e suas aplicações práticas

O log binário do MySQL é, por vezes, mal compreendido, principalmente por usuários de outros bancos de dados. Nesta postagem pretendo abordar alguns aspectos desse importante mecanismo. Write-ahead logging? Também

Banco de Dados

PGCONF 2022: Maior conferência de PostgreSQL do Brasil retorna presencialmente

De volta ao modelo presencial, a PGCONF acontecerá nos dias 26 e 27 de agosto em São José dos Campos/SP. A 4Linux estará presente como uma das patrocinadoras do evento.

Banco de Dados

Domine o Wildfly 8: Curso com foco em Administração e DevOps

Administração com Cluster de Alta Performance em ambiente DevOps. Novidades do Curso além dos recursos novos do Wildfly No curso existe uma máquina DEVOPS (com ferramentas dev, como GIT, Maven)