Como faço para verificar consultas em execução e diagnosticar problemas de consumo de recursos em minha instância de banco de dados do Amazon RDS para PostgreSQL ou do Aurora PostgreSQL?

Resolução

Verifique as consultas em execução

Sua conta de usuário deve ter o perfil rds_superuser para ver todos os processos em execução em uma instância de banco de dados do Amazon RDS para PostgreSQL ou compatível com o Aurora PostgreSQL. Caso contrário, pg_stat_activity mostra somente as consultas que estão sendo executadas para seus próprios processos. Para obter mais informações, consulte o artigo do PostgreSQL sobre The Statistics Collector (Coletor de estatísticas).

1. Conecte-se à instância de banco de dados que está executando o Amazon RDS para PostgreSQL ou o PostgreSQL do Aurora.

2.   Execute o seguinte comando:

SELECT * FROM pg_stat_activity ORDER BY pid;

Também é possível modificar esse comando para visualizar a lista de consultas em execução. As consultas são ordenadas de acordo com o momento em que as conexões foram estabelecidas:

SELECT * FROM pg_stat_activity ORDER BY backend_start;

Se o valor da coluna xact_start for nulo, não haverá nenhuma transação aberta nessa sessão:

SELECT * FROM pg_stat_activity ORDER BY xact_start;

Ou veja a mesma lista de consultas em execução ordenadas de acordo com o momento em que a última consulta começou:

SELECT * FROM pg_stat_activity ORDER BY query_start;

Para ter uma visualização agregada dos eventos de espera, se houver, execute o seguinte comando:

select state, wait_event, wait_event_type, count(*) from pg_stat_activity group by 1,2,3 order by wait_event;

Diagnostique o consumo de recursos

É possível usar pg_stat_activity e o Monitoramento aprimorado para identificar a consulta ou o processo que está consumindo grandes quantidades de recursos do sistema. Depois de ativar o Monitoramento aprimorado, defina a granularidade para um nível suficiente para ver as informações necessárias para diagnosticar o problema. Em seguida, analise pg_stat_activity para ver as atividades atuais em seu banco de dados. Também é possível analisar as métricas de Monitoramento aprimorado naquele momento.

1. Veja a métrica da lista de processos do sistema operacional para identificar a consulta que está consumindo recursos. No exemplo a seguir, o processo está consumindo cerca de 95% do tempo de CPU na instância de banco de dados do RDS. O ID do processo (pid) do processo é 14431. O processo está executando uma declaração SELECT. Também é possível verificar o MEM% para ver o uso da memória do sistema.

2. Conecte-se à instância de banco de dados que está executando o PostgreSQL ou o PostgreSQL d Aurora.

3. Identifique a atividade atual da sessão executando o seguinte comando:

SELECT * FROM pg_stat_activity WHERE pid = PID;

Observação: substitua PID pelo pid que você identificou na etapa 1.

4. Confira o resultado do comando:

datid            | 14008
datname          | postgres
pid              | 14431
usesysid         | 16394
usename          | master
application_name | psql
client_addr      | 27.0.3.145
client_hostname  |
client_port      | 52003
backend_start    | 2020-03-11 23:08:55.786031+00
xact_start       | 2020-03-11 23:12:16.960942+00
query_start      | 2020-03-11 23:12:16.960942+00
state_change     | 2020-03-11 23:12:16.960945+00
wait_event_type  |
wait_event       |
state            | active
backend_xid      |
backend_xmin     | 812
query            | SELECT COUNT(*) FROM columns c1, columns c2, columns c3, columns c4, columns c5;
backend_type     | client backend

Para interromper o processo que está executando a consulta, invoque a consulta a seguir de outra sessão. Substitua PID pelo pid do processo que você identificou na etapa 3.

SELECT pg_terminate_backend(PID);

Importante: antes de encerrar as transações, avalie o possível efeito de cada transação sobre o estado do seu banco de dados e da sua aplicação.

Informações relacionadas

Como posso solucionar problemas de alta utilização da CPU no Amazon RDS ou no Amazon Aurora PostgreSQL?

Noções básicas de perfis e permissões do PostgreSQL

Tarefas comuns de DBA do Amazon RDS para PostgreSQL

psql (no site do PostgreSQL)

pg_stat_activity (no site do PostgreSQL)