Présentation de 7 conseils pour améliorer les performances de MySQL

Note du traducteur : à mesure que la taille et la charge augmentent, les performances de MySQL auront tendance à diminuer. N'oubliez pas ces conseils pour que MySQL fonctionne correctement.

L'une des façons de mesurer une application est d'examiner ses performances. L'un des indicateurs de performance est l'expérience utilisateur, qui, en termes simples, consiste à « si les utilisateurs doivent attendre plus longtemps pour obtenir ce qu'ils veulent ».

Cet indicateur change selon les applications. Pour les applications d’achats mobiles, le temps de réponse ne peut excéder quelques secondes. Pour les pages RH destinées aux salariés, cela peut prendre quelques secondes de plus.

Il existe de nombreuses études sur la manière dont les performances affectent le comportement des utilisateurs :

• Il est peu probable que 79 % des clients reviennent sur un site Web lent

• 47 % des consommateurs s'attendent à ce que les pages Web se chargent en 2 secondes ou moins

• 40 % des utilisateurs abandonneront un site Web si le chargement prend plus de 3 secondes

• Un retard d'1 seconde dans le temps de chargement des pages peut entraîner une perte de 7 % et une réduction de 11 % des pages vues

Quels que soient les critères utilisés, il est un must Maintenir de bonnes performances des applications. Sinon, les utilisateurs se plaindront (ou pire, passeront à une autre application). L'un des facteurs qui affectent les performances des applications est la performance de la base de données. L'interaction entre les applications, les sites Web et les bases de données est essentielle pour déterminer les performances de votre application.

Un élément essentiel de cette interaction est la manière dont l'application interroge la base de données et la manière dont la base de données répond aux requêtes. Quoi qu’il en soit, MySQL est l’un des systèmes de gestion de bases de données les plus populaires. Dans les environnements de production, de plus en plus d'entreprises se tournent vers MySQL (et d'autres bases de données open source) comme solution de base de données.

Il existe de nombreuses façons de configurer MySQL qui peuvent contribuer à garantir que la base de données répond rapPrésentation de 7 conseils pour améliorer les performances de MySQLement aux requêtes et à minimiser la dégradation des performances des applications.

Voici quelques conseils de base pour vous aPrésentation de 7 conseils pour améliorer les performances de MySQLer à optimiser les performances de la base de données MySQL.

Conseil d'optimisation n° 1 : Apprenez à utiliser EXPLAIN

Les deux décisions les plus importantes que vous prenez avec n'importe quelle base de données sont la conception de la façon dont les relations entre les entités d'application sont mappées aux tables (schéma de base de données) et la conception de la façon dont l'application obtient les données requises dans le format requis (requête).

Les applications complexes peuvent avoir des modèles et des requêtes complexes. Si vous souhaitez obtenir les performances et l'évolutivité dont votre application a besoin, vous ne pouvez pas vous fier uniquement à votre intuition pour comprendre comment exécuter une requête.

Vous devriez apprendre à utiliser la commande EXPLAIN au lieu de deviner et d'imaginer au hasard. Cette commande montre comment exécuter la requête et vous donne une Présentation de 7 conseils pour améliorer les performances de MySQLée des performances auxquelles vous pouvez vous attendre et de la façon dont la requête évoluera à mesure que la taille de vos données change.

Il existe de nombreux outils – tels que MySQL Workbench – qui peuvent visualiser la sortie EXPLAIN, mais vous devez toujours comprendre les bases pour le comprendre.

La commande EXPLAIN fournit une sortie dans deux formats différents : un format tabulaire à l'ancienne et un document JSON structuré plus moderne, qui fournit plus de détails (illustré ci-dessous) :

mysql> explain format=json select avg(k) from sbtest1 where Présentation de 7 conseils pour améliorer les performances de MySQL between 1000 and 2000 \G
*************************** 1. row ***************************
EXPLAIN: {
  “query_block”: {
    “select_Présentation de 7 conseils pour améliorer les performances de MySQL”: 1,
    “cost_info”: {
      “query_cost”: “762.40”
    },
    “table”: {
      “table_name”: “sbtest1”,
      “access_type”: “range”,
      “possible_keys”: [
        “PRIMARY”
      ],
      “key”: “PRIMARY”,
      “used_key_parts”: [
        “Présentation de 7 conseils pour améliorer les performances de MySQL”
      ],
      “key_length”: “4”,
      “rows_examined_per_scan”: 1874,
      “rows_produced_per_join”: 1874,
      “filtered”: “100.00”,
      “cost_info”: {
        “read_cost”: “387.60”,
        “eval_cost”: “374.80”,
        “prefix_cost”: “762.40”,
        “data_read_per_join”: “351K”
      },
      “used_columns”: [
        “Présentation de 7 conseils pour améliorer les performances de MySQL”,
        “k”
      ],
      “attached_condition”: “(`sbtest`.`sbtest1`.`Présentation de 7 conseils pour améliorer les performances de MySQL` between 1000 and 2000)”
    }
  }
}

Un composant ce qu'il faut examiner, c'est le "coût des requêtes". Le coût de la requête signifie que MySQL consPrésentation de 7 conseils pour améliorer les performances de MySQLère le coût de cette requête particulière en termes de coût total d'exécution de la requête et se base sur de nombreux facteurs différents.

La surcharge des requêtes simples est généralement inférieure à 1 000. Les requêtes dont le coût est compris entre 1 000 et 100 000 sont consPrésentation de 7 conseils pour améliorer les performances de MySQLérées comme des requêtes à coût moyen et seront généralement plus rapPrésentation de 7 conseils pour améliorer les performances de MySQLes si vous n'exécutez que quelques centaines de ces requêtes par seconde (au lieu de dizaines de milliers).

Les requêtes coûtant plus de 100 000 peuvent être consPrésentation de 7 conseils pour améliorer les performances de MySQLérées comme coûteuses. En règle générale, ces requêtes s'exécuteront toujours rapPrésentation de 7 conseils pour améliorer les performances de MySQLement lorsque vous êtes un seul utilisateur du système, mais vous devez soigneusement réfléchir à la fréquence à laquelle vous utilisez ces requêtes dans les applications interactives (en particulier à mesure que le nombre d'utilisateurs augmente).

Bien sûr, ces chiffres ne sont qu'une représentation approximative de la performance, mais ils illustrent les principes généraux. Votre système peut gérer mieux ou moins bien les charges de travail de requêtes, en fonction de son architecture et de sa configuration.

Le principal facteur qui détermine le coût d'une requête est de savoir si la requête utilise correctement les index. La commande EXPLAIN peut vous indiquer si une requête utilise un index (généralement en raison de la manière dont l'index a été créé dans la base de données ou de la manière dont la requête elle-même a été conçue). C'est pourquoi il est si important d'apprendre à utiliser EXPLAIN .

Conseil d'optimisation n°2 : créez le bon index

Les index améliorent l'efficacité des requêtes en réduisant la quantité de données dans la base de données que la requête doit analyser. Les index dans MySQL sont utilisés pour accélérer l'accès à la base de données et aPrésentation de 7 conseils pour améliorer les performances de MySQLer à appliquer les contraintes de la base de données (telles que UNIQUE et FOREIGN KEY).

Un index de base de données ressemble beaucoup à un index de livre. Ils sont enregistrés à leur propre emplacement et contiennent des informations qui existent déjà dans la base de données principale. Ce sont des méthodes de référence ou des cartes qui indiquent où résPrésentation de 7 conseils pour améliorer les performances de MySQLent les données. Les index ne modifient aucune donnée dans la base de données. Ils indiquent simplement l'emplacement des données.

Il n’existe pas d’index parfaitement adapté à n’importe quelle charge de travail. Au lieu de cela, vous devez toujours afficher les index dans le contexte de la requête dans laquelle le système s'exécute.

Non seulement une base de données bien indexée s'exécute plus rapPrésentation de 7 conseils pour améliorer les performances de MySQLement, mais même un index manquant peut ralentir l'exploration de la base de données. Utilisez EXPLAIN (comme mentionné précédemment) pour rechercher les index manquants et les ajouter. Mais attention : n'ajoutez pas d'index dont vous n'avez pas besoin ! Les index inutiles ralentiront votre base de données
(voir cette introduction aux meilleures pratiques d'indexation MySQL).

Conseil d'optimisation n°3 : refusez d'utiliser les paramètres par défaut

Comme tout logiciel, MySQL dispose de nombreux paramètres configurables qui peuvent être utilisés pour modifier le comportement (et finalement les performances). Comme pour tout logiciel, les administrateurs négligent bon nombre de ces paramètres configurables et finissent par les utiliser en mode par défaut.

Pour obtenir les meilleures performances de MySQL, il est important de comprendre les paramètres configurables de MySQL et, plus important encore, de les définir pour qu'ils s'adaptent au mieux à votre environnement de base de données.

Par défaut, MySQL est destiné aux installations de développement à petite échelle, et non à l'échelle de production. Vous souhaitez généralement configurer MySQL pour utiliser toutes les ressources mémoire disponibles et autoriser autant de connexions que votre application l'exige.

Voici trois paramètres d'optimisation des performances MySQL que vous devez toujours vérifier attentivement :

innodb_ buffer_ pool_size : le pool de tampons est utilisé pour stocker les données et les index mis en cache. C'est la principale raison d'utiliser un système doté d'une grande quantité de RAM comme serveur de base de données. Si vous exécutez uniquement le moteur de stockage InnoDB, 80 % de la mémoire est généralement allouée au pool de mémoire tampon. Si vous exécutez des requêtes très complexes, ou si vous disposez d'un grand nombre de connexions simultanées à une base de données ou d'un grand nombre de tables, vous souhaiterez peut-être réduire cette valeur d'un cran pour allouer plus de mémoire à d'autres opérations.

Lors de la définition de la taille du pool de tampons InnoDB, vous devez vous assurer de ne pas la définir trop grande, sinon cela entraînerait un échange. Cela affectera certainement les performances de la base de données. Un moyen simple de vérifier consiste à examiner l'activité d'échange dans le diagramme de présentation du système dans Percona Monitoring and Management :

Comme le montre l'image, il est parfois possible de faire quelques échanges. Toutefois, si vous constatez une activité de swap soutenue de 1 Mo par seconde ou plus, vous devez réduire la taille du pool de mémoire tampon (ou toute autre utilisation de la mémoire).

Ne vous inquiétez pas si la valeur de innodb_ Buffer_ pool_ size n'est pas obtenue correctement au premier accès. À partir de MySQL 5.7, il est possible de modifier dynamiquement la taille du pool de tampons InnoDB sans redémarrer le serveur de base de données.

innodb_log_file_size : Il s'agit de la taille d'un seul fichier journal InnoDB. Par défaut, InnoDB utilise deux valeurs, vous pouvez donc doubler ce nombre pour obtenir la taille de l'espace de journalisation circulaire qu'InnoDB utilise pour garantir la durabilité des transactions. Cela optimise également l'application des modifications à la base de données. La définition de innodb_log_file_size est une question de compromis. Plus l'espace de restauration est important, meilleures sont les performances pour les charges de travail gourmandes en écriture, mais plus la récupération après un crash en cas de panne de courant ou d'autres problèmes sera longue.

Comment savoir si les performances de MySQL sont limitées par la taille actuelle du fichier journal InnoDB ? Vous pouvez le savoir en regardant quelle quantité d’espace de journalisation disponible est réellement utilisée. Le moyen le plus simple consiste à afficher le tableau de bord Percona Monitor and Management InnoDB Metrics. Dans l'image ci-dessous, la taille du fichier journal InnoDB n'est pas assez grande car l'espace utilisé est très proche de l'espace de rétablissement disponible (indiqué par la ligne rouge). La taille du fichier journal doit être au moins 20 % supérieure à l'espace requis pour que le système fonctionne de manière optimale.

MAX_Connexions : les applications volumineuses nécessitent généralement un nombre élevé de connexions à la valeur par défaut. Contrairement à d'autres variables, il n'y a aucun problème de performances (en soi) si elle n'est pas définie correctement. A l'inverse, si le nombre de connexions n'est pas suffisant pour les besoins de votre application, votre application ne pourra pas se connecter à la base de données (ce qui ressemblera à un temps d'arrêt pour vos utilisateurs). Il est donc important de gérer correctement cette variable.

Si vous exécutez une application complexe avec plusieurs composants sur plusieurs serveurs, il peut être difficile de savoir combien de connexions sont nécessaires. Heureusement, MySQL permet de voir facilement combien de connexions sont utilisées pendant les périodes de pointe. Généralement, vous souhaitez vous assurer qu'il existe un écart d'au moins 30 % entre le nombre maximum de connexions utilisées par votre application et le nombre maximum de connexions disponibles. Un moyen simple d'afficher ces chiffres consiste à utiliser le graphique des connexions MySQL dans le tableau de bord de présentation MySQL de Percona Monitoring and Management. Le schéma ci-dessous montre un système robuste avec un grand nombre de connexions supplémentaires disponibles.

Une chose à retenir est que si la base de données est en cours d'exécution lentes, les applications créent souvent trop de connexions. Dans ce cas, vous devez résoudre les problèmes de performances de la base de données plutôt que de simplement autoriser davantage de connexions. Un plus grand nombre de connexions peut aggraver les problèmes de performances sous-jacents.

(Remarque : lorsque vous définissez la variable max_Connections de manière significativement supérieure à la valeur par défaut, vous devez souvent envisager d'augmenter d'autres paramètres, tels que la taille du cache de la table et le nombre de fichiers MySQL ouverts. Cependant, cela est au-delà de la portée de cet article Catégorie. )

Conseil d'optimisation n°4 : Gardez la base de données en mémoire

Ces dernières années, nous avons assisté à une transition vers les disques SSD. Bien que les disques SSD soient beaucoup plus rapPrésentation de 7 conseils pour améliorer les performances de MySQLes que les disques durs en rotation, ils ne peuvent toujours pas se comparer aux données de la RAM. Cette différence vient non seulement des performances de stockage elles-mêmes, mais également du travail supplémentaire que la base de données doit effectuer lors de la récupération des données à partir du stockage sur disque ou SSD.

Avec les dernières améliorations matérielles, il est de plus en plus possible de stocker des bases de données en mémoire, qu'elles soient exécutées dans le cloud ou qu'elles gèrent votre propre matériel.

La meilleure nouvelle est que vous n'avez pas besoin de mettre toute votre base de données en mémoire pour bénéficier au maximum des avantages en termes de performances de la mémoire. Vous stockez simplement l’ensemble de données de travail (les données les plus fréquemment consultées) en mémoire.

Vous avez peut-être vu des articles qui fournissent des chiffres précis sur la partie de la base de données qui doit être conservée en mémoire, allant de 10 % à 33 %. La vérité est qu’il n’existe pas de chiffre unique. La quantité de données pouvant être conservée en mémoire pour optimiser les performances dépend de la charge de travail. Plutôt que de rechercher un numéro « magique » spécifique, vérifiez les E/S que la base de données exécute dans son état stable (généralement quelques heures après le démarrage). Jetez un œil à READ, car si la base de données est en mémoire, READ peut être entièrement éliminé. L'écriture doit toujours avoir lieu, quelle que soit la quantité de mémoire dont vous disposez.

Ci-dessous, vous pouvez voir les E/S dans le graphique des E/S InnoDB dans le tableau de bord InnoDBMetrics surveillé et géré par Percona.

Dans le graphique ci-dessus, vous pouvez voir le pic de Gundam A de 2 000 opérations d'E/S par seconde indique que (au moins pour certaines parties de la charge de travail) l'ensemble de travail de la base de données ne tient pas dans la mémoire.

Conseil d'optimisation n°5 : utilisez le stockage SSD

Si votre base de données ne tient pas en mémoire (même si ce n'est pas le cas), vous avez toujours besoin d'un stockage rapPrésentation de 7 conseils pour améliorer les performances de MySQLe pour gérer les écritures et les ré-après démarrage) pour éviter les problèmes de performances. Aujourd’hui, SSD est synonyme de stockage rapPrésentation de 7 conseils pour améliorer les performances de MySQLe.

Certains "experts" préconisent encore l'utilisation de disques tournants (disques mécaniques) pour des raisons de coût ou de fiabilité. Franchement, ces arguments sont souvent dépassés ou tout simplement faux lorsqu’il s’agit d’exploiter des bases de données. Aujourd'hui, les SSD offrent des performances et une fiabilité impressionnantes à un prix élevé.

Cependant, tous les SSD ne sont pas adaptés. Pour les serveurs de bases de données, vous devez utiliser un SSD conçu pour les charges de travail du serveur qui protégeront les données (par exemple, lors d'une panne de courant). Évitez les SSD commerciaux conçus pour les ordinateurs de bureau et les ordinateurs portables.

Les SSD connectés via la technologie NVMe ou Intel OpTan offrent les meilleures performances. Même lorsqu'ils sont connectés à distance en tant que périphérique SAN, NAS ou cloud, les disques SSD offrent toujours des performances supérieures à celles des disques rotatifs.

Conseil d'optimisation n°6 : évolutivité

Même les serveurs hautes performances ont leurs limites. Il existe deux méthodes d'expansion : vers le haut et vers l'extérieur. La mise à l’échelle signifie acheter plus de matériel. Cela peut coûter cher et le matériel devient rapPrésentation de 7 conseils pour améliorer les performances de MySQLement obsolète. L'évolutivité pour gérer plus de charge présente plusieurs avantages :

1. Peut utiliser des systèmes plus petits et moins coûteux.

2. Grâce à l'expansion horizontale, l'expansion linéaire est plus rapPrésentation de 7 conseils pour améliorer les performances de MySQLe et plus facile.

3. Étant donné que la base de données est distribuée sur plusieurs machines physiques, la base de données ne sera pas affectée par un seul point de défaillance matérielle.

Bien que la mise à l'échelle horizontale présente des avantages, elle présente également certaines limites. La mise à l'échelle nécessite une réplication, telle que la réplication MySQL de base ou le cluster Percona XtraDB, pour la synchronisation des données. Mais en retour, vous bénéficiez de performances supplémentaires et d’une haute disponibilité. Si vous avez besoin d'une plus grande mise à l'échelle, utilisez le partitionnement MySQL.

Vous devez également vous assurer que les applications connectées à l'architecture du cluster peuvent trouver les données dont elles ont besoin – généralement via un serveur proxy et un équilibreur de charge (tels que ProxySQL ou HAProxy).

Lorsque vous planifiez une mise à l'échelle, évitez de la faire trop tôt. Travailler avec des bases de données distribuées a tendance à être plus complexe. Le matériel moderne et les serveurs MySQL permettent une bonne expérience en utilisant un seul serveur. La version candPrésentation de 7 conseils pour améliorer les performances de MySQLate de MySQL 8 récemment publiée montre qu'elle peut gérer plus de 2 millions de requêtes simples sur un seul système.

Conseil d'optimisation n°7 : Observabilité

Les meilleurs systèmes sont conçus dans un souci d'observabilité - MySQL ne fait pas exception.

Une fois que votre environnement MySQL est opérationnel et correctement réglé, vous ne pouvez pas simplement le configurer et ne pas le gérer. Les environnements de base de données peuvent être affectés par les modifications du système ou de la charge de travail. Préparez-vous à des surprises telles que des pics de trafic, des erreurs d'application et des pannes MySQL. Ces choses peuvent arriver et arriveront.

Lorsque des problèmes surviennent, vous devez les résoudre rapPrésentation de 7 conseils pour améliorer les performances de MySQLement et efficacement. La seule façon d’y parvenir est de mettre en place une sorte de solution de surveillance et de l’initialiser de manière appropriée. Cela vous permet de voir ce qui se passe dans votre environnement de base de données pendant son exécution en production et d'analyser les données du serveur lorsque des problèmes surviennent. Idéalement, un système vous permet de prévenir les problèmes avant qu’ils ne surviennent ou avant qu’ils ne se développent au point où les utilisateurs peuvent voir leur impact.

Les outils de surveillance incluent MySQL Enterprise Monitor, Monyog et Percona Monitoring and Management (PMM), ce dernier ayant l'avantage supplémentaire d'être gratuit et open source. Ces outils offrent une grande opérabilité pour la surveillance et le dépannage.

Alors que de plus en plus d'entreprises se tournent vers des bases de données open source (en particulier MySQL) pour gérer et servir leurs données commerciales dans des environnements de production à grande échelle, elles devront se concentrer sur l'optimisation et le fonctionnement optimal de ces bases de données. Comme pour tout ce qui est essentiel à vos objectifs commerciaux, les performances de votre base de données peuvent faire ou défaire vos objectifs ou résultats commerciaux. MySQL est une solution de base de données qui peut fournir une solution de base de données de qualité pour les applications et les sites Web, mais elle doit être adaptée à vos besoins et surveillée pour détecter et éviter les goulots d'étranglement et les problèmes de performances.

Peter Zaitsev est le co-fondateur et PDG de Percona, un fournisseur de solutions et de services MySQL et MongoDB d'entreprise. "High Performance MySQL" publié par O'Reilly est l'un des livres sur les performances MySQL les plus populaires. Zaitsev blogue fréquemment sur PerconaDatabasePerformanceBlog.com et prend la parole lors de conférences à travers le monde.

Cet article détaille 7 conseils pour améliorer les performances de MySQL. Pour plus de contenu connexe, veuillez faire attention au site Web php chinois.

Recommandations associées :

Explication détaillée de l'Présentation de 7 conseils pour améliorer les performances de MySQLentification de la version PostgreSQL

Expliquez ce que sont B/S et C/S.

Comment implémenter un menu vertical via CSS3+html5

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!