¿Cómo puedo identificar y solucionar problemas de rendimiento y consultas de ejecución lenta en mi instancia de RDS para PostgreSQL o Aurora PostgreSQL?

Resolución

El rendimiento de la instancia de Amazon RDS for PostgreSQL puede verse afectado por varios motivos, por ejemplo:

• Hardware de tamaño insuficiente
• Cambios en la carga de trabajo
• Aumento del tráfico
• Problemas de memoria
• Planes de consulta subóptimos

Identificar la causa

Utilice una combinación de estas herramientas para identificar la causa de las consultas de ejecución lenta:

• Métricas de Amazon CloudWatch
• Métricas de Supervisión mejorada
• Métricas de Información sobre rendimiento
• Estadísticas de bases de datos
• Herramientas de bases de datos nativas

Métricas de CloudWatch

Para identificar los cuellos de botella de rendimiento causados por la falta de recursos, supervise estas métricas comunes de CloudWatch disponibles para su instancia de base de datos de Amazon RDS.

• CPUUtilization: porcentaje de capacidad de procesamiento de computación utilizado
• FreeableMemory: RAM disponible en la instancia de base de datos (en megabytes)
• SwapUsage: espacio de intercambio utilizado por la instancia de base de datos (en megabytes)

Un porcentaje más alto de uso de CPU generalmente indica una carga de trabajo activa en la instancia y la necesidad de más recursos de CPU. Una mayor utilización de la memoria junto con el consumo de espacio de intercambio indica un intercambio frecuente debido a la baja disponibilidad de memoria para la carga de trabajo. Esto puede significar que su instancia no puede mantenerse al día con la carga de trabajo. El uso elevado de recursos de CPU y memoria suele deberse a consultas de larga duración, al aumento repentino del tráfico o a una gran cantidad de conexiones inactivas.

Ejecute este comando para ver las consultas activas con tiempo de ejecución:

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

Ejecute este comando para buscar el número de conexiones inactivas presentes en la base de datos:

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

A continuación, ejecute este comando para terminar las conexiones inactivas que consumen recursos:

Nota: Asegúrese de reemplazar example-pid por el PID de la conexión inactiva que obtuvo de pg_stat_activity:

SELECT pg_terminate_backend(example-pid);

Verifique que se alcanza el rendimiento de red deseado en la instancia de RDS mediante la revisión de estas métricas:

• NetworkReceiveThroughput
• NetworkTransmitThroughput

Estas métricas indican el tráfico de red entrante y saliente en bytes/segundo. Las clases de instancias de tamaño insuficiente o no optimizadas para Amazon Elastic Block Service (Amazon EBS) pueden afectar al rendimiento de la red y provocar instancias lentas. Un rendimiento de red bajo puede provocar respuestas lentas para todas las solicitudes de aplicaciones, independientemente del rendimiento de la base de datos.

Evalúe el rendimiento de E/S mediante la revisión de estas métricas:

• ReadIOPS y WriteIOPS: número promedio de operaciones de lectura o escritura de disco por segundo
• ReadLatency y WriteLatency: tiempo promedio empleado para una operación de lectura o escritura en milisegundos
• ReadThroughput y WriteThroughput: número promedio de megabytes leídos o escritos en el disco por segundo
• DiskQueueDepth: número de operaciones de E/S que esperan ser escritas o leídas desde el disco

Para 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?

Métricas de supervisión mejorada

Con Supervisión mejorada, puede ver las métricas a nivel de sistema operativo y la lista de los 100 procesos principales que consumen mucha CPU y memoria. Puede activar Supervisión mejorada por segundo para identificar problemas de rendimiento intermitentes en la instancia de RDS.

Puede evaluar las métricas del sistema operativo disponibles para diagnosticar problemas de rendimiento que puedan estar relacionados con la CPU, la carga de trabajo, la E/S, la memoria y la red.

En la lista de procesos, identifique el proceso con valores altos para CPU% o Mem%. A continuación, busque la conexión relacionada en la base de datos.

Por ejemplo:

NAME	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 esta consulta para buscar la información relacionada con la conexión y la consulta:

SELECT * FROM pg_stat_activity WHERE pid = 10322;

Métricas de Información sobre rendimiento

La información sobre rendimiento le permite evaluar las cargas de trabajo de bases de datos divididas por esperas, SQL, host o usuarios. También puede capturar métricas a nivel de base de datos y SQL.

La pestaña Top SQL (SQL principal) del panel información sobre rendimiento muestra las instrucciones SQL que más contribuyen a la carga de la base de datos. Una carga de base de datos o carga por espera (AAS) superior al valor máximo de vCPU indica una carga de trabajo limitada en la clase de instancia.

La latencia promedio por llamada en las estadísticas de SQL proporciona el tiempo de ejecución promedio de una consulta. Es común ver que una instrucción SQL diferente a la que tiene el tiempo de ejecución promedio más alto es la principal contribuyente de la carga de base de datos. Esto se debe a que la lista Top SQL (SQL principal) se basa en el tiempo total de ejecución.

Estadísticas de bases de datos

Las siguientes estadísticas pueden ayudarlo a evaluar el rendimiento de la base de datos en PostgreSQL:

• Estadísticas de distribución de datos
• Estadísticas ampliadas
• Estadísticas de monitoreo

Para obtener información sobre cómo leer y entender estas estadísticas, consulte Comprender las estadísticas en PostgreSQL.

Herramientas de bases de datos nativas

Para identificar consultas lentas, usa la herramienta nativapgBadger. Para obtener más información, consulteOptimizar y ajustar las consultas en Amazon RDS for PostgreSQL en función de herramientas nativas y externas.

Optimice el rendimiento

Ajustar la configuración de memoria

El servidor de base de datos PostgreSQL asigna un área de memoria determinada durante toda su vida útil para almacenar datos en caché y mejorar los accesos de lectura y escritura. Esta área de memoria se denomina búferes compartidos. La cantidad de memoria que la base de datos utiliza para los búferes de memoria compartida se controla mediante los parámetros shared_buffers.

Además del área de memoria compartida, cada proceso de backend consume memoria para realizar operaciones dentro de un servidor de base de datos. La cantidad de memoria que se utiliza se basa en los valores establecidos para los parámetros work_mem ymaintenance_work_mem. Para obtener más información, consulte la documentación de PostgreSQL para la configuración del servidor.

Si observa continuamente una alta presión de memoria en la instancia de base de datos, considere la posibilidad de reducir los valores de estos parámetros. Puede reducir los valores de estos parámetros en el grupo de parámetros personalizados que está adjunto a su instancia de base de datos.

Administración del plan de consultas Aurora PostgreSQL

Utilice la administración de planes de consultas de la edición compatible con PostgreSQL de Amazon Aurora 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 compatibles con Aurora PostgreSQL.

Solucionar problemas de consultas de ejecución lenta

Por lo general, las consultas se ejecutan lentamente cuando hay problemas de infraestructura o el consumo general de recursos es alto. Las consultas lentas también pueden ser el resultado de una planificación de consultas subóptima por parte del planificador de consultas. El planificador de consultas de PostgreSQL utiliza las estadísticas creadas para la tabla a la hora de crear planes de consultas. Estos planes pueden verse afectados por cambios en el esquema y estadísticas obsoletas. La sobrecarga en la tabla y los índices también puede provocar consultas lentas.

El daemon autovacuum es responsable de crear procesos de trabajo de vacío automático que eliminan las tuplas muertas de las tablas cuando se alcanza el umbral de tuplas muertas. El daemon autovaccum también es responsable de ejecutar la operación ANALYZE que actualiza las estadísticas almacenadas para una tabla en particular.

Ejecute la siguiente consulta para obtener información sobre:

• Tuplas muertas
• Número de operaciones de vacío o vacío automático
• Número de ejecuciones de autoanálisis o análisis
• Cuándo se ejecutaron estas operaciones por última vez

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;

Puede usar la vista pg_stat_activity para buscar datos relacionados con las actividades actuales. Esta vista proporciona el pid del backend, la consulta y otros detalles. Para buscar consultas de larga duración, ejecute esta 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;

Tenga en cuenta que las consultas que esperan bloqueos pueden ser lentas. Por lo tanto, compruebe si la consulta está esperando bloqueos mediante la ejecución de esta 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;

La instancia de RDS para PostgreSQL le permite crear la extensión pg_stat_statements desde la base de datos:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Con pg_stat_statements, puede ver las estadísticas de las consultas. Asegúrese de agregar la entrada pg_stat_statements a shared_preload_libraries antes de crear la extensión.

Nota: Puede modificar los parámetros de este módulo solo cuando se conecta un grupo de parámetros personalizado a la instancia de base de datos.

Use estas consultas para identificar las principales consultas de SQL que afectan al rendimiento de su instancia.

Para buscar consultas que pasen más tiempo en la base de datos, ejecute esta consulta para las versiones 12 y anteriores de PostgreSQL:

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

Ejecute esta consulta para las versiones 13 y posteriores de PostgreSQL:

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 encontrar consultas con una tasa de aciertos de caché de búfer más baja, ejecute esta consulta para las versiones 12 y anteriores de PostgreSQL:

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;

Ejecute esta consulta para las versiones 13 y posteriores de PostgreSQL:

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 capturar consultas o planes de consultas de larga duración en los registros de errores de la base de datos, configure correctamente el parámetro log_min_duration_statement para su instancia y luego use el módulo auto_explain. El parámetro log_min_duration_statement hace que se registre la duración de cada declaración completada si la declaración se ejecutó durante al menos la cantidad de tiempo especificada. Por ejemplo, si establece este parámetro en 250 ms, se registrarán todas las instrucciones SQL que se ejecuten durante 250 ms o más. El módulo auto_explain le permite capturar el plan de explicación de las consultas que se ejecutan en la base de datos.

También puede capturar el plan mediante los comandos explain y explain analyze. Identifique las oportunidades de ajuste de consultas en función del módulo auto_explain o de los comandos explain para la consulta. Para más información, consulte la documentación de PostgreSQL acerca del uso de EXPLAIN.

Si su sistema está bien ajustado y sigue teniendo problemas con la limitación de los recursos, considere la posibilidad de escalar la clase de instancias hacia arriba. Escale la clase de instancias hacia arriba para asignar a su instancia de base de datos más recursos informáticos y de memoria. Para obtener más información, consulte Especificaciones de hardware para clases de instancias de base de datos.

---

Información relacionada

¿Cómo puedo solucionar los problemas de uso elevado de la CPU para Amazon RDS o la edición compatible con PostgreSQL de Amazon Aurora?

Trabajar con parámetros en la instancia de base de datos de RDS para PostgreSQL

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