Les bases de données jouent un rôle essentiel dans les performances de n'importe quelle application. La base de données MySQL ne fait pas exception. Il est donc important de comprendre les nombreuses façons dont le réglage, la conception et la configuration des bases de données peuvent vous aider à améliorer les performances d'une application. Vous trouverez ci-dessous quelques méthodes pour optimiser MySQL afin d'obtenir des performances optimales.
Optimiser les requêtes
L'optimisation des performances de la base de données débute à partir de l'application. La manière dont l'application traduit les exigences métier en requêtes de base de données fait toute la différence en termes de complexité et d'efficacité de l'application. L'évaluation réelle des performances indique dans quelle mesure chaque instance de base de données contribue aux besoins de l'entreprise.
Conception de schémas
La manière dont les entités et les relations sont définies dans une base de données relationnelle détermine la facilité ou la complexité d'une réponse à une requête de base de données. De plus, la façon dont la clé primaire et les index secondaires sont définis joue un rôle important.
Configuration du serveur
La configuration du serveur permet d'optimiser et de maximiser l'utilisation des ressources système. Les ressources système correspondent aux cœurs de processeurs (CPU), à la mémoire sur la machine physique ou la machine virtuelle (VM), au système de stockage sous-jacent et au réseau.
Réglages dynamiques du serveur
Surveillance, optimisation et réglage continus des performances pour adapter les charges de travail des bases de données à la dynamique mondiale réelle.
Cet article se concentre sur le réglage des requêtes. Les autres sujets seront traités dans les articles suivants.
Nous commençons souvent le processus d'ajustement des requêtes en examinant la requête de base de données. Une meilleure façon de procéder serait de commencer par évaluer l'efficacité avec laquelle les exigences métier ont été traduites en une requête de base de données. L'interprétation et le traitement des besoins d'une requête pour une entreprise déterminent si le coût est bas ou élevé.
La première étape consiste à hiérarchiser les requêtes à optimiser :
Ces deux catégories doivent être optimisées lors de la conception du schéma de base de données.
Une transaction est une unité logique dans laquelle toutes les instructions contenues peuvent être entièrement validées ou annulées. La transaction est une fonctionnalité qui assure l'atomicité, la cohérence, l'isolement et la durabilité (ACID) pour MySQL.
Dans InnoDB, le moteur de stockage de MySQL, toutes les activités utilisateur se produisent au sein d'une transaction. Le mode autocommit est activé par défaut, ce qui signifie que chaque instruction SQL constitue une transaction unique. Pour effectuer une transaction à plusieurs instructions lorsque le commit automatique est activé, commencez la transaction explicitement par START_TRANSACTION ou BEGIN, et terminez-la par COMMIT ou ROLLBACK. Si le mode autocommit est désactivé, une transaction est toujours ouverte dans la session jusqu'à ce qu'un COMMIT ou ROLLBACK la termine et en démarre une nouvelle.
En ce qui concerne les transactions, il est recommandé qu'elles soient aussi courtes que possible. En effet, les transactions longues présentent plusieurs inconvénients, comme expliqué plus en détail dans cet article.
Les requêtes peuvent être capturées côté application ou côté base de données.
Il est recommandé de consigner les requêtes de base de données et leur temps d'exécution. La journalisation côté application permet d'évaluer facilement l'efficacité des requêtes dans leur contexte commercial. Par exemple, les utilisateurs peuvent consigner le temps de réponse de chaque requête ou le temps de réponse de certaines fonctionnalités. C'est également un moyen simple d'obtenir la durée d'exécution totale des transactions comportant plusieurs instructions.
De plus, le temps de réponse aux requêtes mesuré à partir de la journalisation côté application est une mesure de bout en bout, incluant le temps réseau. Elle complète le temps d'exécution de la requête consignée à partir de la base de données et permet de déterminer facilement si le problème est lié au réseau ou à la base de données.
Insights sur les requêtes MySQL Cloud SQL
L'outil Insights sur les requêtes de Cloud SQL permet de capturer, de surveiller et de diagnostiquer les requêtes.
L'outil Insights sur les requêtes permet de trouver facilement les requêtes les plus fréquentes en fonction de la durée et de la fréquence d'exécution.
L'outil propose des options de filtrage telles que la période, la base de données, le compte utilisateur et l'adresse du client. Il comporte des graphiques illustrant l'utilisation du processeur, ainsi que la répartition des E/S et des temps d'attente de verrouillage. La table "Requêtes et tags les plus fréquents" liste les requêtes les plus fréquentes par temps d'exécution, les requêtes étant normalisées. Outre la durée d'exécution, cette métrique inclut des statistiques sur les "lignes moyennes analysées" et les "lignes moyennes renvoyées" qui fournissent des insights sur l'efficacité des requêtes.
Veuillez consulter la documentation pour découvrir toutes les fonctionnalités proposées et comment les activer.
Utiliser performance_schema
Sur Cloud SQL pour MySQL, la fonctionnalité performance_schema est activée par défaut pour les versions MySQL 8.0 et ultérieures avec au moins 15 Go de mémoire. Son activation ou sa désactivation nécessite le redémarrage de l'instance.
Lorsque performance_schema est activé, les instruments d'instruction de requête sont activés par défaut. La table sys.statement_analysis fournit des statistiques agrégées pour les requêtes normalisées. Elle répond à des questions telles que :
Si vous utilisez MySQL Workbench, les rapports sur le schéma des performances sont basés sur la vue système. Le rapport comporte une section "Instructions SQL à coût élevé" qui fournit des informations sur les performances des requêtes.
Utiliser un journal lent et des outils
Le journal lent capture toutes les requêtes qui s'exécutent au-delà de la valeur long_query_time. Il consigne également le temps d'exécution des requêtes, le temps de verrouillage, ainsi que les lignes de données examinées et envoyées. Les statistiques d'exécution supplémentaires en font un candidat idéal pour analyser les requêtes de base de données plutôt que d'utiliser le journal général.
Il est recommandé d'activer les journaux lents. Normalement, la valeur long_query_time doit rester à un seuil raisonnable pour capturer les requêtes que vous avez l'intention d'examiner et d'optimiser.
log_output=FILE
slow_query_log=ON
long_query_time=2
De temps à autre, il peut être judicieux de définir la valeur long_query_time sur 0 afin de capturer toutes les requêtes pendant une courte période et d'obtenir un aperçu du volume de requêtes et des performances.
Des outils, tels que mysqldumpslow et pt-query-digest, extraient les signatures de requête et génèrent un rapport pour afficher les statistiques des requêtes.
D'autres outils de surveillance tiers génèrent des rapports sur les statistiques de requête, par exemple : Surveillance et gestion de Percona, Contrôle des performances de base de données de SolarWinds (anciennement VividCortex), etc.
Après avoir capturé les requêtes dans les transactions, l'étape suivante consiste à les optimiser.
La commande EXPLAIN fournit le plan d'exécution de la requête. À partir de la version 8.0.18, la commande EXPLAIN ANALYZE exécute une instruction et génère une sortie EXPLAIN ainsi que la durée de l'exécution.
L'insight sur les requêtes MySQL permet d'accéder facilement au plan EXPLAIN.
Que recherchons-nous dans le résultat ?
Les variables d'état de session peuvent être utilisées pour obtenir les détails de l'exécution de la requête.
Commencez par effacer les variables de session, puis exécutez la requête et examinez les compteurs. Par exemple, l'état "Handler_*" indique le format d'accès aux données et le nombre de lignes. Created_* indiquerait si une table temporaire et/ou une table temporaire sur le disque sont créées. Sort_* afficherait le nombre de tentatives de fusion de tri et le nombre de lignes triées. Vous trouverez des explications sur d'autres variables de session dans la documentation.
L'instruction SHOW PROFILE indique la durée d'exécution de la requête par étape d'exécution, ce qui peut également fournir des informations utiles.
Une fois que vous avez compris le plan d'exécution des requêtes, vous pouvez le modifier et l'optimiser de plusieurs manières.
Afin d'optimiser la configuration du serveur pour certaines requêtes, il est fortement recommandé d'utiliser les variables au niveau de la session plutôt que de modifier la valeur globale qui affecte toutes les sessions.
Les valeurs de session fréquemment utilisées sont les suivantes :
En résumé, nous avons abordé trois aspects concernant l'optimisation des requêtes :
Profitez de 300 $ de crédits gratuits et de plus de 20 produits Always Free pour commencer à créer des applications sur Google Cloud.