Consultas lentas de MariaDB SQL en estado de estadísticas

Resumen

Uno de los trabajos de una base de datos al ejecutar una consulta es trazar la mejor manera de ejecutar la consulta en sí. Mientras que en la mayoría de los casos MariaDB (y MySQL) hace un gran trabajo de optimización, para algunas consultas complejas con una gran cantidad de uniones puede pasar demasiado tiempo tratando de optimizar la consulta en lugar de ejecutarla.

Por ejemplo, en una consulta basada en WordPress con 16 sentencias JOIN, vimos un tiempo de ejecución de casi 4 minutos. con solo 5000 filas. Ejecutar EXPLAIN en la consulta (que en realidad no ejecuta la consulta) da más o menos lo mismo, lo que significa que el retraso es la sobreoptimización no los datos o la consulta en sí.

Esto se debe a la optimizer_search_ depth configuración predeterminada, que por defecto es 62. Al reducir este número a una profundidad de 5, el tiempo EXPLAIN se redujo a 0,052 segundos y la consulta en sí se redujo a menos de 6 segundos.

Para esta consulta en particular, significó que el resultado fue casi 3900% más lento con los valores predeterminados del servidor!

Instrucciones

1. Usando su shell de MariaDB, ejecute la consulta y luego vea qué es MariaDB durante la consulta:
   show full processlist;
2. Si ve una consulta con el estado "Estadísticas" durante mucho tiempo, esta optimización podría ser aplicable. Por ejemplo:
   
3. Confirme cuál es su optimizer_search_ depth actual se establece en:
   show variables like "optimizer_search_depth";
   Por ejemplo, puede ver:
   +------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| optimizer_search_depth | 62 |
+------------------------+-------+
4. Esto indica que su servidor aún ejecuta los valores predeterminados del sistema.
5. Prueba a configurar el servidor para optimizar automáticamente la profundidad configurando Optimizer_Search_Profundidad en cero:
   SET SESSION optimizer_search_depth = 0;
6. Vuelva a ejecutar la consulta SQL lenta para confirmar que se resolvió el problema.
7. Si la configuración actualizada ha funcionado, configúrela de forma permanente editando /etc/my.conf y configurando explícitamente en [msqld] sección:
   optimizer_search_depth=0
8. Reinicie MariaDB para aplicar:
   systemctl restart mariadb

Consejo

Si sabe que su estructura de datos está muy controlada, puede experimentar configurando explícitamente optimizer_search_ depth a un valor específico (por ejemplo, 5) para reducir aún más. Sin embargo, una configuración explícita no es necesariamente más rápida en todos los casos, así que asegúrese de realizar una prueba exhaustiva.

Si es un cliente de Conetix con un servidor privado virtual que tiene este problema, podemos ayudarlo a confirmar que esta es la causa y solucionarlo sin costo adicional. Póngase en contacto con nuestro equipo de soporte para obtener más ayuda.

Lecturas adicionales

https://www.percona.com/blog/2012/04/20/joining-many-tables-in-mysql-optimizer_search_ depth/

https://mariadb.com/resources/blog/optimizador-de-configuración-profundidad-de-búsqueda-en-mysql/