Gerenciar a rotatividade de conexão do Aurora PostgreSQL com agrupamento

Quando as aplicações cliente se conectam e desconectam com uma frequência tal que o tempo de resposta do cluster de banco de dados do Aurora PostgreSQL diminui, diz-se que o cluster está passando por rotatividade de conexão. Cada nova conexão com o endpoint do cluster de banco de dados do Aurora PostgreSQL consome recursos, reduzindo assim os recursos que podem ser usados para processar a workload em si. A rotatividade de conexão é um problema que recomendamos gerenciar com algumas das práticas recomendadas descritas a seguir.

Para começar, você pode melhorar os tempos de resposta nos clusters de banco de dados do Aurora PostgreSQL que têm altas taxas de rotatividade de conexão. Por exemplo, você pode usar um agrupador de conexões, como o RDS Proxy. O agrupador de conexões fornece aos clientes um cache de conexões prontas para uso. Quase todas as versões do Aurora PostgreSQL comportam o RDS Proxy. Para obter mais informações, consulte Proxy do Amazon RDS com Aurora PostgreSQL.

Se a sua versão específica do Aurora PostgreSQL não for compatível com o RDS Proxy, você poderá usar outro agrupador de conexões compatível com o PostgreSQL, como o PgBouncer. Para saber mais, consulte o site do PgBouncer.

Para ver se o cluster de banco de dados do Aurora PostgreSQL pode se beneficiar do agrupamento de conexões, confira o arquivo postgresql.log para consultar sobre conexões e desconexões. Você também pode usar o Performance Insights para descobrir o nível de rotatividade de conexão que o cluster de banco de dados do Aurora PostgreSQL está enfrentando. A seguir, você encontrará informações sobre os dois tópicos.

Registrar em log conexões e desconexões

O log_connections PostgreSQL e os parâmetros do log_disconnections podem capturar conexões e desconexões com a instância do gravador do cluster de banco de dados do Aurora PostgreSQL. Por padrão, esse parâmetro é desativado. Para ativar esses parâmetros, use um grupo de parâmetros personalizado e altere o valor para 1. Para obter mais informações sobre grupos de parâmetros personalizados, consulte Trabalhar com grupos de parâmetros de cluster de banco de dados. Para verificar as configurações, conecte-se ao endpoint do cluster de banco de dados para o Aurora PostgreSQL usando psql e consulta da maneira a seguir.

labdb=> SELECT setting FROM pg_settings
  WHERE name = 'log_connections';
 setting
---------
on
(1 row)
labdb=> SELECT setting FROM pg_settings
  WHERE name = 'log_disconnections';
setting
---------
on
(1 row)

Com esses dois parâmetros ativados, o log captura todas as novas conexões e desconexões. Você vê o usuário e o banco de dados de cada nova conexão autorizada. No momento da desconexão, a duração da sessão também é registrada em log, como mostrado no exemplo a seguir.

2022-03-07 21:44:53.978 UTC [16641] LOG: connection authorized: user=labtek database=labdb application_name=psql
2022-03-07 21:44:55.718 UTC [16641] LOG: disconnection: session time: 0:00:01.740 user=labtek database=labdb host=[local]

Para verificar se há rotatividade de conexão em sua aplicação, ative esses parâmetros se eles ainda não estiverem ativados. Depois disso, reúna dados no log do PostgreSQL para análise executando sua aplicação com uma workload e um período realistas. Você pode visualizar o arquivo de log no console do RDS. Escolha a instância do gravador do seu cluster de banco de dados do Aurora PostgreSQL e selecione a guia Logs & events (Logs e eventos). Para obter mais informações, consulte Como visualizar e listar arquivos de log do banco de dados.

Ou você pode baixar o arquivo de log por meio do console e usar a sequência de comandos a seguir. Essa sequência encontra o número total de conexões autorizadas e descartadas por minuto.

grep "connection authorized\|disconnection: session time:" postgresql.log.2022-03-21-16|\
awk {'print $1,$2}' |\
sort |\
uniq -c |\
sort -n -k1

No exemplo de saída, você pode ver um pico nas conexões autorizadas seguido de desconexões a partir de 16:12:10.

.....
,......
.........
5 2022-03-21 16:11:55 connection authorized:
9 2022-03-21 16:11:55 disconnection: session
5 2022-03-21 16:11:56 connection authorized:
5 2022-03-21 16:11:57 connection authorized:
5 2022-03-21 16:11:57 disconnection: session
32 2022-03-21 16:12:10 connection authorized:
30 2022-03-21 16:12:10 disconnection: session
31 2022-03-21 16:12:11 connection authorized:
27 2022-03-21 16:12:11 disconnection: session
27 2022-03-21 16:12:12 connection authorized:
27 2022-03-21 16:12:12 disconnection: session
41 2022-03-21 16:12:13 connection authorized:
47 2022-03-21 16:12:13 disconnection: session
46 2022-03-21 16:12:14 connection authorized:
41 2022-03-21 16:12:14 disconnection: session
24 2022-03-21 16:12:15 connection authorized:
29 2022-03-21 16:12:15 disconnection: session
28 2022-03-21 16:12:16 connection authorized:
24 2022-03-21 16:12:16 disconnection: session
40 2022-03-21 16:12:17 connection authorized:
42 2022-03-21 16:12:17 disconnection: session
40 2022-03-21 16:12:18 connection authorized:
40 2022-03-21 16:12:18 disconnection: session
.....
,......
.........
1 2022-03-21 16:14:10 connection authorized:
1 2022-03-21 16:14:10 disconnection: session
1 2022-03-21 16:15:00 connection authorized:
1 2022-03-21 16:16:00 connection authorized:

Com essas informações, você pode decidir se a workload pode se beneficiar de um agrupador de conexões. Para obter uma análise mais detalhada, você pode usar o Performance Insights.

Detectar a rotatividade de conexão com o Performance Insights

Você pode usar o Performance Insights para avaliar a quantidade de rotatividade de conexão no cluster de banco de dados da edição compatível com Aurora PostgreSQL. Ao criar um cluster de banco de dados do Aurora PostgreSQL, a configuração do Performance Insights é ativada por padrão. Se você desmarcou essa opção ao criar o cluster de banco de dados, modifique o cluster para ativar o recurso. Para obter mais informações, consulte Modificar um cluster de bancos de dados Amazon Aurora.

Com o Performance Insights em execução no cluster de banco de dados do Aurora PostgreSQL, você pode escolher as métricas que deseja monitorar. Você pode acessar o Performance Insights no painel de navegação do console. Você também pode acessar o Performance Insights com a guia Monitoring (Monitoramento) da instância do gravador para o cluster de banco de dados do Aurora PostgreSQL, conforme mostrado na imagem a seguir.

No console do Performance Insights, escolha Manage metrics (Gerenciar métricas). Para analisar a atividade de conexão e desconexão do cluster de banco de dados do Aurora PostgreSQL, escolha as métricas a seguir. Estas métricas são todas do PostgreSQL.

• xact_commit: o número de transações confirmadas.

• total_auth_attempts: o número de tentativas de conexões de usuários autenticados por minuto.

• numbackends: o número de back-ends conectados ao banco de dados no momento.

Para salvar as configurações e exibir a atividade de conexão, escolha Update graph (Atualizar grafo).

Na imagem a seguir, você pode ver o impacto da execução do pgbench com 100 usuários. A linha que mostra as conexões é consistentemente ascendente. Para saber mais sobre o pgbench e como usá-lo, consulte pgbench na documentação do PostgreSQL.

A imagem mostra que executar uma workload com até 100 usuários sem um agrupador de conexões pode aumentar de maneira significativa o número de total_auth_attempts ao longo do processamento da workload.

Com o agrupamento de conexões do RDS Proxy, as tentativas de conexão aumentam no início da workload. Após a configuração do agrupamento de conexões, a média diminui. Os recursos usados pelas transações e pelo uso do back-end permanecem consistentes ao longo do processamento da workload.

Para obter mais informações sobre como usar o Performance Insights com o cluster de banco de dados do Aurora PostgreSQL, consulte Monitorar a carga de banco de dados com o Performance Insights no Amazon Aurora. Para analisar as métricas, consulte Análise de métricas usando o painel do Performance Insights.

Demonstrar os benefícios do agrupamento de conexões

Conforme mencionado anteriormente, se você determinar que o cluster de banco de dados do Aurora PostgreSQL tem um problema de rotatividade de conexão, é possível usar o RDS Proxy para melhorar a performance. A seguir, você encontrará um exemplo que mostra as diferenças no processamento de uma workload quando as conexões estão agrupadas e quando não estão. O exemplo usa o pgbench para modelar uma workload de transação.

Assim como o psql, o pgbench é uma aplicação cliente do PostgreSQL que você pode instalar e executar em sua máquina cliente local. Também é possível instalá-la e executá-la por meio da instância do Amazon EC2 que você usa para gerenciar o cluster de banco de dados do Aurora PostgreSQL. Para obter mais informações, consulte pgbench na documentação do PostgreSQL.

Para analisar esse exemplo, crie primeiro o ambiente do pgbench em seu banco de dados. O comando a seguir é o modelo básico para inicializar as tabelas do pgbench no banco de dados especificado. Este exemplo usa a conta de usuário principal padrão, postgres, para o login. Se necessário, altere-a para seu cluster de banco de dados do Aurora PostgreSQL. Crie o ambiente do pgbench em um banco de dados na instância do gravador do cluster.

nota

O processo de inicialização do pgbench descarta e recria as tabelas chamadas pgbench_accounts, pgbench_branches, pgbench_history e pgbench_tellers. O banco de dados que você escolheu para dbname ao inicializar o pgbench não deve usar esses nomes.

pgbench -U postgres -h db-cluster-instance-1.111122223333.aws-region.rds.amazonaws.com -p 5432 -d -i -s 50 dbname

Para o pgbench, especifique os seguintes parâmetros:

-d

Gera um relatório de depuração à medida que o pgbench é executado.

-h

Especifica o endpoint da instância do gravador do cluster de banco de dados do Aurora PostgreSQL.

-i

Inicializa o ambiente do pgbench no banco de dados para os testes de comparação.

-p

Identifica a porta usada para conexões de banco de dados. O padrão para o Aurora PostgreSQL geralmente é 5432 ou 5433.

-s

Especifica o fator de escalabilidade a ser usado para preencher as tabelas com linhas. O fator de escalabilidade padrão é 1, o que gera 1 linha na tabela pgbench_branches, 10 linhas na tabela pgbench_tellers e 100 mil linhas na tabela pgbench_accounts.

-U

Especifica a conta de usuário para a instância do gravador do cluster de banco de dados do Aurora PostgreSQL.

Depois que o ambiente do pgbench estiver configurado, você poderá executar testes de comparação com e sem agrupamento de conexões. O teste padrão consiste em uma série de cinco comandos SELECT, UPDATE e INSERT por transação que são executados repetidamente pelo tempo especificado. Você pode especificar o fator de escalabilidade, o número de clientes e outros detalhes para modelar seus próprios casos de uso.

Como exemplo, o comando a seguir executa a comparação por 60 segundos (a opção -T, para tempo) com 20 conexões simultâneas (a opção -C). A opção -C faz com que o teste seja executado usando uma nova conexão a cada vez, e não uma vez por sessão do cliente. Essa configuração fornece uma indicação da sobrecarga da conexão.

pgbench -h docs-lab-apg-133-test-instance-1.c3zr2auzukpa.us-west-1.rds.amazonaws.com -U postgres -p 5432 -T 60 -c 20 -C labdb
Password:**********
pgbench (14.3, server 13.3)
  starting vacuum...end.
  transaction type: <builtin: TPC-B (sort of)>
    scaling factor: 50
    query mode: simple
    number of clients: 20
    number of threads: 1
    duration: 60 s
    number of transactions actually processed: 495
    latency average = 2430.798 ms
    average connection time = 120.330 ms
    tps = 8.227750 (including reconnection times)

A execução do pgbench na instância do gravador de um cluster de banco de dados do Aurora PostgreSQL sem reutilizar conexões mostra que cerca de oito transações apenas são processadas a cada segundo. Isso totaliza 495 transações durante o teste de 1 minuto.

Se você reutilizar conexões, a resposta do cluster de banco de dados do Aurora PostgreSQL para o número de usuários será quase 20 vezes mais rápida. Com a reutilização, em vez de 495, são processadas ao todo 9.042 transações no mesmo período e para o mesmo número de conexões de usuários. A diferença é que, no exemplo a seguir, cada conexão está sendo reutilizada.

pgbench -h docs-lab-apg-133-test-instance-1.c3zr2auzukpa.us-west-1.rds.amazonaws.com -U postgres -p 5432 -T 60 -c 20 labdb
Password:*********
pgbench (14.3, server 13.3)
      starting vacuum...end.
      transaction type: <builtin: TPC-B (sort of)>
      scaling factor: 50
      query mode: simple
      number of clients: 20
      number of threads: 1
      duration: 60 s
      number of transactions actually processed: 9042
      latency average = 127.880 ms
      initial connection time = 2311.188 ms
      tps = 156.396765 (without initial connection time)

Este exemplo mostra que o agrupamento de conexões pode melhorar significativamente os tempos de resposta. Para obter mais informações sobre como configurar o RDS Proxy para o cluster de banco de dados do Aurora PostgreSQL, consulte Usar o Amazon RDS Proxy para o Aurora.