Réglage des variables système MySQL pour des performances élevées

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 constituent une force en soi. Même la traditionally, lorsqu’il n’existait que des bases de données de type relationnel, 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 dont un produit fonctionne de manière satisfaisante sur une base de données relationnelle depuis très, très longtemps. Et dans neuf cas sur dix, vous êtes sur MySQL (ou MariaDB). Dans ces cas-là, plonger un peu plus profondément sous le capot génère d’énormes avantages en termes d’amélioration des performances des applications et est tout à fait approprié. bit mérite d'être appris.

Curieuse? Plongeons-nous!

Pas curieux? Eh bien, plongez-vous quand même, car votre carrière en dépend! 😛

Optimiser le cache des requêtes MySQL

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 j'ai le cache de requêtes actuelally allumé:

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;

Il est intéressant de noter que cette variable accepte également un third valeur qui indique « à la demande », ce qui signifie que MySQL mettra en cache uniquement les requêtes que nous lui demandons, mais nous n'entrerons pas dans ces détails 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 self-explicatif:

MariaDB [(none)]> SHOW VARIABLES LIKE 'query_cache_size';
+------------------+----------+
| Variable_name    | Value    |
+------------------+----------+
| query_cache_size | 16777216 |
+------------------+----------+

J'ai donc un cache de requêtes d'environ 16 Mo. Notez que même si la mise en cache des requêtes est activée, cette taille est zero, la mise en cache est effectivement désactivée. C'est pourquoi il ne suffit pas de vérifier une seule variable. Maintenant, vous devez définir une taille de cache de requêtes, mais quelle doit-elle être ? Tout d'abord, veuillez noter que la fonctionnalité de mise en cache des requêtes le feraself besoin de 4 Ko pour stocker ses métadonnées, alors pourquoiatever vous sélectionnez devrait être au-dessus de cela.

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 comment le moteur de requête agiraally finir par performer dépend de quelques autres choses :

• Tout d'abord, le query_cache_size La 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_size est réglé sur un nombre trop élevé, il y aura deux types de problèmes : 1) Le moteur devra faire extra travailler à stocker et à localiser les résultats des requêtes dans cette zone de 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 wisely. Si tu te sens frustréateEt je ne vous ai pas laissé de chiffre concret ici, j'ai bien peur que ce soit ainsi que les choses se passent une fois que vous avez dépassé le stade du développement et que vous passez à 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 commettre une erreur. 🙂

Threading, pools de threads, attente et délais d'attente

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 permet tout simplement pas de devenir fou et de tout manger. 2) Gérer, disons, un million de threads est une tâche énorme en soi, et si le serveur MySQL pouvait créerate autant de threads, il mourrait en essayant de gérer les frais généraux.

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épart. 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 utilisés, de nouvelles connexions sont naturelles.ally refusé. Voyons quelle est la taille du pool de threads :

ariaDB [(none)]> show variables like 'thread_pool_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| thread_pool_size | 4     |
+------------------+-------+

Ma machine autorise donc au maximum quatre connexions en même temps. Il est intéressant de noter que le chiffre 4 vient du fait que j'ai un processeur quatre cœurs processou, ce qui signifie que mon ordinateur ne peut exécuter que 4 tâches parallèles à la fois (je parle ici de tâches véritablement parallèles, pas de tâches parallèles). concuren louer). Idéeally, c'est la limite qui devrait déterminer 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 très mauvaise idée, sauf si vous disposez d'une machine à 32 cœurs, car les performances se dégradent considérablement. Le lapinbit Le trou des pools de threads dans MySQL est profond, mais si vous êtes intéressé, voici une discussion plus détaillée.

Attente et délais d'attente

Une fois qu'un fil a été crééated et attaché à un client, ce serait un gaspillage de ressources si le client n'envoyait aucune requête pendant les secondes (ou minutes) suivantes. En conséquence, MySQL termineatesa 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 utile si vous avez des requêtes de longue durée et que vous avez des actus.ally je veux les attendre (mais même alors, huit heures, c'est absurde !) mais terrible dans la plupart des cas. Lorsqu'une requête est exécutée, cette valeur est définie sur 0 (c'est-à-dire pourreveuh), mais généralally, celle-ci doit être réglée sur une valeur très faible (5 secondes, par exemple, ou peut-être même moins) pour libérer la connexion pour d'autres processpar exemple.

Réglage des tables temporaires

Commençons par ce que sont les tables temporaires dans MySQL.

Supposons que nous ayons un MySQL qui structureally ressemble à ceci : TABLE A UNION (TABLE B INNER JOIN C). Autrement dit, nous souhaitons joindre les tables B et C, puis effectuer une union du résultat avec la table A. Maintenant, MySQL devrait d'abord joindre les tables B et C, mais avant de pouvoir effectuer une union, il lui faut pour stocker ces données quelque part. C'est là qu'interviennent les tables temporaires : MySQL les utilise pour stocker des données à des fins intermédiaires.ate effectue temporairement 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.

Vient maintenant le point : si les tables temporaires sont crééesated sont supérieurs à 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 désormais simple : faire de notre mieux pour deviner le plus précisate taille des données pour les tables temporaires et ajustez ces variables à cette limite. Cependant, je voudrais mettre en garde contre toute absurdité : fixer cette limite à 16 Go (en supposant que vous disposez d'autant de RAM) alors que la plupart de vos tables temporaires font moins de 24 Mo est une folie : vous gaspillez simplement de la RAM qui pourrait " J'ai été utilisé 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 seul article, ni même toutes les variables importantes dans un seul article lorsque la documentation MySQL est disponible.self s'étend sur plusieurs milliers de mots. Bien que nous ayons abordé ici certaines variables universelles, 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:

1. 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.
2.  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.
3. 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? 🙂