Résoudre les problèmes de mémoire insuffisante dans Azure Database pour MySQL – Serveur flexible

S'APPLIQUE À : Azure Database pour MySQL - Serveur unique Azure Database pour MySQL - Serveur flexible

Important

Le serveur unique Azure Database pour MySQL est en voie de mise hors service. Nous vous conseillons vivement de procéder à une mise à niveau vers Azure Database pour MySQL – Serveur flexible. Pour obtenir plus d’informations sur la migration vers Azure Database pour MySQL – Serveur flexible, consultez Qu’en est-il du Serveur unique Azure Database pour MySQL ?

Pour s’assurer qu’une instance d’Azure Database pour MySQL – Serveur flexible fonctionne de manière optimale, il est très important de disposer d’une allocation et d’une utilisation appropriées de la mémoire. Par défaut, lorsque vous créez une instance d’Azure Database pour MySQL – Serveur flexible, la mémoire physique disponible dépend du niveau et de la taille que vous sélectionnez pour votre charge de travail. En outre, la mémoire est allouée pour les mémoires tampons et les caches pour améliorer les opérations de base de données. Pour plus d’informations, consultez Comment MySQL utilise la mémoire.

Notez qu’Azure Database pour MySQL – Serveur flexible consomme de la mémoire pour atteindre le plus d’accès au cache possible. Par conséquent, l'utilisation de la mémoire peut souvent osciller entre 80 et 90 % de la mémoire physique disponible d'une instance. Sauf s’il y a un problème avec la progression de la charge de travail de requête, ce n’est pas une préoccupation. Toutefois, vous pouvez rencontrer des problèmes de mémoire pour des raisons telles que :

• Configuration de mémoires tampons trop volumineuses.
• Requêtes sous-optimales en cours d’exécution.
• Requêtes exécutant des jointures et tri de jeux de données volumineux.
• Définition trop élevée des connexions maximales sur un serveur de base de données.

La majorité de la mémoire d’un serveur est utilisée par les mémoires tampons et caches globaux d’InnoDB, qui incluent des composants tels que innodb_buffer_pool_size, innodb_log_buffer_size, key_buffer_size et query_cache_size.

La valeur du paramètre innodb_buffer_pool_size spécifie la zone de mémoire dans laquelle InnoDB met en cache les tables de base de données et les données liées à l’index. MySQL tente de prendre en charge autant de données de table et d’index dans le pool de mémoires tampons que possible. Un pool de mémoires tampons plus volumineux nécessite moins d’opérations d’E/S en cours de déviation vers le disque.

Surveillance de l'utilisation de la mémoire

Azure Database pour MySQL – Serveur flexible fournit une plage de métriques pour évaluer les performances de votre instance de base de données. Pour mieux comprendre l’utilisation de la mémoire pour votre serveur de base de données, affichez les métriques Pourcentage de mémoire hôte ou Pourcentage de mémoire.

Si vous remarquez que l’utilisation de la mémoire a soudainement augmenté et que la mémoire disponible tombe rapidement, surveillez d’autres métriques, telles que le pourcentage du processeur hôte, les connexions totales et le pourcentage d’E/S, pour déterminer si un pic soudain de la charge de travail est la source du problème.

Il est important de noter que chaque connexion établie avec le serveur de base de données nécessite l’allocation d’une certaine quantité de mémoire. Par conséquent, une augmentation des connexions de base de données peut entraîner des pénuries de mémoire.

Causes de l’utilisation élevée de la mémoire

Examinons d’autres causes d’utilisation élevée de la mémoire dans MySQL. Ces causes dépendent des caractéristiques de la charge de travail.

Augmentation des tables temporaires

MySQL utilise des « tables temporaires », qui sont un type spécial de table conçu pour stocker un jeu de résultats temporaire. Les tables temporaires peuvent être réutilisées plusieurs fois pendant une session. Étant donné que toutes les tables temporaires créées sont locales à une session, différentes sessions peuvent avoir différentes tables temporaires. Dans les systèmes de production avec de nombreuses sessions effectuant des compilations de jeux de résultats temporaires volumineux, vous devez vérifier régulièrement le compteur d’état global created_tmp_tables, qui suit le nombre de tables temporaires créées pendant les heures de pointe. Une quantité élevée de tables temporaires en mémoire peut rapidement entraîner une insuffisance de mémoire disponible dans une instance d’Azure Database pour MySQL – Serveur flexible.

Avec MySQL, la taille de table temporaire est déterminée par les valeurs de deux paramètres, comme décrit dans le tableau suivant.

Paramètre	Description
tmp_table_size	Spécifie la taille maximale des tables temporaires internes en mémoire.
max_heap_table_size	Spécifie la taille maximale à laquelle les tables MEMORY créées par l’utilisateur peuvent croître.

Notes

Lorsque vous déterminez la taille maximale d’une table temporaire en mémoire interne, MySQL considère la valeur inférieure des valeurs définies pour les paramètres tmp_table_size et max_heap_table_size.

Recommandations

Pour résoudre les problèmes de mémoire faible liés aux tables temporaires, tenez compte des recommandations suivantes.

• Avant d’augmenter la valeur tmp_table_size, vérifiez que votre base de données est indexée correctement, en particulier pour les colonnes impliquées dans les jointures et regroupées par opérations. L’utilisation des index appropriés sur les tables sous-jacentes limite le nombre de tables temporaires créées. L’augmentation de la valeur de ce paramètre et du paramètre max_heap_table_size sans vérifier que vos index peuvent autoriser les requêtes inefficaces à s’exécuter sans index et à créer plus de tables temporaires que nécessaire.
• Ajustez les valeurs des paramètres max_heap_table_size et tmp_table_size pour répondre aux besoins de votre charge de travail.
• Si les valeurs que vous définissez pour les paramètres max_heap_table_size et tmp_table_size sont trop faibles, les tables temporaires peuvent régulièrement se déverser vers le stockage, ajoutant ainsi de la latence à vos requêtes. Vous pouvez suivre les tables temporaires qui se déversent sur le disque à l’aide du compteur d’état global created_tmp_disk_tables. En comparant les valeurs des variables created_tmp_disk_tables et created_tmp_tables, vous affichez le nombre de tables temporaires internes sur disque créées au nombre total de tables temporaires internes créées.

Cache de table

En tant que système multithread, MySQL gère un cache de descripteurs de fichiers de table afin que les tables puissent être ouvertes simultanément indépendamment par plusieurs sessions. MySQL utilise une quantité de descripteurs de fichiers de mémoire et de système d’exploitation pour maintenir ce cache de table. La variable table_open_cache définit la taille du cache de table.

Recommandations

Pour résoudre les problèmes de mémoire faible liés au cache de table, tenez compte des recommandations suivantes.

• Le paramètre table_open_cache spécifie le nombre de tables ouvertes pour tous les threads. L’augmentation de cette valeur augmente le nombre de descripteurs de fichiers dont mysqld a besoin. Vous pouvez vérifier si vous devez augmenter le cache de table en vérifiant la variable d’état opened_tables dans le compteur d’état global d’affichage. Augmentez la valeur de ce paramètre par incréments pour prendre en charge votre charge de travail.
• Définir une valeur trop faible pour table_open_cache peut amener Azure Database pour MySQL – Serveur flexible à consacrer davantage de temps à l’ouverture et à la fermeture des tables nécessaires au traitement des requêtes.
• Une valeur trop élevée peut entraîner l'utilisation d'une plus grande quantité de mémoire et l'exécution par le système d'exploitation de descripteurs de fichiers, ce qui entraîne le refus de connexions ou l'échec du traitement des requêtes.

Autres mémoires tampons et le cache de requête

Lors de la résolution des problèmes liés à la mémoire faible, vous pouvez utiliser quelques mémoires tampons supplémentaires et un cache pour faciliter la résolution.

Net buffer (net_buffer_length)

La mémoire tampon nette est dimensionnée pour les mémoires tampons de connexion et de thread pour chaque thread client et peut atteindre la valeur spécifiée pour max_allowed_packet. Si une instruction de requête est volumineuse, par exemple, toutes les insertions/mises à jour ont une valeur très importante, l’augmentation de la valeur du paramètre net_buffer_length permet alors d’améliorer les performances.

Join buffer (join_buffer_size)

La mémoire tampon de jointure est allouée aux lignes de table de cache lorsqu’une jointure ne peut pas utiliser d’index. Si votre base de données comporte de nombreuses jointures effectuées sans index, envisagez d’ajouter des index pour des jointures plus rapides. Si vous ne pouvez pas ajouter d’index, envisagez d’augmenter la valeur du paramètre join_buffer_size, qui spécifie la quantité de mémoire allouée par connexion.

Sort buffer (sort_buffer_size)

La mémoire tampon de tri est utilisée pour effectuer des tris pour certaines requêtes ORDER BY et GROUP BY. Si vous voyez de nombreux Sort_merge_passes par seconde dans la sortie SHOW GLOBAL STATUS, envisagez d’augmenter la valeur sort_buffer_size pour accélérer les opérations ORDER BY ou GROUP BY qui ne peuvent pas être améliorées à l’aide de l’optimisation des requêtes ou d’une meilleure indexation.

Évitez d'augmenter arbitrairement la valeur sort_buffer_size, sauf si vous disposez d'informations connexes qui indiquent le contraire. La mémoire de cette mémoire tampon est affectée par connexion. Dans la documentation MySQL, l’article Variables système serveur spécifie que sur Linux, il existe deux seuils, de 256 Ko et de 2 Mo, et que l’utilisation de valeurs plus élevées peut ralentir considérablement l’allocation de mémoire. Par conséquent, évitez d’augmenter la valeur de sort_buffer_size au-delà de 2 Mo, car la perte en termes de performance l’emportera sur tous les avantages.

Cache de requête (query_cache_size)

Le cache de requête est une zone de mémoire utilisée pour mettre en cache les jeux de résultats de requête. Le paramètre query_cache_size détermine la quantité de mémoire allouée pour mettre en cache les résultats de la requête. Par défaut, le cache des requêtes est désactivé. En outre, le cache de requête est déconseillé dans MySQL version 5.7.20 et supprimé dans MySQL version 8.0. Si le cache de requêtes est actuellement activé dans votre solution, avant de le désactiver, vérifiez qu’il n’y a pas de requêtes qui s’appuient sur celui-ci.

Calcul du taux d’accès au cache de mémoire tampon

Le taux d’accès au cache de mémoire tampon est important dans l’environnement Azure Database pour MySQL – Serveur flexible pour comprendre si le pool de mémoires tampons peut prendre en charge les demandes de charge de travail ou non, et en règle générale, il est recommandé d’avoir toujours un taux d’accès au cache de pool de mémoires tampons supérieur à 99 %.

Pour calculer le taux d’accès au pool de mémoires tampons InnoDB pour les demandes de lecture, vous pouvez exécuter GLOBAL SHOW STATUS pour récupérer les compteurs « Innodb_buffer_pool_read_requests » et « Innodb_buffer_pool_reads », puis calculer la valeur à l’aide de la formule indiquée ci-dessous.

InnoDB Buffer pool hit ratio = Innodb_buffer_pool_read_requests / (Innodb_buffer_pool_read_requests + Innodb_buffer_pool_reads) * 100

Considérez l'exemple suivant.

mysql> show global status like "innodb_buffer_pool_reads";
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| Innodb_buffer_pool_reads | 197   |
+--------------------------+-------+
1 row in set (0.00 sec)

mysql> show global status like "innodb_buffer_pool_read_requests";
+----------------------------------+----------+
| Variable_name                    | Value    |
+----------------------------------+----------+
| Innodb_buffer_pool_read_requests | 22479167 |
+----------------------------------+----------+
1 row in set (0.00 sec)

L’utilisation des valeurs ci-dessus, le calcul du ratio d’accès au pool de mémoires tampons InnoDB pour les demandes de lecture génère le résultat suivant :

InnoDB Buffer pool hit ratio = 22479167/(22479167+197) * 100 

Buffer hit ratio = 99.99%

En plus de sélectionner le taux d’accès au cache de mémoire tampon des instructions, pour toutes les instructions DML, les écritures dans le pool de mémoires tampons InnoDB se produisent en arrière-plan. Toutefois, s’il est nécessaire de lire ou de créer une page et qu’aucune page propre n’est disponible, il est également nécessaire d’attendre que les pages soient vidées en premier.

Le compteur Innodb_buffer_pool_wait_free compte combien de fois cela s’est produit. Une valeur Innodb_buffer_pool_wait_free supérieure à 0 est un indicateur fort que le pool de mémoires tampons InnoDB est trop petit et que l’augmentation de la taille du pool de mémoires tampons ou de la taille d’instance est nécessaire pour prendre en charge les écritures entrant dans la base de données.

Recommandations

• Vérifiez que votre base de données dispose de suffisamment de ressources pour exécuter vos requêtes. Parfois, vous devrez peut-être mettre à l’échelle la taille de l’instance pour obtenir plus de mémoire physique afin que les mémoires tampons et les caches puissent prendre en charge votre charge de travail.
• Évitez les transactions volumineuses et les transactions durables en les décomposant en transactions plus petites.
• Utilisez des alertes « Pourcentage de la mémoire hôte » pour obtenir des notifications si le système dépasse l’un des seuils spécifiés.
• Utilisez des Query Performance Insights ou des classeurs Azure pour identifier les requêtes problématiques et les requêtes lentes, puis les optimiser.
• Pour les serveurs de base de données de production, collectez des diagnostics à intervalles réguliers pour vérifier que tout s’exécute correctement. Si ce n’est pas le cas, résolvez les problèmes que vous identifiez.

Étapes suivantes

Pour trouver des réponses de pairs aux questions qui vous préoccupent le plus, ou pour poster une question ou répondre à une question, visitez le forum Stack Overflow.