Pour la plupart des développeurs d'applications, la base de données est un autel de dieux démons qu'il vaut mieux laisser inapproché. Mais il ne doit pas en être ainsi!
Toutes choses étant égales par ailleurs, le niveau de confort d'un développeur avec la base de données sous-jacente définit son niveau d'ancienneté. Peu de base de données et peu d'expérience en codage = développeur junior; peu de base de données et bonne expérience de codage = développeur de niveau intermédiaire; bonne base de données et bonne expérience de codage = développeur senior.
C'est une dure réalité que même les développeurs avec 6-8 ans à leur actif ont du mal à expliquer les subtilités de l'optimiseur de requêtes et préfèrent regarder vers le paradis lorsqu'ils sont interrogés sur réglage de la base de données.
Pourquoi ?
Étonnamment, la raison n'est pas la paresse (bien que dans une partie elle le soit).
Le fait est que les bases de données sont une force qui leur est propre. Même traditionnellement, lorsqu'il n'y avait que les types relationnels de bases de données à gérer, les maîtriser était un miracle et un cheminement de carrière en soi; ces jours-ci, nous avons tant de types de bases de données qu'il est tout simplement impossible de s'attendre à ce qu'une seule âme mortelle maîtrise tout.
Cela dit, il y a de fortes chances que vous soyez toujours satisfait des bases de données relationnelles ou que vous fassiez partie d'une équipe qui a un produit fonctionnant de manière satisfaisante sur une base de données relationnelle pendant très, très longtemps. Et dans neuf cas sur dix, vous êtes sur MySQL (ou MariaDB). Dans ces cas, plonger un peu plus profondément sous le capot offre des avantages énormes pour améliorer les performances des applications et vaut la peine d'être appris.
Curieuse? Plongeons-nous!
Pas curieux? Eh bien, plongez-vous quand même, car votre carrière en dépend! 😛
Optimize the MySQL query cache
Presque toute l'optimisation dans le domaine des ordinateurs se résume à la mise en cache. D'un côté, le processeur maintient plusieurs niveaux de cache pour accélérer ses calculs, et de l'autre, les applications Web utilisent de manière agressive des solutions de mise en cache comme Redis pour serveur des résultats précalculés aux utilisateurs plutôt que de frapper la base de données à chaque fois.
Mais bon, même la pauvre base de données MySQL a son propre cache de requêtes! Autrement dit, chaque fois que vous interrogez quelque chose et que les données sont toujours obsolètes, MySQL servira ces résultats en cache plutôt que d'exécuter à nouveau la requête, ce qui rendra l'application ridiculement plus rapide.
Vous pouvez vérifier si le cache de requêtes est disponible (note, disponible, non activé) dans votre base de données en exécutant cette requête dans la console de base de données:
MariaDB [(none)]> SHOW VARIABLES LIKE 'have_query_cache';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| have_query_cache | YES |
+------------------+-------+
Ainsi, vous pouvez voir que j'exécute MariaDB et que la mise en cache des requêtes est disponible pour être activée. Il est extrêmement peu probable que vous le désactiviez si vous utilisez une installation MySQL standard.
Voyons maintenant si le cache de requêtes est réellement activé:
MariaDB [(none)]> SHOW VARIABLES LIKE 'query_cache_type';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| query_cache_type | ON |
+------------------+-------+Oui. Mais si vous ne le faites pas, vous pouvez l'activer en disant:
MariaDB [(none)]> SET GLOBAL query_cache_type = ON;Fait intéressant, cette variable accepte également une troisième valeur qui indique «à la demande», ce qui signifie que MySQL ne mettra en cache que les requêtes auxquelles nous lui demandons, mais nous n'entrerons pas dans cela ici.
Avec cela, vous avez activé la mise en cache des requêtes et avez fait le premier pas vers une configuration MySQL plus robuste! Je dis la première étape car si l'activation est une amélioration majeure, nous devons ajuster la mise en cache des requêtes en fonction de notre configuration. Alors apprenons à faire ça.
L'autre variable intéressante ici est query_cache_size, dont la fonction est explicite:
MariaDB [(none)]> SHOW VARIABLES LIKE 'query_cache_size';
+------------------+----------+
| Variable_name | Value |
+------------------+----------+
| query_cache_size | 16777216 |
+------------------+----------+Donc, j'ai un cache de requête d'environ 16 Mo de taille. Notez que même si la mise en cache des requêtes est activée, mais que cette taille est égale à zéro, la mise en cache est effectivement désactivée. C'est pourquoi la vérification d'une seule variable ne suffit pas. Maintenant, vous devez définir une taille de cache de requête, mais de combien devrait-elle être? Tout d'abord, veuillez noter que la fonction de mise en cache des requêtes aura elle-même besoin de 4 Ko pour stocker ses métadonnées, donc tout ce que vous sélectionnez doit être au-dessus.
Supposons que vous définissiez la taille du cache de requête sur 500 Ko:
MariaDB [(none)]> SET GLOBAL query_cache_size = 500000;
Est-ce que cela suffit? Eh bien, non, car les performances du moteur de requête dépendent de plusieurs choses:
- Tout d'abord, le
query_cache_sizeLa variable doit être suffisamment grande pour contenir le résultat de vos requêtes. S'il est trop petit, rien ne sera mis en cache. - Deuxièmement, si
query_cache_sizeest réglé sur un nombre trop élevé, il y aura deux types de problèmes: 1) Le moteur devra effectuer un travail supplémentaire pour stocker et localiser les résultats de la requête dans cette zone mémoire massive. 2) Si la plupart des requêtes aboutissent à des tailles beaucoup plus petites, le cache sera fragmenté et les avantages de l'utilisation d'un cache seront perdus.
Comment savez-vous que le cache est fragmenté? Vérifiez le nombre total de blocs dans le cache comme ceci:
MariaDB [(none)]> show status like 'Qcache_total_blocks';
+---------------------+-------+
| Variable_name | Value |
+---------------------+-------+
| Qcache_total_blocks | 33 |
+---------------------+-------+Si le nombre est très élevé, le cache est fragmenté et doit être vidé.
Donc, pour éviter ces problèmes, assurez-vous que la taille de query_cache_size est choisi avec sagesse. Si vous vous sentez frustré de ne pas vous avoir laissé un chiffre concret ici, je crains que ce ne soit la façon dont les choses se passent une fois que vous avez dépassé le stade du développement et que vous entrez dans l'ingénierie. Vous devez examiner l'application que vous exécutez et voir quelles sont les tailles de requête pour les résultats de requête importants, puis définir ce nombre. Et même dans ce cas, vous pourriez finir par faire une erreur. 🙂
Threading, thread pools, waiting, and timeouts
C'est probablement la partie la plus intéressante du fonctionnement de MySQL et bien faire les choses signifie rendre votre application plusieurs fois plus rapide!
Threading
MySQL est un serveur multi-thread. Cela signifie que chaque fois qu'il y a une nouvelle connexion au serveur MySQL, il ouvre un nouveau thread avec les données de connexion et transmet un descripteur au client (lisez cet article si vous vous demandez qu'est-ce qu'un fil). Le client envoie ensuite toutes les requêtes sur ce thread et reçoit les résultats. Cela nous amène à poser une question naturelle: combien de threads MySQL peut-il tourner? La réponse se trouve dans la section suivante.
Pool de threads
Aucun programme dans un système informatique ne peut ouvrir autant de threads qu'il le souhaite. La raison est double: 1) Les threads coûtent de la mémoire (RAM), et le système d'exploitation ne vous permettra tout simplement pas de devenir fou et de tout dévorer. 2) Gérer, par exemple, un million de threads est une tâche énorme en soi, et si le serveur MySQL pouvait créer autant de threads, il mourrait en essayant de gérer la surcharge.
Pour éviter ces problèmes, MySQL est livré avec un pool de threads - un nombre fixe de threads qui font partie d'un pool au début. Les nouvelles demandes de connexion amènent MySQL à récupérer l'un de ces threads et à renvoyer les données de connexion, et si tous les threads sont épuisés, les nouvelles connexions sont naturellement refusées. Voyons la taille du pool de threads:
ariaDB [(none)]> show variables like 'thread_pool_size';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| thread_pool_size | 4 |
+------------------+-------+Donc, ma machine permet un maximum de quatre connexions en même temps. Il est intéressant de noter que le numéro 4 vient du fait que j'ai un processeur à quatre cœurs, ce qui signifie que mon ordinateur ne peut exécuter que 4 tâches parallèles à la fois (je parle ici de tâches vraiment parallèles, pas de tâches simultanées). Idéalement, c'est la limite qui devrait conduire la valeur de thread_pool_size, mais sur des machines plus robustes, il en profite jusqu'à un certain point. Si vous ne voulez pas que toutes les nouvelles connexions attendent et que vous êtes d'accord pour prendre un certain impact sur les performances (encore une fois, c'est un domaine que vous pouvez juger le mieux en fonction des performances de votre application sous charge), le faire passer à 8 pourrait être une bonne idée.
Cependant, le définir au-delà de 16 est une idée terrible, sauf si vous avez une machine à 32 cœurs, car les performances se dégradent considérablement. Le trou de lapin des pools de threads dans MySQL est profond, mais si vous êtes intéressé, voici une discussion plus détaillée.
Waiting and timeouts
Une fois qu'un thread a été créé et attaché à un client, ce serait un gaspillage de ressources si le client n'envoyait aucune requête pendant les prochaines secondes (ou minutes). En conséquence, MySQL met fin à une connexion après une période d'inactivité. Ceci est contrôlé par le wait_timeout variable:
MariaDB [(none)]> show variables like 'wait%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout | 28800 |
+---------------+-------+La valeur résultante est en secondes. Alors oui, par défaut, MySQL est configuré pour attendre plus de 8 heures avant de couper le cordon! Cela peut être bon si vous avez des requêtes de longue durée et que vous voulez réellement les attendre (mais même dans ce cas, huit heures est absurde!) Mais terrible dans la plupart des cas. Lorsqu'une requête est exécutée, cette valeur est définie sur 0 (ce qui signifie pour toujours), mais généralement, elle doit être définie sur une valeur très faible (5 secondes, par exemple, ou peut-être même moins) pour libérer la connexion pour d'autres processus.
Tuning temporary tables
Commençons par ce que sont les tables temporaires dans MySQL.
Supposons que nous ayons un MySQL qui ressemble structurellement à ceci: TABLE A UNION (TABLE B INNER JOIN C). Autrement dit, nous sommes intéressés à joindre les tables B et C, puis à effectuer une union du résultat avec la table A. Maintenant, MySQL commencerait par joindre les tables B et C, mais avant de pouvoir effectuer une union, il a besoin pour stocker ces données quelque part. C'est là que les tables temporaires entrent en jeu - MySQL les utilise pour stocker temporairement des données à des étapes intermédiaires dans des requêtes complexes, et une fois la requête terminée, cette table temporaire est supprimée.
Maintenant, la question est: pourquoi devrions-nous nous préoccuper de tout cela?
Simplement parce que la table temporaire, juste un résultat de requête, est des données qui sont utilisées par MySQL dans le calcul, et la vitesse de son accès (entre autres limitations) déterminera la vitesse à laquelle la requête sera exécutée. Par exemple, stocker la table temporaire dans la RAM sera plusieurs fois plus rapide que la stocker sur le disque.
Il existe deux variables qui contrôlent ce comportement:
MariaDB [(none)]> show variables like 'MariaDB [(none)]> show variables like 'tmp_table_size';
+----------------+----------+
| Variable_name | Value |
+----------------+----------+
| tmp_table_size | 16777216 |
+----------------+----------+
';
+---------------------+----------+
| Variable_name | Value |
+---------------------+----------+
| max_heap_table_size | 16777216 |
+---------------------+----------+
MariaDB [(none)]> show variables like 'tmp_table_size';
+----------------+----------+
| Variable_name | Value |
+----------------+----------+
| tmp_table_size | 16777216 |
+----------------+----------+Le premier, max_heap_table_size, nous indique la quantité de RAM pouvant être utilisée par une table MySQL («tas» se réfère ici à la structure de données utilisée dans l'allocation et la gestion de la RAM - en savoir plus ici), tandis que le second, tmp_table_size, indique la taille maximale de la table temporaire. Dans mon cas, les deux sont réglés sur 16 Mo, bien que le point que j'essaie de faire en sorte d'augmenter seulement tmp_table_size ne fonctionnera pas dans l'ensemble, MySQL serait toujours limité par max_table_heap_size.
Maintenant vient le point: si les tables temporaires en cours de création sont plus grandes que la limite autorisée par ces variables, MySQL serait obligé de les écrire sur le disque dur, ce qui entraînerait des performances extrêmement médiocres. Notre travail est maintenant simple: faites de notre mieux pour deviner la taille de données la plus précise pour les tables temporaires et ajustez ces variables jusqu'à cette limite. Cependant, je tiens à mettre en garde contre l'absurdité: définir cette limite à 16 Go (en supposant que vous ayez autant de RAM) alors que la plupart de vos tables temporaires ont une taille inférieure à 24 Mo est une folie - vous gaspillez simplement de la RAM qui pourrait ' ont été utilisés par d'autres requêtes ou parties du système (cache, par exemple).
Conclusion
Il n'est pas possible de couvrir toutes les variables système dans un article, ni même toutes les variables importantes dans un article lorsque la documentation MySQL elle-même couvre plusieurs milliers de mots. Bien que nous ayons couvert quelques variables universelles ici, je vous encourage à examiner les variables système du moteur que vous utilisez (InnoDB or MyISAM).
Mon résultat le plus souhaitable pour écrire cet article est que vous reteniez trois choses:
- MySQL est un logiciel typique qui fonctionne dans les limites fixées par le système d'exploitation. Ce n'est pas un programme mystérieux qui fait Dieu sait quoi et qui est impossible à apprivoiser. De plus, heureusement, il n'est pas si difficile de comprendre comment il est configuré et contrôlé par ses variables système.
- Il n'y a pas de paramètre unique qui fera de votre installation MySQL un zoom. Vous n'avez pas d'autre choix que de regarder dans vos systèmes en cours d'exécution (rappelez-vous, l'optimisation vient après l'application est en production, pas avant), faites les meilleures estimations et mesures, et vivez avec la réalité que cela ne sera jamais parfait.
- Le réglage des variables n'est pas le seul moyen d'optimiser MySQL - des requêtes d'écriture efficaces sont un autre gros problème, mais c'est quelque chose que j'aborderai dans un autre article. Mais le fait est que même si vous avez fait une analyse divine et réglé ces paramètres au mieux, il vous est toujours possible de tout arrêter brutalement.
Quelle est votre variable système préférée pour le réglage? 🙂
