Name: Tremplin Numérique
Price range: $$$

Logo MySQL

Vous êtes confronté à une charge excessive du serveur MySQL ou vous constatez que certaines requêtes prennent trop de temps à s’exécuter? Voici comment commencer lorsque vous étudiez les problèmes de performances de MySQL.

Avant de commencer, vous devez savoir que chaque serveur et ensemble de données présente ses propres problèmes de performances. Cet article vise à fournir des conseils généraux sur la façon de vérifier la configuration de votre serveur et les requêtes individuelles pour les problèmes cachés.

Sommaire

Faire face à la charge du serveur

L’indication la plus évidente que quelque chose ne va pas provient souvent d’un ralentissement général. Si vous constatez des périodes prolongées d’utilisation de ressources élevées, la modification du fichier de configuration de MySQL peut conduire à des paramètres plus optimaux.

Un bon point de départ est d’exécuter MySQLTuner. Ce script évalue automatiquement votre serveur MySQL par rapport à 300 indicateurs de performance possibles. Il produira une liste de suggestions qui pourraient vous aider à tirer le meilleur parti de votre environnement.

MySQLTuner est distribué sous forme de script Perl, vous aurez donc besoin de Perl installé sur votre système. Utilisez les commandes suivantes pour télécharger et exécuter MySQLTuner:

wget http://mysqltuner.pl/ -O mysqltuner.pl
chmod +x mysqltuner.pl
./mysqltuner.pl --host 127.0.0.1 --username root --pass mysql-password

La syntaxe de connexion est similaire à celle mysql client en ligne de commande. Vous devez vous connecter en tant que root user afin que le script ait un accès complet à votre serveur.

MySQLTuner est utile car son évaluation est basée sur ton hébergement de serveur ton base de données. Néanmoins, le résultat est simplement suggestif – toutes les recommandations n’auront pas un impact, et certaines pourraient en fait réduire les performances.

MySQLTuner est un script purement en lecture seule. Il n’apportera aucune modification aux paramètres de votre serveur. Si vous acceptez une suggestion, vous devez mettre à jour manuellement la variable indiquée dans votre fichier de configuration MySQL. L’emplacement de ce fichier varie selon la distribution du système d’exploitation. Les emplacements communs incluent /etc/mysql/my.cnf et /etc/mysql/mysql.conf.d/mysqld.cnf.

Après avoir modifié une variable, redémarrez le serveur MySQL:

sudo /etc/init.d/mysql restart

Vous devez maintenant laisser le serveur fonctionner normalement pendant un certain temps. Vous pouvez ensuite réexécuter MySQLTuner pour réévaluer les performances du serveur. Cela pourrait suggérer une autre modification de la même variable. Continuez à apporter des modifications, mais assurez-vous d’équilibrer tous les paramètres. Vous ne pourrez pas nécessairement définir chaque variable à sa valeur suggérée sans que de nouvelles suggestions n’apparaissent. La sortie de MySQLTuner conseille de laisser le serveur fonctionner pendant 24 heures pour obtenir des données d’évaluation précises.

Personnalisation des tailles de tampon

La modification de la taille de la mémoire tampon et du cache peut apporter une nette amélioration des performances. MySQL utilise par défaut des tailles de tampon relativement petites, ce qui ne fonctionnera pas bien pour les charges de travail plus importantes. Les valeurs sont écrites dans le fichier de configuration MySQL en utilisant K, M, ou G pour indiquer l’unité de stockage (par ex. 512M signifie 512 mégaoctets).

innodb_buffer_pool_size : En règle générale, ce paramètre est généralement défini sur 70 à 80% de votre mémoire disponible. Il définit la taille du pool utilisé pour mettre en mémoire tampon les requêtes sur les tables InnoDB. Essayez de rendre cela au moins aussi grand que la taille totale de votre ensemble de données, à condition que vous ayez suffisamment de mémoire disponible.
innodb_buffer_pool_instances : Une valeur comprise entre 1 et 64, définissant le nombre de pools de mémoire tampon InnoDB à opérer. Chaque page stockée dans le pool de mémoire tampon est affectée de manière aléatoire à l’une des instances. Un plus grand nombre d’instances peut améliorer la concurrence.
innodb_log_file_size : Taille maximale des fichiers de journalisation dans un groupe de journaux. Ces fichiers sont utilisés lors de la récupération après incident pour restaurer les transactions incomplètes. Des valeurs plus élevées améliorent les performances mais augmentent le temps de récupération en cas de panne.
key_buffer_size : Ceci est similaire à innodb_buffer_pool_size mais est utilisé pour les tables MyISAM. Notez que si vous utilisez exclusivement des tables MyISAM ou InnoDB, vous devez définir la variable appropriée en conséquence et changer l’autre à une taille relativement faible, telle que 32M. Sinon, vous gaspilleriez de la RAM en provisionnant un grand espace tampon pour un type de table inutilisé.
join_buffer_size : Ceci définit la taille du tampon utilisé pour les jointures sans index. L’augmentation de la taille de ce tampon accélérera les requêtes, qui utilisent des jointures non indexées. Une valeur trop élevée peut entraîner des problèmes de mémoire, car un tampon de jointure est alloué pour chaque jointure complète entre les tables. Les jointures complexes entre plusieurs tables nécessiteront plusieurs tampons, chacun des join_buffer_size capacité, qui peut rapidement consommer beaucoup de RAM. La valeur par défaut est 256K.
sort_buffer_size : Aimer join_buffer_size , mais applicable à tri opérations utilisant filesort. Des valeurs plus élevées peuvent accélérer le tri des ensembles de résultats volumineux, mais risquent d’augmenter l’utilisation de la mémoire sur un serveur très actif.

Vous devez toujours évaluer les modifications par rapport à la taille de votre ensemble de données et aux ressources matérielles de votre serveur. La définition de ces valeurs trop faibles aura un impact sur les performances des requêtes, tandis qu’à l’inverse, leur définition trop élevée peut entraîner une utilisation excessive de la mémoire ou même un épuisement de la mémoire. MySQLTuner émettra des avertissements si votre configuration risque de consommer toute la mémoire système disponible.

Analyse des requêtes lentes

L’activation du journal des requêtes lentes vous donne un aperçu des requêtes peu performantes. Vous pouvez le faire à partir d’une session shell MySQL:

sudo mysql
SET GLOBAL slow_query_on = "On";
SET GLOBAL slow_query_log_file = "/slow-query.log";
SET GLOBAL long_query_time = 5;

Cette configuration enregistrera toutes les requêtes qui prennent plus de cinq secondes à s’exécuter /slow-query.log. Examinez ce fichier régulièrement pour identifier les requêtes de longue durée.

Une fois que vous avez trouvé une requête problématique, vous pouvez utiliser un EXPLAIN déclaration pour avoir un aperçu de la cause du ralentissement. Préfixez la requête avec EXPLAIN et exécutez la commande dans un shell MySQL. Vous obtiendrez une sortie tabulée montrant comment MySQL prévoit d’exécuter la requête.

Le EXPLAIN la sortie comprend des informations sur les index disponibles, les clés utilisées et le nombre d’enregistrements évalués. Interpréter EXPLAIN les données sont un sujet en soi. Des conseils détaillés sur la signification de chaque champ sont disponibles dans la documentation MySQL.

Il peut être plus facile d’utiliser MySQL Workbench pour exécuter un EXPLAIN graphiquement. Cela peut vous aider à visualiser l’approche du moteur de base de données pour récupérer le jeu de résultats. Dans MySQL Workbench, appuyez sur Ctrl + T pour ouvrir un nouvel onglet de requête. Écrivez votre requête et appuyez sur Ctrl + Alt + X pour l’exécuter en tant que EXPLAIN (Vous n’avez pas besoin d’ajouter le EXPLAIN préfixe manuellement.). Dans le volet des résultats, vous verrez le plan d’exécution visuel mettant en évidence les opérations impliquées.

Le rôle des index

Il est important de vous assurer que votre ensemble de données contient les index appropriés. Une utilisation correcte des index augmente considérablement les performances des requêtes.

SELECT * FROM users WHERE Email = 'example@example.com';

Cette requête doit avoir un index sur le users.Email domaine. Sans index, MySQL aurait besoin d’effectuer une analyse complète de la table, provoquant un examen lent de chaque enregistrement.

Avec l’index, le moteur de base de données est capable d’identifier les enregistrements beaucoup plus rapidement. Pour ce faire, il crée une nouvelle structure de données contenant la valeur du champ et un pointeur vers l’enregistrement source. Les pointeurs peuvent ensuite être triés afin que MySQL puisse accéder directement aux données pertinentes.

Pour ajouter un index à un champ existant, utilisez le ADD INDEX déclaration avec ALTER TABLE:

ALTER TABLE my_table ADD INDEX my_index (my_field);

Vous devriez alors courir OPTIMIZE TABLE my_table pour indexer les données existantes et recalculer les statistiques de requête.

Lorsque vous travaillez avec plusieurs champs, vous pouvez créer un indice de couverture. Il s’agit d’un index qui intègre tous les champs.

SELECT * FROM my_table WHERE x = 1 AND y = 2 ORDER BY z;
 
ALTER TABLE my_table ADD INDEX covering_index (x, y, z);

Lors de l’utilisation d’index de couverture, l’ordre des champs est important. Si vous avez interrogé WHERE z = 1 ORDER BY x, l’index créé ci-dessus ne serait pas utilisé.

Vous devez vous assurer que les champs utilisés dans WHERE ou JOIN ... ON les clauses sont couvertes par un index. L’interrogation des champs non indexés peut rapidement devenir un goulot d’étranglement des performances. Méfiez-vous de l’indexation chaque Cependant, si vous n’interrogez jamais ce champ, l’index est une surcharge inutile qui doit toujours être maintenue par MySQL.

Vous pouvez identifier les requêtes qui bénéficieraient d’un index en activant le journal des requêtes non indexées. Suivez les instructions ci-dessus pour activer le journal des requêtes lentes. Vous devriez alors courir SET GLOBAL log_queries_not_using_indexes = "On" à partir d’un shell MySQL. Cela démarrera la journalisation des requêtes avec des index manquants dans le journal des requêtes lentes. Les requêtes non indexées seront incluses même si elles ne dépassent pas le temps de requête lent configuré.

Conclusion

Il n’y a pas d’approche unique pour améliorer les performances de MySQL. Les étapes à suivre dépendent des ressources de votre serveur, de la taille de votre ensemble de données et du niveau de conflit de ressources causé par d’autres charges de travail exécutées sur la machine.

Vous ne devez pas non plus négliger votre couche d’application – ce n’est peut-être pas MySQL qui est à l’origine de vos problèmes de performances. Inspectez la manière dont votre code effectue des requêtes. S’il y a une boucle excessive, comme l’utilisation d’une requête dans une routine N + 1, la refactorisation de ce code peut être beaucoup plus impactante que la microgestion de votre serveur MySQL.