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 Enhanced Monitoring e Performance Insights 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 e sys lo schema delle prestazioni. In Aurora MySQL versione 3, la strumentazione di memoria è abilitata per impostazione predefinita quando lo schema delle prestazioni è abilitato. In Aurora MySQL versione 2, per impostazione predefinita è abilitata solo la strumentazione di memoria per l'utilizzo della memoria Performance Schema. Per informazioni sulle tabelle disponibili nello schema delle prestazioni per tenere traccia dell'utilizzo della memoria e abilitare la strumentazione di memoria dello schema di prestazioni, vedere Tabelle di riepilogo della memoria
Sebbene nello schema delle prestazioni siano disponibili informazioni dettagliate per tenere traccia dell'utilizzo corrente della memoria, lo schema sys di MySQL
Nello sys schema, sono disponibili le seguenti viste per tenere traccia dell'utilizzo della memoria per connessione, componente e query.
| Vista | Descrizione |
|---|---|
|
Fornisce informazioni sull'utilizzo della memoria del motore da parte dell'host. Ciò può essere utile per identificare quali server applicativi o host client stanno consumando memoria. |
|
|
Fornisce informazioni sull'utilizzo della memoria del motore in base all'ID del thread. L'ID del thread in MySQL può essere una connessione client o un thread in background. È possibile mappare il thread IDs alla IDs connessione MySQL utilizzando la vista sys.processlist o la tabella performance_schema.threads |
|
|
Fornisce informazioni sull'utilizzo della memoria del motore da parte dell'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 in base ai buffer o ai componenti del motore. Ad esempio, potresti vedere l' |
|
|
Fornisce una panoramica dell'utilizzo totale della memoria tracciata nel motore di database. |
In Aurora MySQL versione 3.05 e successive, puoi anche tenere traccia dell'utilizzo massimo della memoria tramite statement digest nelle tabelle di riepilogo delle istruzioni Performance Schema.MAX_TOTAL_MEMORY colonna può aiutarti a identificare la quantità massima di memoria utilizzata da Query Digest dall'ultima reimpostazione delle statistiche o dal riavvio dell'istanza del database. Ciò può essere utile per identificare query specifiche che potrebbero consumare molta memoria.
Nota
Lo schema e lo sys schema delle prestazioni mostrano l'utilizzo corrente della memoria sul server e i livelli massimi di memoria consumata per ogni componente del motore e della connessione. Poiché lo schema delle prestazioni viene mantenuto in memoria, le informazioni vengono reimpostate al riavvio dell'istanza DB. Per mantenere una cronologia nel tempo, si consiglia di 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 FreeableMemory a livello globale CloudWatch, possiamo vedere che l'utilizzo della memoria è aumentato notevolmente alle 02:59 UTC del 26/03/2020.
Questo non ci dice il quadro completo. Per determinare quale componente utilizza più memoria, puoi accedere al database e guardaresys.memory_global_by_current_bytes. Questa tabella contiene un elenco di eventi di memoria che MySQL tiene traccia, insieme a informazioni sull'allocazione della memoria per evento. Ogni evento di tracciamento della memoria inizia conmemory/%, 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, vedere Convenzioni di denominazione degli strumenti Performance Schema nella documentazione MySQL
Dalla seguente interrogazione, possiamo trovare il probabile colpevole in base acurrent_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 registrato nella strumentazione di memoria. performance_schema
Nota
Se riscontri che il Performance Schema utilizza molta memoria e desideri limitarne l'utilizzo, puoi regolare i parametri del database in base alle tue esigenze. Per ulteriori informazioni, vedere Il modello di allocazione della memoria Performance Schema
Per motivi di leggibilità, è possibile eseguire nuovamente la stessa query ma escludere gli eventi di Performance Schema. L'output mostra quanto segue:
-
Il principale consumatore di memoria è
memory/sql/Prepared_statement::main_mem_root. -
La
current_alloccolonna ci dice che MySQL ha 4,91 GiB attualmente assegnati a questo evento. -
Ci
high_alloc columndice che 4,91 GiB è il limite massimo dall'ultima reimpostazionecurrent_allocdelle 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 dire che questa memoria viene utilizzata per le dichiarazioni preparate. Se vuoi vedere quali connessioni utilizzano questa memoria, puoi controllare memory_by_thread_by_current_bytes
Nell'esempio seguente, a ogni connessione sono allocati circa 7 MiB, con un limite massimo di circa 6,29 MiB (). current_max_alloc Ciò ha senso, perché l'esempio utilizza 80 tabelle e 800 sysbench connessioni con istruzioni preparate. Se si desidera ridurre l'utilizzo della memoria in questo scenario, è possibile ottimizzare l'utilizzo da parte dell'applicazione delle istruzioni preparate 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 del thread (thd_id) qui può riferirsi ai thread in background del server o alle connessioni al database. Se vuoi mappare i valori dell'ID del thread alla connessione al database IDs, puoi usare la performance_schema.threads tabella o la sys.processlist vista, conn_id dov'è l'ID di 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 sysbench carico di lavoro, che chiude le connessioni e libera la memoria. Ricontrollando gli eventi, possiamo confermare che la memoria è stata liberata, ma ci dice high_alloc comunque qual è il limite massimo. La high_alloc colonna può essere molto utile per identificare brevi picchi di utilizzo della memoria, dai quali potrebbe non essere possibile identificare immediatamente l'utilizzocurrent_alloc, poiché 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 eseguire il ripristinohigh_alloc, è possibile troncare le tabelle di riepilogo della performance_schema memoria, ma in questo modo viene ripristinata tutta la strumentazione della memoria. Per ulteriori informazioni, vedere le caratteristiche generali della tabella 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 sono i brevi picchi di utilizzo della memoria su un server di database. Possono trattarsi di cadute periodiche della memoria liberabilesys.memory_global_by_current_bytes, difficili da risolvere current_alloc in quanto la memoria è già stata liberata.
Nota
Se le statistiche dello schema delle prestazioni sono state reimpostate o l'istanza del database è stata riavviata, queste informazioni non saranno disponibili in o p. sys erformance_schema Per conservare queste informazioni, ti consigliamo di configurare la raccolta di metriche esterne.
Il seguente grafico della os.memory.free metrica di Enhanced Monitoring mostra brevi picchi di 7 secondi nell'utilizzo della memoria. Enhanced Monitoring consente di eseguire il monitoraggio 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 delle viste di riepilogo della sys memoria e delle tabelle di high_alloc riepilogo delle istruzioni Performance Schema per cercare di identificare le sessioni e le
Come previsto, poiché l'utilizzo della memoria non è attualmente elevato, nella visualizzazione sys dello schema sottostante non è possibile individuare i principali autori di violazioni. 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 visualizzazione in base all'ordinehigh_alloc, ora possiamo vedere che il memory/temptable/physical_ram componente è un ottimo candidato in questo caso. Al suo massimo, ha consumato 515,00 MiB.
Come suggerisce il nome, l'utilizzo della memoria memory/temptable/physical_ram degli strumenti per il motore di TEMP archiviazione in MySQL, introdotto in MySQL 8.0. Per ulteriori informazioni su come MySQL utilizza le tabelle temporanee, vedere Uso interno delle tabelle temporanee in MySQL nella documentazione di MySQL
Nota
Stiamo usando la vista in questo esempio. 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)
NelEsempio 1: utilizzo continuo ed elevato della memoria, abbiamo controllato l'utilizzo corrente della memoria per ogni connessione per determinare quale connessione è 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 dichiarazioni, gli utenti e gli host offensivi, utilizziamo il Performance Schema. Lo schema delle prestazioni contiene più tabelle di riepilogo delle istruzioni suddivise in diverse dimensioni, come nome dell'evento, statement digest, host, thread e utente. Ogni visualizzazione ti consentirà di approfondire dove vengono eseguite determinate istruzioni e cosa stanno facendo. Questa sezione è dedicata aMAX_TOTAL_MEMORY, ma è possibile trovare ulteriori informazioni su tutte le colonne disponibili nella documentazione delle 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 vedereMAX_TOTAL_MEMORY.
Da ciò possiamo vedere quanto segue:
-
La query con digest
20676ce4a690592ff05debcffcbc26faeb76f22005e7628364d7a498769d0c4asembra essere un buon candidato per questo utilizzo della memoria. IlMAX_TOTAL_MEMORYnumero è 537450710, che corrisponde al picco massimo registrato durante l'evento.memory/temptable/physical_ramsys.x$memory_global_by_current_bytes -
È stato eseguito quattro volte (
COUNT_STAR), la prima alle 04:08:34.943 256 del 26/03/2020 e l'ultima alle 04:43:06.998 310 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 events_statements_summary_by_digest tabella e su altre tabelle di riepilogo delle istruzioni Performance Schema, vedere Tabelle di riepilogo delle dichiarazioni
Puoi anche eseguire un'istruzione EXPLAIN o EXPLAIN
Nota
EXPLAIN ANALYZEpuò fornire più informazioni diEXPLAIN, ma esegue anche la query, quindi fai 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 gestiva? Possiamo vedere nello schema delle prestazioni che l'destructive_operatorutente aveva 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 da quali utenti, si consiglia di abilitare Aurora Advanced Auditing. Se è inoltre necessario conservare 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 tracciamento della memoria specializzati per diversi componenti. Questi eventi specifici consentono il tracciamento granulare dell'utilizzo della memoria nei principali sottosistemi InnoDB, ad esempio:
-
memory/innodb/buf0buf— Dedicato al monitoraggio delle allocazioni di memoria per il pool di buffer InnoDB. -
memory/innodb/ibuf0ibuf— Tiene traccia in particolare delle modifiche alla memoria relative al buffer di modifica di InnoDB.
Per identificare i principali consumatori di memoria, possiamo interrogare: 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, utilizzando 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, memory/innodb/buf0buf come 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 BUFFER POOL AND MEMORY sezione del rapporto sullo stato di InnoDB, vediamo che 5.051.647.748 byte (4,7 GiB) sono allocati alla cache degli oggetti del dizionario, che rappresenta l'89% della memoria tracciata damemory/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 accedeva in precedenza per consentire il riutilizzo degli oggetti e migliorare le prestazioni. L'elevata allocazione di memoria alla cache degli oggetti del dizionario suggerisce un gran numero di oggetti di database nella cache del dizionario di dati.
Ora che sappiamo che la cache del dizionario dei dati è un consumatore primario, procediamo a ispezionare la cache del dizionario dei dati per individuare eventuali tabelle aperte. Per trovare il numero di tabelle nella cache delle definizioni delle tabelle, interroga la 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 dei dati limitando il table_definition_cache parametro nel cluster DB o nel gruppo di parametri dell'istanza DB. Per Aurora MySQL, questo valore funge da limite flessibile per il numero di tabelle nella cache delle definizioni delle tabelle. Il valore predefinito dipende dalla classe dell'istanza ed è impostato come segue:
LEAST({DBInstanceClassMemory/393040}, 20000)
Quando il numero di tabelle supera il table_definition_cache limite, un meccanismo usato 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 per cancellare la cache delle definizioni delle tabelle
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 delle interrogazioni:
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 delle tabelle.
Per risolvere il problema:
-
Rivedi e ottimizza lo schema del database, in particolare le relazioni con chiavi esterne.
-
Valuta la possibilità di passare a una classe di istanza DB più grande 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 DB Aurora MySQL e prevenire potenziali problemi di prestazioni dovuti alla pressione della memoria.
