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

Resolução

Verifique as consultas em execução

Sua conta de usuário deve receber a função rds_superuser para ver todos os processos em execução em uma instância de banco de dados do RDS para PostgreSQL ou 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 a documentação do PostgreSQL para o The Statistics Collector.

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

2.    Execute o seguinte comando:

SELECT * FROM pg_stat_activity ORDER BY pid;

Você também pode modificar esse comando para ver a lista de consultas em execução ordenada pelo momento em que as conexões foram estabelecidas:

SELECT * FROM pg_stat_activity ORDER BY backend_start;

Se o valor da coluna 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 por quando a última consulta foi executada:

SELECT * FROM pg_stat_activity ORDER BY query_start;

Para 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;

Diagnosticar o consumo de recursos

Usando pg_stat_activity e ativando o Monitoramento Aprimorado, você pode identificar a consulta/processo que está consumindo grandes quantidades de recursos do sistema. Depois de ativar o monitoramento aprimorado, certifique-se de que o conjunto de granularidade seja suficiente para ver as informações necessárias para diagnosticar o problema. Em seguida, você pode verificar pg_stat_activity para ver as atividades atuais em seu banco de dados e as métricas de monitoramento aprimorado naquele momento.

1.Identifique a consulta que está consumindo recursos, visualizando a métrica da lista de processos do sistema operacional. 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 é 14431e o processo está executando uma instrução SELECT. Você também pode ver o uso da memória do sistema verificando o MEM%.

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

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

SELECT * FROM pg_stat_activity WHERE pid = PID;

**Nota: **Substitua o PIDpelo 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. Certifique-se de substituir o PIDpelo pid do processo que você identificou na etapa 3.

SELECT pg_terminate_backend(PID);

**Importante:**Antes de encerrar as transações, avalie o impacto potencial que cada transação tem no estado do seu banco de dados e de sua aplicação.

Informações relacionadas

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

Criação de funções

Monitoramento aprimorado

Documentação do PostgreSQL para psql

Documentação do PostgreSQL para pg_stat_activity