Come posso risolvere i problemi di memoria liberabile insufficiente in un database Amazon RDS per MySQL?

Risoluzione

Importante: Performance Insights giungerà al termine del suo ciclo di vita il 30 giugno 2026. Entro tale data puoi passare alla modalità Avanzata di Database Insights. Se non esegui l'aggiornamento, i cluster di database che utilizzano Performance Insights passeranno automaticamente alla modalità Standard di Database Insights. Solo la modalità Avanzata di Database Insights supporta i piani di esecuzione e l'analisi on demand. Se i cluster dovessero passare automaticamente alla modalità Standard, potresti non essere in grado di utilizzare queste funzionalità sulla console. Per attivare la modalità Avanzata, consulta Attivazione della modalità avanzata di Database Insights per Amazon RDS e Attivazione della modalità avanzata di Database Insights per Amazon Aurora.

Migliora le prestazioni del database

Per migliorare le prestazioni del database, ottimizza le query. Utilizza Amazon RDS Performance Insights per monitorare le istanze database e identificare le query problematiche. Quindi imposta un allarme in Amazon CloudWatch per la metrica FreeableMemory in modo da ricevere una notifica quando la memoria disponibile raggiunge il 95%. È consigliabile mantenere libero almeno il 5% della memoria dell'istanza.

Verifica l'allocazione della memoria in Amazon RDS per MySQL

Calcola l'allocazione della memoria

Per calcolare l'utilizzo approssimativo della memoria dell'istanza database, utilizza la seguente formula:

Utilizzo totale della memoria = (innodb_additional_mem_pool_size + innodb_buffer_pool_size + innodb_log_buffer_size + key_buffer_size + query_cache_size + tmp_table_size) + max_connections (binlog_cache_size + join_buffer_size + read_buffer_size + read_rnd_buffer_size + sort_buffer_size + thread_stack ) + (performance_schema max_connections * 429498)`

Assicurati che siano state allocate al database risorse sufficienti per eseguire le query. Alcune query, come le procedure archiviate, possono richiedere una quantità illimitata di memoria durante l'esecuzione. Per evitare transazioni che durano a lungo, suddividi le query di grandi dimensioni in query più piccole. Per ulteriori informazioni sull'utilizzo della memoria da parte di Amazon RDS per MySQL, consulta How MySQL Uses Memory (Come MySQL utilizza la memoria) sul sito web MySQL.

È consigliabile aggiornare regolarmente la versione secondaria di MySQL dell'istanza. Le versioni secondarie precedenti potrebbero contenere bug correlati alla perdita di memoria. Per ulteriori informazioni sulle versioni di MySQL, consulta MySQL 8.0 release notes (Note di rilascio di MySQL 8.0) sul sito web di MySQL.

Controlla le dimensioni del pool di buffer

Per visualizzare le transazioni di lunga durata, le statistiche sull'utilizzo della memoria o i blocchi, utilizza il comando SHOW ENGINE INNODB STATUS. Per ulteriori informazioni sulla query SHOW ENGINE, consulta SHOW ENGINE query (Query SHOW ENGINE) sul sito web MySQL. Esamina l'output, quindi controlla la voce BUFFER POOL AND MEMORY per informazioni sull'allocazione della memoria di InnoDB, come Total Memory Allocated, Internal Hash Tables e Buffer Pool Size. Se il carico di lavoro riscontra spesso deadlock, modifica il parametro innodb_lock_wait_timeout nel gruppo di parametri personalizzato. InnoDB si basa sull'impostazione innodb_lock_wait_timeout per ripristinare le transazioni quando si verifica un deadlock.

Un pool di buffer più grande richiede un numero inferiore di operazioni di I/O reindirizzate sul disco. Per impostazione predefinita, innodb_buffer_pool_size utilizza fino al 75% della memoria disponibile allocata all'istanza database Amazon RDS: innodb_buffer_pool_size = DBInstanceClassMemory*3/4. Per ulteriori informazioni sui pool di buffer, consulta Buffer Pool (Pool di buffer) sul sito web MySQL.

Per identificare l'origine dell'utilizzo della memoria, controlla prima di tutto innodb_buffer_pool_size. Quindi, se necessario, modifica il valore del parametro nel gruppo di parametri personalizzato per ridurre innodb_buffer_pool_size.

Per ulteriori informazioni, consulta Best practices for configuring parameters for Amazon RDS for MySQL, part 1: Parameters related to performance (Procedure consigliate per la configurazione dei parametri di Amazon RDS per MySQL, parte 1: parametri relativi alle prestazioni).

Controlla i thread di MySQL

La memoria viene anche allocata per ogni thread di MySQL collegato a un'istanza database MySQL. Per ulteriori informazioni sui thread di MySQL che richiedono memoria allocata, consulta la sezione Diagnosi e risoluzione dello stato dei parametri incompatibili per un limite di memoria nella pagina Problemi relativi a MySQL e MariaDB.

MySQL crea tabelle interne temporanee per eseguire alcune operazioni. Se le tabelle raggiungono il valore più basso di tmp_table_size o max_heap_table_size, MySQL le converte da tabelle basate sulla memoria a tabelle basate su disco. Se più sessioni creano tabelle interne temporanee, potrebbe verificarsi un aumento dell'utilizzo della memoria. Per ridurlo, utilizza solo il numero massimo di tabelle nelle query. Per ulteriori informazioni, consulta Server System Variables (Variabili di sistema del server) e How MySQL uses memory (Come MySQL utilizza la memoria) sul sito web MySQL.

Se aumenti i valori tmp_table_size and max_heap_table_size, le tabelle temporanee più grandi vengono salvate in memoria. Per verificare se MYSQL ha creato una tabella temporanea implicita, utilizza la variabile created_tmp_tables. Per ulteriori informazioni su questa variabile, consulta created_tmp_tables sul sito web di MySQL.

Visualizza le operazioni JOIN e SORT attive

Se identifichi una query che richiede una tabella temporanea, devi disporre di memoria aggiuntiva da allocare alla tabella. Per visualizzare le connessioni e le query attive nel database, utilizza il comando SHOW FULL PROCESSLIST. Per ulteriori informazioni su SHOW FULL PROCESSLIST ed esempi di query, consulta SHOW PROCESSLIST query (Query SHOW PROCESSLIST) sul sito web MySQL.

Se MySQL alloca più buffer dello stesso tipo, come join_buffer_size or sort_buffer_size, durante un'operazione JOIN o SORT, l'utilizzo della memoria aumenta. Ad esempio, MySQL alloca un buffer JOIN per eseguire JOIN tra due tabelle. Per le query che utilizzano più tabelle JOIN in cui tutte le query richiedono un buffer JOIN, MySQL alloca un buffer JOIN in meno rispetto al numero totale di tabelle.

Se configuri le variabili di sessione con un valore troppo alto, potresti ricevere errori. Per evitarli, alloca la memoria minima necessaria a variabili a livello di sessione come join_buffer_size e sort_buffer_size.

Nota: se esegui inserimenti in blocco nelle tabelle MYISAM, MySQL utilizza bulk_insert_buffer_size byte di memoria. Per ulteriori informazioni, consulta Best practice per l'utilizzo di MySQL.

Attiva Schema delle prestazioni

Se attivi Performance Insights, MySQL alloca i buffer interni per Schema delle prestazioni all'avvio dell'istanza e durante le operazioni del server. Per ulteriori informazioni sull’utilizzo della memoria da parte di Schema delle prestazioni, consulta The Performance Schema memory-allocation model (Modello di allocazione della memoria di Schema delle prestazioni) sul sito web MySQL.

Monitora l'utilizzo della memoria nell'istanza

Controlla le metriche in CloudWatch

Per verificare la mancanza di memoria, utilizza la scheda Monitoraggio della console Aurora e RDS per monitorare le metriche di CloudWatch DatabaseConnections, CPUUtilization, ReadIOPS e WriteIOPS.

Per DatabaseConnections, ogni connessione effettuata al database richiede una memoria allocata che potrebbe ridurre la memoria liberabile. Utilizza la seguente formula per calcolare la quota max_connections massima stimata: DBInstanceClassMemory/12582880

Per verificare se hai superato la quota max_connections, controlla la metrica di CloudWatch DatabaseConnections.

Per verificare la pressione sulla memoria, monitora le metriche di CloudWatch SwapUsage e FreeableMemory. È consigliabile mantenere i livelli di pressione sulla memoria al di sotto del 95% per migliorare le prestazioni del database. Per ulteriori informazioni, consulta Perché la mia istanza database Amazon RDS utilizza la memoria di swap anche se ho memoria sufficiente?

Utilizza lo schema sys di MySQL per monitorare l'utilizzo della memoria

Utilizza lo schema sys di MySQL per monitorare la connessione, il componente e la query sulla memoria. Per ulteriori informazioni, consulta Chapter 30 MySQL sys schema (Capitolo 30 Schema sys di MySQL) sul sito web MySQL. Utilizza lo schema sys di MySQL e le tabelle di Schema delle prestazioni per identificare e monitorare l'utilizzo corrente della memoria.

Nota: devi attivare Schema delle prestazioni per utilizzare lo schema sys.

Per monitorare l'utilizzo della memoria nello schema sys, accedi al database e intraprendi le seguenti azioni:

• Utilizza memory_by_host_by_current_bytes per determinare l'host che utilizza più memoria.
• Utilizza memory_by_thread_by_current_bytes per determinare l'ID del thread che utilizza più memoria.
  Nota: l'ID del thread in MySQL può essere una connessione client o un thread in background. Puoi utilizzare la vista sys.processlist o la tabella performance_schema.threads e mappare gli ID dei thread sugli ID delle connessioni di MySQL.
• Utilizza memory_by_user_by_current_bytes per determinare l'utente che utilizza più memoria.
• Utilizza memory_global_by_current_bytes per determinare il componente del motore che utilizza più memoria.
• Utilizza memory_global_total per visualizzare l'utilizzo totale della memoria monitorato nel motore di database.
• Utilizza sys.memory_global_by_current_bytes per determinare il componente che utilizza più memoria.
  Nota: per gli eventi di memoria relativi a Schema delle prestazioni, utilizza memory/performance_schema/%. Per InnoDB utilizza memory/innodb/%.

Per identificare i componenti delle funzionalità che utilizzano più memoria a livello globale, esegui questa query:

SELECT  SUBSTRING_INDEX(SUBSTRING_INDEX(event_name, '/', 2),    '/',    -1    )  AS event_type,    ROUND(SUM(CURRENT_NUMBER_OF_BYTES_USED)/1024/1024, 2) AS MB_CURRENTLY_USED    FROM performance_schema.memory_summary_global_by_event_name    GROUP BY event_type    HAVING MB_CURRENTLY_USED>0;

Se performance_schema utilizza la maggior parte della memoria, esegui queste query per determinare il nome dell'evento (event_name) che utilizza la memoria.

select * from sys.memory_global_by_current_bytes where event_name like '%performance_schema%' and current_count > 0;

select * from performance_schema.memory_summary_global_by_event_name WHERE EVENT_NAME LIKE 'memory/performance_schema/%'order by CURRENT_NUMBER_OF_BYTES_USED desc;

Quindi esegui queste query:

select * from sys.memory_global_by_current_bytes where event_name like 'memory/sql%' and current_count > 0;

select p.id,p.user,p.host,p.db,p.command,p.state,p.info,t.thread_id,t.type from information_schema.processlist p, performance_schema.threads t where p.id=t.processlist_id and t.thread_id=thread_id;

Nota: sostituisci memory/sql% con il tuo tipo di evento.

Per visualizzare i dettagli sull'allocazione della memoria per ogni evento di un thread specifico, esegui questa query:

select * from performance_schema.memory_summary_by_thread_by_event_name where thread_id=thread_id and CURRENT_COUNT_USED >0 order by CURRENT_NUMBER_OF_BYTES_USED desc;

Puoi anche utilizzare l'evento performance_schema per mostrare la quantità di memoria allocata da MySQL ai buffer interni utilizzati da Schema delle prestazioni. Per vedere quanta memoria è allocata, esegui questa query:

select * from performance_schema.memory_summary_global_by_event_name WHERE EVENT_NAME LIKE 'memory/performance_schema/%';

Gli strumenti di memoria sono nella tabella setup_instruments in formato memory/code_area/instrument_name.

Per attivare gli strumenti di memoria in Schema delle prestazioni, imposta la colonna ENABLED dello strumento su YES nella tabella performance_schema.setup_instruments.

Nota: in MySQL 8.x, gli strumenti di memoria sono attivi per impostazione predefinita quando Schema delle prestazioni è attivo.

Monitora l'utilizzo delle risorse

Per monitorare l'utilizzo delle risorse in un'istanza database, attiva Monitoraggio avanzato. Quindi imposta una granularità compresa tra 1 e 5 secondi. La granularità predefinita è 60 secondi. Puoi utilizzare Monitoraggio avanzato per visualizzare la memoria libera e attiva in tempo reale.

Per monitorare i thread che utilizzano più CPU e memoria, esegui questo comando per elencare i thread dell'istanza database:

select THREAD_ID, PROCESSLIST_ID, THREAD_OS_ID from performance_schema.threads;

Quindi esegui questo comando per mappare thread_OS_ID su thread_ID:

select p.* from information_schema.processlist p, performance_schema.threads t where p.id=t.processlist_id and t.thread_os_id=thread-ID;

Nota: sostituisci thread-ID con l'ID del thread.

Informazioni correlate

Strumenti di monitoraggio di Amazon RDS

Risoluzione dei problemi di utilizzo della memoria per i database Aurora MySQL