Le traduzioni sono generate tramite traduzione automatica. In caso di conflitto tra il contenuto di una traduzione e la versione originale in Inglese, quest'ultima prevarrà.
Risoluzione dei problemi di utilizzo della memoria per i database Aurora MySQL
Sebbene CloudWatch, Monitoraggio avanzato e Approfondimenti sulle prestazioni forniscano una buona panoramica dell’utilizzo della memoria a livello di sistema operativo, ad esempio la quantità di memoria utilizzata dal processo del database, non consentono di analizzare quali connessioni o componenti all’interno del motore potrebbero causare questo utilizzo di memoria.
Per risolvere questo problema, è possibile utilizzare lo schema delle prestazioni e lo schema sys. Nella versione 3 di Aurora MySQL, la strumentazione della memoria viene abilitata per impostazione predefinita quando viene abilitato lo schema delle prestazioni. Nella versione 2 di Aurora MySQL, solo la strumentazione della memoria per l’utilizzo della memoria dello schema delle prestazioni viene abilitata per impostazione predefinita. Per informazioni sulle tabelle disponibili nello schema delle prestazioni per monitorare l’utilizzo della memoria e abilitare la strumentazione della memoria dello schema delle prestazioni, consulta la pagina relativa alle tabelle di riepilogo della memoria
Sebbene nello schema delle prestazioni siano disponibili informazioni dettagliate per monitorare l’utilizzo corrente della memoria, lo schema sys
Nello schema sys sono disponibili le seguenti viste per monitorare l’utilizzo della memoria per connessione, componente e query.
| Vista | Descrizione |
|---|---|
|
Fornisce informazioni sull’utilizzo della memoria del motore per host. Ciò può essere utile per identificare quali server di applicazioni o host client stanno consumando memoria. |
|
|
Fornisce informazioni sull’utilizzo della memoria del motore per ID thread. L’ID thread in MySQL può essere una connessione client o un thread in background. È possibile mappare gli ID thread agli ID connessione MySQL utilizzando la vista sys.processlist |
|
|
Fornisce informazioni sull’utilizzo della memoria del motore per utente. Ciò può essere utile per identificare quali account utente o client stanno consumando memoria. |
|
|
Fornisce informazioni sull’utilizzo della memoria del motore per componente del motore. Ciò può essere utile per identificare l’utilizzo della memoria a livello globale da parte dei buffer o dei componenti del motore. Ad esempio, è possibile visualizzare l’evento |
|
|
Fornisce una panoramica dell’utilizzo totale della memoria monitorata nel motore del database. |
In Aurora MySQL 3.05 e versioni successive, è anche possibile tenere traccia dell’utilizzo massimo della memoria per digest di istruzioni nelle tabelle di riepilogo delle istruzioni dello schema delle prestazioniMAX_TOTAL_MEMORY consente di identificare la quantità massima di memoria utilizzata dal digest di query dall’ultimo ripristino delle statistiche o dal riavvio dell’istanza database. Ciò può essere utile per identificare query specifiche che potrebbero consumare molta memoria.
Nota
Lo schema delle prestazioni e lo schema sys mostrano l’utilizzo corrente della memoria sul server e i valori massimi di memoria consumata per ogni connessione e componente del motore. Poiché lo schema delle prestazioni viene mantenuto in memoria, le informazioni vengono reimpostate al riavvio dell’istanza database. Per mantenere una cronologia nel tempo, è consigliabile configurare il recupero e l’archiviazione di questi dati al di fuori dello schema delle prestazioni.
Argomenti
Esempio 1: utilizzo continuo ed elevato della memoria
Guardando a livello globale FreeableMemory in CloudWatch, possiamo vedere che l’utilizzo della memoria è aumentato notevolmente alle 02:59 UTC del 2024-03-26.
Questo non ci fornisce un quadro completo. Per determinare quale componente utilizza più memoria, è possibile accedere al database e vedere sys.memory_global_by_current_bytes. Questa tabella contiene un elenco di eventi di memoria monitorati da MySQL, insieme a informazioni sull’allocazione di memoria per evento. Ogni evento di monitoraggio della memoria inizia con memory/%, seguito da altre informazioni sul componente/funzionalità del motore a cui è associato l’evento.
Ad esempio, memory/performance_schema/% è per gli eventi di memoria relativi allo schema delle prestazioni, memory/innodb/% è per InnoDB e così via. Per ulteriori informazioni sulle convenzioni di denominazione degli eventi, consulta la pagina relativa alle convenzioni di denominazione per la strumentazione dello schema delle prestazioni
Dalla seguente query, possiamo trovare il probabile responsabile in base a current_alloc, ma possiamo anche vedere molti eventi 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)
Abbiamo detto in precedenza che lo schema delle prestazioni è archiviato in memoria, il che significa che viene anche monitorato nella strumentazione della memoria performance_schema.
Nota
Se si riscontra che lo schema delle prestazioni utilizza molta memoria e si desidera limitarne l’utilizzo, è possibile ottimizzare i parametri del database in base alle proprie esigenze. Per ulteriori informazioni, consulta la pagina relativa al modello di allocazione della memoria dello schema delle prestazioni
Per una maggiore leggibilità, è possibile rieseguire la stessa query escludendo gli eventi dello schema delle prestazioni. L’output mostra quanto segue:
-
Il principale consumatore di memoria è
memory/sql/Prepared_statement::main_mem_root. -
La colonna
current_allocindica che MySQL ha 4,91 GiB attualmente allocati a questo evento. -
high_alloc columnindica che 4,91 GiB è il valore massimo dicurrent_allocdall’ultimo ripristino delle statistiche o dal riavvio del server. Ciò significa chememory/sql/Prepared_statement::main_mem_rootè al suo valore massimo.
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)
Dal nome dell’evento, possiamo dedurre che questa memoria viene utilizzata per le istruzioni preparate. Per vedere quali connessioni utilizzano questa memoria, è possibile controllare memory_by_thread_by_current_bytes
Nell’esempio seguente, a ogni connessione sono allocati circa 7 MiB, con un valore massimo di circa 6,29 MiB (current_max_alloc). Ciò è logico perché l’esempio utilizza sysbench con 80 tabelle e 800 connessioni con istruzioni preparate. Se si desidera ridurre l’utilizzo della memoria in questo scenario, è possibile ottimizzare l’utilizzo delle istruzioni preparate da parte dell’applicazione per ridurre il consumo di memoria.
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)
Come accennato in precedenza, il valore dell’ID thread (thd_id) può riferirsi ai thread in background del server o alle connessioni al database. Per mappare i valori dell’ID thread agli ID connessione al database, è possibile utilizzare la tabella performance_schema.threads o la vista sys.processlist, dove conn_id è l’ID connessione.
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)
Ora interrompiamo il carico di lavoro sysbench, che chiude le connessioni e libera la memoria. Ricontrollando gli eventi, possiamo verificare che la memoria è stata liberata, ma high_alloc indica ancora il limite massimo. La colonna high_alloc può essere molto utile per individuare brevi picchi di utilizzo della memoria, che potrebbero non essere immediatamente individuabili da current_alloc, che mostra solo la memoria attualmente allocata.
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)
Se si desidera ripristinare high_alloc, è possibile troncare le tabelle di riepilogo della memoria performance_schema, ma ciò ripristina tutta la strumentazione della memoria. Per ulteriori informazioni, consulta la pagina relativa alle caratteristiche generali delle tabelle dello schema delle prestazioni
Nell’esempio seguente, possiamo vedere che high_alloc viene ripristinato dopo il troncamento.
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)
Esempio 2: picchi di memoria transitori
Un altro evento comune è rappresentato dai brevi picchi di utilizzo della memoria su un server di database. Possono trattarsi di cali periodici di memoria liberabile, difficili da risolvere con current_alloc e sys.memory_global_by_current_bytes, in quanto la memoria è già stata liberata.
Nota
Se le statistiche dello schema delle prestazioni sono state ripristinate o l’istanza database è stata riavviata, queste informazioni non saranno disponibili in sys o performance_schema. Per mantenere queste informazioni, consigliamo di configurare la raccolta di metriche esterne.
Il seguente grafico della metrica os.memory.free di Monitoraggio avanzato mostra brevi picchi di utilizzo della memoria di 7 secondi. Monitoraggio avanzato consente di monitorare a intervalli di appena 1 secondo, il che è perfetto per rilevare picchi transitori come questi.
Per aiutare a diagnosticare la causa dell’utilizzo della memoria, possiamo utilizzare una combinazione di high_alloc nelle viste di riepilogo della memoria sys e nelle tabelle di riepilogo delle istruzioni dello schema delle prestazioni
Come previsto, poiché l’utilizzo della memoria non è attualmente elevato, non riusciamo a individuare alcun problema rilevante nella vista dello schema sys in current_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)
Espandendo la vista per ordinare in base a high_alloc, ora possiamo vedere che il componente memory/temptable/physical_ram è un ottimo candidato. Al suo valore massimo, ha consumato 515,00 MiB.
Come suggerisce il nome, memory/temptable/physical_ram misura l’utilizzo della memoria per il motore di archiviazione TEMP in MySQL, introdotto in MySQL 8.0. Per ulteriori informazioni su come MySQL utilizza le tabelle temporanee, consulta la pagina relativa all’uso delle tabelle temporanee interne in MySQL
Nota
In questo esempio utilizziamo la vista sys.x$memory_global_by_current_bytes.
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)
Nell’Esempio 1: utilizzo continuo ed elevato della memoria, abbiamo controllato l’utilizzo corrente della memoria per ogni connessione per determinare quale connessione fosse responsabile dell’utilizzo della memoria in questione. In questo esempio, la memoria è già stata liberata, quindi non è utile controllare l’utilizzo della memoria per le connessioni correnti.
Per approfondire e trovare le istruzioni, gli utenti e gli host responsabili, utilizziamo lo schema delle prestazioni. Lo schema delle prestazioni contiene più tabelle di riepilogo delle istruzioni suddivise in base a diverse dimensioni, quali nome dell’evento, digest di istruzioni, host, thread e utente. Ogni vista consentirà di approfondire dove vengono eseguite determinate istruzioni e cosa stanno facendo. Questa sezione si concentra su MAX_TOTAL_MEMORY, ma è possibile trovare ulteriori informazioni su tutte le colonne disponibili nella documentazione relativa alle tabelle di riepilogo delle istruzioni dello schema delle prestazioni
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)
Per prima cosa controlliamo events_statements_summary_by_digest per vedere MAX_TOTAL_MEMORY.
Da ciò possiamo vedere quanto segue:
-
La query con digest
20676ce4a690592ff05debcffcbc26faeb76f22005e7628364d7a498769d0c4asembra essere un buon candidato per questo utilizzo di memoria.MAX_TOTAL_MEMORYè 537450710, che corrisponde al valore massimo rilevato per l’eventomemory/temptable/physical_raminsys.x$memory_global_by_current_bytes. -
È stato eseguito quattro volte (
COUNT_STAR), la prima alle 04:08:34.943256 del 2024-03-26 e l’ultima alle 04:43:06.998310 del 2024-03-26.
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)
Ora che conosciamo il digest incriminato, possiamo ottenere maggiori dettagli come il testo della query, l’utente che l’ha eseguita e dove è stata eseguita. In base al testo del digest restituito, possiamo vedere che si tratta di un’espressione di tabella comune (CTE) che crea quattro tabelle temporanee ed esegue quattro scansioni di tabelle, il che è molto inefficiente.
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)
Per ulteriori informazioni sulla tabella events_statements_summary_by_digest e su altre tabelle di riepilogo delle istruzioni dello schema delle prestazioni, consulta la pagina relativa alle tabelle di riepilogo delle istruzioni
È inoltre possibile eseguire un’istruzione EXPLAIN
Nota
EXPLAIN ANALYZE può fornire più informazioni di EXPLAIN, ma esegue anche la query, quindi prestare attenzione.
-- 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)
Ma chi lo ha eseguito? Nello schema delle prestazioni possiamo vedere che l’utente destructive_operator aveva un valore MAX_TOTAL_MEMORY di 537450710, che corrisponde ancora una volta ai risultati precedenti.
Nota
Lo schema delle prestazioni è archiviato in memoria, quindi non deve essere considerato l’unica fonte per il controllo. Se è necessario mantenere una cronologia delle istruzioni eseguite e degli utenti che le hanno eseguite, si consiglia di abilitare Aurora Advanced Auditing. Se è necessario conservare anche le informazioni sull’utilizzo della memoria, si consiglia di configurare il monitoraggio per esportare e archiviare questi valori.
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)
Esempio 3: la memoria liberabile diminuisce continuamente e non viene recuperata
Il motore di database InnoDB utilizza una serie di eventi di monitoraggio della memoria specializzati per diversi componenti. Questi eventi specifici consentono un monitoraggio granulare dell’utilizzo della memoria nei sottosistemi chiave di InnoDB, ad esempio:
-
memory/innodb/buf0buf: dedicato al monitoraggio delle allocazioni di memoria per il pool di buffer InnoDB. -
memory/innodb/ibuf0ibuf: monitora in modo specifico le modifiche di memoria relative al buffer di modifiche di InnoDB.
Per identificare i principali consumatori di memoria, è possibile eseguire una query su 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)
I risultati mostrano che memory/innodb/memory è il principale consumatore, con 5,28 GiB di memoria attualmente allocata. Questo evento funge da categoria per le allocazioni di memoria tra vari componenti InnoDB non associati a eventi di attesa più specifici, come memory/innodb/buf0buf menzionato in precedenza.
Dopo aver stabilito che i componenti InnoDB sono i principali consumatori di memoria, possiamo approfondire le specifiche utilizzando il seguente comando MySQL:
SHOW ENGINE INNODB STATUS \G;
Il comando SHOW ENGINE INNODB STATUS
Analizzando la sezione BUFFER POOL AND MEMORY del rapporto sullo stato di InnoDB, vediamo che 5.051.647.748 byte (4,7 GiB) sono allocati alla cache degli oggetti del dizionariomemory/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
La cache degli oggetti del dizionario è una cache globale condivisa che archivia in memoria gli oggetti del dizionario di dati a cui si è avuto accesso in precedenza per consentire il riutilizzo degli oggetti e migliorare le prestazioni. L’elevata allocazione di memoria alla cache degli oggetti del dizionario suggerisce la presenza di un numero elevato di oggetti di database nella cache del dizionario di dati.
Ora che sappiamo che la cache del dizionario di dati è un consumatore primario, procediamo a ispezionare la cache del dizionario di dati per individuare eventuali tabelle aperte. Per trovare il numero di tabelle nella cache delle definizioni di tabella, eseguire una query sulla variabile di stato globale open_table_definition
mysql> show global status like 'open_table_definitions'; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | Open_table_definitions | 20000 | +------------------------+-------+ 1 row in set (0.00 sec)
Per ulteriori informazioni, consulta la pagina relativa ad apertura e chiusura delle tabelle in MySQL
È possibile limitare il numero di definizioni di tabella nella cache del dizionario di dati limitando il parametro table_definition_cache nel cluster di database o nel gruppo di parametri dell’istanza database. Per Aurora MySQL, questo valore funge da limite flessibile per il numero di tabelle nella cache delle definizioni di tabella. Il valore predefinito dipende dalla classe dell’istanza ed è impostato come segue:
LEAST({DBInstanceClassMemory/393040}, 20000)
Quando il numero di tabelle supera il limite table_definition_cache, un meccanismo basato sugli elementi utilizzati meno di recente (LRU) elimina e rimuove le tabelle dalla cache. Tuttavia, le tabelle coinvolte nelle relazioni con chiavi esterne non vengono inserite nell’elenco LRU, impedendone la rimozione.
Nel nostro scenario attuale, eseguiamo FLUSH TABLES
mysql> show global status like 'open_table_definitions'; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | Open_table_definitions | 12 | +------------------------+-------+ 1 row in set (0.00 sec)
Nonostante questa riduzione, osserviamo che l’allocazione di memoria per memory/innodb/memory rimane elevata a 5,18 GiB e anche la memoria del dizionario allocata rimane invariata. Ciò è evidente dai seguenti risultati della query:
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
Questo utilizzo persistentemente elevato della memoria può essere attribuito alle tabelle coinvolte nelle relazioni con chiavi esterne. Queste tabelle non vengono inserite nell’elenco LRU per la rimozione, il che spiega perché l’allocazione di memoria rimane elevata anche dopo aver svuotato la cache delle definizioni di tabella.
Per risolvere questo problema:
-
Esaminare e ottimizzare lo schema del database, in particolare le relazioni con chiavi esterne.
-
Valutare la possibilità di passare a una classe di istanze database più ampia con più memoria per ospitare gli oggetti del dizionario.
Seguendo questi passaggi e comprendendo i modelli di allocazione della memoria, è possibile gestire meglio l’utilizzo della memoria nell’istanza database Aurora MySQL e prevenire potenziali problemi di prestazioni dovuti alla pressione della memoria.
