¿Cómo puedo comprobar las consultas en ejecución y diagnosticar los problemas de consumo de recursos para mi instancia de base de datos de Amazon RDS para PostgreSQL o Aurora PostgreSQL?

Resolución

Comprobación de las consultas en ejecución

Tu cuenta de usuario debe tener el rol de rds_superuser para ver todos los procesos que se ejecutan en una instancia de base de datos de Amazon RDS para PostgreSQL o Aurora compatible con PostgreSQL. De lo contrario, pg_stat_activity muestra las consultas que se están ejecutando para sus propios procesos. Para obtener más información, consulta la documentación de PostgreSQL para el recopilador de estadísticas.

1.    Conéctate a la instancia de base de datos que ejecuta Amazon RDS para PostgreSQL o Aurora PostgreSQL.

2.    Ejecuta el siguiente comando:

SELECT * FROM pg_stat_activity ORDER BY pid;

También puedes modificar este comando para ver la lista de consultas en ejecución. Las consultas se ordenan según el momento en que se establecieron las conexiones:

SELECT * FROM pg_stat_activity ORDER BY backend_start;

Si el valor de la columna xact_start es nulo, entonces no hay ninguna transacción abierta en esa sesión:

SELECT * FROM pg_stat_activity ORDER BY xact_start;

O bien, ve la misma lista de consultas en ejecución ordenadas según el momento en que se inició la última consulta:

SELECT * FROM pg_stat_activity ORDER BY query_start;

Para obtener una vista agregada de los eventos de espera, si los hay, ejecuta el siguiente comando:

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

Diagnóstico del consumo de recursos

Puedes usar pg_stat_activity y la supervisión mejorada para identificar la consulta o el proceso que consume grandes cantidades de recursos del sistema. Después de activar la supervisión mejorada, define la granularidad en un nivel que sea suficiente para ver la información que necesitas para diagnosticar el problema. A continuación, revisa pg_stat_activity para ver las actividades actuales en tu base de datos. También puedes revisar las métricas de supervisión mejorada en ese momento.

1.    Consulta la métrica de la lista de procesos del sistema operativo para identificar la consulta que consume recursos. En el siguiente ejemplo, el proceso consume aproximadamente el 95 % del tiempo de la CPU en la instancia de base de datos de RDS. El ID de proceso (pid) del proceso es 14431. El proceso ejecuta una instrucción SELECT. También puedes comprobar el MEM% para ver el uso de la memoria del sistema.

2.    Conéctate a la instancia de base de datos que PostgreSQL o Aurora PostgreSQL está ejecutando.

3.    Ejecuta el siguiente comando para identificar la actividad actual de la sesión:

SELECT * FROM pg_stat_activity WHERE pid = PID;

Nota: Sustituye PID por el pid que has identificado en el paso 1.

4.    Comprueba el resultado del 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 detener el proceso que ejecuta la consulta, invoca la siguiente consulta desde otra sesión. Sustituye el PIDpor el pid del proceso que ha identificado en el paso 3.

SELECT pg_terminate_backend(PID);

Importante: Antes de finalizar las transacciones, evalúa el posible efecto que cada transacción tiene en el estado de la base de datos y aplicación.

Información relacionada

¿Cómo puedo solucionar problemas de uso elevado de la CPU en Amazon RDS o Aurora PostgreSQL?

Descripción de los roles y permisos de PostgreSQL

Creación de roles

psql (en el sitio web de PostgreSQL)

pg_stat_activity (en el sitio web de PostgreSQL)