¿Cómo puedo identificar y solucionar los problemas de rendimiento y las consultas de ejecución lenta en mi instancia de base de datos compatible con Amazon RDS para PostgreSQL o Aurora PostgreSQL?

Resolución

El tamaño insuficiente del hardware, los cambios en la carga de trabajo, el aumento del tráfico, los problemas de memoria o las consultas no optimizadas afectan al rendimiento de las instancias de base de datos. Para resolver los problemas de rendimiento, tome las siguientes medidas.

Identificación de la causa

Comprobación de las métricas de CloudWatch

Para identificar los cuellos de botella en el rendimiento causados por la insuficiencia de recursos, supervise las métricas de Amazon CloudWatch CPUUtilization, FreaableMemory y SwapUsage.

Cuando la utilización de la CPU es alta, una carga de trabajo activa en la instancia de base de datos requiere más recursos de CPU. La baja disponibilidad de memoria para la carga de trabajo provoca un intercambio frecuente que se traduce en un uso elevado de la memoria y del espacio de intercambio. Las consultas de larga duración, el aumento repentino del tráfico o una gran cantidad de conexiones inactivas pueden provocar un uso elevado de la CPU y de los recursos de memoria.

Para ver las consultas activas en tiempo de ejecución, ejecute el siguiente comando:

SELECT pid, usename, age(now(),xact_start) query_time, query FROM pg_stat_activity WHERE state='active';

Para ver las conexiones inactivas de la base de datos, ejecute el siguiente comando:

SELECT count(*) FROM pg_stat_activity WHERE state='idle';

Para terminar las conexiones inactivas, ejecute el siguiente comando:

SELECT pg_terminate_backend(example-pid);

Nota: Sustituya example-pid por el identificador de proceso de la conexión inactiva.

Para comprobar que la instancia de base de datos alcanza el rendimiento de red esperado, compruebe las métricas NetworkReceiveThroughput y NetworkTransmitThroughput. Las clases de instancias de Amazon Elastic Block Service (Amazon EBS) de tamaño insuficiente o no optimizadas pueden afectar al rendimiento de la red y provocar que las instancias sean lentas. Un rendimiento de red bajo puede provocar respuestas lentas para todas las solicitudes de las aplicaciones, independientemente del rendimiento de la base de datos.

Para evaluar el rendimiento de E/S, compruebe las métricas ReadIOPS, WriteIOPS, ReadLatency, WriteLatency, ReadThroughput, WriteThroughput y DiskQueueDepth. Para obtener más información, consulte ¿Cómo puedo resolver problemas de latencia de los volúmenes de Amazon EBS provocados por un cuello de botella de IOPS en mi instancia de Amazon RDS?

Uso de Supervisión mejorada

Utilice la supervisión mejorada para ver las métricas a nivel del sistema operativo (SO) y enumerar los 100 procesos principales que utilizan mucha CPU y memoria. Active la supervisión mejorada con un nivel de granularidad establecido en 1 para identificar los problemas de rendimiento intermitentes en la instancia de base de datos.

Evalúe las métricas del sistema operativo disponibles para diagnosticar los problemas de rendimiento relacionados con la CPU, la carga de trabajo, la E/S, la memoria y la red. En la lista de procesos, identifique los procesos con valores altos de CPU% o Mem%.

Ejemplo:

NAME (NOMBRE)	VIRT	RES	CPU%	MEM%	VMLIMIT
postgres: postgres postgres 178.2.0.44(42303) SELECT [10322]	250,66 MB	27,7 MB	85,93	2,21	ilimitado

Conéctese a la base de datos y, a continuación, ejecute la siguiente consulta para encontrar la conexión con una CPU alta en la base de datos:

SELECT * FROM pg_stat_activity WHERE pid = 10322;

Nota: Sustituya 10322 por el identificador de proceso de la conexión.

Comprobación de las métricas de Performance Insights

Utilice Performance Insights para evaluar las cargas de trabajo de las bases de datos por esperas, SQL, host o usuarios. También puede obtener las métricas a nivel de base de datos y SQL.

Utilice la pestaña Top SQL del panel de Performance Insights para ver las instrucciones SQL que más contribuyen a la carga de la base de datos. Si la carga de la base de datos o la carga por esperas (AAS) supera el máximo de vCPU, la carga de trabajo de la clase de instancia de base de datos se limita.

Utilice la latencia media por llamada en las estadísticas de SQL para ver el tiempo medio de ejecución de una consulta. Top SQL se basa en el tiempo total de ejecución. Como resultado, el SQL con el mayor tiempo de ejecución suele ser diferente del SQL que contribuye más a la carga de la base de datos.

Comprobación de las estadísticas de la base de datos

Para evaluar el rendimiento de la base de datos en PostgreSQL, compruebe las estadísticas de distribución de datos, las estadísticas ampliadas y las estadísticas de supervisión. Para obtener información sobre las estadísticas, consulte Descripción de las estadísticas en PostgreSQL.

Comprobación de las herramientas de bases de datos nativas

Para identificar las consultas lentas, use la herramienta nativa pgbadger en el sitio web de GitHub. Para obtener más información, consulte Optimización y ajuste de consultas en Amazon RDS para PostgreSQL en función de herramientas nativas y externas.

Optimización del rendimiento

Ajuste de la configuración de memoria

Puede establecer el parámetro shared_buffers en un valor que ayude a mejorar el rendimiento de las consultas.

Los parámetros work_mem y maintenance_work_mem definen la cantidad de memoria que se usa para los procesos de backend. Para obtener más información, consulte 20.4 Resource consumption (20.4 Consumo de recursos) en el sitio web de PostgreSQL. Si con frecuencia consume mucha memoria en la instancia de base de datos, reduzca los valores de los parámetros en el grupo de parámetros personalizados adjunto a la instancia.

Uso de la administración de planes de consultas compatible con Aurora PostgreSQL

Utilice la administración de planes de consultas compatible con Aurora PostgreSQL para controlar cómo y cuándo cambian los planes de ejecución de consultas. Para obtener más información, consulte Prácticas recomendadas para la administración de planes de consultas de Aurora PostgreSQL.

Solución de problemas de consultas de ejecución lenta

Los problemas de infraestructura, la planificación de consultas no optimizada o el alto uso general de los recursos provocan que las consultas se ejecuten con lentitud. El planificador de consultas de PostgreSQL usa estadísticas de tablas para crear planes de consultas. Los cambios en el esquema y las estadísticas antiguas pueden afectar a los planes. La sobrecarga de tablas e índices también puede hacer que las consultas se ejecuten con lentitud.

Cuando una tabla alcanza el umbral de tuplas inactivas, el daemon de autovacuum crea procesos de trabajo de autovacuum que eliminan las tuplas inactivas de la tabla. El daemon de autovacuum también ejecuta la operación ANALYZE que actualiza las estadísticas de la tabla.

Ejecute la siguiente consulta para comprobar si hay tuplas inactivas y operaciones de autovacuum o vacuum y ejecuciones de autoanalyze o analyze:

SELECT schemaname, relname, n_live_tup,n_dead_tup, last_autoanalyze, last_analyze, last_autovacuum, last_vacuum,autovacuum_count+vacuum_count vacuum_count, analyze_count+autoanalyze_count analyze_count
FROM pg_stat_user_tables
ORDER BY 5 DESC;

Use la vista pg_stat_activity para buscar datos relacionados con las actividades actuales, como una consulta o un identificador de proceso de backend. Para buscar consultas de ejecución prolongada, ejecute la siguiente consulta:

SELECT pid, datname, query, extract(epoch from now()) - extract(epoch from xact_start) AS duration, case
WHEN wait_event IS NULL THEN 'CPU'
ELSE wait_event_type||':'||wait_event end wait FROM pg_stat_activity
WHERE query!=current_query() AND xact_start IS NOT NULL ORDER BY 4 DESC;

Es posible que las consultas que esperan ser bloqueadas sean lentas. Para comprobar si una consulta está a la espera de ser bloqueada, ejecute la siguiente consulta:

SELECT pid, virtualtransaction AS vxid, locktype AS lock_type, mode AS lock_mode, granted,fastpath,CASE
WHEN virtualxid IS NOT NULL AND transactionid IS NOT NULL
THEN virtualxid || ' ' || transactionid
WHEN virtualxid::text IS NOT NULL
THEN virtualxid
ELSE transactionid::text
END AS xid_lock, relname, page, tuple, classid, objid, objsubid
FROM pg_locks LEFT OUTER JOIN pg_class ON (pg_locks.relation = pg_class.oid)
WHERE -- do not show our view's locks
pid != pg_backend_pid() AND
virtualtransaction IS DISTINCT FROM virtualxid
ORDER BY 1, 2, 5 DESC, 6, 3, 4, 7;

Utilice pg_stat_statements para ver las estadísticas de las consultas. Antes de crear la extensión pg_stat_statements, añada la entrada pg_stat_statements a shared_preload_libraries. Para crear la extensión pg_stat_statements en la base de datos, ejecute la siguiente consulta:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Nota: Puede modificar los parámetros de pg_stats_statements solo cuando haya un grupo de parámetros personalizado adjunto a la instancia de base de datos.

Para identificar las consultas de SQL que afectan al rendimiento de la instancia de base de datos, ejecute las siguientes consultas.

PostgreSQL versión 12 y anteriores:

SELECT query, calls, total_time, mean_time FROM pg_stat_statements ORDER BY 3 DESC;

PostgreSQL versión 13 y posteriores:

SELECT query, calls, total_plan_time+total_exec_time AS total_time, mean_plan_time + mean_exec_time AS mean_time FROM pg_stat_statements ORDER BY 3 DESC;

Para buscar consultas con un índice de aciertos de caché de búfer inferior, ejecute las siguientes consultas.

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_percent
FROM pg_stat_statements ORDER BY 5 ASC
LIMIT 10;

Para buscar consultas o planes de consultas de ejecución prolongada en los registros de errores de la base de datos, configure el parámetro log_min_duration_statement para su instancia de base de datos y, a continuación, utilice el módulo auto_explain.

También puede usar los comandos explain y explain analyze para obtener el plan de consulta. Utilice el módulo auto_explain o los comandos explain para identificar cómo puede ajustar el ajuste de las consultas. Para obtener más información, consulte 14.1 Using EXPLAIN (14.1 Uso de EXPLAIN) y F3. auto_explain - log execution plans of slow queries (F3. auto_explain: planes de ejecución de registros de consultas lentas) en el sitio web de PostgreSQL.

Si ha optimizado el sistema y sigue teniendo problemas de rendimiento, se recomienda escalar verticalmente la clase de instancia de base de datos. Cuando escala verticalmente la instancia de base de datos, asigna más recursos de procesamiento y memoria.

Información relacionada

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

Uso de parámetros en su instancia de base de datos de RDS para PostgreSQL

¿Por qué mi instancia de base de datos de Amazon RDS utiliza memoria de intercambio cuando tengo memoria suficiente?