Les traductions sont fournies par des outils de traduction automatique. En cas de conflit entre le contenu d'une traduction et celui de la version originale en anglais, la version anglaise prévaudra.
Résolution des problèmes d’utilisation de la mémoire pour les bases de données Aurora MySQL
CloudWatch, Enhanced Monitoring et Performance Insights fournissent une bonne vue d’ensemble de l’utilisation de la mémoire au niveau du système d’exploitation, notamment de la quantité de mémoire utilisée par le processus de base de données. Cependant, ils ne vous permettent pas de déterminer les connexions ou les composants du moteur susceptibles d’être à l’origine de cette utilisation de mémoire.
Pour résoudre ce problème, vous pouvez utiliser le schéma de performance et le schéma sys. Dans Aurora MySQL version 3, l’instrumentation de la mémoire est activée par défaut lorsque le schéma de performance est activé. Dans Aurora MySQL version 2, seule l’instrumentation de la mémoire pour l’utilisation de la mémoire du schéma de performance est activée par défaut. Pour en savoir plus sur les tables disponibles dans le schéma de performance afin de suivre l’utilisation de la mémoire et sur l’activation de l’instrumentation de la mémoire du schéma de performance, consultez Memory Summary Tables
Bien que des informations détaillées soient disponibles dans le schéma de performance pour suivre l’utilisation actuelle de la mémoire, le schéma système
Dans le schéma sys, les vues suivantes sont disponibles pour suivre l’utilisation de la mémoire par connexion, composant et requête.
| Vue | Description |
|---|---|
|
Fournit des informations sur l’utilisation de la mémoire du moteur par hôte. Cette vue peut être utile pour identifier les serveurs d’applications ou les hôtes clients qui consomment de la mémoire. |
|
|
Fournit des informations sur l’utilisation de la mémoire du moteur par ID de thread. L’ID de thread dans MySQL peut être une connexion client ou un thread d’arrière-plan. Vous pouvez mapper les ID de thread aux ID de connexion MySQL en utilisant la vue sys.processlist |
|
|
Fournit des informations sur l’utilisation de la mémoire du moteur par utilisateur. Cette vue peut être utile pour identifier les comptes utilisateurs ou les clients consommant de la mémoire. |
|
|
Fournit des informations sur l’utilisation de la mémoire du moteur par composant du moteur. Cette vue peut être utile pour identifier l’utilisation globale de la mémoire par les tampons ou les composants du moteur. Par exemple, vous pouvez voir l’événement |
|
|
Fournit une vue d’ensemble de l’utilisation totale de la mémoire suivie dans le moteur de base de données. |
Dans Aurora MySQL 3.05 et versions ultérieures, vous pouvez également suivre l’utilisation maximale de la mémoire par résumé d’instruction dans les tableaux récapitulatifs des instructions du schéma de performanceMAX_TOTAL_MEMORY peut vous aider à identifier la mémoire maximale utilisée par le résumé des requêtes depuis la dernière réinitialisation des statistiques ou depuis le redémarrage de l’instance de base de données. Cela peut être utile pour identifier des requêtes spécifiques susceptibles de consommer beaucoup de mémoire.
Note
Le schéma de performance et le schéma sys indiquent l’utilisation actuelle de la mémoire sur le serveur, ainsi que le maximum de mémoire consommée par connexion et par composant du moteur. Le schéma de performance étant conservé en mémoire, les informations sont réinitialisées au redémarrage de l’instance de base de données. Pour en conserver un historique au fil du temps, nous vous recommandons de configurer la récupération et le stockage de ces données en dehors du schéma de performance.
Rubriques
Exemple 1 : utilisation élevée de la mémoire en permanence
En examinant FreeableMemory dans CloudWatch, nous constatons que l’utilisation de la mémoire a considérablement augmenté le 26/03/2024 à 02:59 UTC.
Nous ne disposons toutefois pas de toutes les informations requises. Pour déterminer quel composant utilise le plus de mémoire, vous pouvez vous connecter à la base de données et consulter sys.memory_global_by_current_bytes. Cette table contient une liste des événements de mémoire suivis par MySQL, ainsi que des informations sur l’allocation de mémoire par événement. Chaque événement de suivi de la mémoire commence par memory/%, suivi d’autres informations sur le composant ou la fonctionnalité du moteur auquel l’événement est associé.
Par exemple, memory/performance_schema/% indique les événements de mémoire liés au schéma de performance, memory/innodb/% correspond à InnoDB, etc. Pour plus d’informations sur les conventions de dénomination utilisées dans les événements, consultez Performance Schema Instrument Naming Conventions
Dans la requête suivante, nous pouvons identifier le responsable le plus probable en fonction de current_alloc, mais nous constatons également la présence de nombreux événements memory/performance_schema/%.
mysql> SELECT * FROM sys.memory_global_by_current_bytes LIMIT 10; +-----------------------------------------------------------------------------+---------------+---------------+-------------------+------------+------------+----------------+ | event_name | current_count | current_alloc | current_avg_alloc | high_count | high_alloc | high_avg_alloc | +-----------------------------------------------------------------------------+---------------+---------------+-------------------+------------+------------+----------------+ | memory/sql/Prepared_statement::main_mem_root | 512817 | 4.91 GiB | 10.04 KiB | 512823 | 4.91 GiB | 10.04 KiB | | memory/performance_schema/prepared_statements_instances | 252 | 488.25 MiB | 1.94 MiB | 252 | 488.25 MiB | 1.94 MiB | | memory/innodb/hash0hash | 4 | 79.07 MiB | 19.77 MiB | 4 | 79.07 MiB | 19.77 MiB | | memory/performance_schema/events_errors_summary_by_thread_by_error | 1028 | 52.27 MiB | 52.06 KiB | 1028 | 52.27 MiB | 52.06 KiB | | memory/performance_schema/events_statements_summary_by_thread_by_event_name | 4 | 47.25 MiB | 11.81 MiB | 4 | 47.25 MiB | 11.81 MiB | | memory/performance_schema/events_statements_summary_by_digest | 1 | 40.28 MiB | 40.28 MiB | 1 | 40.28 MiB | 40.28 MiB | | memory/performance_schema/memory_summary_by_thread_by_event_name | 4 | 31.64 MiB | 7.91 MiB | 4 | 31.64 MiB | 7.91 MiB | | memory/innodb/memory | 15227 | 27.44 MiB | 1.85 KiB | 20619 | 33.33 MiB | 1.66 KiB | | memory/sql/String::value | 74411 | 21.85 MiB | 307 bytes | 76867 | 25.54 MiB | 348 bytes | | memory/sql/TABLE | 8381 | 21.03 MiB | 2.57 KiB | 8381 | 21.03 MiB | 2.57 KiB | +-----------------------------------------------------------------------------+---------------+---------------+-------------------+------------+------------+----------------+ 10 rows in set (0.02 sec)
Nous avons mentionné précédemment que le schéma de performance est stocké en mémoire, ce qui signifie qu’il est également suivi dans l’instrumentation de la mémoire performance_schema.
Note
Si vous constatez que le schéma de performance utilise beaucoup de mémoire et que vous souhaitez limiter son utilisation, vous pouvez ajuster les paramètres de base de données en fonction de vos besoins. Pour plus d’informations, consultez The Performance Schema Memory-Allocation Model
Pour une meilleure lisibilité, vous pouvez réexécuter la même requête, mais exclure les événements du schéma de performance. Voici les informations que nous donne le résultat :
-
Le principal consommateur de mémoire est
memory/sql/Prepared_statement::main_mem_root. -
La colonne
current_allocnous indique que MySQL dispose actuellement de 4,91 Gio alloués à cet événement. -
high_alloc columnindique que 4,91 Gio est le seuil maximum decurrent_allocdepuis la dernière réinitialisation des statistiques ou depuis le redémarrage du serveur. Autrement dit,memory/sql/Prepared_statement::main_mem_roota atteint sa valeur la plus élevée.
mysql> SELECT * FROM sys.memory_global_by_current_bytes WHERE event_name NOT LIKE 'memory/performance_schema/%' LIMIT 10; +-----------------------------------------------+---------------+---------------+-------------------+------------+------------+----------------+ | event_name | current_count | current_alloc | current_avg_alloc | high_count | high_alloc | high_avg_alloc | +-----------------------------------------------+---------------+---------------+-------------------+------------+------------+----------------+ | memory/sql/Prepared_statement::main_mem_root | 512817 | 4.91 GiB | 10.04 KiB | 512823 | 4.91 GiB | 10.04 KiB | | memory/innodb/hash0hash | 4 | 79.07 MiB | 19.77 MiB | 4 | 79.07 MiB | 19.77 MiB | | memory/innodb/memory | 17096 | 31.68 MiB | 1.90 KiB | 22498 | 37.60 MiB | 1.71 KiB | | memory/sql/String::value | 122277 | 27.94 MiB | 239 bytes | 124699 | 29.47 MiB | 247 bytes | | memory/sql/TABLE | 9927 | 24.67 MiB | 2.55 KiB | 9929 | 24.68 MiB | 2.55 KiB | | memory/innodb/lock0lock | 8888 | 19.71 MiB | 2.27 KiB | 8888 | 19.71 MiB | 2.27 KiB | | memory/sql/Prepared_statement::infrastructure | 257623 | 16.24 MiB | 66 bytes | 257631 | 16.24 MiB | 66 bytes | | memory/mysys/KEY_CACHE | 3 | 16.00 MiB | 5.33 MiB | 3 | 16.00 MiB | 5.33 MiB | | memory/innodb/sync0arr | 3 | 7.03 MiB | 2.34 MiB | 3 | 7.03 MiB | 2.34 MiB | | memory/sql/THD::main_mem_root | 815 | 6.56 MiB | 8.24 KiB | 849 | 7.19 MiB | 8.67 KiB | +-----------------------------------------------+---------------+---------------+-------------------+------------+------------+----------------+ 10 rows in set (0.06 sec)
Le nom de l’événement révèle que cette mémoire est utilisée pour des instructions préparées. Pour déterminer quelles connexions utilisent cette mémoire, vérifiez memory_by_thread_by_current_bytes
Dans l’exemple suivant, environ 7 Mio sont alloués à chaque connexion, avec un seuil maximum d’environ 6,29 Mio (current_max_alloc). Cela est logique, car cet exemple utilise sysbench avec 80 tables et 800 connexions avec des instructions préparées. Si vous souhaitez réduire l’utilisation de la mémoire dans ce scénario, vous pouvez optimiser l’utilisation des instructions préparées par votre application afin de limiter la consommation de la mémoire.
mysql> SELECT * FROM sys.memory_by_thread_by_current_bytes; +-----------+-------------------------------------------+--------------------+-------------------+-------------------+-------------------+-----------------+ | thread_id | user | current_count_used | current_allocated | current_avg_alloc | current_max_alloc | total_allocated | +-----------+-------------------------------------------+--------------------+-------------------+-------------------+-------------------+-----------------+ | 46 | rdsadmin@localhost | 405 | 8.47 MiB | 21.42 KiB | 8.00 MiB | 155.86 MiB | | 61 | reinvent@10.0.4.4 | 1749 | 6.72 MiB | 3.93 KiB | 6.29 MiB | 14.24 MiB | | 101 | reinvent@10.0.4.4 | 1845 | 6.71 MiB | 3.72 KiB | 6.29 MiB | 14.50 MiB | | 55 | reinvent@10.0.4.4 | 1674 | 6.68 MiB | 4.09 KiB | 6.29 MiB | 14.13 MiB | | 57 | reinvent@10.0.4.4 | 1416 | 6.66 MiB | 4.82 KiB | 6.29 MiB | 13.52 MiB | | 112 | reinvent@10.0.4.4 | 1759 | 6.66 MiB | 3.88 KiB | 6.29 MiB | 14.17 MiB | | 66 | reinvent@10.0.4.4 | 1428 | 6.64 MiB | 4.76 KiB | 6.29 MiB | 13.47 MiB | | 75 | reinvent@10.0.4.4 | 1389 | 6.62 MiB | 4.88 KiB | 6.29 MiB | 13.40 MiB | | 116 | reinvent@10.0.4.4 | 1333 | 6.61 MiB | 5.08 KiB | 6.29 MiB | 13.21 MiB | | 90 | reinvent@10.0.4.4 | 1448 | 6.59 MiB | 4.66 KiB | 6.29 MiB | 13.58 MiB | | 98 | reinvent@10.0.4.4 | 1440 | 6.57 MiB | 4.67 KiB | 6.29 MiB | 13.52 MiB | | 94 | reinvent@10.0.4.4 | 1433 | 6.57 MiB | 4.69 KiB | 6.29 MiB | 13.49 MiB | | 62 | reinvent@10.0.4.4 | 1323 | 6.55 MiB | 5.07 KiB | 6.29 MiB | 13.48 MiB | | 87 | reinvent@10.0.4.4 | 1323 | 6.55 MiB | 5.07 KiB | 6.29 MiB | 13.25 MiB | | 99 | reinvent@10.0.4.4 | 1346 | 6.54 MiB | 4.98 KiB | 6.29 MiB | 13.24 MiB | | 105 | reinvent@10.0.4.4 | 1347 | 6.54 MiB | 4.97 KiB | 6.29 MiB | 13.34 MiB | | 73 | reinvent@10.0.4.4 | 1335 | 6.54 MiB | 5.02 KiB | 6.29 MiB | 13.23 MiB | | 54 | reinvent@10.0.4.4 | 1510 | 6.53 MiB | 4.43 KiB | 6.29 MiB | 13.49 MiB | . . . . . . | 812 | reinvent@10.0.4.4 | 1259 | 6.38 MiB | 5.19 KiB | 6.29 MiB | 13.05 MiB | | 214 | reinvent@10.0.4.4 | 1279 | 6.38 MiB | 5.10 KiB | 6.29 MiB | 12.90 MiB | | 325 | reinvent@10.0.4.4 | 1254 | 6.38 MiB | 5.21 KiB | 6.29 MiB | 12.99 MiB | | 705 | reinvent@10.0.4.4 | 1273 | 6.37 MiB | 5.13 KiB | 6.29 MiB | 13.03 MiB | | 530 | reinvent@10.0.4.4 | 1268 | 6.37 MiB | 5.15 KiB | 6.29 MiB | 12.92 MiB | | 307 | reinvent@10.0.4.4 | 1263 | 6.37 MiB | 5.17 KiB | 6.29 MiB | 12.87 MiB | | 738 | reinvent@10.0.4.4 | 1260 | 6.37 MiB | 5.18 KiB | 6.29 MiB | 13.00 MiB | | 819 | reinvent@10.0.4.4 | 1252 | 6.37 MiB | 5.21 KiB | 6.29 MiB | 13.01 MiB | | 31 | innodb/srv_purge_thread | 17810 | 3.14 MiB | 184 bytes | 2.40 MiB | 205.69 MiB | | 38 | rdsadmin@localhost | 599 | 1.76 MiB | 3.01 KiB | 1.00 MiB | 25.58 MiB | | 1 | sql/main | 3756 | 1.32 MiB | 367 bytes | 355.78 KiB | 6.19 MiB | | 854 | rdsadmin@localhost | 46 | 1.08 MiB | 23.98 KiB | 1.00 MiB | 5.10 MiB | | 30 | innodb/clone_gtid_thread | 1596 | 573.14 KiB | 367 bytes | 254.91 KiB | 970.69 KiB | | 40 | rdsadmin@localhost | 235 | 245.19 KiB | 1.04 KiB | 128.88 KiB | 808.64 KiB | | 853 | rdsadmin@localhost | 96 | 94.63 KiB | 1009 bytes | 29.73 KiB | 422.45 KiB | | 36 | rdsadmin@localhost | 33 | 36.29 KiB | 1.10 KiB | 16.08 KiB | 74.15 MiB | | 33 | sql/event_scheduler | 3 | 16.27 KiB | 5.42 KiB | 16.04 KiB | 16.27 KiB | | 35 | sql/compress_gtid_table | 8 | 14.20 KiB | 1.77 KiB | 8.05 KiB | 18.62 KiB | | 25 | innodb/fts_optimize_thread | 12 | 1.86 KiB | 158 bytes | 648 bytes | 1.98 KiB | | 23 | innodb/srv_master_thread | 11 | 1.23 KiB | 114 bytes | 361 bytes | 24.40 KiB | | 24 | innodb/dict_stats_thread | 11 | 1.23 KiB | 114 bytes | 361 bytes | 1.35 KiB | | 5 | innodb/io_read_thread | 1 | 144 bytes | 144 bytes | 144 bytes | 144 bytes | | 6 | innodb/io_read_thread | 1 | 144 bytes | 144 bytes | 144 bytes | 144 bytes | | 2 | sql/aws_oscar_log_level_monitor | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes | | 4 | innodb/io_ibuf_thread | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes | | 7 | innodb/io_write_thread | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes | | 8 | innodb/io_write_thread | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes | | 9 | innodb/io_write_thread | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes | | 10 | innodb/io_write_thread | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes | | 11 | innodb/srv_lra_thread | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes | | 12 | innodb/srv_akp_thread | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes | | 18 | innodb/srv_lock_timeout_thread | 0 | 0 bytes | 0 bytes | 0 bytes | 248 bytes | | 19 | innodb/srv_error_monitor_thread | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes | | 20 | innodb/srv_monitor_thread | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes | | 21 | innodb/buf_resize_thread | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes | | 22 | innodb/btr_search_sys_toggle_thread | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes | | 32 | innodb/dict_persist_metadata_table_thread | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes | | 34 | sql/signal_handler | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes | +-----------+-------------------------------------------+--------------------+-------------------+-------------------+-------------------+-----------------+ 831 rows in set (2.48 sec)
Comme indiqué précédemment, la valeur de l’ID de thread (thd_id) ici peut faire référence aux threads d’arrière-plan du serveur ou aux connexions à la base de données. Si vous souhaitez associer les valeurs des ID de thread aux ID de connexion à la base de données, vous pouvez utiliser la table performance_schema.threads ou la vue sys.processlist, où conn_id correspond à l’ID de connexion.
mysql> SELECT thd_id,conn_id,user,db,command,state,time,last_wait FROM sys.processlist WHERE user='reinvent@10.0.4.4'; +--------+---------+-------------------+----------+---------+----------------+------+-------------------------------------------------+ | thd_id | conn_id | user | db | command | state | time | last_wait | +--------+---------+-------------------+----------+---------+----------------+------+-------------------------------------------------+ | 590 | 562 | reinvent@10.0.4.4 | sysbench | Execute | closing tables | 0 | wait/io/redo_log_flush | | 578 | 550 | reinvent@10.0.4.4 | sysbench | Sleep | NULL | 0 | idle | | 579 | 551 | reinvent@10.0.4.4 | sysbench | Execute | closing tables | 0 | wait/io/redo_log_flush | | 580 | 552 | reinvent@10.0.4.4 | sysbench | Execute | updating | 0 | wait/io/table/sql/handler | | 581 | 553 | reinvent@10.0.4.4 | sysbench | Execute | updating | 0 | wait/io/table/sql/handler | | 582 | 554 | reinvent@10.0.4.4 | sysbench | Sleep | NULL | 0 | idle | | 583 | 555 | reinvent@10.0.4.4 | sysbench | Sleep | NULL | 0 | idle | | 584 | 556 | reinvent@10.0.4.4 | sysbench | Execute | updating | 0 | wait/io/table/sql/handler | | 585 | 557 | reinvent@10.0.4.4 | sysbench | Execute | closing tables | 0 | wait/io/redo_log_flush | | 586 | 558 | reinvent@10.0.4.4 | sysbench | Execute | updating | 0 | wait/io/table/sql/handler | | 587 | 559 | reinvent@10.0.4.4 | sysbench | Execute | closing tables | 0 | wait/io/redo_log_flush | . . . . . . | 323 | 295 | reinvent@10.0.4.4 | sysbench | Sleep | NULL | 0 | idle | | 324 | 296 | reinvent@10.0.4.4 | sysbench | Execute | updating | 0 | wait/io/table/sql/handler | | 325 | 297 | reinvent@10.0.4.4 | sysbench | Execute | closing tables | 0 | wait/io/redo_log_flush | | 326 | 298 | reinvent@10.0.4.4 | sysbench | Execute | updating | 0 | wait/io/table/sql/handler | | 438 | 410 | reinvent@10.0.4.4 | sysbench | Execute | System lock | 0 | wait/lock/table/sql/handler | | 280 | 252 | reinvent@10.0.4.4 | sysbench | Sleep | starting | 0 | wait/io/socket/sql/client_connection | | 98 | 70 | reinvent@10.0.4.4 | sysbench | Query | freeing items | 0 | NULL | +--------+---------+-------------------+----------+---------+----------------+------+-------------------------------------------------+ 804 rows in set (5.51 sec)
Nous arrêtons maintenant la charge de travail sysbench, qui ferme les connexions et libère de la mémoire. En vérifiant à nouveau les événements, nous pouvons confirmer que la mémoire est libérée, mais high_alloc nous indique tout de même quel est le seuil maximum. La colonne high_alloc peut être très utile pour identifier de courts pics d’utilisation de la mémoire, contrairement à current_alloc, qui indique uniquement la mémoire actuellement allouée.
mysql> SELECT * FROM sys.memory_global_by_current_bytes WHERE event_name='memory/sql/Prepared_statement::main_mem_root' LIMIT 10; +----------------------------------------------+---------------+---------------+-------------------+------------+------------+----------------+ | event_name | current_count | current_alloc | current_avg_alloc | high_count | high_alloc | high_avg_alloc | +----------------------------------------------+---------------+---------------+-------------------+------------+------------+----------------+ | memory/sql/Prepared_statement::main_mem_root | 17 | 253.80 KiB | 14.93 KiB | 512823 | 4.91 GiB | 10.04 KiB | +----------------------------------------------+---------------+---------------+-------------------+------------+------------+----------------+ 1 row in set (0.00 sec)
Si vous souhaitez réinitialiser high_alloc, vous pouvez tronquer les tableaux récapitulatifs de la mémoire performance_schema, mais toute l’instrumentation de la mémoire sera réinitialisée. Pour plus d’informations, consultez Performance Schema General Table Characteristics
Dans l’exemple suivant, nous pouvons voir que high_alloc est réinitialisé après la troncature.
mysql> TRUNCATE `performance_schema`.`memory_summary_global_by_event_name`; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM sys.memory_global_by_current_bytes WHERE event_name='memory/sql/Prepared_statement::main_mem_root' LIMIT 10; +----------------------------------------------+---------------+---------------+-------------------+------------+------------+----------------+ | event_name | current_count | current_alloc | current_avg_alloc | high_count | high_alloc | high_avg_alloc | +----------------------------------------------+---------------+---------------+-------------------+------------+------------+----------------+ | memory/sql/Prepared_statement::main_mem_root | 17 | 253.80 KiB | 14.93 KiB | 17 | 253.80 KiB | 14.93 KiB | +----------------------------------------------+---------------+---------------+-------------------+------------+------------+----------------+ 1 row in set (0.00 sec)
Exemple 2 : pics de mémoire transitoires
Les courts pics d’utilisation de la mémoire sur un serveur de base de données constituent un autre phénomène courant. Il peut s’agir de baisses périodiques de mémoire libérable difficiles à gérer avec sys.memory_global_by_current_bytes dans current_alloc, car la mémoire a déjà été libérée.
Note
Si les statistiques du schéma de performance ont été réinitialisées ou si l’instance de base de données a été redémarrée, ces informations ne seront pas disponibles dans sys ni performance_schema. Pour conserver ces informations, nous vous recommandons de configurer la collecte de métriques externes.
Le graphique suivant de la métrique os.memory.free dans Surveillance améliorée présente de courts pics d’utilisation de la mémoire de 7 secondes. La section Surveillance améliorée vous permet de surveiller l’utilisation à des intervalles pouvant atteindre une seconde, ce qui est parfait pour détecter ce type de pic transitoire.
Pour aider à diagnostiquer la cause de l’utilisation de la mémoire, nous pouvons utiliser à la fois high_alloc dans les vues récapitulatives de la mémoire sys et les tableaux récapitulatifs des instructions du schéma de performance
Comme prévu, étant donné que l’utilisation de la mémoire n’est pas élevée actuellement, nous ne voyons aucun responsable majeur dans la vue du schéma sys, souscurrent_alloc.
mysql> SELECT * FROM sys.memory_global_by_current_bytes LIMIT 10; +-----------------------------------------------------------------------------+---------------+---------------+-------------------+------------+------------+----------------+ | event_name | current_count | current_alloc | current_avg_alloc | high_count | high_alloc | high_avg_alloc | +-----------------------------------------------------------------------------+---------------+---------------+-------------------+------------+------------+----------------+ | memory/innodb/hash0hash | 4 | 79.07 MiB | 19.77 MiB | 4 | 79.07 MiB | 19.77 MiB | | memory/innodb/os0event | 439372 | 60.34 MiB | 144 bytes | 439372 | 60.34 MiB | 144 bytes | | memory/performance_schema/events_statements_summary_by_digest | 1 | 40.28 MiB | 40.28 MiB | 1 | 40.28 MiB | 40.28 MiB | | memory/mysys/KEY_CACHE | 3 | 16.00 MiB | 5.33 MiB | 3 | 16.00 MiB | 5.33 MiB | | memory/performance_schema/events_statements_history_long | 1 | 14.34 MiB | 14.34 MiB | 1 | 14.34 MiB | 14.34 MiB | | memory/performance_schema/events_errors_summary_by_thread_by_error | 257 | 13.07 MiB | 52.06 KiB | 257 | 13.07 MiB | 52.06 KiB | | memory/performance_schema/events_statements_summary_by_thread_by_event_name | 1 | 11.81 MiB | 11.81 MiB | 1 | 11.81 MiB | 11.81 MiB | | memory/performance_schema/events_statements_summary_by_digest.digest_text | 1 | 9.77 MiB | 9.77 MiB | 1 | 9.77 MiB | 9.77 MiB | | memory/performance_schema/events_statements_history_long.digest_text | 1 | 9.77 MiB | 9.77 MiB | 1 | 9.77 MiB | 9.77 MiB | | memory/performance_schema/events_statements_history_long.sql_text | 1 | 9.77 MiB | 9.77 MiB | 1 | 9.77 MiB | 9.77 MiB | +-----------------------------------------------------------------------------+---------------+---------------+-------------------+------------+------------+----------------+ 10 rows in set (0.01 sec)
En élargissant la vue pour effectuer un tri par high_alloc, nous constatons que le composant memory/temptable/physical_ram est très probablement responsable de l’utilisation élevée de la mémoire. À son maximum, il consommait 515,00 Mio.
Comme son nom l’indique, memory/temptable/physical_ram instrumente l’utilisation de la mémoire pour le moteur de stockage TEMP de MySQL, introduit dans MySQL 8.0. Pour plus d’informations sur la façon dont MySQL utilise les tables temporaires, consultez Internal temporary table use in MySQL
Note
Nous utilisons la vue sys.x$memory_global_by_current_bytes dans cet exemple.
mysql> SELECT event_name, format_bytes(current_alloc) AS "currently allocated", sys.format_bytes(high_alloc) AS "high-water mark" FROM sys.x$memory_global_by_current_bytes ORDER BY high_alloc DESC LIMIT 10; +-----------------------------------------------------------------------------+---------------------+-----------------+ | event_name | currently allocated | high-water mark | +-----------------------------------------------------------------------------+---------------------+-----------------+ | memory/temptable/physical_ram | 4.00 MiB | 515.00 MiB | | memory/innodb/hash0hash | 79.07 MiB | 79.07 MiB | | memory/innodb/os0event | 63.95 MiB | 63.95 MiB | | memory/performance_schema/events_statements_summary_by_digest | 40.28 MiB | 40.28 MiB | | memory/mysys/KEY_CACHE | 16.00 MiB | 16.00 MiB | | memory/performance_schema/events_statements_history_long | 14.34 MiB | 14.34 MiB | | memory/performance_schema/events_errors_summary_by_thread_by_error | 13.07 MiB | 13.07 MiB | | memory/performance_schema/events_statements_summary_by_thread_by_event_name | 11.81 MiB | 11.81 MiB | | memory/performance_schema/events_statements_summary_by_digest.digest_text | 9.77 MiB | 9.77 MiB | | memory/performance_schema/events_statements_history_long.sql_text | 9.77 MiB | 9.77 MiB | +-----------------------------------------------------------------------------+---------------------+-----------------+ 10 rows in set (0.00 sec)
DansExemple 1 : utilisation élevée de la mémoire en permanence, nous avons vérifié l’utilisation actuelle de la mémoire pour chaque connexion afin de déterminer quelle connexion est responsable de l’utilisation de la mémoire en question. Dans cet exemple, la mémoire est déjà libérée. Il n’est donc pas utile de vérifier l’utilisation de la mémoire pour les connexions en cours.
Pour aller plus loin dans notre recherche et trouver les instructions, les utilisateurs et les hôtes problématiques, utilisons le schéma de performance. Le schéma de performance contient plusieurs tableaux récapitulatifs des instructions divisés en différentes dimensions, telles que le nom de l’événement, le résumé de l’instruction, l’hôte, le thread et l’utilisateur. Chaque vue vous permet de mieux comprendre où certaines instructions sont exécutées et ce qu’elles font. Cette section se concentre sur MAX_TOTAL_MEMORY, mais vous trouverez plus d’informations sur toutes les colonnes disponibles dans la documentation Tableaux récapitulatifs des instructions du schéma de performance
mysql> SHOW TABLES IN performance_schema LIKE 'events_statements_summary_%'; +------------------------------------------------------------+ | Tables_in_performance_schema (events_statements_summary_%) | +------------------------------------------------------------+ | events_statements_summary_by_account_by_event_name | | events_statements_summary_by_digest | | events_statements_summary_by_host_by_event_name | | events_statements_summary_by_program | | events_statements_summary_by_thread_by_event_name | | events_statements_summary_by_user_by_event_name | | events_statements_summary_global_by_event_name | +------------------------------------------------------------+ 7 rows in set (0.00 sec)
Vérifions d’abord events_statements_summary_by_digest pour voir MAX_TOTAL_MEMORY.
Nous en tirons les informations suivantes :
-
La requête avec le résumé
20676ce4a690592ff05debcffcbc26faeb76f22005e7628364d7a498769d0c4asemble être un responsable probable de cette utilisation de la mémoire.MAX_TOTAL_MEMORYindique 537450710, ce qui correspond au seuil maximum que nous avons observé pour l’événementmemory/temptable/physical_ramdanssys.x$memory_global_by_current_bytes. -
Elle a été exécutée quatre fois (
COUNT_STAR): la première fois le 26/03/2024 04:08:34.943256, et la dernière fois le 26/03/2024 04:43:06.998310.
mysql> SELECT SCHEMA_NAME,DIGEST,COUNT_STAR,MAX_TOTAL_MEMORY,FIRST_SEEN,LAST_SEEN FROM performance_schema.events_statements_summary_by_digest ORDER BY MAX_TOTAL_MEMORY DESC LIMIT 5; +-------------+------------------------------------------------------------------+------------+------------------+----------------------------+----------------------------+ | SCHEMA_NAME | DIGEST | COUNT_STAR | MAX_TOTAL_MEMORY | FIRST_SEEN | LAST_SEEN | +-------------+------------------------------------------------------------------+------------+------------------+----------------------------+----------------------------+ | sysbench | 20676ce4a690592ff05debcffcbc26faeb76f22005e7628364d7a498769d0c4a | 4 | 537450710 | 2024-03-26 04:08:34.943256 | 2024-03-26 04:43:06.998310 | | NULL | f158282ea0313fefd0a4778f6e9b92fc7d1e839af59ebd8c5eea35e12732c45d | 4 | 3636413 | 2024-03-26 04:29:32.712348 | 2024-03-26 04:36:26.269329 | | NULL | 0046bc5f642c586b8a9afd6ce1ab70612dc5b1fd2408fa8677f370c1b0ca3213 | 2 | 3459965 | 2024-03-26 04:31:37.674008 | 2024-03-26 04:32:09.410718 | | NULL | 8924f01bba3c55324701716c7b50071a60b9ceaf17108c71fd064c20c4ab14db | 1 | 3290981 | 2024-03-26 04:31:49.751506 | 2024-03-26 04:31:49.751506 | | NULL | 90142bbcb50a744fcec03a1aa336b2169761597ea06d85c7f6ab03b5a4e1d841 | 1 | 3131729 | 2024-03-26 04:15:09.719557 | 2024-03-26 04:15:09.719557 | +-------------+------------------------------------------------------------------+------------+------------------+----------------------------+----------------------------+ 5 rows in set (0.00 sec)
Maintenant que nous connaissons le résumé problématique, nous pouvons obtenir plus d’informations telles que le texte de la requête, l’utilisateur qui l’a exécutée et l’endroit où elle a été exécutée. Grâce au texte du résumé renvoyé, nous voyons qu’il s’agit d’une expression de table commune (CTE) qui crée quatre tables temporaires et effectue quatre analyses de tables, ce qui est très inefficace.
mysql> SELECT SCHEMA_NAME,DIGEST_TEXT,QUERY_SAMPLE_TEXT,MAX_TOTAL_MEMORY,SUM_ROWS_SENT,SUM_ROWS_EXAMINED,SUM_CREATED_TMP_TABLES,SUM_NO_INDEX_USED FROM performance_schema.events_statements_summary_by_digest WHERE DIGEST='20676ce4a690592ff05debcffcbc26faeb76f22005e7628364d7a498769d0c4a'\G; *************************** 1. row *************************** SCHEMA_NAME: sysbench DIGEST_TEXT: WITH RECURSIVE `cte` ( `n` ) AS ( SELECT ? FROM `sbtest1` UNION ALL SELECT `id` + ? FROM `sbtest1` ) SELECT * FROM `cte` QUERY_SAMPLE_TEXT: WITH RECURSIVE cte (n) AS ( SELECT 1 from sbtest1 UNION ALL SELECT id + 1 FROM sbtest1) SELECT * FROM cte MAX_TOTAL_MEMORY: 537450710 SUM_ROWS_SENT: 80000000 SUM_ROWS_EXAMINED: 80000000 SUM_CREATED_TMP_TABLES: 4 SUM_NO_INDEX_USED: 4 1 row in set (0.01 sec)
Pour plus d’informations sur la table events_statements_summary_by_digest et les autres tableaux récapitulatifs des instructions du schéma de performance, consultez Statement summary tables
Vous pouvez également exécuter une instruction EXPLAIN
Note
L’instruction EXPLAIN ANALYZE peut fournir plus d’informations que EXPLAIN, mais elle exécute également la requête. Vous devez donc être prudent.
-- EXPLAIN mysql> EXPLAIN WITH RECURSIVE cte (n) AS (SELECT 1 FROM sbtest1 UNION ALL SELECT id + 1 FROM sbtest1) SELECT * FROM cte; +----+-------------+------------+------------+-------+---------------+------+---------+------+----------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+-------+---------------+------+---------+------+----------+----------+-------------+ | 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 19221520 | 100.00 | NULL | | 2 | DERIVED | sbtest1 | NULL | index | NULL | k_1 | 4 | NULL | 9610760 | 100.00 | Using index | | 3 | UNION | sbtest1 | NULL | index | NULL | k_1 | 4 | NULL | 9610760 | 100.00 | Using index | +----+-------------+------------+------------+-------+---------------+------+---------+------+----------+----------+-------------+ 3 rows in set, 1 warning (0.00 sec) -- EXPLAIN format=tree mysql> EXPLAIN format=tree WITH RECURSIVE cte (n) AS (SELECT 1 FROM sbtest1 UNION ALL SELECT id + 1 FROM sbtest1) SELECT * FROM cte\G; *************************** 1. row *************************** EXPLAIN: -> Table scan on cte (cost=4.11e+6..4.35e+6 rows=19.2e+6) -> Materialize union CTE cte (cost=4.11e+6..4.11e+6 rows=19.2e+6) -> Index scan on sbtest1 using k_1 (cost=1.09e+6 rows=9.61e+6) -> Index scan on sbtest1 using k_1 (cost=1.09e+6 rows=9.61e+6) 1 row in set (0.00 sec) -- EXPLAIN ANALYZE mysql> EXPLAIN ANALYZE WITH RECURSIVE cte (n) AS (SELECT 1 from sbtest1 UNION ALL SELECT id + 1 FROM sbtest1) SELECT * FROM cte\G; *************************** 1. row *************************** EXPLAIN: -> Table scan on cte (cost=4.11e+6..4.35e+6 rows=19.2e+6) (actual time=6666..9201 rows=20e+6 loops=1) -> Materialize union CTE cte (cost=4.11e+6..4.11e+6 rows=19.2e+6) (actual time=6666..6666 rows=20e+6 loops=1) -> Covering index scan on sbtest1 using k_1 (cost=1.09e+6 rows=9.61e+6) (actual time=0.0365..2006 rows=10e+6 loops=1) -> Covering index scan on sbtest1 using k_1 (cost=1.09e+6 rows=9.61e+6) (actual time=0.0311..2494 rows=10e+6 loops=1) 1 row in set (10.53 sec)
Mais qui l’a exécutée ? Dans le schéma de performance, nous pouvons voir que l’utilisateur destructive_operator a atteint la valeur MAX_TOTAL_MEMORY 537450710, ce qui correspond aux résultats précédents.
Note
Comme le schéma de performance est stocké en mémoire, il ne doit pas être considéré comme la seule source d’audit. Si vous devez conserver un historique des instructions exécutées et des utilisateurs qui les ont exécutées, nous vous recommandons d’activer l’audit avancé avec Aurora. Si vous devez également conserver des informations sur l’utilisation de la mémoire, nous vous recommandons de configurer la surveillance afin d’exporter et de stocker ces valeurs.
mysql> SELECT USER,EVENT_NAME,COUNT_STAR,MAX_TOTAL_MEMORY FROM performance_schema.events_statements_summary_by_user_by_event_name ORDER BY MAX_CONTROLLED_MEMORY DESC LIMIT 5; +----------------------+---------------------------+------------+------------------+ | USER | EVENT_NAME | COUNT_STAR | MAX_TOTAL_MEMORY | +----------------------+---------------------------+------------+------------------+ | destructive_operator | statement/sql/select | 4 | 537450710 | | rdsadmin | statement/sql/select | 4172 | 3290981 | | rdsadmin | statement/sql/show_tables | 2 | 3615821 | | rdsadmin | statement/sql/show_fields | 2 | 3459965 | | rdsadmin | statement/sql/show_status | 75 | 1914976 | +----------------------+---------------------------+------------+------------------+ 5 rows in set (0.00 sec) mysql> SELECT HOST,EVENT_NAME,COUNT_STAR,MAX_TOTAL_MEMORY FROM performance_schema.events_statements_summary_by_host_by_event_name WHERE HOST != 'localhost' AND COUNT_STAR>0 ORDER BY MAX_CONTROLLED_MEMORY DESC LIMIT 5; +------------+----------------------+------------+------------------+ | HOST | EVENT_NAME | COUNT_STAR | MAX_TOTAL_MEMORY | +------------+----------------------+------------+------------------+ | 10.0.8.231 | statement/sql/select | 4 | 537450710 | +------------+----------------------+------------+------------------+ 1 row in set (0.00 sec)
Exemple 3 : la mémoire libérable diminue continuellement et n’est pas récupérée
Le moteur de base de données InnoDB utilise divers événements de suivi de mémoire spécialisés pour différents composants. Ces événements spécifiques permettent un suivi granulaire de l’utilisation de la mémoire dans les principaux sous-systèmes InnoDB, par exemple :
-
memory/innodb/buf0buf— Dédié à la surveillance des allocations de mémoire pour le pool de tampons InnoDB. -
memory/innodb/ibuf0ibuf— Suit spécifiquement les modifications de mémoire liées au tampon de modification d’InnoDB.
Pour identifier les principaux consommateurs de mémoire, nous pouvons interroger sys.memory_global_by_current_bytes :
mysql> SELECT event_name,current_alloc FROM sys.memory_global_by_current_bytes LIMIT 10; +-----------------------------------------------------------------+---------------+ | event_name | current_alloc | +-----------------------------------------------------------------+---------------+ | memory/innodb/memory | 5.28 GiB | | memory/performance_schema/table_io_waits_summary_by_index_usage | 495.00 MiB | | memory/performance_schema/table_shares | 488.00 MiB | | memory/sql/TABLE_SHARE::mem_root | 388.95 MiB | | memory/innodb/std | 226.88 MiB | | memory/innodb/fil0fil | 198.49 MiB | | memory/sql/binlog_io_cache | 128.00 MiB | | memory/innodb/mem0mem | 96.82 MiB | | memory/innodb/dict0dict | 96.76 MiB | | memory/performance_schema/rwlock_instances | 88.00 MiB | +-----------------------------------------------------------------+---------------+ 10 rows in set (0.00 sec)
Les résultats révèlent que memory/innodb/memory est le principal consommateur, lequel utilise 5,28 Gio sur la mémoire actuellement allouée. Cet événement sert de catégorie pour les allocations de mémoire entre divers composants InnoDB non associés à des événements d’attente plus spécifiques, comme memory/innodb/buf0buf mentionné précédemment.
Après avoir établi que les composants InnoDB sont les principaux consommateurs de mémoire, nous pouvons approfondir notre recherche à l’aide de la commande MySQL suivante :
SHOW ENGINE INNODB STATUS \G;
La commande SHOW ENGINE INNODB STATUS
En analysant la section BUFFER POOL AND MEMORY du rapport d’état d’InnoDB, nous constatons que 5 051 647 748 octets (4,7 Gio) sont alloués au cache d’objets du dictionnairememory/innodb/memory.
---------------------- BUFFER POOL AND MEMORY ---------------------- Total large memory allocated 0Dictionary memory allocated 5051647748Buffer pool size 170512 Free buffers 142568 Database pages 27944 Old database pages 10354 Modified db pages 6 Pending reads 0
Le cache d’objets du dictionnaire est un cache global partagé qui stocke en mémoire les objets du dictionnaire de données qui ont déjà été consultés afin de permettre leur réutilisation et d’améliorer les performances. L’allocation de mémoire élevée pour le cache d’objets du dictionnaire suggère qu’un grand nombre d’objets de base de données se trouve dans le cache.
Maintenant que nous savons que le cache du dictionnaire de données est l’un des principaux consommateurs, nous allons l’inspecter pour détecter les tables ouvertes. Pour connaître le nombre de tables ouvertes dans le cache de définition de table, interrogez la variable d’état globale open_table_definitions
mysql> show global status like 'open_table_definitions'; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | Open_table_definitions | 20000 | +------------------------+-------+ 1 row in set (0.00 sec)
Pour plus d’informations, consultez How MySQL Opens and Closes Tables
Vous pouvez restreindre le nombre de définitions de tables dans le cache du dictionnaire de données en limitant le paramètre table_definition_cache dans le groupe de paramètres du cluster de bases de données ou de l’instance de base de données. Pour Aurora MySQL, cette valeur joue le rôle de limite flexible pour le nombre de tables dans le cache de définition de table. La valeur par défaut dépend de la classe d’instance et est définie comme suit :
LEAST({DBInstanceClassMemory/393040}, 20000)
Lorsque le nombre de tables dépasse la limite table_definition_cache, un mécanisme basé sur les éléments les moins récemment utilisés, ou LRU, évacue et supprime des tables du cache. Toutefois, les tables impliquées dans les relations de clé étrangère ne sont pas placées dans la liste LRU, ce qui empêche leur suppression.
Dans notre scénario actuel, nous exécutons FLUSH TABLES
mysql> show global status like 'open_table_definitions'; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | Open_table_definitions | 12 | +------------------------+-------+ 1 row in set (0.00 sec)
Malgré cette réduction, nous observons que l’allocation de mémoire pour memory/innodb/memory de 5,18 Gio reste élevée, et que la mémoire allouée au dictionnaire reste également inchangée. Cela ressort clairement des résultats de requête suivants :
mysql> SELECT event_name,current_alloc FROM sys.memory_global_by_current_bytes LIMIT 10; +-----------------------------------------------------------------+---------------+ | event_name | current_alloc | +-----------------------------------------------------------------+---------------+ | memory/innodb/memory | 5.18 GiB | | memory/performance_schema/table_io_waits_summary_by_index_usage | 495.00 MiB | | memory/performance_schema/table_shares | 488.00 MiB | | memory/sql/TABLE_SHARE::mem_root | 388.95 MiB | | memory/innodb/std | 226.88 MiB | | memory/innodb/fil0fil | 198.49 MiB | | memory/sql/binlog_io_cache | 128.00 MiB | | memory/innodb/mem0mem | 96.82 MiB | | memory/innodb/dict0dict | 96.76 MiB | | memory/performance_schema/rwlock_instances | 88.00 MiB | +-----------------------------------------------------------------+---------------+ 10 rows in set (0.00 sec)
---------------------- BUFFER POOL AND MEMORY ---------------------- Total large memory allocated 0Dictionary memory allocated 5001599639Buffer pool size 170512 Free buffers 142568 Database pages 27944 Old database pages 10354 Modified db pages 6 Pending reads 0
Cette utilisation constamment élevée de la mémoire peut être attribuée aux tables impliquées dans des relations de clé étrangère. Ces tables ne sont pas placées dans la liste LRU de tables à supprimer, ce qui explique pourquoi l’allocation de mémoire reste élevée même après avoir vidé le cache de définition de table.
Pour résoudre ce problème :
-
Passez en revue et optimisez le schéma de votre base de données, en particulier les relations de clé étrangère.
-
Envisagez de passer à une classe d’instance de base de données plus vaste qui dispose de plus de mémoire pour héberger les objets de votre dictionnaire.
En suivant ces étapes et en comprenant les modèles d’allocation de mémoire, vous gérerez plus efficacement l’utilisation de la mémoire dans votre instance de base de données Aurora MySQL et éviterez les problèmes de performances potentiels dus à la pression de la mémoire.
