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

Descripción corta

Para determinar la causa del uso elevado de la CPU, utiliza las siguientes herramientas:

• Métricas de Amazon CloudWatch
• Métricas de supervisión mejorada
• Métricas de Información de rendimiento
• 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 en el sitio web de PostgreSQL
• 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

Resolución

Revisión de las métricas de CloudWatch

Utiliza las métricas de CloudWatch para identificar los patrones de la CPU durante periodos prolongados. Compara 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 causa un aumento de la utilización de la CPU.

Tras identificar el periodo, revisa los datos de supervisión mejorada que están asociados a tu instancia de base de datos. Puedes configurar la supervisión mejorada para que recopile intervalos de 1, 5, 10, 15, 30 o 60 segundos para recopilar datos a un nivel más detallado.

Uso de supervisión mejorada

La supervisión mejorada proporciona una vista a nivel del sistema operativo (SO). Por ejemplo, puedes revisar el promedio de carga de trabajo, la distribución de la CPU (System% o Nice%) y la lista de procesos del sistema operativo. Para obtener más información, consulta Supervisión del sistema operativo.

Puedes comprobar los datos de loadAverageMinute en intervalos de 1, 5 y 15 minutos. Cuando el promedio de carga es mayor que la cantidad de vCPU, la instancia experimenta una carga pesada. 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. Cuando estés solucionando la causa del uso excesivo de la CPU, comprueba el promedio de carga para evitar falsos positivos.

Por ejemplo, supongamos que tienes una instancia de base de datos que utiliza una clase de instancia db.m5.2xlarge con 3000 E/S por segundo aprovisionadas que alcanza la cuota de la CPU. Hay ocho vCPU asociadas a la clase de instancia. Si el mismo promedio de carga supera los 170, entonces la máquina está experimentando una carga pesada durante el periodo de tiempo 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% es la cantidad de CPU que usa tu carga de trabajo en la base de datos.

Tras activar la supervisión mejorada, también puedes consultar la lista de procesos del sistema operativo asociada a la instancia de base de datos. La supervisión mejorada te ayuda a identificar un máximo de 100 procesos que afectan al rendimiento. Puedes usar 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.

Uso de Información de rendimiento

Utiliza la información de rendimiento de Amazon RDS para identificar la consulta responsable de la carga de la base de datos. Comprueba la pestaña SQL que corresponde a un periodo de tiempo específico.

Comprobación de las vistas y los catálogos nativos de PostgreSQL

En el nivel del motor de base de datos, puedes usar pg_stat_activity y pg_stat_statements. Si el problema se produce en tiempo real, utiliza 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.

Utiliza los datos para comprobar los aumentos a lo largo del tiempo o los aumentos en los servidores de aplicaciones. También puedes comprobar si un servidor de aplicaciones tiene sesiones bloqueadas o problemas de bloqueo. Para obtener más información, consulta pg_stat_activity y pg_stat_statements en el sitio web de PostgreSQL.

Para activar pg_stat_statements, sigue estos pasos:

1. Modifica el grupo de parámetros de base de datos personalizado existente.

2. Agrega pg_stat_statements a shared_preload_libraries.

3. Establece track_activity_query_size en 4096.

4. Establece pg_stat_statements.track en TODO.

5. Establece pg_stat_statements.max en 10000.

6. Elige Aplicar inmediatamente y, a continuación, reinicia la instancia de base de datos.

7. En la base de datos que deseas supervisar, ejecuta el siguiente comando:

   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, utiliza uno de los siguientes métodos para supervisar el resultado. Puedes ver la consulta que pasa más tiempo en la base de datos, que tiene una menor proporción de aciertos en la caché de búfer o que se realiza por ejecución.

Para ver qué consultas pasan más tiempo en la base de datos, ejecuta la siguiente consulta para tu versión de PostgreSQL.

PostgreSQL versión 12 y anteriores:

SELECT total_time, queryFROM 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, queryFROM pg_stat_statements  
ORDER BY 1 DESC LIMIT 10;

Para enumerar las consultas con una tasa de aciertos de caché de búfer inferior, ejecuta la siguiente consulta para tu versión de PostgreSQL.

PostgreSQL versión 12 y anteriores:

SELECT query, calls, total_time, rows, 100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percentFROM 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_percentFROM 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, ejecuta la siguiente consulta para tu versión de PostgreSQL.

PostgreSQL versión 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_writtenFROM 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;

Comprobación de conexiones inactivas en la base de datos

Las conexiones inactivas de la base de datos pueden usar recursos de computación, como memoria y CPU. Cuando la instancia haga un uso elevado de la CPU, comprueba si hay conexiones inactivas en la base de datos. Para obtener más información, consulta Impacto en el rendimiento de las conexiones inactivas de PostgreSQL.

Puedes usar la supervisión mejorada para revisar la lista de procesos del sistema operativo para comprobar si hay conexiones inactivas. Sin embargo, la lista muestra solo un máximo de 100 procesos. Para comprobar si hay conexiones inactivas en el nivel de la base de datos, ejecuta las siguientes consultas.

Ve 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_activityWHERE 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';

Obtén los recuentos de conexiones para cada nombre de usuario y 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, ejecuta 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 tu aplicación causa demasiadas conexiones, modifícala para que los recursos de memoria y CPU no administren estas conexiones. Puedes limitar la cantidad de conexiones o usar un agrupador de conexiones como PgBouncer. También puedes usar el proxy de Amazon RDS para configurar grupos de conexiones.

Ejecución del 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 las estadísticas para ayudar a determinar los planes de ejecución más eficaces para las consultas. Si no ejecutas ANALYZE con frecuencia en las tablas de tu base de datos, es posible que las consultas usen más recursos de procesamiento, debido a que las estadísticas del sistema están obsoletas.

Las estadísticas obsoletas se producen por los siguientes motivos:

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

Autovacuum comprueba si hay tablas «infladas» en la base de datos y recupera el espacio para su reutilización. Para asegurarte de que las estadísticas de la tabla se actualicen con regularidad, el daemon autovacuum ejecuta el comando ANALYZE cuando el umbral de tuplas establecido está inactivo. 

Consulta los siguientes recursos para obtener más información:

• Understanding autovacuum in Amazon RDS for PostgreSQL environments
• Caso práctico del ajuste de autovacuum en Amazon RDS para PostgreSQL

Para saber cuándo se ejecutaron por última vez autovacuum y autoanalyze en las tablas, ejecuta la siguiente consulta:

SELECT relname, last_autovacuum, last_autoanalyze FROM pg_stat_user_tables;

Para evitar problemas de rendimiento tras una actualización de la versión principal del motor, ejecuta el comando ANALYZE para actualizar la tabla pg_statistic. Ejecuta el comando ANALYZE para cada base de datos de tu instancia de base de datos de RDS para PostgreSQL.

Para evitar problemas de rendimiento debido a una mayor utilización de los recursos, vuelve 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, ejecuta el siguiente comando sin parámetros:

ANALYZE VERBOSE

Comprobación de los registros de errores de PostgreSQL

Usa Amazon RDS para activar el registro de consultas de PostgreSQL. A continuación, comprueba los registros de errores de PostgreSQL para confirmar que has configurado los parámetros log_min_duration_statement y log_statement con los valores adecuados. Para obtener más información, consulta Error en los informes y registros en el sitio web de PostgreSQL.

Reducción del uso de la CPU

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

• Utiliza EXPLAIN y EXPLAIN ANALYZE para identificar maneras de ajustar los planes de consulta. Para obtener más información, consulta Uso de EXPLAIN en el sitio web de PostgreSQL.
• Si hay una consulta que se ejecuta repetidamente, utiliza instrucciones preparadas para reducir la presión sobre la CPU. Las instrucciones preparadas que se ejecuten repetidamente almacenarán en caché el plan de consulta. Cuando el plan ya está en caché para futuras ejecuciones, hay menos tiempo para planificar la consulta.

Información relacionada

Prácticas recomendadas para trabajar con PostgreSQL