Ajuste del rendimiento de la base de datos: los desarrolladores generalmente lo aman o lo odian. Disfruto esto y quiero compartir algunos de los métodos que he usado recientemente para ajustar consultas mal ejecutadas en PostgreSQL. Mis métodos no son exhaustivos, sino más bien un libro de texto para aquellos que solo se esfuerzan por afinar.
Buscar consultas lentas
La primera forma obvia de comenzar a ajustar es encontrar operadores específicos que funcionen mal.
pg_stats_statements
El módulo
pg_stats_statements es un excelente lugar para comenzar. Simplemente realiza un seguimiento de las estadísticas de ejecución de las instrucciones SQL y puede ser una manera fácil de encontrar consultas ineficientes.
Una vez que haya instalado este módulo, estará disponible una vista del sistema llamada
pg_stat_statements con todas sus propiedades. Una vez que tenga la oportunidad de recopilar suficientes datos, busque consultas que tengan un valor
total_tiempo relativamente alto
. Concéntrese primero en estos operadores.
SELECT * FROM pg_stat_statements ORDER BY total_time DESC;
auto_explain
El módulo
auto_explain también
es útil para encontrar consultas lentas, pero tiene 2 ventajas obvias: registra el plan de ejecución real y admite la grabación de declaraciones anidadas utilizando la opción
log_nested_statements . Las declaraciones anidadas son declaraciones que se ejecutan dentro de una función. Si su aplicación usa muchas funciones, auto_explain es invaluable para obtener planes de ejecución detallados.
La opción
log_min_duration controla qué planes de ejecución de consultas se registran en función de cuánto tiempo se ejecutan. Por ejemplo, si establece el valor en 1000, se registrarán todos los registros que demoren más de 1 segundo.
Ajuste de índice
Otra estrategia de ajuste importante es garantizar que los índices se usen correctamente. Como requisito previo, debemos incluir el recopilador de estadísticas.
Postgres Statistics Collector es un subsistema de primera clase que recopila todo tipo de estadísticas de rendimiento útiles.
Al habilitar este recopilador, obtiene toneladas de
vistas pg_stat _... que contienen todas las propiedades. En particular, encontré que esto es especialmente útil para encontrar índices faltantes y no utilizados.
Índices faltantes
Los índices faltantes pueden ser una de las soluciones más fáciles para mejorar el rendimiento de las consultas. Sin embargo, no son una bala de plata y deben usarse correctamente (más sobre esto más adelante). Si tiene habilitado el recopilador de estadísticas, puede ejecutar la siguiente consulta (
fuente ).
SELECT relname, seq_scan - idx_scan AS too_much_seq, CASE WHEN seq_scan - coalesce(idx_scan, 0) > 0 THEN 'Missing Index?' ELSE 'OK' END, pg_relation_size(relname::regclass) AS rel_size, seq_scan, idx_scan FROM pg_stat_all_tables WHERE schemaname = 'public' AND pg_relation_size(relname::regclass) > 80000 ORDER BY too_much_seq DESC;
La consulta encuentra tablas que tienen más escaneos secuenciales (escaneos de índice) que escaneos de índice, una señal obvia de que el índice ayudará. Esto no le dirá en qué columnas crear el índice, por lo que tomará un poco más de trabajo. Sin embargo, saber qué tablas las necesitan es un buen primer paso.
Índices no utilizados
Indice todas las entidades, ¿verdad? ¿Sabía que los índices no utilizados pueden afectar negativamente el rendimiento de escritura? La razón es que al crear el índice de Postgres, se carga con la tarea de actualizar este índice después de las operaciones de escritura (INSERT / UPDATE / DELETE). Por lo tanto, agregar un índice es un acto de equilibrio, ya que puede acelerar la lectura de datos (si se creó correctamente), pero ralentizará las operaciones de escritura. Para buscar índices no utilizados, puede ejecutar la siguiente consulta.
SELECT indexrelid::regclass as index, relid::regclass as table, 'DROP INDEX ' || indexrelid::regclass || ';' as drop_statement FROM pg_stat_user_indexes JOIN pg_index USING (indexrelid) WHERE idx_scan = 0 AND indisunique is false;
Nota sobre estadísticas del entorno de desarrollo
Confiar en las estadísticas de una base de datos de desarrollo local puede ser problemático. Idealmente, puede obtener las estadísticas anteriores de su máquina de trabajo o generarlas a partir de una copia de seguridad de trabajo restaurada. Por qué Los factores ambientales pueden cambiar el comportamiento del optimizador de consultas de Postgres. Dos ejemplos:
- cuando la máquina tiene menos memoria, PostgreSQL puede no ser capaz de realizar una Hash Join, de lo contrario puede y lo hará más rápido.
- Si no hay tantas filas en la tabla (como en la base de datos de desarrollo), PostgresSQL puede preferir realizar un análisis secuencial de la tabla en lugar de utilizar un índice disponible. Cuando los tamaños de tabla son pequeños, Seq Scan puede ser más rápido. (Nota: puedes correr
SET enable_seqscan = OFF
en una sesión para que el optimizador elija usar índices, incluso si los escaneos secuenciales pueden ser más rápidos. Esto es útil cuando se trabaja con bases de datos de desarrollo que no tienen muchos datos)
Comprender los planes de ejecución
Ahora que ha encontrado algunas consultas lentas, es hora de comenzar la diversión.
EXPLICAR
El comando
EXPLAIN ciertamente se requiere al configurar consultas. Él te dice lo que realmente está sucediendo. Para usarlo, simplemente agregue
EXPLICAR a la consulta y ejecútelo. PostgreSQL le mostrará el plan de ejecución que utilizó.
Cuando use EXPLAIN para la sintonización, recomiendo siempre usar la opción
ANALYZE (
EXPLAIN ANALYZE ), ya que le brinda resultados más precisos. La opción ANALIZAR realmente ejecuta la declaración (en lugar de solo evaluarla) y luego la explica.
Tomemos un chapuzón y comencemos a entender la salida de
EXPLAIN . Aquí hay un ejemplo:
Nudos
Lo primero que hay que entender es que cada bloque sangrado con el "->" anterior (junto con la línea superior) se llama nodo. Un nodo es una unidad lógica de trabajo (un "paso", si lo desea) con el costo y el tiempo de entrega asociados. El costo y el tiempo presentados en cada nodo son acumulativos y reúnen a todos los nodos secundarios. Esto significa que la línea superior (nodo) muestra el costo total y el tiempo real para todo el operador. Esto es importante porque puede profundizar fácilmente para determinar qué nodos son el cuello de botella.
Costo
cost=146.63..148.65
El primer número es el costo inicial (el costo de obtener el primer registro), y el segundo número es el costo de procesar todo el nodo (costo total de principio a fin).
De hecho, este es el costo que tendrán que cumplir las estimaciones de PostgreSQL para ejecutar la declaración. Este número no significa cuánto tiempo llevará completar la solicitud, aunque generalmente se necesita una relación directa para completarla. El costo es una combinación de 5 componentes de trabajo utilizados para evaluar el trabajo requerido: muestreo secuencial, muestreo inconsistente (aleatorio), procesamiento de filas, operador de procesamiento (función) y registro del índice de procesamiento. El costo es la entrada / salida y la carga del procesador, y es importante saber que el costo relativamente alto significa que PostgresSQL cree que tendrá que hacer más trabajo. El optimizador decide qué plan de ejecución usar según el costo. El optimizador prefiere costos más bajos.
Tiempo real
actual time=55.009..55.012
En milisegundos, el primer número es el tiempo de inicio (tiempo para recuperar el primer registro), y el segundo número es el tiempo requerido para procesar el nodo completo (tiempo total desde el inicio hasta el final). Fácil de entender, ¿verdad?
En el ejemplo anterior, tomó 55.009 ms obtener el primer registro y 55.012 ms para completar el nodo completo.
Obtenga más información sobre los planes de ejecución.
Hay algunos artículos realmente buenos para comprender los resultados de EXPLAIN. En lugar de tratar de volver a contarlos aquí, recomiendo tomarse el tiempo para comprenderlos realmente yendo a estos 2 maravillosos recursos:
Solicitar ajuste
Ahora que sabe qué operadores funcionan mal y puede ver sus planes de ejecución, es hora de comenzar a ajustar su consulta para mejorar el rendimiento. Aquí realiza cambios en sus consultas y / o agrega índices para intentar obtener un mejor plan de ejecución. Comience con cuellos de botella y vea si hay algún cambio que pueda hacer para reducir los costos y / o el tiempo de entrega.
Caché de datos y nota de costos
Al realizar cambios y evaluar los planes de implementación, para ver si habrá mejoras, es importante saber que las implementaciones futuras pueden depender del almacenamiento en caché de los datos que dan una idea de los mejores resultados. Si ejecuta la solicitud una vez, realice una corrección y ejecútela por segunda vez, lo más probable es que se ejecute mucho más rápido, incluso si el plan de ejecución no es más favorable. Esto se debe a que PostgreSQL podría almacenar en caché los datos utilizados en el primer inicio y puede usarlos en el segundo inicio. Por lo tanto, debe completar las consultas al menos 3 veces y promediar los resultados para comparar los costos.
Lo que aprendí puede ayudar a mejorar los planes de ejecución:
- Índices
- Excluya la exploración secuencial (exploración secuencial) agregando índices (si el tamaño de la tabla no es pequeño)
- Cuando utilice un índice de varias columnas, asegúrese de prestar atención al orden en el que define las columnas incluidas - Más información
- Pruebe los índices que son muy selectivos para los datos de uso frecuente. Esto hará que su uso sea más eficiente.
- Condición DONDE
- Evitar ME GUSTA
- Evite llamadas a funciones en la cláusula WHERE
- Evite grandes condiciones en ()
- ÚNETE
- Al unir tablas, intente usar una expresión de igualdad simple en la cláusula ON (es decir, a.id = b.person_id). Esto le permite utilizar métodos de unión más eficientes (es decir, Hash Join, no Nested Loop Join)
- Convierta subconsultas en sentencias JOIN cuando sea posible, ya que esto generalmente le permite al optimizador comprender el objetivo y posiblemente elegir el mejor plan.
- Utilice COMPUESTOS correctamente: ¿utiliza GROUP BY o DISTINCT solo porque obtiene resultados duplicados? Esto generalmente indica un uso incorrecto de JOIN y puede generar costos más altos.
- Si el plan de ejecución usa Hash Join, puede ser muy lento si las estimaciones del tamaño de la tabla son incorrectas. Por lo tanto, asegúrese de que las estadísticas de su tabla sean precisas revisando la estrategia de aspiración.
- Evite las subconsultas correlacionadas siempre que sea posible; pueden aumentar significativamente el costo de una solicitud
- Use EXISTS cuando verifique la existencia de cadenas en función de un criterio, ya que es similar a un cortocircuito (detiene el procesamiento cuando encuentra al menos una coincidencia)
- Recomendaciones generales