Requêtes SQL MariaDB lentes dans l'état Statistiques

Aperçu

L'une des tâches d'une base de données lors de l'exécution d'une requête consiste à définir la meilleure façon d'exécuter la requête elle-même. Alors que dans la plupart des cas, MariaDB (et MySQL) fait un excellent travail d'optimisation, pour certaines requêtes complexes avec un nombre élevé de jointures, il peut passer trop de temps par défaut à essayer d'optimiser la requête au lieu de l'exécuter réellement.

Par exemple, sur une requête basée sur WordPress avec 16 instructions JOIN, nous avons constaté un temps d'exécution de près de 4 minutes avec seulement 5000 lignes. L'exécution d'un EXPLAIN sur la requête (qui n'exécute pas réellement la requête) donne à peu près le même résultat, ce qui signifie le retard est la sur-opimisation pas les données ou la requête elle-même.

Cela est dû à optimizer_search_depth paramètre, dont la valeur par défaut est 62. En réduisant ce nombre à une profondeur de 5, le temps EXPLAIN a été réduit à 0,052 seconde et la requête elle-même réduite à moins de 6 secondes.

Pour cette requête particulière, cela signifiait que le résultat était presque 3 900 % plus lent avec les valeurs par défaut du serveur !

Instructions

1. À l'aide de votre shell MariaDB, exécutez la requête, puis visualisez ce qu'est MariaDB pendant la requête :
   show full processlist;
2. Si vous voyez une requête avec l'état "Statistiques" pendant une longue période, cette optimisation peut être applicable. Par example:
   
3. Confirmez votre optimizer_search_depth actuel est réglé sur:
   show variables like "optimizer_search_depth";
   Par exemple, vous pouvez donc voir :
   +------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| optimizer_search_depth | 62 |
+------------------------+-------+
4. Cela indique que votre serveur exécute toujours les paramètres par défaut du système.
5. Testez la configuration du serveur pour optimiser automatiquement la profondeur en définissant l'optimizer_search_depth sur zéro :
   SET SESSION optimizer_search_depth = 0;
6. Réexécutez la requête SQL lente pour confirmer que le problème a été résolu.
7. Si le paramètre mis à jour a fonctionné, définissez-le de manière permanente en modifiant /etc/my.conf et en le définissant explicitement dans [msqld] section:
   optimizer_search_depth=0
8. Redémarrez MariaDB pour postuler :
   systemctl restart mariadb

Astuce

Si vous savez que votre structure de données est très contrôlée, vous pouvez essayer de définir explicitement la optimizer_search_depth à une valeur spécifique (par exemple 5) pour réduire davantage . Cependant, un réglage explicite n'est pas nécessairement plus rapide dans tous les cas, alors assurez-vous de bien tester.

Si vous êtes un client Conetix avec un serveur privé virtuel ayant ce problème, nous pouvons vous aider à confirmer qu'il s'agit de la cause et le résoudre pour vous sans frais supplémentaires. Veuillez contacter notre équipe d'assistance pour obtenir de l'aide.

Lectures complémentaires

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

https://mariadb.com/resources/blog/setting-optimizer-search-depth-in-mysql/