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 carico di lavoro per i database Aurora MySQL Il carico di lavoro del database può essere visualizzato come lettura e scrittura. Una volta compreso il “normale” carico di lavoro del database, è possibile ottimizzare le query e il server di database per soddisfare la domanda man mano che questa cambia. Esistono diversi motivi per cui le prestazioni possono cambiare, quindi il primo passo è capire cosa è cambiato. + È stato effettuato un aggiornamento della versione principale o secondario? Un aggiornamento della versione principale include modifiche al codice del motore, in particolare nell’ottimizzatore, che possono modificare il piano di esecuzione delle query. Quando si aggiornano le versioni del database, in particolare le versioni principali, è molto importante analizzare il carico di lavoro del database e ottimizzarlo di conseguenza. L’ottimizzazione può comportare l’ottimizzazione e la riscrittura delle query o l’aggiunta e l’aggiornamento delle impostazioni dei parametri, a seconda dei risultati dei test. Capire cosa sta causando l’impatto ti consentirà di iniziare a concentrarti su quell’area specifica. Per ulteriori informazioni, consulta [Novità di MySQL 8.0](https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html) e [Server and status variables and options added, deprecated, or removed in MySQL 8.0](https://dev.mysql.com/doc/refman/8.0/en/added-deprecated-removed.html) nella documentazione di MySQL e [Confronto tra Aurora MySQL versione 2 e Aurora MySQL versione 3](AuroraMySQL.Compare-v2-v3.md) + Si è verificato un aumento dei dati in fase di elaborazione (numero di righe)? + Sono presenti più query in esecuzione contemporaneamente? + Sono state apportate modifiche allo schema o al database? + Sono stati rilevati difetti o correzioni del codice? **Contents** + [Parametri dell’host dell’istanza](#ams-workload-instance) + [Utilizzo CPU](#ams-workload-cpu) + [Utilizzo della memoria](#ams-workload-instance-memory) + [Throughput di rete](#ams-workload-network) + [Parametri del database](#ams-workload-db) + [Risoluzione dei problemi di utilizzo della memoria per i database Aurora MySQL](ams-workload-memory.md) + [Esempio 1: utilizzo continuo ed elevato della memoria](ams-workload-memory.md#ams-workload-memory.example1) + [Esempio 2: picchi di memoria transitori](ams-workload-memory.md#ams-workload-memory.example2) + [Esempio 3: la memoria liberabile diminuisce continuamente e non viene recuperata](ams-workload-memory.md#ams-workload-memory.example3) + [Risoluzione dei problemi di memoria insufficiente per i database Aurora MySQL](AuroraMySQLOOM.md) ## Parametri dell’host dell’istanza Monitora le metriche dell’host dell’istanza come CPU, memoria e attività di rete per capire se è stata apportata una modifica del carico di lavoro. Esistono due concetti principali per comprendere le modifiche del carico di lavoro: + Utilizzo: utilizzo di un dispositivo, ad esempio CPU o disco. Può essere basato sul tempo o sulla capacità. + Basato sul tempo: la quantità di tempo in cui una risorsa è occupata in un determinato periodo di osservazione. + Basato sulla capacità: la quantità di throughput che un sistema o un componente è in grado di fornire, espressa in percentuale della sua capacità. + Saturazione: il grado in cui una risorsa richiede più lavoro di quanto ne possa elaborare. Quando l’utilizzo basato sulla capacità raggiunge il 100%, il lavoro aggiuntivo non può essere elaborato e deve essere messo in coda. ### Utilizzo CPU È possibile fare riferimento ai seguenti strumenti per identificare l’utilizzo e la saturazione della CPU: + CloudWatch fornisce la metrica `CPUUtilization`. Se raggiunge il 100%, l’istanza è satura. Tuttavia, le metriche di CloudWatch hanno una media di 1 minuto e mancano di granularità. Per ulteriori informazioni sui parametri di CloudWatch, consulta [Parametri a livello di istanza per Amazon Aurora](Aurora.AuroraMonitoring.Metrics.md#Aurora.AuroraMySQL.Monitoring.Metrics.instances). + Monitoraggio avanzato fornisce le metriche restituite dal comando del sistema operativo `top`. Mostra le medie di carico e i seguenti stati della CPU, con una granularità di 1 secondo: + `Idle (%)` = Tempo di inattività + `IRQ (%)` = Interruzioni del software + `Nice (%)` = Periodo di tempo in cui i processi hanno una priorità [nice](https://en.wikipedia.org/wiki/Nice_(Unix)). + `Steal (%)` = Tempo dedicato ad altri tenant (in relazione alla virtualizzazione) + `System (%)` = Orario sistema + `User (%)` = Orario utente + `Wait (%)` = Attesa I/O Per ulteriori informazioni sulle metriche di Monitoraggio avanzato, consulta [Parametri del sistema operativo per Aurora](USER_Monitoring-Available-OS-Metrics.md#USER_Monitoring-Available-OS-Metrics-RDS). ### Utilizzo della memoria Se il sistema è sotto pressione in termini di memoria e il consumo di risorse sta raggiungendo la saturazione, si dovrebbe verificare un elevato grado di errori di scansione delle pagine, paginazione, scambio ed esaurimento della memoria delle pagine. È possibile fare riferimento ai seguenti strumenti per identificare l’utilizzo e la saturazione della memoria: CloudWatch fornisce la metrica `FreeableMemory` che mostra quanta memoria può essere recuperata svuotando alcune cache del sistema operativo e la memoria attualmente disponibile. Per ulteriori informazioni sui parametri di CloudWatch, consulta [Parametri a livello di istanza per Amazon Aurora](Aurora.AuroraMonitoring.Metrics.md#Aurora.AuroraMySQL.Monitoring.Metrics.instances). Monitoraggio avanzato fornisce le seguenti metriche che possono aiutarti a identificare i problemi di utilizzo della memoria: + `Buffers (KB)`: la quantità di memoria utilizzata per il buffering delle richieste di I/O prima della scrittura sul dispositivo di archiviazione, in kilobyte. + `Cached (KB)`: la quantità di memoria utilizzata per la memorizzazione nella cache dell’I/O basato sul file system. + `Free (KB)`: la quantità di memoria non assegnata, in kilobyte. + `Swap`: memorizzato nella cache, gratuito e totale. Ad esempio, se vedi che l’istanza database utilizza memoria `Swap`, la quantità totale di memoria per il carico di lavoro è maggiore di quella attualmente disponibile sull’istanza. Ti consigliamo di aumentare le dimensioni dell’istanza database o di ottimizzare il carico di lavoro per utilizzare meno memoria. Per ulteriori informazioni sulle metriche di Monitoraggio avanzato, consulta [Parametri del sistema operativo per Aurora](USER_Monitoring-Available-OS-Metrics.md#USER_Monitoring-Available-OS-Metrics-RDS). Per informazioni più dettagliate sull’utilizzo di Schema delle prestazioni e dello schema `sys` per determinare quali connessioni e componenti stanno utilizzando la memoria, consulta [Risoluzione dei problemi di utilizzo della memoria per i database Aurora MySQL](ams-workload-memory.md) ### Throughput di rete CloudWatch fornisce le seguenti metriche per il throughput totale di rete, il tutto calcolato in media su 1 minuto: + `NetworkReceiveThroughput`: la quantità di throughput di rete ricevuto dai client da ogni istanza nel cluster di database Aurora. + `NetworkTransmitThroughput`: la quantità di throughput di rete inviato ai client da ogni istanza nel cluster di database Aurora. + `NetworkThroughput`: la quantità di throughput di rete ricevuto dai client e trasmesso agli stessi da ogni istanza nel cluster di database Aurora. + `StorageNetworkReceiveThroughput`: la quantità di throughput di rete ricevuto dal sottosistema di archiviazione Aurora mediante ogni istanza nel cluster di database. + `StorageNetworkTransmitThroughput`: la quantità di throughput di rete inviata al sottosistema di archiviazione Aurora mediante ogni istanza nel cluster di database Aurora. + `StorageNetworkThroughput`: la quantità di throughput di rete ricevuta dal sottosistema di archiviazione Aurora e inviata allo stesso mediante ogni istanza nel cluster di database Aurora. Per ulteriori informazioni sui parametri di CloudWatch, consulta [Parametri a livello di istanza per Amazon Aurora](Aurora.AuroraMonitoring.Metrics.md#Aurora.AuroraMySQL.Monitoring.Metrics.instances). Monitoraggio avanzato fornisce i grafi `network` ricevuti (**RX**) e trasmessi (**TX**), con una granularità fino a 1 secondo. Per ulteriori informazioni sulle metriche di Monitoraggio avanzato, consulta [Parametri del sistema operativo per Aurora](USER_Monitoring-Available-OS-Metrics.md#USER_Monitoring-Available-OS-Metrics-RDS). ## Parametri del database Esamina le seguenti metriche CloudWatch per le modifiche del carico di lavoro: + `BlockedTransactions`: il numero medio di transazioni nel database bloccate ogni secondo. + `BufferCacheHitRatio` la percentuale di richieste gestite dalla cache del buffer. + `CommitThroughput`: il numero medio di operazioni di conferma al secondo. + `DatabaseConnections`: il numero di connessioni di rete client all’istanza del database. + `Deadlocks`: il numero medio di deadlock nel database al secondo. + `DMLThroughput`: il numero medio delle inserzioni, degli aggiornamenti e delle eliminazioni al secondo. + `ResultSetCacheHitRatio`: la percentuale di richieste gestite dalla cache del buffer. + `RollbackSegmentHistoryListLength`: i log di undo che registrano le transazioni sottoposte a commit con record contrassegnati per l’eliminazione. + `RowLockTime`: il tempo totale impiegato per l’acquisizione di blocchi di riga per le tabelle InnoDB. + `SelectThroughput`: numero medio di query di selezione al secondo. Per ulteriori informazioni sui parametri di CloudWatch, consulta [Parametri a livello di istanza per Amazon Aurora](Aurora.AuroraMonitoring.Metrics.md#Aurora.AuroraMySQL.Monitoring.Metrics.instances). Considera le seguenti domande quando esamini il carico di lavoro: 1. Sono state apportate modifiche recenti nella classe dell’istanza database, ad esempio la riduzione della dimensione dell’istanza da 8xlarge a 4xlarge o il passaggio da db.r5 a db.r6? 1. È possibile creare un clone e riprodurre il problema o si verifica solo su quell’istanza? 1. Si verifica un esaurimento delle risorse del server, un elevato esaurimento della CPU o della memoria? In caso affermativo, ciò potrebbe significare che è necessario hardware aggiuntivo. 1. Una o più query richiedono più tempo? 1. Le modifiche sono causate da un aggiornamento, in particolare da un aggiornamento della versione principale? In caso affermativo, confronta le metriche precedenti e successive all’aggiornamento. 1. Sono state apportate modifiche al numero di istanze database di lettura? 1. Hai abilitato la registrazione di log generali, di audit o binari? Per ulteriori informazioni, consulta [Registrazione di log per i database Aurora MySQL](aurora-mysql-troubleshooting-logging.md). 1. Hai abilitato, disabilitato o modificato l’uso della replica dei log binari (binlog)? 1. Esistono transazioni di lunga durata che contengono un gran numero di blocchi di righe? Esamina la lunghezza dell’elenco della cronologia di InnoDB (HLL) per indicazioni di transazioni di lunga durata. Per ulteriori informazioni, consulta [La lunghezza dell'elenco della cronologia di InnoDB è aumentata in modo significativo](proactive-insights.history-list.md) il post sul blog [Why is my SELECT query running slowly on my Amazon Aurora MySQL DB cluster?](https://repost.aws/knowledge-center/aurora-mysql-slow-select-query) 1. Se un HLL di grandi dimensioni è causato da una transazione di scrittura, significa che i log `UNDO` si stanno accumulando (non vengono puliti regolarmente). In una transazione di scrittura di grandi dimensioni, questo accumulo può crescere rapidamente. In MySQL, `UNDO` è memorizzato nel [tablespace SYSTEM](https://dev.mysql.com/doc/refman/5.7/en/innodb-system-tablespace.html). Il tablespace `SYSTEM` non è riducibile. Il log `UNDO` potrebbe far crescere il tablespace `SYSTEM` fino a diversi GB o addirittura TB. Dopo l’eliminazione, rilascia lo spazio allocato eseguendo un backup logico (dump) dei dati, quindi importa il dump in una nuova istanza database. 1. Se un HLL di grandi dimensioni è causato da una transazione di lettura (query a esecuzione prolungata), può significare che la query sta utilizzando una grande quantità di spazio temporaneo. Rilascia lo spazio temporaneo riavviando. Esamina le metriche del database Approfondimenti sulle prestazioni per eventuali modifiche nella sezione `Temp`, ad esempio `created_tmp_tables`. Per ulteriori informazioni, consulta [Monitoraggio del carico DB con Performance Insights su Amazon Aurora](USER_PerfInsights.md). 1. È possibile suddividere le transazioni di lunga durata in transazioni più piccole che modificano un minor numero di righe? 1. Ci sono cambiamenti nelle transazioni bloccate o aumenti delle situazioni di stallo? Esamina le metriche del database Approfondimenti sulle prestazioni per eventuali modifiche alle variabili di stato nella sezione `Locks`, ad esempio `innodb_row_lock_time`, ` innodb_row_lock_waits` e ` innodb_dead_locks`. Usa intervalli di 1 minuto o 5 minuti. 1. I tempi di attesa sono aumentati? Esamina gli eventi e i tipi di attesa di Approfondimenti sulle prestazioni utilizzando intervalli di 1 minuto o 5 minuti. Analizza i principali eventi di attesa e verifica se sono correlati alle modifiche del carico di lavoro o ai conflitti del database. Ad esempio, `buf_pool mutex` indica la contesa del pool di buffer. Per ulteriori informazioni, consulta [Regolazione di Aurora MySQL con eventi di attesa](AuroraMySQL.Managing.Tuning.wait-events.md). # 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](https://dev.mysql.com/doc/refman/8.3/en/performance-schema-memory-summary-tables.html) nella documentazione di MySQL. Per ulteriori informazioni sull’utilizzo dello Schema delle prestazioni con Approfondimenti sulle prestazioni, vedi [Panoramica dello schema di prestazioni per Approfondimenti sulle prestazioni su Aurora MySQL](USER_PerfInsights.EnableMySQL.md). Sebbene nello schema delle prestazioni siano disponibili informazioni dettagliate per monitorare l’utilizzo corrente della memoria, lo [schema sys](https://dev.mysql.com/doc/refman/8.0/en/sys-schema.html) di MySQL offre viste sulle tabelle dello schema delle prestazioni che è possibile utilizzare per individuare rapidamente dove viene utilizzata la memoria. Nello schema `sys` sono disponibili le seguenti viste per monitorare l’utilizzo della memoria per connessione, componente e query. | Vista | Descrizione | | --- | --- | | [memory\$1by\$1host\$1by\$1current\$1bytes](https://dev.mysql.com/doc/refman/8.0/en/sys-memory-by-host-by-current-bytes.html) | 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. | | [memory\$1by\$1thread\$1by\$1current\$1bytes](https://dev.mysql.com/doc/refman/8.0/en/sys-memory-by-thread-by-current-bytes.html) | 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](https://dev.mysql.com/doc/refman/8.0/en/sys-processlist.html) o la tabella [performance\$1schema.threads](https://dev.mysql.com/doc/refman/8.0/en/performance-schema-threads-table.html). | | [memory\$1by\$1user\$1by\$1current\$1bytes](https://dev.mysql.com/doc/refman/8.0/en/sys-memory-by-user-by-current-bytes.html) | Fornisce informazioni sull’utilizzo della memoria del motore per utente. Ciò può essere utile per identificare quali account utente o client stanno consumando memoria. | | [memory\$1global\$1by\$1current\$1bytes](https://dev.mysql.com/doc/refman/8.0/en/sys-memory-global-by-current-bytes.html) | 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 `memory/innodb/buf_buf_pool` per il pool di buffer InnoDB o l’evento `memory/sql/Prepared_statement::main_mem_root` per le istruzioni preparate. | | [memory\$1global\$1total](https://dev.mysql.com/doc/refman/8.0/en/sys-memory-global-total.html) | 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 prestazioni](https://dev.mysql.com/doc/refman/8.0/en/performance-schema-statement-summary-tables.html). Le tabelle di riepilogo delle istruzioni contengono digest di istruzioni normalizzati e statistiche aggregate sull’esecuzione. La colonna `MAX_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. **Topics** + [Esempio 1: utilizzo continuo ed elevato della memoria](#ams-workload-memory.example1) + [Esempio 2: picchi di memoria transitori](#ams-workload-memory.example2) + [Esempio 3: la memoria liberabile diminuisce continuamente e non viene recuperata](#ams-workload-memory.example3) ## 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. ![\[Grafico FreeableMemory che mostra un elevato utilizzo della memoria.\]](http://docs.aws.amazon.com/it_it/AmazonRDS/latest/AuroraUserGuide/images/ams-freeable-memory.png) 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](https://dev.mysql.com/doc/refman/8.0/en/performance-schema-instrument-naming.html) nella documentazione di MySQL. 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](https://dev.mysql.com/doc/refman/8.0/en/performance-schema-memory-model.html) nella documentazione di MySQL. 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_alloc` indica che MySQL ha 4,91 GiB attualmente allocati a questo evento. + `high_alloc column` indica che 4,91 GiB è il valore massimo di `current_alloc` dall’ultimo ripristino delle statistiche o dal riavvio del server. Ciò significa che `memory/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\$1by\$1thread\$1by\$1current\$1bytes](https://dev.mysql.com/doc/refman/8.0/en/sys-memory-by-thread-by-current-bytes.html). 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](https://dev.mysql.com/doc/refman/8.0/en/performance-schema-table-characteristics.html) nella documentazione di MySQL. 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 p`erformance_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. ![\[Grafico che mostra i picchi transitori di utilizzo della memoria nel tempo con uno schema periodico che indica potenziali problemi di gestione della memoria.\]](http://docs.aws.amazon.com/it_it/AmazonRDS/latest/AuroraUserGuide/images/ams-free-memory-spikes.png) 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](https://dev.mysql.com/doc/refman/8.0/en/performance-schema-statement-summary-tables.html) per cercare di identificare le sessioni e le connessioni che causano il problema. 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](https://dev.mysql.com/doc/refman/8.0/en/internal-temporary-tables.html) nella documentazione di 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](#ams-workload-memory.example1), 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](https://dev.mysql.com/doc/refman/8.0/en/performance-schema-statement-summary-tables.html). ``` 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 `20676ce4a690592ff05debcffcbc26faeb76f22005e7628364d7a498769d0c4a` sembra essere un buon candidato per questo utilizzo di memoria. `MAX_TOTAL_MEMORY` è 537450710, che corrisponde al valore massimo rilevato per l’evento `memory/temptable/physical_ram` in `sys.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](https://dev.mysql.com/doc/refman/8.0/en/performance-schema-statement-summary-tables.html) nella documentazione di MySQL. È inoltre possibile eseguire un’istruzione [EXPLAIN](https://dev.mysql.com/doc/refman/8.0/en/explain.html) o [EXPLAIN ANALYZE](https://dev.mysql.com/doc/refman/8.0/en/explain.html#explain-analyze) per visualizzare maggiori dettagli. **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 | | 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](AuroraMySQL.Auditing.md). 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](https://dev.mysql.com/doc/refman/8.4/en/show-engine.html) fornisce un rapporto completo sullo stato del motore di archiviazione InnoDB, incluse statistiche dettagliate sull’utilizzo della memoria per diversi componenti InnoDB. Può aiutare a identificare quali strutture o operazioni specifiche di InnoDB consumano più memoria. Per ulteriori informazioni, consulta la pagina relativa alle [strutture in memoria InnoDB](https://dev.mysql.com/doc/refman/8.0/en/innodb-in-memory-structures.html) nella documentazione di MySQL. 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 dizionario](https://dev.mysql.com/doc/refman/8.0/en/data-dictionary-object-cache.html), che rappresenta l’89% della memoria monitorata da `memory/innodb/memory`. ``` ---------------------- BUFFER POOL AND MEMORY ---------------------- Total large memory allocated 0 Dictionary memory allocated 5051647748 Buffer 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\$1table\$1definition](https://dev.mysql.com/doc/refman/8.4/en/server-status-variables.html#statvar_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) ``` Per ulteriori informazioni, consulta la pagina relativa ad [apertura e chiusura delle tabelle in MySQL](https://dev.mysql.com/doc/refman/8.0/en/table-cache.html) nella documentazione di 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](https://dev.mysql.com/doc/refman/8.4/en/flush.html) per cancellare la cache delle definizioni di tabella. Questa azione comporta un calo significativo della variabile di stato globale [Open\$1table\$1definitions](https://dev.mysql.com/doc/refman/8.0/en/server-status-variables.html#statvar_Open_table_definitions), da 20.000 a 12, come mostrato di seguito: ``` 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 0 Dictionary memory allocated 5001599639 Buffer 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: 1. Esaminare e ottimizzare lo schema del database, in particolare le relazioni con chiavi esterne. 1. 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. # Risoluzione dei problemi di memoria insufficiente per i database Aurora MySQL Il parametro a livello di istanza `aurora_oom_response` di Aurora MySQL può consentire all’istanza database di monitorare la memoria di sistema e stimare la memoria utilizzata da varie istruzioni e connessioni. Se il sistema esaurisce la memoria, può eseguire una serie di azioni per tentare di liberarla. L’obiettivo è quello di evitare il riavvio del database a causa di problemi di memoria insufficiente (OOM). Il parametro a livello di istanza acquisisce una stringa di azioni separate da virgola che un’istanza database esegue quando la memoria è insufficiente. Il parametro `aurora_oom_response` è supportato per Aurora MySQL versione 2 e 3. Per il parametro `aurora_oom_response` è possibile utilizzare i seguenti valori e combinazioni di valori. Una stringa vuota indica che non viene intrapresa alcuna azione e di fatto disattiva la funzionalità, esponendo il database a riavvii OOM. + `decline`: rifiuta le nuove query quando l’istanza database ha poca memoria. + `kill_connect`: chiude le connessioni al database che consumano una grande quantità di memoria e termina le transazioni correnti e le istruzioni DDL (Data Definition Language). Questa risposta non è supportata per Aurora MySQL versione 2. Per ulteriori informazioni, consulta [KILL statement](https://dev.mysql.com/doc/refman/8.0/en/kill.html) nella documentazione di MySQL. + `kill_query`: termina le query in ordine discendente relativamente al consumo di memoria fino a che la memoria dell’istanza non supera la soglia minima. Le istruzioni DDL non vengono terminate. Per ulteriori informazioni, consulta [KILL statement](https://dev.mysql.com/doc/refman/8.0/en/kill.html) nella documentazione di MySQL. + `print`: stampa solo le query che consumano una grande quantità di memoria. + `tune`: ottimizza le cache delle tabelle interne per restituire un po’ di memoria al sistema. Aurora MySQL riduce la memoria utilizzata per le cache come `table_open_cache` e `table_definition_cache` in condizioni di memoria insufficiente. Eventualmente, Aurora MySQL riporta l’utilizzo della memoria alla normalità quando il sistema non è più in condizioni di memoria insufficiente. Per ulteriori informazioni, consulta [table\$1open\$1cache](https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_table_open_cache) e [table\$1definition\$1cache](https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_table_definition_cache) nella documentazione di MySQL. + `tune_buffer_pool`: riduce la dimensione del pool di buffer per liberare parte della memoria e renderla disponibile al server del database per l’elaborazione delle connessioni. Questa risposta è supportata per Aurora MySQL versione 3.06 e successive. È necessario associare `tune_buffer_pool` con `kill_query` o `kill_connect` nel valore del parametro `aurora_oom_response`. In caso contrario, il ridimensionamento del pool di buffer non avverrà, anche se si include `tune_buffer_pool` nel valore del parametro. Nelle versioni di Aurora MySQL precedenti alla 3.06, per le classi di istanze database con memoria inferiore o uguale a 4 GiB, quando l’istanza è sotto pressione di memoria, le azioni predefinite includono `print`, `tune`, `decline` e `kill_query`. Per le classi di istanze database con memoria superiore a 4 GiB, il valore del parametro è vuoto per impostazione predefinita (disabilitato). In Aurora MySQL versione 3.06 e successive, per le classi di istanze database con memoria inferiore o uguale a 4 GiB, Aurora MySQL chiude anche le connessioni che consumano più memoria (`kill_connect`). Per le classi di istanze database con memoria superiore a 4 GiB, il valore del parametro predefinito è `print`. In Aurora MySQL versione 3.09 e successive, per le classi di istanze database con memoria superiore a 4 GiB, il valore del parametro predefinito è `print,decline,kill_connect`. Se si verificano spesso problemi di memoria insufficiente, è possibile monitorare l’utilizzo della memoria tramite le [tabelle di riepilogo della memoria](https://dev.mysql.com/doc/refman/8.3/en/performance-schema-memory-summary-tables.html) quando `performance_schema` è abilitato. Per le metriche di Amazon CloudWatch relative a OOM, consulta [Parametri a livello di istanza per Amazon Aurora](Aurora.AuroraMonitoring.Metrics.md#Aurora.AuroraMySQL.Monitoring.Metrics.instances). Per le variabili di stato globali relative a OOM, consulta [Variabili di stato globali di Aurora MySQL](AuroraMySQL.Reference.GlobalStatusVars.md).