Optimiser la consommation de mémoire élevée dans les instances

Preview

Cette fonctionnalité est soumise aux "Conditions des offres de pré-DG" dans la section "Conditions d'utilisation générales" des Conditions spécifiques au service. Les fonctionnalités pré-DG sont disponibles "en l'état" et peuvent avoir une assistance limitée. Pour en savoir plus, consultez les descriptions des étapes de lancement.

Il s'agit d'un problème courant lorsque les instances consomment beaucoup de mémoire ou rencontrent des problèmes de mémoire saturée (OOM, Out Of Memory). Une instance de base de données exécutée avec une utilisation élevée de la mémoire entraîne souvent des problèmes de performances, des blocages ou même des temps d'arrêt de la base de données.

Certains blocs de mémoire MySQL sont utilisés dans le monde entier. Cela signifie que toutes les charges de travail de requête partagent des emplacements de mémoire, sont occupées en permanence et ne sont libérées que lorsque le processus MySQL s'arrête. Certains blocs de mémoire sont basés sur une session : dès que la session est fermée, la mémoire utilisée par cette session est également libérée pour le système.

Chaque fois qu'une instance Cloud SQL pour MySQL utilise une quantité de mémoire élevée, Cloud SQL vous recommande d'identifier la requête ou le processus qui utilise beaucoup de mémoire et de la libérer. La consommation de mémoire MySQL est divisée en trois parties principales :

• Consommation de mémoire des threads et processus
• Consommation de mémoire tampon
• Consommation de mémoire cache

Consommation de mémoire des threads et processus

Chaque session utilisateur consomme de la mémoire en fonction des requêtes en cours d'exécution, des tampons ou du cache utilisés par cette session. Elle est contrôlée par les paramètres de session de MySQL. Les principaux paramètres sont les suivants :

• thread_stack
• net_buffer_length
• read_buffer_size
• read_rnd_buffer_size
• sort_buffer_size
• join_buffer_size
• max_heap_table_size
• tmp_table_size

Pour N nombre de requêtes exécutées à un moment donné, chaque requête consomme de la mémoire en fonction de ces paramètres pendant la session.

Consommation de mémoire tampon

Cette partie de la mémoire est commune à toutes les requêtes et est contrôlée par des paramètres tels que Innodb_buffer_pool_size, Innodb_log_buffer_size et key_buffer_size.

Consommation de mémoire cache

La mémoire cache inclut un cache de requêtes, qui permet d'enregistrer les requêtes et leurs résultats pour une récupération plus rapide des données des mêmes requêtes ultérieures. Elle inclut également le cache binlog pour conserver les modifications apportées au journal binaire pendant l'exécution de la transaction. Elle est contrôlé par binlog_cache_size.

Autre consommation de mémoire

La mémoire est également utilisée par les opérations de jointure et de tri. Si vos requêtes utilisent des opérations de jointure ou de tri, elles utilisent la mémoire en fonction de join_buffer_size et sort_buffer_size.

En dehors de cela, le schéma de performances, si vous l'activez, consomme de la mémoire. Pour vérifier l'utilisation de la mémoire par le schéma de performances, utilisez la requête suivante :

SELECT *
FROM
  performance_schema.memory_summary_global_by_event_name
WHERE EVENT_NAME LIKE 'memory/performance_schema/%';

De nombreux outils disponibles dans MySQL vous permettent de configurer l'utilisation de la mémoire via le schéma de performances. Pour en savoir plus, consultez la documentation MySQL.

Le paramètre associé à MyISAM pour l'insertion groupée de données est bulk_insert_buffer_size.

Pour savoir comment MySQL utilise la mémoire, consultez la documentation MySQL.

Recommandations

Utiliser l'explorateur de métriques pour identifier l'utilisation de la mémoire

Vous pouvez examiner l'utilisation de la mémoire d'une instance avec la métrique database/memory/components.usage dans l'explorateur de métriques.

Si vous avez moins de 5% de mémoire dans database/memory/components.cache et database/memory/components.free combinés, le risque d'événement OOM est élevé. Pour surveiller l'utilisation de la mémoire et éviter les événements OOM, nous vous recommandons de configurer une règle d'alerte avec une condition de seuil de métrique de 95% ou plus dans database/memory/components.usage.

Le tableau suivant montre la relation entre la mémoire de votre instance et le seuil d'alerte recommandé:

Mémoire de l'instance	Seuil d'alerte recommandé
Jusqu'à 100 Go	95 %
De 100 Go à 200 Go	96 %
De 200 Go à 300 Go	97 %
Plus de 300 Go	98 %

Calculer la consommation de mémoire

Calculez l'utilisation maximale de la mémoire par votre base de données MySQL et sélectionnez le type d'instance approprié pour votre base de données MySQL. Utilisez la formule suivante :

Utilisation maximale de la mémoire MySQL = innodb_buffer_pool_size + innodb_additional_mem_pool_size + innodb_log_buffer_size + tmp_table_size + key_buffer_size + ((read_buffer_size + read_rnd_buffer_size + sort_buffer_size + join_buffer_size) x max_connections)

Voici les paramètres utilisés dans la formule :

• innodb_buffer_pool_size : taille en octets du pool de mémoire tampon, la zone de mémoire dans laquelle InnoDB met en cache les données de table et d'index.
• innodb_additional_mem_pool_size : taille en octets d'un pool de mémoire utilisé par InnoDB pour stocker les informations du dictionnaire de données et d'autres structures de données internes.
• innodb_log_buffer_size : taille en octets du tampon utilisé par InnoDB pour écrire dans les fichiers journaux sur le disque.
• tmp_table_size : taille maximale des tables temporaires internes en mémoire créées par le moteur de stockage MEMORY et, à partir de MySQL 8.0.28, le moteur de stockage TempTable.
• Key_buffer_size : taille du tampon utilisé pour les blocs d'index. Les blocs d'index pour les tables MyISAM sont mis en mémoire tampon et sont partagés par tous les threads.
• Read_buffer_size : chaque thread qui effectue une analyse séquentielle pour une table MyISAM attribue une mémoire tampon de cette taille (en octets) à chaque table analysée.
• Read_rnd_buffer_size : cette variable est utilisée pour les lectures de tables MyISAM, pour tout moteur de stockage et pour l'optimisation de la lecture multiplage.
• Sort_buffer_size : chaque session devant effectuer un tri alloue un tampon de cette taille. Le paramètre sort_buffer_size n'est spécifique à aucun moteur de stockage et s'applique de manière générale pour l'optimisation.
• Join_buffer_size : taille minimale du tampon utilisé pour les analyses d'index de base, les analyses de plage et les jointures qui n'utilisent pas d'index et effectuent donc des analyses complètes de table.
• Max_connections : nombre maximal de connexions client simultanées autorisé.

Remarque : De nombreux autres petits composants consomment de la mémoire, qui dépend des requêtes. Il doit donc y avoir également de la place pour ces composants.

Résoudre les problèmes de consommation élevée de mémoire

• Exécutez SHOW PROCESSLIST pour afficher les requêtes en cours qui consomment actuellement de la mémoire. La commande affiche tous les threads connectés et leurs instructions SQL en cours d'exécution et tente de les optimiser. Examinez attentivement les colonnes d'état et de durée.

  mysql> SHOW [FULL] PROCESSLIST;
  

• Consultez SHOW ENGINE INNODB STATUS dans la section BUFFER POOL AND MEMORY pour afficher l'utilisation actuelle du pool de mémoire tampon et de la mémoire, ce qui peut vous aider à définir la taille de votre pool de mémoire tampon.

  mysql> SHOW ENGINE INNODB STATUS \G
  ----------------------
  BUFFER POOL AND MEMORY
  ----------------------
  Total memory allocated 398063986; in additional pool allocated 0
  Dictionary memory allocated 12056
  Buffer pool size 89129
  Free buffers 45671
  Database pages 1367
  Old database pages 0
  Modified db pages 0
  

• Utilisez la commande SHOW variables de MySQL pour vérifier les valeurs des compteurs, qui fournissent des informations comme le nombre de tables temporaires, le nombre de threads, le nombre de caches de table, les pages modifiées, les tables ouvertes et l'utilisation du pool de mémoire tampon.

  mysql> SHOW variables like 'VARIABLE_NAME'
  

Appliquer les modifications

Après avoir analysé l'utilisation de la mémoire par différents composants, définissez l'option appropriée dans votre base de données MySQL. Pour modifier l'option dans une instance Cloud SQL pour MySQL, vous pouvez utiliser la console Google Cloud ou gcloud CLI. Pour modifier la valeur de l'option à l'aide de la console Google Cloud, modifiez la section Options, sélectionnez l'option et saisissez la nouvelle valeur.

Enfin, si l'utilisation de la mémoire est encore élevée et que vous estimez que l'exécution de requêtes et les valeurs des options sont optimisées, envisagez d'augmenter la taille de l'instance pour éviter les problèmes OOM.