Il s'agit d'un problème courant lorsque les instances consomment beaucoup de mémoire ou rencontrent des événements 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. Voici les principaux paramètres :
thread_stacknet_buffer_lengthread_buffer_sizeread_rnd_buffer_sizesort_buffer_sizejoin_buffer_sizemax_heap_table_sizetmp_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.
Le pool de mémoire tampon InnoDB, configuré par le flag innodb_buffer_pool_size, occupe une quantité importante de mémoire sur votre instance Cloud SQL pour MySQL et sert de cache pour améliorer les performances. Pour réduire le risque d'événements de mémoire saturée (OOM, Out Of Memory), vous pouvez activer le pool de mémoire tampon géré.
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 instruments 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
Les sections suivantes proposent quelques recommandations pour une utilisation optimale de la mémoire.
Activer le pool de mémoire tampon géré
L'activation du pool de mémoire tampon géré vous aide à réduire la consommation de mémoire du pool de mémoire tampon InnoDB (ou innodb_buffer_pool_size) lorsque la mémoire de l'instance est élevée.
Cette réduction libère de la mémoire que d'autres processus de base de données peuvent ensuite consommer.
Si l'utilisation de la mémoire de votre instance est élevée, elle peut rencontrer des événements de mémoire saturée (OOM, Out Of Memory). Nous vous recommandons d'activer le pool de mémoire tampon géré sur votre instance pour éviter les événements OOM.
Lorsque l'utilisation de la mémoire se stabilise à une valeur inférieure pendant 10 minutes ou plus, MySQL augmente progressivement la valeur de innodb_buffer_pool_size jusqu'à sa valeur d'origine. Vous pouvez également augmenter la valeur de l'option innodb_buffer_pool_size à la valeur de votre choix une fois que l'utilisation de la mémoire s'est stabilisée.
Critères d'éligibilité
Vous ne pouvez pas activer le pool de mémoire tampon géré pour les instances à cœur partagé, ni pour MySQL 5.6 ou MySQL 5.7.
Activer la fonctionnalité
Pour activer le pool de mémoire tampon géré pour votre instance, définissez le flag innodb_cloudsql_managed_buffer_pool sur on. Pour en savoir plus sur la définition des options de base de données, consultez Définir une option de base de données.
La modification de la valeur de l'option innodb_cloudsql_managed_buffer_pool ne nécessite pas de redémarrage de l'instance Cloud SQL.
Si vous avez activé le pool de mémoire tampon géré et que la consommation de mémoire de votre instance dépasse le pourcentage seuil par défaut de sa mémoire allouée, Cloud SQL commence à réduire la taille de son innodb_buffer_pool_size.
Ce pourcentage de seuil par défaut varie entre 90% et 97% en fonction de la capacité de RAM de votre instance. Pour modifier le seuil, définissez l'indicateur innodb_cloudsql_managed_buffer_pool_threshold_pct sur une autre valeur de pourcentage. Par exemple, pour ajuster le seuil à 97%, utilisez la commande suivante :
gcloud sql instances patch INSTANCE_NAME \
--database-flags=EXISTING_FLAGS,innodb_cloudsql_managed_buffer_pool=on,\
innodb_cloudsql_managed_buffer_pool_threshold_pct=97
Vous pouvez définir l'option innodb_cloudsql_managed_buffer_pool_threshold_pct sur une valeur entière comprise entre 50 et 99. La modification de la valeur du seuil d'utilisation de la mémoire ne nécessite pas le redémarrage de l'instance Cloud SQL.
Logique d'ajustement
Le pool de mémoire tampon géré ne réduit pas innodb_buffer_pool_size à une taille minimale fixe et prédéterminée. Au lieu de cela, il réduit la taille de manière itérative et dynamique jusqu'à ce que l'utilisation totale de la mémoire de l'instance repasse en dessous du pourcentage de seuil configuré (innodb_cloudsql_managed_buffer_pool_threshold_pct). Il réduit le pool de mémoire tampon en ajustant la valeur de l'indicateur innodb_buffer_pool_size, en tirant parti de la fonctionnalité de redimensionnement du pool de mémoire tampon intégrée à InnoDB.
Pour éviter que innodb_buffer_pool_size ne se réduise à une taille qui aurait un impact important sur les performances lorsque l'utilisation de la mémoire reste élevée malgré la réduction, la fonctionnalité utilise un seuil de sécurité interne. Les valeurs représentent le pourcentage de la mémoire totale de l'instance qui doit être alloué au pool de mémoire tampon.
| Taille du conteneur MySQL | Taille minimale du pool de mémoire tampon |
|---|---|
| 1 025 à 2 048 Mo | 35 % |
| 2 049 à 6 528 Mo | 30 % |
| 6 529 à 11 315 Mo | 40 % |
| 11 316 à 22 630 Mo | 45 % |
| Autres tailles (par défaut) | 50 % |
La diminution de innodb_buffer_pool_size dépend de la capacité de mémoire de votre instance de base de données. Le tableau suivant indique la diminution en pourcentage pour chaque taille de conteneur :
| Taille du conteneur MySQL | Pourcentage de diminution |
|---|---|
| 1 025 à 2 048 Mo | 15 % |
| 2 049 à 6 528 Mo | 11 % |
| 6 529 à 11 315 Mo | 8 % |
| 11 316 à 22 630 Mo | 6 % |
| Autres tailles (par défaut) | 5 % |
Après avoir calculé la nouvelle valeur réduite, le pool de mémoire tampon géré arrondit innodb_buffer_pool_size au multiple le plus proche des valeurs innodb_buffer_pool_instances et innodb_buffer_pool_chunk_size.
Lorsque le pool de mémoire tampon géré ajuste la valeur de innodb_buffer_pool_size, les modifications ne sont pas reflétées dans la console Google Cloud . Pour afficher la valeur actuelle de innodb_buffer_pool_size lorsque le pool de mémoire tampon géré est activé, vous pouvez utiliser le client MySQL :
mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_size';
Limites
La réduction de la taille du pool de mémoire tampon ne peut pas empêcher les erreurs de mémoire insuffisante dans tous les cas. Par exemple, certaines charges de travail peuvent consommer de la mémoire de manière non durable ou augmenter à un rythme soudain, certaines instances Cloud SQL peuvent être sous-provisionnées ou le pool de mémoire tampon peut ne pas être préchauffé. Il est possible que Cloud SQL ne puisse pas libérer de la mémoire assez rapidement pour s'adapter à des changements soudains de la charge de travail de la mémoire. De plus, Cloud SQL ne peut pas gérer les valeurs mal configurées des autres options de mémoire.
Surveillance
Vous pouvez surveiller le pool de mémoire tampon géré dans le journal des erreurs MySQL. Dans l'explorateur de journaux, vous pouvez filtrer le journal mysql.err pour afficher les entrées avec le préfixe Managed Buffer Pool Plugin: ou Tuner Plugin: afin de trouver les derniers événements d'ajustement.
Lorsque le pool de mémoire tampon géré démarre pour la première fois, il émet un journal semblable à celui-ci :
[Note] [MY-000000] [Server] Managed Buffer Pool Plugin: MySQL Instance memory limit: 29533, Current MySQL memory usage: 2663641088, Max Allowed MySQL memory usage: 30732730368 ...
Les journaux suivants montrent des exemples de diminution automatique de innodb_buffer_pool_size :
[Note] [MY-000000] [Server] Managed Buffer Pool Plugin: Decreasing InnoDB Buffer Pool Size.
[Note] [MY-000000] [Server] Managed Buffer Pool Plugin: Updated innodb_buffer_pool_size=805306368 bytes.
Vous pouvez configurer des métriques basées sur les journaux pour suivre les événements d'ajustement du pool de mémoire tampon géré au fil du temps.
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.
En général, si vous avez moins de 10% 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 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é |
|---|---|
| Inférieur ou égal à 16 Go | 90 % |
| Plus de 16 Go | 95 % |
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é.
Résoudre les problèmes de consommation élevée de mémoire
Exécutez
SHOW PROCESSLISTpour afficher les requêtes en cours qui consomment 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 STATUSdans la sectionBUFFER POOL AND MEMORYpour 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 0Utilisez la commande
SHOW variablesde 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 la 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.