Conseils d'optimisation des performances pour MySQL
L'optimisation des performances est un aspect essentiel de la gestion des bases de données. Vous pouvez optimiser les performances à chaque étape de la gestion de la base de données, en commençant par choisir les composants matériels et logiciels permettant d'héberger les serveurs de base de données, mais aussi la conception de modèles de données et la configuration de schémas. Ce document fournit des conseils d'optimisation des performances pour les bases de données MySQL dans le cloud, en particulier Cloud SQL pour MySQL, y compris les bonnes pratiques pour instancier de nouvelles bases de données et pour optimiser les bases de donnnées existantes.
Articles associés
Remarques concernant le matériel
Les configurations matérielles sont un élément important à prendre en compte pour les performances de la base de données. Il est important de bien comprendre le nombre d'utilisateurs actifs et simultanés d'une application, la taille de la base de données et des index, ainsi que la latence attendue de votre application ou service, avant de définir les configurations matérielles. Voici quelques-uns des points importants à prendre en compte concernant le matériel :
Processeur (CPU)
La puissance de traitement est l'un des facteurs les plus importants d'un système de base de données performant. Le nombre de connexions/utilisateurs/threads simultanés détermine le nombre de cœurs requis pour traiter les requêtes de base de données. Le processeur alloué à la base de données doit être capable de gérer une charge de travail normale + une charge de travail maximale (extrême) pour que les applications fonctionnent à des niveaux optimaux.
Dans le cas de Cloud SQL (offre MySQL entièrement gérée de Google Cloud), le processeur est alloué sous la forme d'un processeur virtuel (vCPU). Au final, le nombre de processeurs virtuels alloués à une base de données détermine la quantité de mémoire et le débit du réseau pour une instance de base de données, car chaque processeur virtuel dispose d'une quantité maximale de mémoire et même le débit du réseau varie en fonction du nombre de processeurs virtuels. Cloud SQL offre une flexibilité qui permet de faire évoluer le nombre de processeurs virtuels de votre instance. Il est ainsi plus facile de répondre aux exigences de votre application en termes de mémoire et de débit de réseau.
Memory
Il est important de déterminer la quantité de mémoire à allouer à une base de données en vous assurant que l'ensemble de travail convient pour le pool de mémoire tampon. Un ensemble de travail correspond aux données activement utilisées à tout moment par la base de données. La mémoire allouée doit être suffisante pour contenir cet ensemble de travail ou les données fréquemment utilisées, qui sont généralement constituées de données de base de données, d'index, de tampons de session, du cache de dictionnaire et de tables de hachage. Pour vérifier si la mémoire est suffisamment allouée, vous pouvez vérifier l'état des lectures du disque dans la base de données. Idéalement, les lectures de disque doivent être inférieures ou très minimales dans des conditions de charge de travail normales.
En cas d'allocation de mémoire insuffisante pour l'instance, celle-ci peut rencontrer des problèmes d'insuffisance de mémoire qui entraîneront le redémarrage de l'instance de base de données et causeront des interruptions des bases de données ou des applications.
Stockage
Le stockage de base de données est un autre composant qui joue un rôle important dans l'optimisation des performances. Cloud SQL propose deux types de stockage
- SSD (par défaut)
- HDD
Les disques SSD offrent de bien meilleures performances et un meilleur débit que les disques durs. Nous avons donc toujours choisi les disques SSD pour améliorer les performances, en particulier pour les charges de travail de production.
Les opérations d'entrée/de sortie par seconde (IOP) en lecture et écriture qui sont allouées à l'instance dépendent du volume d'espace de stockage alloué lors de la création de l'instance. Plus la taille du disque est élevée, plus les IOPS en lecture et en écriture sont nombreuses. Par conséquent, il est recommandé de créer des instances de données de plus grande taille pour de meilleures performances d'IOPs. La capture d'écran suivante de la console Google Cloud présente la synthèse des ressources (y compris la capacité maximale) allouées à l'instance de base de données au moment de la création. Elle permet aux utilisateurs de vérifier et de comprendre précisément comment leur base de données va être configurée une fois instanciée.
Région
L'un des moyens de réduire la latence du réseau consiste à choisir la région d'instance la plus proche de l'application ou du service. Cloud SQL pour MySQL est disponible dans toutes les régions Google Cloud, ce qui permet aux utilisateurs d'instancier une base de données aussi près que possible des utilisateurs finaux.
Scaling élastique
Cloud SQL offre un moyen simple d'augmenter ou de réduire les ressources (processeur, mémoire ou stockage) attribuées à une instance de base de données. Cela peut être utile pour les charges de travail ayant des besoins en ressources variables. Par exemple, les utilisateurs peuvent augmenter (scaling à la hausse) les ressources au cours de la période pendant laquelle la charge de travail augmente, puis les réduire lorsque le pic de charge de travail est terminé.
Configurations MySQL
Cette section présente les bonnes pratiques de configuration des bases de données MySQL permettant d'améliorer les performances.
Version
Choisissez la dernière version de MySQL lorsque vous créez une base de données. Les dernières versions comportent des corrections de bugs et des optimisations pour améliorer les performances par rapport aux anciennes versions. Cloud SQL fournit la dernière version de MySQL disponible sur le marché et en fait la version par défaut lors de la création d'une base de données. Obtenez plus d'informations sur les versions MySQL compatibles avec Cloud SQL.
Taille du pool de mémoire tampon InnoDB
Pour les instances MySQL, InnoDB est le seul moteur de stockage compatible. La taille du pool de mémoire tampon Innodb est le premier paramètre qu'un utilisateur doit définir pour des performances optimales. Le pool de mémoire tampon est la zone de mémoire qui est allouée au stockage des caches de table, des caches d'index, des données modifiées avant le vidage et d'autres structures internes telles que l'AHI (Adaptive Hash Index).
Cloud SQL définit la valeur par défaut d'environ (72 %) de la mémoire de l'instance à allouer au pool de mémoire tampon InnoDB, en fonction de la taille de l'instance (les valeurs par défaut varient en fonction de la taille des instances). En savoir plus sur les paramètres du pool de mémoire tampon selon différentes tailles d'instances. Cloud SQL offre la flexibilité nécessaire pour modifier la taille du pool de mémoire tampon en fonction des besoins de vos applications à l'aide d'options de base de données.
La taille du pool de mémoire tampon doit être suffisante pour que la mémoire disponible sur l'instance soit suffisante pour le tampon de session, le cache du dictionnaire et les tables performance_schema (si cette option est activée), en dehors du pool de mémoire tampon InnoDB.
Les utilisateurs peuvent comparer les lectures de disques à partir de l'instance pour déterminer la quantité de données lues à partir des disques par rapport aux lectures satisfaites à partir du pool de mémoire tampon. Si le nombre de lectures de disques est plus important, l'augmentation de la taille du pool de mémoire tampon et de la mémoire de l'instance améliorera les performances des requêtes de lecture.
Taille du fichier journal de rétablissement/InnoDB
Le fichier journal InnoDB ou journal de rétablissement enregistre les modifications des données de la table. La taille du fichier journal InnoDB définit la taille du fichier journal de rétablissement unique.
Pour les charges de travail lourdes en écriture ayant une taille de journal de rétablissement plus élevée, il y a plus d'espace pour les écritures sans avoir à effectuer une activité de vidage fréquente des points de contrôle et à enregistrer les E/S de disque, ce qui améliore les performances d'écriture. La taille totale du journal de rétablissement, qui peut être calculée comme suit : (innodb_log_file_size * innodb_log_files_in_group) doit être suffisante pour au moins 1 à 2 heures de données en écriture pendant les périodes chargées d'accès à la base de données.
Cloud SQL définit une valeur par défaut de 512 Mo. Cloud SQL offre également la flexibilité nécessaire pour augmenter la taille du fichier journal InnoDB à l'aide d'options de base de données.
REMARQUE : L'augmentation de la valeur de la taille du fichier journal InnoDB augmente le temps de récupération après plantage.
Durabilité
L'option innodb_flush_log_at_trx_commit contrôle la fréquence à laquelle les données de journal sont vidées sur le disque et indique s'il faut les vider à chaque commit de transaction.
Vous pouvez augmenter les performances en écriture sur les instances répliquées avec accès en lecture en définissant les valeurs de innodb_flush_log_at_trx_commit sur 0 ou 2.
Cloud SQL ne permet pas de modifier le paramètre de durabilité sur l'instance principale Cloud SQL. Cloud SQL autorise toutefois la modification de l'option sur les instances répliquées avec accès en lecture. Réduire la durabilité des instances répliquées avec accès en lecture améliore les performances en écriture sur ces instances. Cela permet de réduire le délai de réplication sur les instances répliquées. En savoir plus sur innodb_flush_log_at_trx_commit
Taille de la mémoire tampon du journal InnoDB
La taille de la mémoire tampon du journal InnoDB correspond à la quantité de mémoire tampon utilisée par InnoDB pour écrire dans le fichier journal (journal de rétablissement).
Si les transactions (insertions, mises à jour ou suppressions) de la base de données sont volumineuses et que la mémoire tampon utilise plus de 16 Mo, InnoDB doit effectuer une E/S de disque avant de valider la transaction, ce qui affecte les performances. Pour éviter les E/S de disque, augmentez la valeur de innodb_log_buffer_size.
Cloud SQL définit une valeur par défaut de 16 Mo pour la taille de la mémoire tampon du journal InnoDB. La variable d'état MySQL innodb_log_waits indique le nombre de fois où la valeur innodb_log_buffer_size était faible et où InnoDB a dû attendre que le vidage soit effectué avant de valider la transaction. Si la valeur de innodb_log_waits est supérieure à 0 et augmente, augmentez la valeur deinnodb_log_buffer_size à l'aide des options de base de données pour de meilleures performances. Vous pouvez identifier la valeur des champs innodb_log_buffer_size et innodb_log_waits en exécutant les requêtes suivantes dans le shell MySQL (CLI). Ces requêtes montrent la valeur des variables d'état et des variables globales dans MySQL.
SHOW GLOBAL VARIABLES LIKE 'innodb_log_buffer_size';
SHOW GLOBAL STATUS LIKE 'innodb_log_waits';
Capacité d'E/S d'InnoDB
La capacité d'E/S d'InnoDB définit le nombre d'IOPS disponibles pour les tâches en arrière-plan (comme le vidage de la page du pool de mémoire tampon et la fusion des données à partir de la mémoire tampon de modification).
Cloud SQL définit les valeurs par défaut de 5 000 pour innodb_io_capacity et de 10 000 pour innodb_io_capacity_max.
Ces valeurs par défaut sont optimales pour la plupart des charges de travail. Toutefois, si votre charge de travail est lourde en écriture ou si les modifications non appliquées sur l'instance sont élevées, et si vous disposez de suffisamment d'IOPS disponibles sur l'instance, envisagez d'augmenter le nombre les valeurs de innodb_io_capacity et innodb_io_capacity_max. La valeur des modifications appliquées est obtenue à l'aide de la requête suivante dans le shell MySQL :
mysql -e 'show engine InnoDB status \G;' | grep Ibuf
Tampons de session
Les tampons de session correspondent à la mémoire allouée à des sessions individuelles. Si votre application ou vos requêtes incluent un grand nombre d'opérations d'insertion, de mise à jour, de tri, de jointure et que vous avez besoin de tampons plus élevés, vous pouvez définir des valeurs de tampon élevées lors de l'exécution de la requête dans une session spécifique pour éviter de surcharger les performances. Les utilisateurs peuvent éviter une allocation excessive de mémoire tampon à des niveaux mondiaux, ce qui augmente les valeurs pour toutes les connexions et, par conséquent, augmente l'utilisation totale de la mémoire de l'instance. La modification de la valeur par défaut pour les tampons suivants contribue à améliorer les performances des requêtes. Ces valeurs peuvent être modifiées à l'aide des options de base de données.
Notez qu'il s'agit de valeurs de tampon par session. L'augmentation des limites peut affecter toutes les connexions et, à terme, entraîner une hausse de l'utilisation de la mémoire globale.
Table_open_cache et Table_definition_cache
Si l'instance de base de données contient trop de tables (dépasse les milliers) (dans une ou plusieurs bases de données), augmentez les valeurs de table_open_cache et table_definition_cache pour améliorer la vitesse d'ouverture des tables.
Table_definition_cache accélère l'ouverture des tables et ne comporte qu'une seule entrée par table. Le cache de définition de table prend moins d'espace et n'utilise pas de descripteur de fichier. Si le nombre d'instances de table dans le cache d'objets de dictionnaire dépasse la limite de table_definition_cache, un mécanisme LRU commence à marquer des instances de table à évincer et les supprime du cache des objets du dictionnaire pour faire de la place à la nouvelle définition de table. Ce processus est effectué chaque fois qu'un nouvel espace de table est ouvert. Seuls les espaces de table inactifs sont fermés. Ce processus d'éviction ralentit l'ouverture des tables.
Table_open_cache définit le nombre de tables ouvertes pour tous les threads. Vous pouvez vérifier si vous devez augmenter le cache de la table en consultant la variable d'état Opened_tables. Si la valeur de Opened_tables est élevée et que vous n'utilisez pas FLUSHTABLES, envisagez d'augmenter la valeur de la variable table_open_cache.
Les variables Table_open_cache et table_definition_cache peuvent être définies sur le nombre réel de tables dans l'instance.. En savoir plus sur l'outil de recommandation Cloud SQL pour nombre élevé de tables ouvertes.
Remarque : Cloud SQL offre une flexibilité pour modifier ces valeurs.
Recommandations concernant les schémas
Toujours définir les clés primaires
La définition des clés primaires dans la table permet d'organiser physiquement les données de manière à faciliter la recherche, la récupération et le tri des enregistrements, et ainsi à améliorer les performances.
De préférence, les clés primaires incrémentées automatiquement par valeur entière sont idéales pour les systèmes OLTP.
L'absence de clés primaires est également l'une des principales raisons du retard de réplication ou du délai de réplication dans les scénarios de réplication basés sur les lignes.
Créer des index
La création d'index permet d'accélérer la récupération des données et d'améliorer les performances des requêtes de lecture. Créez des index pour les colonnes utilisées dans les clauses WHERE, ORDER BY et GROUP BY des requêtes.
REMARQUE :Un nombre trop important d'index ou des index inutilisés peuvent également nuire aux performances de la base de données.
Bonnes pratiques pour l'optimisation des performances
Effectuer des analyses comparatives
Effectuez des tests de performance ou des analyses comparatives pour voir si la configuration est optimale. Vous pouvez aussi l'améliorer en ajustant les configurations en termes de matériel, de base de données MySQL ou de conception de schéma. Modifiez un paramètre à la fois et comparez-le aux résultats de l'analyse comparative pour voir s'il y a une amélioration.
Regroupement de connexions
Le regroupement de connexions est une technique de création et de gestion d'un pool de connexions prêtes à être utilisées par tous les processus qui en ont besoin. Le regroupement de connexions peut considérablement améliorer les performances de votre application, tout en réduisant l'utilisation globale des ressources. Découvrez comment gérer les connexions depuis l'application, y compris le nombre de connexions et le délai avant expiration.
Répartir la charge de travail de lecture sur les instances répliquées avec accès en lecture
Les instances répliquées avec accès en lecture (plusieurs, dans la zone) peuvent être utilisées pour décharger la charge de travail de lecture de l'instance principale. Cela réduit la surcharge ou la charge sur l'instance principale, et améliore les performances de l'instance principale. D'autres ressources sont également disponibles pour les requêtes de lecture sur l'instance répliquée avec accès en lecture.
ProxySQL, un proxy MySQL Open Source hautes performances capable d'acheminer des requêtes de base de données, peut servir à effectuer un scaling horizontal de la base de données Cloud SQL pour MySQL.
Éviter les requêtes de longue durée
Les requêtes exécutées pendant plusieurs minutes ou plusieurs heures sont susceptibles de nuire aux performances.
- Les journaux d'annulation permettent de stocker l'ancienne version des lignes modifiées afin d'annuler la transaction et de fournir la lecture cohérente (instantané des données) dans une transaction. Ces journaux d'annulation sont stockés sous forme de listes liées, les versions récentes pointant vers d'anciennes versions, qui pointent elles-mêmes vers des versions plus anciennes, etc. Les transactions de longue durée ont tendance à retarder la suppression définitive des journaux d'annulation et donc à augmenter la liste des journaux d'annulation. InnoDB doit traverser le volume élevé de journaux d'annulation et la longue liste de liens, ce qui réduit les performances.
- Les requêtes de longue durée consomment également des ressources (comme la mémoire, les tampons et les verrous) qui ne sont pas libérées longtemps et qui affectent les autres requêtes en raison d'un manque de ressources.
Éviter les transactions importantes
Un trop grand nombre de modifications d'enregistrements (mise à jour, suppression, insertion) dans une seule transaction contient des ressources (verrous, tampon) pour un trop grand nombre d'enregistrements. Les tampons de journaux risquent de déborder sur les E/S disque. Les requêtes restantes devront attendre que les ressources ou les verrous soient libérés. Cela entraîne un volume excessif de données dans le pool de mémoire tampon, ce qui empêche toute utilisation ultérieure de celui-ci. Un rollback de transactions de ce type dégrade également les performances de la base de données. Pour résoudre ce problème, nous vous recommandons de scinder les transactions importantes en transactions plus petites et plus rapides.
Optimiser les requêtes
Optimisez toujours les requêtes pour obtenir de meilleurs résultats, c'est-à-dire moins de ressources et une exécution plus rapide. Consultez les recommandations concernant l'optimisation des requêtes MySQL.
Outils d'optimisation des performances
Surveillance
Cloud SQL propose des tableaux de bord prédéfinis pour plusieurs produits Google Cloud, y compris un tableau de bord de surveillance Cloud SQL par défaut. Ce tableau de bord permet aux utilisateurs de surveiller l'état général de leurs instances principales et répliquées. Les utilisateurs peuvent également créer leurs propres tableaux de bord personnalisés pour afficher les métriques qui les intéressent. L'utilisation de ces tableaux de bord et métriques permet d'identifier divers goulots d'étranglement qui affectent les performances, comme une utilisation intensive du processeur ou de la mémoire, et de les traiter à l'aide des recommandations mentionnées précédemment. Les alertes peuvent également être configurées en fonction de ces métriques.
Option de requête lente
Vous pouvez activer l'option Requêtes lentes sur l'instance Cloud SQL pour MySQL afin d'identifier les requêtes dont la durée d'exécution est supérieure à long_query_time. Ces requêtes lentes peuvent être analysées plus en détail et ajustées pour améliorer les performances. Découvrez comment activer et vérifier les requêtes lentes pour les instances Cloud SQL.
Schéma des performances
Le schéma des performances fournit une surveillance de bas niveau de l'instance MySQL. Le schéma des performances peut être activé sur une instance Cloud SQL pour MySQL avec une mémoire supérieure à 15 Go. Les rapports de schéma Sys fournissent différents rapports permettant d'identifier les goulots d'étranglement, les temps d'attente, les index manquants, l'utilisation de la mémoire, etc.
Insights sur les requêtes
Lesinsights sur les requêtes sont une fonctionnalité native de Cloud SQL qui permet de profiler des requêtes et de les analyser afin d'améliorer leurs performances. Les insights sur les requêtes offrent une surveillance intuitive et fournissent des informations de diagnostic qui vous aident à aller au-delà de la détection afin d'identifier la cause des problèmes de performances.
Recommandations concernant les performances
L'outil de recommandation Cloud SQL sur le nombre élevé de tables est également une fonctionnalité native de Cloud SQL qui fournit des recommandations de performances aux utilisateurs de Cloud SQL pour améliorer les performances des bases de données existantes. Il fournit aussi des suggestions de configuration afin d'améliorer les performances et de réduire le coût des instances. Pour en savoir plus, consultez les recommandations de Cloud SQL.
Produits et services associés
Google Cloud propose une base de données MySQL gérée conçue pour répondre aux besoins de votre entreprise, de la suppression de votre centre de données sur site à l'exécution d'applications SaaS, en passant par la migration de systèmes d'entreprise principaux.
