Gráficos, informes y análisis: todo esto está de alguna manera presente en el back-office de cualquier empresa, incluso muy pequeña. Cuando se llena en tablas regulares en Excel / Numbers / Libre, pero los datos aún no son muy grandes, las soluciones tradicionales para las necesidades internas de la empresa a menudo se crean utilizando bases de datos relacionales como PostgreSQL, MySQL o MariaDB.
Estas bases de datos son gratuitas, gracias a SQL pueden integrarse convenientemente con otros componentes del sistema, son populares y la mayoría de los desarrolladores y analistas pueden trabajar con ellas. Pueden digerir la carga (tráfico y volúmenes) lo suficientemente voluminosa como para aguantar con calma hasta que la empresa pueda permitirse soluciones más complejas (y costosas) para análisis e informes.
Posición inicial
Sin embargo, incluso en una tecnología que se ha estudiado repetidamente, siempre hay diferentes matices que pueden aumentar repentinamente las preocupaciones de los ingenieros. Además de la confiabilidad, el problema más mencionado con las bases de datos es su rendimiento. Obviamente, con un aumento en la cantidad de datos, la tasa de respuesta de DB disminuye, pero si esto ocurre de manera predecible y es consistente con el aumento de la carga, entonces esto no es tan malo. Siempre puede ver de antemano cuando la base de datos comienza a exigir atención y planificar una actualización o transición a una base de datos fundamentalmente diferente. Mucho peor si el rendimiento de la base de datos se degrada de manera impredecible.
El tema de mejorar el rendimiento de la base de datos es tan antiguo como el mundo y muy extenso, y en este artículo me gustaría centrarme en una sola dirección. Es decir, al evaluar la efectividad de los planes de consulta en una base de datos PostgreSQL, así como al cambiar esta eficiencia con el tiempo para hacer que el comportamiento del planificador de la base de datos sea más predecible.
A pesar del hecho de que muchas de las cosas que se discutirán son aplicables a todas las versiones recientes de esta base de datos, los ejemplos a continuación significan la versión 11.2, la última en este momento.
Antes de profundizar en los detalles, tiene sentido desviarnos y decir algunas palabras acerca de dónde pueden surgir los problemas de rendimiento en las bases de datos relacionales. ¿Con qué está ocupada exactamente la base de datos cuando se "ralentiza"? Falta de memoria (un gran número de accesos de disco o red), un procesador débil, todos estos son problemas obvios con soluciones claras, pero ¿qué más puede afectar la velocidad de ejecución de la consulta?
Refrescar recuerdos
Para que la base de datos responda a la consulta SQL, necesita construir un plan de consulta (en qué tablas y columnas ver qué índices se necesitan, qué elegir de allí, con qué comparar, cuánta memoria se requiere, etc.). Este plan se forma en forma de árbol, cuyos nodos son solo algunas operaciones típicas, con diferente complejidad computacional. Aquí hay algunos de ellos, por ejemplo (N es el número de líneas con las que realizar la operación):
| Operación | Que se hace | Costo |
|---|
| SELECCIONE ... DONDE ... operaciones de recuperación de datos |
| Exploración secuencial | Cargamos cada fila de la tabla y verificamos la condición. | O (N) |
Exploración de índice
(índice b-tree) | Los datos están directamente en el índice, por lo que buscamos por condición los elementos necesarios del índice y tomamos los datos desde allí. | O (log (N)), busca un elemento en un árbol ordenado. |
Exploración de índice
(índice hash) | Los datos están directamente en el índice, por lo que buscamos por condición los elementos necesarios del índice y tomamos los datos desde allí. | O (1), buscando un elemento en una tabla hash, excluyendo el costo de crear hashes |
| Análisis de montón de mapa de bits | Seleccionamos los números de las líneas necesarias por índice, luego cargamos solo las líneas necesarias y realizamos verificaciones adicionales con ellas. | Escaneo de índice + Escaneo de secuencia (M),
Donde M es el número de filas encontradas después de la exploración de índice. Se supone que M << N, es decir index es más útil que Seq Scan. |
| Operaciones de unión (UNIRSE, SELECCIONAR desde varias tablas) |
| Bucle anidado | Para cada fila de la tabla izquierda, busque una fila adecuada en la tabla derecha. | O (N2).
Pero si una de las tablas es mucho más pequeña que la otra (diccionario) y prácticamente no crece con el tiempo, entonces el costo real puede disminuir a O (N). |
| Hash unirse | Para cada fila de las tablas izquierda y derecha, consideramos el hash, que reduce el número de búsquedas de posibles opciones de conexión. | O (N), pero en el caso de una función hash muy ineficiente o un gran número de campos idénticos para la conexión, puede haber O (N 2 ) |
| Fusionar unirse | Por condición, clasificamos las tablas izquierda y derecha, después de lo cual combinamos las dos listas ordenadas | O (N * log (N))
Ordenando costos + revisando la lista. |
| Operaciones de agregación (GROUP BY, DISTINCT) |
| Grupo agregado | Ordenamos la tabla de acuerdo con la condición de agregación y luego en la lista ordenada agrupamos las filas adyacentes. | O (N * log (N)) |
| Agregado de hash | Consideramos el hash para la condición de agregación de cada fila. Para las filas con el mismo hash, realizamos la agregación. | O (N) |
Como puede ver, el costo de una consulta depende en gran medida de cómo se ubican los datos en las tablas y de cómo este orden corresponde a las operaciones hash utilizadas. Nested Loop, a pesar de su costo en O (N
2 ), puede ser más rentable que Hash Join o Merge Join cuando una de las tablas unidas degenera en una o varias filas.
Además de los recursos de la CPU, el costo también incluye el uso de memoria. Ambos son recursos limitados, por lo que el planificador de consultas debe encontrar un compromiso. Si dos tablas son matemáticamente más rentables para conectarse a través de Hash Join, pero simplemente no hay espacio para una tabla hash tan grande en la memoria, la base de datos puede verse obligada a usar Merge Join, por ejemplo. Un bucle anidado "lento" generalmente no requiere memoria adicional y está listo para producir resultados justo después del lanzamiento.
El costo relativo de estas operaciones se muestra más claramente en el gráfico. Estos no son números absolutos, solo una relación aproximada de diferentes operaciones.
La tabla de bucles anidados "comienza" a continuación, porque no requiere cálculos adicionales o asignación de memoria o copia de datos intermedios, pero tiene un costo de O (N
2 ). Merge Join y Hash Join tienen costos iniciales más altos, sin embargo, después de algunos valores de N, comienzan a vencer a Nested Loop a tiempo. El planificador intenta elegir el plan con el costo más bajo y en el cuadro anterior se adhiere a diferentes operaciones con diferentes N (flecha verde discontinua). Con el número de líneas hasta N1, es más rentable usar Nested Loop, de N1 a N2 es más rentable Fusionar combinación, luego después de N2 se vuelve más rentable Hash Join, sin embargo, Hash Join requiere memoria para crear tablas hash. Y al llegar a N3, esta memoria se vuelve insuficiente, lo que conduce al uso forzado de Merge Join.
Al elegir un plan, el planificador estima el costo de cada operación en el plan utilizando un conjunto de costos relativos de algunas operaciones "atómicas" en la base de datos. Como, por ejemplo, cálculos, comparaciones, cargar una página en la memoria, etc. Aquí hay una lista de algunos de estos parámetros de la configuración predeterminada, no hay muchos de ellos:
| Costo relativo constante | Valor por defecto |
|---|
| seq_page_cost | 1.0 |
| random_page_cost | 4.0 4.0 |
| cpu_tuple_cost | 0,01 |
| cpu_index_tuple_cost | 0.005 |
| cpu_operator_cost | 0.0025 |
| paralela_tupla_cost | 0.1 |
| paralelo_setup_cost | 1000,0 |
Es cierto que estas constantes son pocas, aún necesita saber la misma "N", es decir, exactamente cuántas filas de los resultados anteriores tendrán que procesarse en cada operación. El límite superior es obvio aquí: la base de datos "sabe" cuántos datos hay en cualquier tabla y siempre puede calcular "al máximo". Por ejemplo, si tiene dos tablas de 100 filas cada una, unirlas puede producir de 0 a 10,000 filas en la salida. En consecuencia, la siguiente operación de entrada puede tener hasta 10.000 líneas.
Pero si conoce al menos un poco sobre la naturaleza de los datos en las tablas, este número de filas se puede predecir con mayor precisión. Por ejemplo, para dos tablas de 100 filas del ejemplo anterior, si sabe de antemano que la unión no producirá 10 mil filas, pero las mismas 100, el costo estimado de la próxima operación se reduce considerablemente. En este caso, este plan podría ser más efectivo que otros.
Optimización lista para usar
Para que el planificador pueda predecir con mayor precisión el tamaño de los resultados intermedios, PostgreSQL utiliza la recopilación de estadísticas en tablas, que se acumula en pg_statistic, o en su versión más legible, en pg_stats. Se actualiza automáticamente cuando se inicia el vacío, o explícitamente con el comando ANALIZAR. Esta tabla almacena una variedad de información sobre qué datos y qué tipo de naturaleza hay en las tablas. En particular, histogramas de valores, porcentaje de campos vacíos y otra información. El planificador utiliza todo esto para predecir con mayor precisión la cantidad de datos para cada operación en el árbol del plan y, por lo tanto, calcular con mayor precisión el costo de las operaciones y el plan en su conjunto.
Tome por ejemplo la consulta:
SELECT t1.important_value FROM t1 WHERE t1.a > 100
Suponga que el histograma de los valores en la columna "t1.a" reveló que valores superiores a 100 se encuentran en aproximadamente el 1% de las filas de la tabla. Entonces podemos predecir que dicha muestra devolverá aproximadamente una centésima parte de todas las filas de la tabla "t1".
La base de datos le brinda la oportunidad de ver el costo previsto del plan a través del comando EXPLICAR y el tiempo real de su operación, utilizando EXPLAIN ANALYZE.
Parece que con las estadísticas automáticas todo debería estar bien ahora, pero puede haber dificultades. Hay un
buen artículo sobre esto
de Citus Data , con un ejemplo de la ineficiencia de las estadísticas automáticas y la recopilación de estadísticas adicionales usando CREATE STATISTICS (disponible con PG 10.0).
Entonces, para el planificador, hay dos fuentes de errores en el cálculo de costos:
- El costo relativo de las operaciones primitivas (seq_page_cost, cpu_operator_cost, etc.) de manera predeterminada puede ser muy diferente de la realidad (costo de la CPU 0.01, costo de carga de la página srq - 1 o 4 para carga de página aleatoria). Lejos del hecho de que 100 comparaciones equivalen a 1 carga de página.
- Error al predecir el número de filas en operaciones intermedias. El costo real de la operación en este caso puede ser muy diferente del pronóstico.
En consultas complejas, elaborar y pronosticar todos los planes posibles puede llevar mucho tiempo por sí solo. ¿De qué sirve devolver datos en 1 segundo si la base de datos solo estaba planeando una solicitud de un minuto? PostgreSQL tiene un optimizador Geqo para esta situación, es un programador que no crea todas las opciones posibles para los planes, pero comienza con algunas al azar y completa las mejores, prediciendo formas de reducir costos. Todo esto tampoco mejora la precisión del pronóstico, aunque acelera la búsqueda de al menos un plan más o menos óptimo.
Planes repentinos - competidores
Si todo va bien, su solicitud se cumple lo más rápido posible. A medida que aumenta la cantidad de datos, la velocidad de ejecución de la consulta en la base de datos aumenta gradualmente y, después de un tiempo, observándola, puede predecir aproximadamente cuándo será necesario aumentar la memoria o la cantidad de núcleos de CPU o expandir el clúster, etc.
Pero debemos tener en cuenta el hecho de que el plan óptimo tiene competidores con costos de ejecución cercanos, lo que no vemos. Y si la base de datos cambia repentinamente el plan de consulta a otro, esto es una sorpresa. Es bueno que la base de datos salte a un plan más eficiente. Y si no? Veamos la imagen, por ejemplo. Este es el costo previsto y el tiempo real de la implementación de dos planes (rojo y verde):
Aquí, un plan se muestra en verde y su "competidor" más cercano en rojo. La línea punteada muestra un gráfico de los costos proyectados, la línea continua es el tiempo real. La flecha gris discontinua muestra la selección del planificador.
Suponga que un buen viernes por la noche el número previsto de filas en alguna operación intermedia alcanza N1 y el pronóstico "rojo" comienza a superar al "verde". El planificador comienza a usarlo. El tiempo real de ejecución de la consulta salta inmediatamente (cambiando de una línea sólida verde a una roja), es decir, el programa de degradación de la base de datos toma la forma de un paso (o tal vez un "muro"). En la práctica, este "muro" puede aumentar el tiempo de ejecución de la consulta en un orden de magnitud o más.
Vale la pena señalar que esta situación es probablemente más típica para el back office y el análisis que para el front end, ya que este último generalmente se adapta a consultas más simultáneas y, por lo tanto, utiliza consultas más simples en la base de datos, donde el error en los pronósticos del plan es menor. Si se trata de una base de datos para informes o análisis, las consultas pueden ser arbitrariamente complejas.
¿Cómo vivir con eso?
Surge la pregunta: ¿era posible de alguna manera prever tales planes invisibles "bajo el agua"? Después de todo, el problema no es que no sean óptimos, sino que el cambio a otro plan puede ocurrir de manera impredecible y, según la ley de la mezquindad, en el momento más desafortunado para esto.
Desafortunadamente, no puede verlos directamente, pero puede buscar planes alternativos cambiando los pesos reales por los que se seleccionan. El significado de este enfoque es eliminar de la vista el plan actual, que el planificador considera óptimo, para que uno de sus competidores más cercanos se vuelva óptimo y, por lo tanto, pueda ser visto a través del equipo EXPLAIN. Revisando periódicamente los cambios en los costos en tales "competidores" y en el plan principal, puede evaluar la probabilidad de que la base de datos pronto "salte" a otro plan.
Además de recopilar datos sobre pronósticos de planes alternativos, puede ejecutarlos y medir su rendimiento, lo que también da una idea del "bienestar" interno de la base de datos.
Veamos qué herramientas tenemos para tales experimentos.
Primero, puede "prohibir" explícitamente operaciones específicas utilizando variables de sesión. Convenientemente, no es necesario cambiarlos en la configuración y volver a cargar la base de datos, su valor cambia solo en la sesión abierta actual y no afecta a otras sesiones, por lo que puede experimentar directamente con datos reales. Aquí hay una lista de ellos con valores predeterminados. Casi todas las operaciones están incluidas:
| Operaciones utilizadas | Valor por defecto |
|---|
enable_bitmapscan
enable_hashagg
enable_hashjoin
enable_indexscan
enable_indexonlyscan
enable_material
enable_mergejoin
enable_nestloop
enable_parallel_append
enable_seqscan
enable_sort
enable_tidscan
enable_parallel_hash
enable_partition_pruning | en |
enable_partitionwise_join
enable_partitionwise_aggregate | fuera |
Al prohibir o permitir ciertas operaciones, forzamos al planificador a seleccionar otros planes que podamos ver con el mismo comando EXPLAIN. De hecho, la "prohibición" de las operaciones no prohíbe su uso, sino que simplemente aumenta enormemente su costo. En PostgreSQL, cada operación "prohibida" acumula automáticamente un costo igual a 10 mil millones de unidades convencionales. Además, en EXPLICAR, el peso total del plan puede resultar prohibitivamente alto, pero en el contexto de estas decenas de miles de millones, el peso de las operaciones restantes es claramente visible, ya que generalmente se ajusta a pedidos más pequeños.
De particular interés son dos de las siguientes operaciones:
- Hash Join. Su complejidad es O (N), pero con un error con un pronóstico en la cantidad del resultado, no puede caber en la memoria y tendrá que hacer Merge Join, con un costo de O (N * log (N)).
- Bucle anidado. Su complejidad es O (N 2 ), por lo tanto, el error en el pronóstico del tamaño afecta de manera cuadrática la velocidad de dicha conexión.
Por ejemplo, tomemos algunos números reales de las consultas, cuya optimización estábamos involucrados en nuestra empresa.
Plan 1. Con todas las operaciones permitidas, el costo total del plan más óptimo fue de 274962.09 unidades.
Plan 2. Con el bucle anidado "prohibido", el costo aumentó a 40000534153.85. A pesar de la prohibición, estos 40 mil millones que representan la mayor parte del costo son 4 veces el Nested Loop utilizado. Y los 534153.85 restantes: este es precisamente el pronóstico del costo de todas las demás operaciones del plan. Como vemos, es aproximadamente 2 veces más alto que el costo del plan óptimo, es decir, está lo suficientemente cerca de él.
Plan 3. Con la Hash Join "prohibida", el costo fue de 383253.77. El plan se hizo realmente sin utilizar la operación Hash Join, ya que no vemos miles de millones. Sin embargo, su costo es 30% más alto que el óptimo, que también está muy cerca.
En realidad, los tiempos de ejecución de la consulta fueron los siguientes:
Plan 1 (todas las operaciones permitidas) completado en ~ 9 minutos.
El plan 2 (con el bucle anidado "prohibido") se completa en 1,5 segundos.
El plan 3 (con una combinación de hash "prohibida") se completó en ~ 5 minutos.
La razón, como puede ver, es la predicción errónea del costo de Nested Loop. De hecho, cuando se compara EXPLAIN con EXPLAIN ANALYZE, se detecta un error con la definición de ese N desafortunado en la operación intermedia. En lugar de una sola fila predicha, el bucle anidado encontró varios miles de filas, lo que provocó que el tiempo de ejecución de la consulta aumentara en un par de órdenes de magnitud.
Los ahorros con la combinación de hash "prohibida" se asocian con el reemplazo de hashing con clasificación y combinación de combinación, que funcionó más rápido en este caso que la combinación de hash. Tenga en cuenta que este plan 2 en realidad es casi dos veces más rápido que el plan "óptimo" 1. Aunque se predijo que será más lento.
En la práctica, si su solicitud de repente (después de una actualización de la base de datos o solo) comenzó a ejecutarse mucho más tiempo que antes, primero intente negar Hash Join o Nested Loop y vea cómo esto afecta la velocidad de la consulta. En un caso exitoso, podrá al menos prohibir un nuevo plan no óptimo y volver al anterior rápido.
Para hacer esto, no necesita cambiar los archivos de configuración de PostgreSQL con un reinicio de la base de datos, es bastante simple en cualquier consola cambiar el valor de la variable deseada para una sesión abierta desde la base de datos. Las sesiones restantes no se verán afectadas, la configuración cambiará solo para su sesión actual. Por ejemplo, así:
SET enable_hashjoin='on'; SET enable_nestloop='off'; SELECT … FROM … ( )
La segunda forma de influir en la elección del plan es cambiar los pesos de las operaciones de bajo nivel. No existe una receta universal aquí, pero, por ejemplo, si tiene una base de datos con un caché "calentado" y todos los datos se almacenan en la memoria, es probable que el costo de la carga secuencial de páginas no difiera del costo de cargar una página aleatoria. Mientras que en la configuración predeterminada, aleatorio es 4 veces más costoso que secuencial.
O, otro ejemplo, el costo condicional de ejecutar el procesamiento paralelo es 1000 por defecto, mientras que el costo de cargar una página es 1.0. Tiene sentido comenzar cambiando solo uno de los parámetros a la vez para determinar si afecta la elección del plan. Las formas más fáciles son comenzar configurando el parámetro en 0 o en algún valor alto (1 millón).
Sin embargo, tenga en cuenta que al mejorar el rendimiento en una solicitud, puede degradarlo en otra. En general, hay un amplio campo para los experimentos. Es mejor intentar cambiarlos uno a la vez, uno a la vez.
Opciones de tratamiento alternativas
Una historia sobre un planificador estaría incompleta sin mencionar al menos dos extensiones de PostgreSQL.
El primero es
SR_PLAN , para guardar el plan calculado y forzar su uso posterior. Esto ayuda a hacer que el comportamiento de la base de datos sea más predecible en términos de opciones de planes.
El segundo es el
Adaptive Query Optimizer , que implementa retroalimentación al planificador desde la ejecución en tiempo real de la consulta, es decir, el planificador mide los resultados reales de la consulta ejecutada y ajusta sus planes en el futuro con esto en mente. Por lo tanto, la base de datos se "autoajusta" para datos y consultas específicos.
¿Qué más hace la base de datos cuando se ralentiza?
Ahora que hemos ordenado más o menos la planificación de la consulta, veamos qué más se puede mejorar tanto en la base de datos como en las aplicaciones que la utilizan para obtener el máximo rendimiento.
Supongamos que el plan de consulta ya es óptimo. Si excluimos los problemas más obvios (poca memoria o un disco / red lento), entonces todavía hay costos para calcular los hashes. Probablemente haya grandes oportunidades para futuras mejoras en PostgreSQL (usando la GPU o incluso las instrucciones SSE2 / SSE3 / AVX de la CPU), pero hasta ahora esto no se ha hecho y los cálculos hash apenas utilizan las capacidades de hardware del hardware. Puedes ayudar un poco en esta base de datos.
Si observa, por defecto, los índices en PostgreSQL se crean como b-tree. Su utilidad es que son bastante versátiles. Dicho índice puede usarse tanto con condiciones de igualdad como con condiciones de comparación (más o menos). Encontrar un artículo en dicho índice es un costo logarítmico. Pero si su consulta contiene solo una condición de igualdad, los índices también se pueden crear como un índice hash, cuyo costo es constante.
Además, aún puede intentar modificar la solicitud para utilizar su ejecución paralela. Para comprender exactamente cómo reescribirlo, es mejor familiarizarse con la lista de casos en los que el planificador prohíbe automáticamente el paralelismo y evitar tales situaciones.
El manual sobre este tema describe brevemente todas las situaciones, por lo que no tiene sentido repetirlas aquí.
¿Qué hacer si la solicitud aún no es buena para hacer paralelo? Es muy triste ver cómo en su poderosa base de datos multinúcleo, donde usted es el único cliente, un núcleo está 100% ocupado y todos los demás núcleos solo lo miran. En este caso, debe ayudar a la base de datos desde el lado de la aplicación. Dado que a cada sesión se le asigna su propio núcleo, puede abrir varias de ellas y dividir la consulta general en partes, haciendo selecciones más cortas y rápidas, combinándolas en un resultado común que ya está en la aplicación. Esto ocupará los recursos de CPU máximos disponibles en la base de datos PostgreSQL.
En conclusión, me gustaría señalar que las opciones de diagnóstico y optimización anteriores son solo la punta del iceberg, sin embargo, son bastante fáciles de usar y pueden ayudar a identificar rápidamente el problema directamente en los datos operativos sin correr el riesgo de estropear la configuración o interrumpir el funcionamiento de otras aplicaciones.
Consultas exitosas, con planes precisos y cortos.