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

Breve descripción

Si ve que su carga hace un uso elevado de la CPU, utilice una combinación de las siguientes herramientas para identificar la causa:

• Métricas de Amazon CloudWatch
• Métricas de Enhanced Monitoring
• Métricas de Performance Insights
• Vistas y catálogos nativos de PostgreSQL, como pg_stat_statements, pg_stat_activity y pg_stat_user_tables
• Conexiones inactivas en la base de datos
• Comando ANALYZE
• Parámetros de registro de PostgreSQL para registrar consultas de larga ejecución, autovacuum, esperas de bloqueo y solicitudes de conexión y desconexión

Solución

Métricas de Amazon CloudWatch

Utilice las métricas de CloudWatch para identificar los patrones de la CPU durante períodos prolongados. Compare los gráficos WriteIOPs, ReadIOPs, ReadThroughput y WriteThroughput con la utilización de la CPU para encontrar los momentos en los que la carga de trabajo causó un aumento de la utilización de la CPU.

Tras identificar el período, revise los datos de supervisión mejorada que están asociados a su instancia de base de datos. Puede configurar la supervisión mejorada para recopilar datos a intervalos de 1, 5, 10, 15, 30 o 60 segundos. A continuación, puede recopilar datos con mayor nivel de detalle que CloudWatch.

Supervisión mejorada

La supervisión mejorada proporciona una vista a nivel del sistema operativo (SO). Esta vista puede ayudar a identificar la causa de una carga elevada de la CPU con mayor nivel de detalle. Por ejemplo, puede revisar el promedio de carga, la distribución de la CPU (System% o Nice%) y la lista de procesos del sistema operativo.

Con la supervisión mejorada, puede comprobar los datos de loadAverageMinute en intervalos de 1, 5 y 15 minutos. Un promedio de carga superior al número de vCPU indica que la instancia está sometida a una carga elevada. Si el promedio de carga es inferior al número de vCPU de la clase de instancia de base de datos, es posible que la limitación de la CPU no provoque la latencia de la aplicación. Al diagnosticar la causa de la utilización de la CPU, compruebe el promedio de carga para evitar falsos positivos.

Por ejemplo, supongamos que tiene una instancia de base de datos que utiliza una clase de instancia db.m5.2xlarge con 3000 E/S por segundo aprovisionadas que alcanza el límite de la CPU. En el siguiente ejemplo, la clase de instancia tiene ocho vCPU asociadas. Para el mismo promedio de carga, superar un valor de 170 indica que la máquina está sometida a una carga elevada durante el período medido.

Minuto y promedio de carga

Quince	170,25
Cinco	391,31
Uno	596,74

Utilización de la CPU

Usuario (%)	0,71
Sistema (%)	4,9
Nice (%)	93,92
Total (%)	99,97

Nota: En la supervisión mejorada, Nice% representa la cantidad de CPU que usa su carga de trabajo en la base de datos.

Tras activar la supervisión mejorada, también puede consultar la lista de procesos del sistema operativo asociada a la instancia de base de datos. La supervisión mejorada muestra un máximo de 100 procesos y permite identificar los procesos que tienen el mayor impacto en el rendimiento. Puede combinar los resultados de la supervisión mejorada con los resultados de pg_stat_activity para ayudar a identificar el uso de recursos de las consultas.

Información de rendimiento

Utilice la información de rendimiento de Amazon RDS para identificar la consulta responsable de la carga de la base de datos. Compruebe la pestaña SQL que corresponde a un período determinado.

Vista y catálogos nativos de PostgreSQL

En el nivel del motor de base de datos, puede usar pg_stat_activity y pg_stat_statements. Si el problema se produce en tiempo real, utilice pg_stat_activity o pg_stat_statements para agrupar las máquinas, los clientes y las direcciones IP que envían la mayor cantidad de tráfico. Utilice estos datos para comprobar los aumentos a lo largo del tiempo o los aumentos en los servidores de aplicaciones. También puede comprobar si un servidor de aplicaciones tiene sesiones bloqueadas o problemas de bloqueo. Para obtener más información, consulte pg_stat_activity y pg_stat_statements en el sitio web de PostgreSQL.

Para activar pg_stat_statements, modifique el grupo de parámetros personalizados existente y defina los siguientes valores:

• Añada pg_stat_statements a shared_preload_libraries
• track_activity_query_size = 4096
• pg_stat_statements.track = ALL
• pg_stat_statements.max = 10000

Elija Aplicar inmediatamente y, a continuación, reinicie la instancia de base de datos. A continuación, ejecute un comando similar al siguiente en la base de datos que desea supervisar:

demo=> select current_database();current_database
------------------
demo
(1 row)     

demo=> CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Nota: El comando anterior instala la extensión en la base de datos demo.

Después de configurar pg_stat_statements, utilice uno de los siguientes métodos para supervisar el resultado.

Para enumerar las consultas por tiempo total y ver qué consulta permanece más tiempo en la base de datos, ejecute una de las siguientes consultas:

PostgreSQL versiones 12 y anteriores

SELECT total_time, query
FROM pg_stat_statements
ORDER BY total_time DESC LIMIT 10;

PostgreSQL versión 13 y posteriores

SELECT total_plan_time+total_exec_time as total_time, query
FROM pg_stat_statements
ORDER BY 1 DESC LIMIT 10;

Para enumerar las consultas con una menor tasa de aciertos de caché de búfer, ejecute una de las siguientes consultas:

PostgreSQL versiones 12 y anteriores

SELECT query, calls, total_time, rows, 100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
FROM pg_stat_statements
ORDER BY total_time DESC LIMIT 10;

PostgreSQL versión 13 y posteriores

SELECT query, calls, total_plan_time+total_exec_time as total_time, rows, 100.0 * shared_blks_hit / nullif(shared_blks_hit +shared_blks_read, 0) AS hit_percent
FROM pg_stat_statements
ORDER BY 3 DESC LIMIT 10;

Para enumerar las consultas por ejecución y muestrear las consultas a lo largo del tiempo, ejecute la siguiente consulta:

PostgreSQL versiones 12 y anteriores

SELECT query, calls, total_time/calls as avg_time_ms, rows/calls as avg_rows,temp_blks_read/calls as avg_tmp_read, temp_blks_written/calls as avg_temp_written
FROM pg_stat_statements
WHERE calls != 0
ORDER BY total_time DESC LIMIT 10;

PostgreSQL versión 13 y posteriores

SELECT query,
calls,
(total_plan_time+total_exec_time as total_time)/calls as avg_time_ms,
 rows/calls as avg_rows,
temp_blks_read/calls as avg_tmp_read,
 temp_blks_written/calls as avg_temp_written
FROM pg_stat_statements
WHERE calls != 0
ORDER BY 3 DESC LIMIT 10;

Conexiones inactivas en la base de datos

Las conexiones inactivas de la base de datos pueden consumir recursos de computación, como memoria y CPU. Cuando la instancia haga un uso elevado de la CPU, compruebe si hay conexiones inactivas en la base de datos. Para obtener más información, consulte Performance impact of idle PostgreSQL connections. Para comprobar si hay conexiones inactivas, utilice la supervisión mejorada para revisar la lista de procesos del sistema operativo. Sin embargo, esta lista muestra un máximo de 100 procesos.

Para comprobar si hay conexiones inactivas, ejecute las siguientes consultas en el nivel de la base de datos.

Ejecute las siguientes consultas para ver las sesiones actuales que están inactivas y activas:

SELECT pid, datname, state, current_timestamp-least(query_start,xact_start) age, application_name, usename, queryFROM pg_stat_activity
WHERE query != '<IDLE>
'AND query NOT ILIKE '%pg_stat_activity%'
AND usename!='rdsadmin'
ORDER BY query_start desc;

SELECT application_name,pid,wait_event_type,wait_event,current_timestamp-least(query_start,xact_start) AS runtime, query AS current_query
FROM pg_stat_activity
WHERE not pid=pg_backend_pid()
AND query NOT ILIKE '%pg_stat_activity%'
AND usename!='rdsadmin';

Ejecute las siguientes consultas para obtener los recuentos de conexiones por usuario y nombre de aplicación:

postgres=> SELECT application_name,count(*) FROM pg_stat_activity GROUP BY application_name;
    application_name    | count
------------------------+-------
 psql                   |     1
 PostgreSQL JDBC Driver |     1
                        |     5
(3 rows)

postgres=> SELECT usename,count(*) FROM pg_stat_activity GROUP BY usename;
 usename  | count
----------+-------
 master   |     4
 user1    |     1
 rdsadmin |     2
(3 rows)

Tras identificar las conexiones inactivas, ejecute una de las siguientes consultas para finalizar las conexiones:

psql=> SELECT pg_terminate_backend(pid)
   FROM pg_stat_activity
   WHERE usename = 'example-username'
   AND pid <> pg_backend_pid()
   AND state in ('idle');

Alternativa:

SELECT pg\_terminate\_backend (example-pid);

Si su aplicación causa demasiadas conexiones, cámbiela para no consumir recursos de memoria y CPU en la administración de estas conexiones. Puede cambiar la aplicación para limitar el número de conexiones o utilizar un agrupador de conexiones como PGBouncer. También puede usar Amazon RDS Proxy, un servicio administrado que le permite configurar la agrupación de conexiones.

Comando ANALYZE

El comando ANALYZE recopila estadísticas sobre el contenido de las tablas de la base de datos y almacena los resultados en el catálogo del sistema pg_statistic. A continuación, el planificador de consultas utiliza estas estadísticas para ayudar a determinar los planes de ejecución más eficaces para las consultas. Si no ejecuta ANALYZE con frecuencia en las tablas de su base de datos, es posible que las consultas consuman más recursos de computación. Las consultas consumen más recursos debido a las estadísticas obsoletas presentes en el sistema para las relaciones a las que se accede. Estos problemas se producen en las siguientes condiciones:

• Autovacuum no se ejecuta con frecuencia.
• ANALYZE no se ejecutó después de la actualización de la versión principal.

Autovacuum no se ejecuta con frecuencia: Autovacuum es un daemon que automatiza la ejecución de los comandos VACUUM y ANALYZE. Autovacuum comprueba si hay tablas «infladas» en la base de datos y recupera el espacio para su reutilización. El daemon autovacuum se asegura que las estadísticas de la tabla se actualicen periódicamente ejecutando la operación ANALYZE siempre que el umbral de tuplas establecido sea muerto. A continuación, el planificador de consultas puede usar el plan de consultas más eficiente según las estadísticas recientes. Si autovacuum no se ejecuta, el planificador de consultas podría crear planes de consultas menos óptimos y causar un mayor consumo de recursos por parte de las consultas. Consulte los siguientes recursos para obtener más información:

• Understanding autovacuum in Amazon RDS for PostgreSQL environments
• A case study of tuning autovacuum in Amazon RDS for PostgreSQL

Para obtener información sobre cuándo se ejecutaron por última vez autovacuum y autoanalyze en las tablas, ejecute la siguiente consulta:

SELECT relname, last\_autovacuum, last\_autoanalyze FROM pg\_stat\_user\_tables;

ANALYZE no se ejecutó después de la actualización de la versión principal: Las bases de datos de PostgreSQL suelen tener problemas de rendimiento después de cualquier actualización importante de la versión del motor. Un motivo común de estos problemas es que la operación ANALYZE no se realiza después de la actualización para actualizar la tabla pg_statistic. Ejecute la operación ANALYZE para cada base de datos de su instancia de base de datos de RDS para PostgreSQL. Las estadísticas del optimizador no se transfieren durante la actualización de una versión principal. Por lo tanto, para evitar problemas de rendimiento debido a una mayor utilización de los recursos, vuelva a generar todas las estadísticas.

Para generar estadísticas para todas las tablas normales de la base de datos actual después de una actualización de la versión principal, ejecute el siguiente comando sin ningún parámetro:

ANALYZE VERBOSE

Parámetros de registro de PostgreSQL

Utilice Amazon RDS para PostgreSQL para activar el registro de consultas. A continuación, compruebe los registros de errores de PostgreSQL para confirmar que los parámetros log_min_duration_statement y log_statement están configurados con los valores adecuados. Para obtener más información, consulte Error reporting and logging en el sitio web de PostgreSQL.

Reducir el uso de la CPU

Tras identificar las consultas que causan el uso elevado de la CPU, utilice los métodos siguientes para reducir aún más el uso de la CPU:

• Para encontrar oportunidades de ajuste, utilice EXPLAIN y EXPLAIN ANALYZE para identificar las advertencias. Para obtener más información, consulte EXPLAIN en el sitio web de PostgreSQL.
• Si hay una consulta que se ejecuta repetidamente, utilice instrucciones preparadas para reducir la presión sobre la CPU. La ejecución repetida de instrucciones preparadas almacena en la caché el plan de consultas. Como el plan ya está en la caché para futuras ejecuciones, el tiempo de planificación es mucho menor.

Información relacionada

Prácticas recomendadas para trabajar con PostgreSQL