10 conseils de performance essentiels pour MySQL

Comme pour toutes les bases de données relationnelles, MySQL peut s'avérer être une bête compliquée, une bête qui peut s'arrêter à tout moment, laissant vos applications dans l'embarras et votre entreprise en jeu.

La vérité est que les erreurs courantes sous-tendent la plupart des problèmes de performances de MySQL. Pour vous assurer que votre serveur MySQL fonctionne à toute vitesse, offrant des performances stables et cohérentes, il est important d'éliminer ces erreurs, qui sont souvent masquées par une certaine subtilité dans votre charge de travail ou un piège de configuration.

Heureusement, de nombreux problèmes de performances de MySQL se révèlent avoir des solutions similaires, faisant du dépannage et du réglage de MySQL une tâche gérable.

Voici 10 conseils pour obtenir d'excellentes performances avec MySQL.

Astuce de performance MySQL n ° 1: profilez votre charge de travail

La meilleure façon de comprendre comment votre serveur passe son temps est de profiler la charge de travail du serveur. En profilant votre charge de travail, vous pouvez exposer les requêtes les plus coûteuses pour un réglage supplémentaire. Ici, le temps est la métrique la plus importante car lorsque vous émettez une requête sur le serveur, vous vous souciez très peu de tout sauf de la rapidité avec laquelle il se termine.

La meilleure façon de profiler votre charge de travail consiste à utiliser un outil tel que l'analyseur de requêtes de MySQL Enterprise Monitor ou le pt-query-digest de Percona Toolkit. Ces outils capturent les requêtes exécutées par le serveur et renvoient un tableau de tâches triées par ordre décroissant de temps de réponse, faisant instantanément remonter les tâches les plus coûteuses et les plus chronophages vers le haut afin que vous puissiez voir où concentrer vos efforts.

Les outils de profilage de la charge de travail regroupent les requêtes similaires, vous permettant de voir les requêtes qui sont lentes, ainsi que les requêtes qui sont rapides mais exécutées plusieurs fois.

Astuce de performance MySQL n ° 2: Comprendre les quatre ressources fondamentales

Pour fonctionner, un serveur de base de données a besoin de quatre ressources fondamentales: CPU, mémoire, disque et réseau. Si l'un de ces éléments est faible, erratique ou surchargé, le serveur de base de données est très susceptible de fonctionner mal.

La compréhension des ressources fondamentales est importante dans deux domaines particuliers: le choix du matériel et le dépannage des problèmes.

Lorsque vous choisissez du matériel pour MySQL, assurez-vous que les composants sont performants partout. Tout aussi important, équilibrez-les raisonnablement les uns par rapport aux autres. Souvent, les organisations sélectionneront des serveurs dotés de processeurs et de disques rapides mais qui manquent de mémoire. Dans certains cas, l'ajout de mémoire est un moyen peu coûteux d'augmenter les performances de plusieurs ordres de grandeur, en particulier sur les charges de travail liées au disque. Cela peut sembler contre-intuitif, mais dans de nombreux cas, les disques sont surutilisés car il n'y a pas assez de mémoire pour contenir l'ensemble de données de travail du serveur.

Un autre bon exemple de cet équilibre concerne les processeurs. Dans la plupart des cas, MySQL fonctionnera bien avec des processeurs rapides car chaque requête s'exécute dans un seul thread et ne peut pas être parallélisé entre les processeurs.

En ce qui concerne le dépannage, vérifiez les performances et l'utilisation des quatre ressources, en veillant à déterminer si elles fonctionnent mal ou si on leur demande simplement de faire trop de travail. Ces connaissances peuvent aider à résoudre les problèmes rapidement.

Astuce de performance MySQL n ° 3: n'utilisez pas MySQL comme file d'attente

Les files d'attente et les modèles d'accès de type file d'attente peuvent se faufiler dans votre application sans que vous le sachiez. Par exemple, si vous définissez le statut d'un élément afin qu'un processus de travail particulier puisse le réclamer avant d'agir, vous créez alors involontairement une file d'attente. Marquer les e-mails comme non envoyés, les envoyer, puis les marquer comme envoyés est un exemple courant.

Les files d'attente posent des problèmes pour deux raisons principales: elles sérialisent votre charge de travail, empêchant les tâches d'être effectuées en parallèle, et elles se traduisent souvent par une table qui contient les travaux en cours ainsi que les données historiques des travaux traités il y a longtemps. Les deux ajoutent de la latence à l'application et se chargent sur MySQL.

Astuce de performance MySQL n ° 4: Filtrez d'abord les résultats par le moins cher

Un excellent moyen d'optimiser MySQL consiste à effectuer d'abord un travail peu coûteux et imprécis, puis un travail difficile et précis sur le plus petit ensemble de données résultant.

Par exemple, supposons que vous recherchez quelque chose dans un rayon donné d'un point géographique. Le premier outil de la boîte à outils de nombreux programmeurs est la formule du grand cercle (Haversine) pour calculer la distance le long de la surface d'une sphère. Le problème avec cette technique est que la formule nécessite beaucoup d'opérations trigonométriques, qui sont très gourmandes en CPU. Les calculs du grand cercle ont tendance à s'exécuter lentement et à faire monter en flèche l'utilisation du processeur de la machine.

Avant d'appliquer la formule du grand cercle, réduisez vos enregistrements à un petit sous-ensemble du total et découpez l'ensemble résultant en un cercle précis. Un carré qui contient le cercle (avec précision ou imprécision) est un moyen simple de le faire. De cette façon, le monde extérieur à la place n'est jamais touché par toutes ces fonctions trigonométriques coûteuses.

Astuce de performance MySQL n ° 5: Connaissez les deux pièges mortels de l'évolutivité

L'évolutivité n'est pas aussi vague que vous pouvez le croire. En fait, il existe des définitions mathématiques précises de l'évolutivité qui sont exprimées sous forme d'équations. Ces équations mettent en évidence pourquoi les systèmes ne s'adaptent pas aussi bien qu'ils le devraient.

Prenons la loi d'évolutivité universelle, une définition pratique pour exprimer et quantifier les caractéristiques d'évolutivité d'un système. Il explique les problèmes de mise à l'échelle en termes de deux coûts fondamentaux: la sérialisation et la diaphonie.

Les processus parallèles qui doivent s'arrêter pour que quelque chose de sérialisé se produise sont intrinsèquement limités dans leur évolutivité. De même, si les processus parallèles ont besoin de discuter les uns avec les autres en permanence pour coordonner leur travail, ils se limitent.

Évitez la sérialisation et la diaphonie, et votre application évoluera beaucoup mieux. Qu'est-ce que cela se traduit à l'intérieur de MySQL? Cela varie, mais certains exemples éviteraient les verrous exclusifs sur les lignes. Les files d'attente, point n ° 3 ci-dessus, ont tendance à mal évoluer pour cette raison.

Astuce de performance MySQL n ° 6: ne vous concentrez pas trop sur la configuration

Les administrateurs de base de données ont tendance à passer énormément de temps à peaufiner les configurations. Le résultat n'est généralement pas une grande amélioration et peut parfois même être très dommageable. J'ai vu beaucoup de serveurs «optimisés» qui plantaient constamment, manquaient de mémoire et fonctionnaient mal lorsque la charge de travail devenait un peu plus intense.

Les valeurs par défaut fournies avec MySQL sont uniques et très obsolètes, mais vous n'avez pas besoin de tout configurer. Il est préférable de bien comprendre les fondamentaux et de modifier les autres paramètres uniquement si nécessaire. Dans la plupart des cas, vous pouvez obtenir 95% des performances de pointe du serveur en définissant correctement environ 10 options. Les quelques situations où cela ne s'applique pas seront des cas extrêmes propres à votre situation.

Dans la plupart des cas, les outils de «réglage» du serveur ne sont pas recommandés car ils ont tendance à donner des directives qui n'ont pas de sens pour des cas spécifiques. Certains ont même codé des conseils dangereux et inexacts, tels que les taux de succès du cache et les formules de consommation de mémoire. Celles-ci n’ont jamais été exactes et elles sont devenues encore moins correctes avec le temps.

Astuce de performance MySQL n ° 7: faites attention aux requêtes de pagination

Les applications qui paginent ont tendance à mettre le serveur à genoux. En vous montrant une page de résultats, avec un lien pour aller à la page suivante, ces applications regroupent et trient généralement de manière à ne pas utiliser d'index, et elles utilisent un LIMITet offsetqui obligent le serveur à faire beaucoup de travail pour générer, puis rejeter les lignes.

Les optimisations se trouvent souvent dans l'interface utilisateur elle-même. Au lieu d'afficher le nombre exact de pages dans les résultats et les liens vers chaque page individuellement, vous pouvez simplement afficher un lien vers la page suivante. Vous pouvez également empêcher les utilisateurs d'accéder à des pages trop éloignées de la première page.

Du côté de la requête, au lieu d'utiliser LIMITavec offset, vous pouvez sélectionner une ligne de plus que ce dont vous avez besoin, et lorsque l'utilisateur clique sur le lien «page suivante», vous pouvez désigner cette dernière ligne comme point de départ du prochain ensemble de résultats. Par exemple, si l'utilisateur a consulté une page avec les lignes 101 à 120, vous sélectionnerez également la ligne 121; pour afficher la page suivante, vous interrogez le serveur pour les lignes supérieures ou égales à 121, limite 21.

Astuce de performance MySQL n ° 8: enregistrez les statistiques avec empressement, alertez à contrecœur

La surveillance et l'alerte sont essentielles, mais qu'advient-il du système de surveillance typique? Il commence à envoyer des faux positifs et les administrateurs système mettent en place des règles de filtrage des e-mails pour arrêter le bruit. Bientôt, votre système de surveillance est complètement inutile.

J'aime penser à la surveillance de deux manières: en capturant des métriques et en alertant. Il est très important de capturer et de sauvegarder toutes les métriques possibles, car vous serez heureux de les avoir lorsque vous essayez de comprendre ce qui a changé dans le système. Un jour, un problème étrange surgira, et vous adorerez la possibilité de pointer vers un graphique et d'afficher un changement dans la charge de travail du serveur.

En revanche, il y a une tendance à trop alerter. Les gens alertent souvent sur des choses comme le taux de réussite de la mémoire tampon ou le nombre de tables temporaires créées par seconde. Le problème est qu'il n'y a pas de bon seuil pour un tel ratio. Le bon seuil n'est pas seulement différent d'un serveur à l'autre, mais d'heure en heure à mesure que votre charge de travail change.

Par conséquent, alertez avec parcimonie et uniquement sur les conditions qui indiquent un problème défini et exploitable. Un faible taux de réussite de la mémoire tampon n'est pas exploitable, ni n'indique un problème réel, mais un serveur qui ne répond pas à une tentative de connexion est un problème réel qui doit être résolu.

Astuce de performance MySQL n ° 9: Apprenez les trois règles d'indexation

L'indexation est probablement le sujet le plus mal compris dans les bases de données, car il existe de nombreuses façons de se tromper sur le fonctionnement des index et la manière dont le serveur les utilise. Il faut beaucoup d'efforts pour vraiment comprendre ce qui se passe.

Les index, lorsqu'ils sont correctement conçus, servent trois objectifs importants dans un serveur de base de données:

  1. Les index permettent au serveur de trouver des groupes de lignes adjacentes au lieu de lignes uniques. Beaucoup de gens pensent que le but d'un index est de trouver des lignes individuelles, mais trouver des lignes uniques conduit à des opérations aléatoires sur le disque, ce qui est lent. Il est préférable de trouver des groupes de lignes, dont la totalité ou la plupart sont intéressantes, plutôt que de trouver les lignes une à la fois.
  2. Les index permettent au serveur d'éviter le tri en lisant les lignes dans l'ordre souhaité. Le tri est coûteux. La lecture des lignes dans l'ordre souhaité est beaucoup plus rapide.
  3. Les index permettent au serveur de satisfaire des requêtes entières à partir de l'index seul, évitant ainsi d'avoir à accéder à la table. Ceci est connu sous le nom d'index couvrant ou de requête d'index uniquement.

Si vous pouvez concevoir vos index et vos requêtes pour exploiter ces trois opportunités, vous pouvez accélérer vos requêtes de plusieurs ordres de grandeur.

Astuce de performance MySQL n ° 10: Tirez parti de l'expertise de vos pairs

N'essayez pas de faire cavalier seul. Si vous êtes perplexe sur un problème et que vous faites ce qui vous semble logique et sensé, c'est parfait. Cela fonctionnera environ 19 fois sur 20. L'autre fois, vous irez dans un terrier de lapin qui sera très coûteux et prend beaucoup de temps, précisément parce que la solution que vous essayez semble avoir beaucoup de sens.

Créez un réseau de ressources liées à MySQL - et cela va au-delà des ensembles d'outils et des guides de dépannage. Il y a des gens extrêmement compétents qui se cachent sur les listes de diffusion, les forums, les sites Web de questions et réponses, etc. Les conférences, les salons professionnels et les événements de groupes d'utilisateurs locaux offrent de précieuses opportunités pour obtenir des informations et établir des relations avec des pairs qui peuvent vous aider à la rigueur.

Pour ceux qui recherchent des outils pour compléter ces conseils, vous pouvez consulter l'Assistant de configuration Percona pour MySQL, Percona Query Advisor pour MySQL et les plugins de surveillance Percona. (Remarque: vous devrez créer un compte Percona pour accéder à ces deux premiers liens. C'est gratuit.) L'assistant de configuration peut vous aider à générer un fichier my.cnf de base pour un nouveau serveur qui est supérieur aux exemples de fichiers fournis avec le serveur. Le conseiller en requêtes analysera votre SQL pour vous aider à détecter les modèles potentiellement défectueux tels que les requêtes de pagination (n ° 7). Les plugins de surveillance Percona sont un ensemble de plugins de surveillance et de graphique pour vous aider à enregistrer des statistiques avec empressement et à alerter à contrecœur (n ° 8). Tous ces outils sont disponibles gratuitement.