Blog de Amazon Web Services (AWS)

Optimización y ajuste de consultas en Amazon RDS PostgreSQL basadas en herramientas nativas y externas

Por Baji Shaik, Consultor Asociado del equipo de AWS ProServe en India
Octubre 2022: Esta publicación fue revisada para verificar su exactitud.

 

PostgreSQL es uno de los sistemas de bases de datos relacionales de código abierto más populares. Resultado de más de 30 años de trabajo de desarrollo, PostgreSQL ha demostrado ser una base de datos altamente confiable y robusta que puede manejar una gran cantidad de cargas de trabajo de datos complejas. PostgreSQL se considera la principal opción de base de datos de código abierto al migrar desde bases de datos comerciales como Oracle.

AWS ofrece servicios que hacen que las implementaciones de bases de datos PostgreSQL sean fáciles de configurar, administrar y escalar para la nube de una manera rentable. Estos servicios son Amazon RDS para PostgreSQL y Amazon Aurora con compatibilidad con PostgreSQL.

El rendimiento de la base de datos depende de muchos factores a nivel de aplicación, así como de hardware, como CPU y memoria. Esta publicación se dirige a uno de los factores clave de rendimiento: el rendimiento de las consultas. La lentitud de las consultas es un problema común en la mayoría de los entornos. Esta publicación discute lo siguiente:

  • Cómo averiguar qué consultas son lentas, utilizando herramientas de base de datos nativas.
  • Cómo utilizar Amazon RDS Performance Insights para encontrar problemas de rendimiento.
  • Cómo arreglar consultas lentas.

Identificar consultas lentas y ajustar para un mejor rendimiento es una tarea importante para los desarrolladores y administradores de bases de datos que administran entornos RDS y Amazon Aurora PostgreSQL.

PostgreSQL proporciona una serie de herramientas para identificar consultas lentas, como el popular pgBadger. pgBadger es un analizador de archivos de registro de PostgreSQL, construido para agilizar la generación de informes completos de archivos de registro de PostgreSQL. Esta herramienta es un script pequeño de Perl que supera a cualquier otro analizador de archivos de registro de PostgreSQL. Para utilizar esta herramienta para generar informes, debe establecer los niveles de registro (log levels) apropiadamente.

El siguiente diagrama lógico demuestra cómo usar pgBadger. Usted necesitará una instancia de Amazon EC2 para descargar los archivos de registros de PostgreSQL y generar informes de pgBadger.

En el siguiente tutorial se muestra cómo identificar consultas lentas usando pgBadger.

 

Prerrequisitos

Cree un nuevo grupo de parámetros con parámetros de registro para pgBadger.

pgBadger funciona en archivos de registro, y debe analizar cierta información para construir un informe. Su base de datos debe estar configurada para capturar la información que pgBadger requiere.

Primero, establezca los siguientes parámetros. Puede encontrar más información sobre estos parámetros en Uso de parámetros en su instancia de base de datos de RDS for PostgreSQL

y el Registro y Reporte de Errores.

log_checkpoints = on (Solo para RDS)  
log_connections = on  
log_disconnections = on  
log_lock_waits = on  
log_temp_files = 0  
log_autovacuum_min_duration = 0  
log_error_verbosity = default
log_min_duration_statement=0
rds.force_autovacuum_logging_level = 'log'

Habilitar estos parámetros puede crear carga para escribir en los archivos de registro (logs) y usar espacio para estos archivos de registro. Usted no necesita establecer estos parámetros de forma permanente; puede hacerlo solo cuando sea necesario, como cuando quiera analizar el rendimiento de su base de datos. Revierta la configuración después de solucionar el problema.

A continuación, cree una instancia de RDS o Aurora PostgreSQL siguiendo los pasos para crear y conectarse a una base de datos PostgreSQL. Asegúrese de elegir el grupo de parámetros que creó en el paso anterior al crear la instancia.

Descarga de los registros para generar un informe

Puede ver los registros de Postgres en la pestaña Registros y eventos (Logs & events) cuando selecciona su instancia de RDS o Aurora en la consola de administración de AWS, como se muestra en la siguiente captura de pantalla.

  1. Descargue un archivo de registro (log) en su máquina local o en una instancia de Amazon EC2 para generar un informe.
    1. Elija Registros y eventos (Logs & events).
    2. En Registros (Logs), seleccione el archivo de registro que desea descargar.
    3. Elija Descargar.
  2. Puede descargar los archivos de registro usando AWS CLI y el comando download-db-log-file-portion. Por ejemplo:
aws rds download-db-log-file-portion \ --db-instance-identifier myexampledb \ --starting-token 0 --output text \ --log-file-name log/ERROR.4 > errorlog.txt
  1. Ahora puede publicar registros desde Amazon Aurora con compatibilidad PostgreSQL en Amazon CloudWatch Logs en Amazon RDS. Para obtener más información, consulte Publicación de registros de Aurora PostgreSQL en Amazon CloudWatch Logs.

 

Generar el reporte

En este ejemplo se crea una base de datos de muestra con información de empleados.

Para generar un informe sobre un archivo de registro descargado siga los siguientes pasos:

  1. Descargar el repositorio pgBadger.
  2. Descomprimir el archivo pgBadger y encuentrar el binario pgBadger dentro del directorio descomprimido.
  3. Utilice el siguiente comando para generar un informe pgBadger sobre el archivo de registro descargado.
./pgbadger -p "%t:%r:%u@%d:[%p]:" postgresql.log.2019-06-20-12 -o pgbadger_rdsinstance.html
  • -p es para el valor del parámetro log_line_prefix personalizado (RDS tiene un valor fijo como %t: %r: %u@%d: [%p]:).
  • -o es para el archivo HTML de salida.
  1. Al abrir el informe HTML, debería verse como el ejemplo en la siguiente captura de pantalla.

El informe HTML contiene información sobre el número de consultas y consultas únicas, el tiempo total de ejecución de consultas, el tráfico SQL del número de consultas por segundo, y más.

  1. Las consultas de ejecución lenta se muestran en Time Consuming queries (N) en la sección Top, como se muestra en la siguiente captura de pantalla. A partir de ahí, puede iniciar la solución de problemas de las consultas lentas.

pgBadger proporciona información sobre sus consultas SQL, las consultas en espera más frecuentes, las consultas más frecuentes, un histograma de consultas y tiempos de sesión, y los usuarios involucrados en las consultas principales.

También proporciona información en forma gráfica, como gráficos circulares sobre la distribución de estadísticas de bloqueo, y consultas por tipo (select/insert/update/delete).

Todos los gráficos son ampliables y se pueden guardar como imágenes PNG. Las consultas SQL reportadas se resaltan y embellecen automáticamente.

Diagnóstico de lentitud

Tome una consulta lenta del informe y analice:

  • ¿Por qué la consulta fue lenta?
  • ¿Se puede ajustar la consulta? Si es así, ¿cómo?

Encontrar la causa de la lentitud

La mayoría de los desarrolladores usan DISTINCT cuando usan uniones como se muestra a continuación:

SELECT DISTINCT d.dept_no, 
                d.dept_name 
FROM   departments d, 
       dept_emp e 
WHERE  e.dept_no = d.dept_no;

Cuando las tablas involucradas en la consulta tienen una relación uno a muchos, use EXISTS en lugar de DISTINCT como se muestra a continuación. Esto entrega un mejor rendimiento:

SELECT d.dept_no, 
       d.dept_name 
FROM   departments d 
WHERE  EXISTS (SELECT 'X' 
               FROM   dept_emp e 
               WHERE  e.dept_no = d.dept_no);

Uso de AWS Performance Insights:

Para afinar y monitorear instancias de bases de datos RDS y Aurora, AWS lanzó recientemente una funcionalidad llamada Amazon RDS Performance Insights, que proporciona un panel de control fácil de entender para detectar problemas de rendimiento en términos de carga.

Puede habilitar Performance Insights mientras crea la instancia o modifica la instancia.

Para ver las métricas de Performance Insights, elija Performance Insights en el panel izquierdo de sus bases de datos de Amazon RDS.

El panel de Performance Insights se muestra en la siguiente captura de pantalla.

Puede monitorear las consultas SQL que causaron la carga, las esperas de operaciones de E/S y los usuarios y hosts a través de los cuales se ejecutaron las consultas.

Como puede ver en la captura de pantalla anterior, se ha detectado que la misma consulta SQL consume la mayor cantidad de CPU.

Performance Insights soporta métricas de contador para RDS PostgreSQL. Las métricas de contador le permiten personalizar su panel de Performance Insights para incluir hasta 10 gráficos adicionales del sistema operativo y las métricas de la base de datos. Es útil identificar y analizar problemas de rendimiento correlacionando gráficos de carga.

Las métricas de contador de rendimiento son nativas y no nativas.

Como puede ver en la siguiente captura de pantalla, las métricas de contador se actualizan con tuplas recuperadas, tuplas devueltas, latencia de bloques y bloques leídos.

Resumen

Esta publicación explicó las herramientas nativas y de AWS para encontrar consultas lentas en una base de datos, y demostró cómo solucionarlas. La herramienta pgBadger se basa en archivos de registro de PostgreSQL, por lo que el registro debe estar habilitado para que pgBadger analice y cree informes. Performance Insights es una funcionalidad de AWS que muestra la información de la base de datos y del sistema operativo que indica dónde una consulta es lenta (debido a operaciones de E/S, carga o CPU). Performance Insights tiene métricas de contador que brindan información adicional sobre lo que sucede en segundo plano mientras se ejecutan las consultas.

A diferencia de Performance Insights, pgBadger requiere que habilite todos los registros (como se describe en la sección requisitos previos) para obtener el informe. La falta de registros conduce a la generación parcial de informes. Además, pgBadger requiere un servidor o instancia EC2 para descargar y generar un informe. Con las métricas de contador de Performance Insights, se tiene una buena visibilidad de las estadísticas de espera de CPU, carga y operaciones de E/S, así como estadísticas de bases de datos.

 

Este artículo fue traducido del Blog de AWS en Inglés.

 

Acerca del Autor

Baji Shaik es Consultor Asociado del equipo de AWS ProServe en India. Su experiencia abarca una amplia profundidad y amplitud de conocimientos y experiencia en tecnologías de bases de datos SQL/NoSQL. Ha diseñado muchas soluciones de bases de datos exitosas que abordan los desafiantes requisitos del negocio. Ha proporcionado soluciones utilizando PostgreSQL para informes, inteligencia de negocios, almacenamiento de datos, aplicaciones y soporte de desarrollo. También tiene un buen conocimiento de automatización, orquestación y DevOps en entornos de nube.

 

 

 

 

Traductor

Sergio Nuñez es especialista en bases de datos en el Equipo de Aceleración de la Nube de Amazon Web Services. Sergio se enfoca en liderar los esfuerzos de migración de bases de datos a AWS, así como de proporcionar orientación técnica que incluye optimización de costos, monitoreo y experiencia en modernización a los clientes de Amazon.