Optimiza el uso elevado de CPU en las instancias

El uso elevado de CPU afecta negativamente el rendimiento de tu instancia. Cualquier actividad que se realice en la instancia usa CPU. Por lo tanto, si hay un aviso de uso elevado de CPU, primero debes identificar la causa raíz del problema, ya sean consultas mal escritas, transacciones de larga duración o cualquier otra actividad de la base de datos.

En este documento, se describen las formas de identificar cuellos de botella de CPU en una instancia y mitigar los problemas de uso de CPU en la instancia.

Identifica los cuellos de botella de CPU

Usa las estadísticas de consultas para identificar las consultas que tienen un alto consumo de CPU

Estadísticas de consultas te ayuda a detectar, diagnosticar y prevenir problemas de rendimiento de las consultas para las bases de datos de Cloud SQL.

Usa la extensión pg_proctab

Usa la extensión pg_proctab con la combinación de la utilidad pg_top para obtener resultados del sistema operativo que brindan información del uso de CPU por proceso.

Usa consultas

Identifica las conexiones activas por estado

Cada conexión activa a la base de datos requiere cierta cantidad de CPU, por lo que si la instancia tiene una gran cantidad de conexiones, el uso acumulativo podría ser alto. Usa la siguiente consulta para obtener la información sobre la cantidad de conexiones por estado.

SELECT 
  state, 
  usename, 
  count(1) 
FROM 
  pg_stat_activity 
WHERE 
  pid <> pg_backend_pid() 
group by 
  state, 
  usename 
order by 
  1;

El resultado es similar al siguiente:


        state        |    usename    | count 
---------------------+---------------+-------
 active              | ltest         |   318
 active              | sbtest        |    95
 active              |               |     2
 idle                | cloudsqladmin |     2
 idle in transaction | ltest         |    32
 idle in transaction | sbtest        |     5
                     | cloudsqladmin |     3
                     |               |     4
(8 rows)

Si el recuento de conexiones activas es alto, verifica si hay consultas de larga duración o eventos de espera que bloqueen la ejecución de las consultas.

Si el recuento de conexiones inactivas es alto, ejecuta la siguiente consulta para finalizar las conexiones después de realizar las aprobaciones necesarias.

SELECT 
  pg_terminate_backend(pid) 
FROM 
  pg_stat_activity 
WHERE 
  usename = 'sbtest' 
  and pid <> pg_backend_pid() 
  and state in ('idle');

También puedes finalizar las conexiones de forma individual con pg_terminate_backend mediante la siguiente consulta:

SELECT pg_terminate_backend (<pid>);

Aquí, puedes obtener el PID de pg_stat_activity.

Identifica las conexiones de larga duración

Este es un ejemplo de una consulta que muestra consultas de larga duración. En este caso, puedes identificar las consultas que estuvieron activas durante más de 5 minutos.

SELECT 
  pid, 
  query_start, 
  xact_start, 
  now() - pg_stat_activity.query_start AS duration, 
  query, 
  state 
FROM 
  pg_stat_activity 
WHERE 
  (
    now() - pg_stat_activity.query_start
  ) > interval '5 minutes' order by 4 desc;

Revisa el plan de explicación para identificar consultas mal escritas

Usa EXPLAIN PLAN para investigar una consulta mal escrita y reescribir la consulta, si es necesario. También puedes cancelar la consulta de larga duración con el siguiente comando y las aprobaciones necesarias.

SELECT pg_cancel_backend(<pid>);

Supervisa la actividad de VACUUM

La actividad de AUTOVACUUM que borra las tuplas inactivas es una operación con uso intensivo de CPU. Si tu instancia usa la versión 11 de PostgreSQL o una posterior, usa la siguiente consulta para verificar si hay actividad en curso de AUTOVACUUM o VACUUM.

SELECT 
  relid :: regclass, 
  pid, 
  phase, 
  heap_blks_total, 
  heap_blks_scanned, 
  heap_blks_vacuumed, 
  index_vacuum_count, 
  max_dead_tuples, 
  num_dead_tuples 
FROM 
  pg_stat_progress_vacuum;

Verifica si hay una actividad de VACUUM en curso en una instancia mediante la siguiente consulta:

SELECT 
  pid, 
  datname,
  usename, 
  query 
FROM 
  pg_stat_activity 
WHERE 
  query like '%vacuum%';

Además, puedes optimizar y solucionar problemas de las operaciones de VACUUM en PostgreSQL.

Agrega la extensión pg_stat_statements

Configura la extensión pg_stat_statements para obtener información mejorada en el diccionario sobre la actividad de las instancias.

Puntos de control frecuentes

Los puntos de control frecuentes degradan el rendimiento. Considera ajustar la marca checkpoint_timeout si el registro de alerta de PostgreSQL informa la advertencia checkpoint occurring too frequently.

Recopila estadísticas

Asegúrate de que el planificador de consultas tenga las estadísticas más recientes sobre tablas para elegir el mejor plan de consultas. La operación ANALYZE recopila estadísticas sobre el contenido de las tablas en la base de datos y almacena los resultados en el catálogo de sistema pg_statistic. Luego, el planificador de consultas usa estas estadísticas a fin de determinar los planes de ejecución más eficaces para las consultas. El proceso AUTOVACUUM analiza las tablas de forma automática y periódica, por lo que debes ejecutar el siguiente comando para verificar si todas las tablas se analizaron y tienen los metadatos más recientes disponibles para el planificador.

SELECT 
  relname, 
  last_autovacuum, 
  last_autoanalyze 
FROM 
  pg_stat_user_tables;

Configuración inadecuada del sistema

Existen otros factores y parámetros de configuración de marcas o sistemas que influyen en el rendimiento de tu consulta. Ejecuta la siguiente consulta para verificar los eventos de espera y el tipo de evento de espera a fin de obtener las estadísticas sobre el rendimiento de otras opciones de configuración del sistema.

SELECT 
  datname, 
  usename, 
  (
    case when usename is not null then state else query end
  ) AS what, 
  wait_event_type, 
  wait_event, 
  backend_type, 
  count(*) 
FROM 
  pg_stat_activity 
GROUP BY 
  1, 
  2, 
  3, 
  4, 
  5, 
  6 
ORDER BY 
  1, 
  2, 
  3, 
  4 nulls first, 
  5, 
  6;

El resultado es similar al siguiente:

  
 ..  | .. | what           | wait_event_type |      wait_event      | ..    | count
-..--+-..-+----------------+-----------------+----------------------+-..----+------
 ..
 ..  | .. | active         | IO              | CommitWaitFlush      | ..    |   750
 ..  | .. | idle           | IO              | CommitWaitFlush      | ..    |   360
 ..  | .. | active         | LWLock          | BufferMapping        | ..    |   191
  

Supervisa los análisis secuenciales

Los análisis secuenciales frecuentes en tablas de más de unas pocas decenas de filas suelen indicar que falta un índice. Cuando los análisis alcanzan miles o incluso cientos de miles de filas, pueden causar un uso excesivo de CPU.

Los análisis secuenciales frecuentes en tablas con cientos de miles de filas pueden causar un uso excesivo de CPU. Evita los análisis secuenciales en esas tablas mediante la creación de índices necesarios.

Ejecuta la siguiente consulta para verificar la cantidad de veces que se inician análisis secuenciales en cualquier tabla.

SELECT 
  relname, 
  idx_scan,  
  seq_scan, 
  n_live_tup 
FROM 
  pg_stat_user_tables 
WHERE 
  seq_scan > 0 
ORDER BY 
  n_live_tup desc;

Por último, si la CPU sigue siendo alta y crees que esas consultas son tráfico legítimo, considera aumentar los recursos de CPU en tu instancia para evitar fallas o tiempo de inactividad en la base de datos.