¡Hola, Khabrovites! Les traigo a su atención una traducción del artículo
"Cómo un solo cambio de configuración de PostgreSQL mejoró el rendimiento de la consulta lenta en 50x" por Pavan Patibandla. Me ayudó mucho a mejorar el rendimiento de PostgreSQL.
En Amplitude, nuestro objetivo es proporcionar análisis de productos interactivos fáciles de usar para que todos puedan encontrar respuestas a sus preguntas sobre el producto. Para garantizar la usabilidad, Amplitude debe proporcionar estas respuestas rápidamente. Por lo tanto, cuando uno de nuestros clientes se quejó del tiempo que tardó en cargar la lista desplegable de propiedades de eventos en la interfaz de usuario de Amplitude, comenzamos un estudio detallado del problema.
Al rastrear el retraso en diferentes niveles, nos dimos cuenta de que tomó 20 segundos para completar una consulta PostgreSQL en particular. Esto nos sorprendió, ya que ambas tablas tienen índices en la columna de unión.
Solicitud lenta
El plan de ejecución de PostgreSQL para esta consulta fue inesperado para nosotros. A pesar del hecho de que ambas tablas tienen índices, PostgreSQL decidió realizar un Hash Join con escaneo secuencial de una tabla grande. Escanear secuencialmente una tabla grande tomó la mayor parte del tiempo de consulta.
Plan de ejecución lenta de consultas
Inicialmente sospeché que esto podría deberse a la fragmentación. Pero después de verificar los datos, me di cuenta de que los datos solo se agregan a esta tabla y prácticamente no se eliminan de allí. Dado que limpiar el lugar con VACUUM no ayudará mucho aquí, comencé a cavar más. Luego probé la misma solicitud en otro cliente con un buen tiempo de respuesta. Para mi sorpresa, ¡el plan de ejecución de la consulta parecía completamente diferente!
Plan de ejecución para la misma solicitud en otro cliente
Curiosamente, la aplicación A solo obtuvo acceso a 10 veces más datos que la aplicación B, pero el tiempo de respuesta fue 3.000 veces más largo.
Para ver planes alternativos de consulta PostgreSQL, apagué la conexión hash y reinicié la consulta.
Plan de ejecución alternativo para consulta lenta
Bueno aqui! La misma solicitud se completa 50 veces más rápido cuando se usa un bucle anidado en lugar de una combinación hash. Entonces, ¿por qué PostgreSQL eligió el peor plan para la aplicación A?
Con una mirada más cercana al costo estimado y al tiempo de entrega real para ambos planes, las relaciones estimadas de costo y tiempo de entrega real fueron muy diferentes. El principal culpable de esta discrepancia fue la estimación de costos del escaneo secuencial. PostgreSQL estima que los análisis secuenciales serían mejores que más de 4000 análisis de índice, pero de hecho, los análisis de índice fueron 50 veces más rápidos.
Esto me llevó a las
opciones de configuración
random_page_cost y
seq_page_cost . Los valores predeterminados de PostgreSQL son
4 y
1 para
random_page_cost ,
seq_page_cost , que están configurados para el HDD, donde el acceso aleatorio al disco es más costoso que el acceso secuencial. Sin embargo, estos costos fueron inexactos para nuestra implementación utilizando el volumen
gp2 EBS , que son unidades de estado sólido. Para nuestro despliegue, el acceso aleatorio y secuencial es casi el mismo.
Cambié el valor de
random_page_cost a
1 y
volví a
intentar la solicitud. Esta vez, PostgreSQL usó el bucle anidado y la consulta se ejecutó 50 veces más rápido. Después del cambio, también notamos una disminución significativa en el tiempo de respuesta máximo de PostgreSQL.
El rendimiento general de una solicitud lenta ha mejorado significativamente.
Si usa SSD y PostgreSQL con la configuración predeterminada, le aconsejo que intente
configurar random_page_cost y
seq_page_cost . Te sorprenderá la dramática mejora en el rendimiento.
Agregaré de mí mismo que configuré los parámetros mínimos
seq_page_cost = random_page_cost = 0.1 para dar prioridad a los datos en memoria (caché) sobre las operaciones del procesador, ya que he asignado una gran cantidad de RAM para PostgreSQL (el tamaño de la RAM excede el tamaño de la base de datos en el disco). No está muy claro por qué la comunidad de postgres todavía usa la configuración predeterminada que es relevante para un servidor con una pequeña cantidad de RAM y HDD, y no para servidores modernos. Esperemos que esto se arregle pronto.