¿Cuál es el problema con la optimización de la base de datos?

No sabía que había un problema con la optimización de la base de datos.

Me imagino que podría haber un problema si uno no sabe cómo hacerlo, pero eso es cierto para la mayoría de las cosas. Además, como la mayoría de las cosas, es fácil si sabes cómo. También debería ser obvio que cuanto más lo haces, mejor lo haces. Después de que uno sepa cómo optimizar bien para una plataforma de base de datos específica, será más fácil aprender un segundo. Nota: No dije “porque uno sabe cómo optimizar en una plataforma específica, también sabrá cómo optimizar en otra plataforma”.

La optimización de la base de datos generalmente incluye el conocimiento de las opciones específicas de configuración de la plataforma, el código de la base de datos, el código de la aplicación, la configuración del hardware, el modelado de datos, la arquitectura y las habilidades suficientes para hacer valer las pruebas, medir el rendimiento y hacer análisis.

Debido al conjunto diverso y avanzado de habilidades, uno podría decir que hay una curva de aprendizaje más pronunciada para la optimización de la base de datos que el desarrollo web.

Un ejemplo:

Me pidieron que optimizara una base de datos de oráculo de varios terabytes que comenzó a romperse durante los picos de> 100k transacciones por segundo.

El esquema fue creado originalmente por un desarrollador de Java que modeló la base de datos a partir de sus objetos Java. Esta suele ser una idea terrible y no fue la excepción.

Originalmente realizó “ok” a 10k transacciones por segundo cuando se construyó por primera vez. A medida que crecía, seguían comprando más hardware (memoria y núcleos). Con los años, agregaron más funciones de una manera que comprometió aún más el modelo de datos. Agregar núcleos a Oracle es una forma costosa y desafortunadamente muy común de resolver problemas de rendimiento.

A los consultores de Oracle generalmente no les molesta el mal modelado porque les gusta vender licencias. Sospecho lo rápido que están de acuerdo con sus clientes en que los cambios en los modelos de datos y el código de la aplicación están fuera de la mesa como opciones.

Los propietarios de esta aplicación querían que se agotaran todas las vías antes de permitir un cambio en el modelo de datos. Esa fue una solicitud justa porque ese tipo de cambio afectaría la capa MVC de la aplicación y querían evitar cambios innecesarios. Les aseguré que la mayoría de los cambios en la aplicación podrían ofuscarse con vistas, pero no todos. La aplicación ganaba mucho dinero por hora y estos ejecutivos eran muy reacios a cualquier cambio que no pudiera vincularse a las nuevas características que consideraban que generaban ingresos.

Con un pico de> 100 tps con estadísticas de hardware estables como meta, cambié cuidadosamente todo menos el modelo. Hice un buen progreso pero no pude llegar a> 100 tps sin el cambio de modelo. Después de convencerlos de que todo estaba agotado, finalmente se me permitió cambiar el modelo de datos para que coincidiera con los datos. Cuando lo hice, pude volver a escribir el código de la base de datos crítica y no solo cumplí el objetivo, sino que pude reducir la cantidad de núcleos. Por supuesto, esto redujo sus costos de licencia y TCO.

En MySQL hay algunas variables de sesión que pueden usarse para acelerar el análisis de consultas patológicas (muchas uniones, muchos índices).

8.8.5.1 Evaluación del plan de consulta de control
La tarea del optimizador de consultas es encontrar un plan óptimo para ejecutar una consulta SQL. Debido a que la diferencia en el rendimiento entre los planes “buenos” y “malos” puede ser de un orden de magnitud (es decir, segundos versus horas o incluso días), la mayoría de los optimizadores de consultas, incluido el de MySQL, realizan una búsqueda más o menos exhaustiva para un óptimo planificar entre todos los posibles planes de evaluación de consultas. Para las consultas de combinación, el número de posibles planes investigados por el optimizador MySQL crece exponencialmente con el número de tablas referenciadas en una consulta. Para números pequeños de tablas (típicamente menos de 7 a 10) esto no es un problema. Sin embargo, cuando se envían consultas más grandes, el tiempo dedicado a la optimización de consultas puede convertirse fácilmente en el principal cuello de botella en el rendimiento del servidor.

Un método más flexible para la optimización de consultas permite al usuario controlar cuán exhaustivo es el optimizador en su búsqueda de un plan de evaluación de consultas óptimo. La idea general es que cuantos menos planes investigue el optimizador, menos tiempo pasará compilando una consulta. Por otro lado, debido a que el optimizador omite algunos planes, puede fallar encontrar un plan óptimo.

El comportamiento del optimizador con respecto al número de planes que evalúa se puede controlar utilizando dos variables del sistema:

La variable optimizer_prune_level le dice al optimizador que omita ciertos planes basados ​​en estimaciones del número de filas a las que se accede para cada tabla. Nuestra experiencia muestra que este tipo de “conjetura educada” rara vez pierde los planes óptimos y puede reducir drásticamente los tiempos de compilación de consultas. Es por eso que esta opción está activada (optimizer_prune_level = 1) de forma predeterminada. Sin embargo, si cree que el optimizador perdió un mejor plan de consulta, esta opción se puede desactivar (optimizer_prune_level = 0) con el riesgo de que la compilación de la consulta pueda tardar mucho más. Tenga en cuenta que, incluso con el uso de esta heurística, el optimizador aún explora un número aproximadamente exponencial de planes.

La variable optimizer_search_depth indica qué tan lejos en el “futuro” de cada plan incompleto debe buscar el optimizador para evaluar si debe expandirse aún más. Los valores más pequeños de optimizer_search_depth pueden dar como resultado tiempos de compilación de consultas de órdenes de magnitud más pequeños. Por ejemplo, las consultas con 12, 13 o más tablas pueden requerir fácilmente horas e incluso días para compilarse si optimizer_search_depth está cerca del número de tablas en la consulta. Al mismo tiempo, si se compila con optimizer_search_depth igual a 3 o 4, el optimizador puede compilar en menos de un minuto para la misma consulta. Si no está seguro de cuál es un valor razonable para optimizer_search_depth, esta variable se puede establecer en 0 para indicarle al optimizador que determine el valor automáticamente.

El problema con la optimización de la base de datos es que las personas no entienden lo que significa “optimización”.

La optimización significa cambiar la configuración o el esquema para que se ejecute más rápido o use menos recursos mientras sirve una carga de trabajo específica y, en consecuencia, otras cargas de trabajo se sirven de manera menos óptima.

La gente continuamente pregunta cómo “optimizar para cualquier carga de trabajo”, pero esto es un oxímoron. Debe saber cuál es su carga de trabajo antes de poder optimizar.

Es difícil, no obvio, requiere una comprensión profunda de muchos componentes de la infraestructura (no solo de la base de datos).

En muchos casos, la optimización de la base de datos no se trata solo de la base de datos, sino más bien de la combinación de problemas en múltiples capas, como base de datos, sistema operativo, almacenamiento, aplicación, capas de red, errores de la base de datos (como errores del optimizador, etc.), malas acciones humanas, etc.

Para resolver los problemas de optimización de la base de datos de producción en la vida real, uno debe ser una persona muy versátil con profundas habilidades en muchas áreas.

Con frecuencia se intenta demasiado pronto y sin una consideración adecuada de las compensaciones. También puede cambiar las reglas comerciales que está tratando de mejorar; generalmente se hace en nombre de “desnormalizar para el rendimiento”.