Come posso risolvere il problema della scarsa memoria liberabile in un database Amazon RDS per MySQL?

Breve descrizione

In Amazon RDS per MySQL, è possibile monitorare quattro stati della memoria:

• Attiva: la memoria che viene consumata attivamente dai processi o dai thread del database.
• Buffer: un buffer è uno spazio di memoria temporaneo utilizzato per contenere un blocco di dati.
• Memoria libera: la memoria disponibile per l'uso.
• Cache: la memorizzazione nella cache è una tecnica in cui i dati vengono temporaneamente archiviati in memoria per poter essere recuperati con rapidità.

Per impostazione predefinita, quando crei un'istanza Amazon RDS per MySQL, i buffer e le cache vengono allocati per migliorare le operazioni del database. Amazon RDS per MySQL dispone anche di un componente di memoria interna (come key_buffers_size o query_cache_size) che crea tabelle temporanee interne per eseguire determinate operazioni.

Durante l’uso di Amazon RDS per MySQL, assicurati di capire come MySQL utilizza e alloca la memoria. Dopo avere identificato i componenti che utilizzano la memoria, è possibile cercare i colli di bottiglia a livello di istanza e di database. Per prestazioni ottimali, devi monitorare queste particolari metriche e configurare le sessioni.

Risoluzione

In che modo MySQL utilizza la memoria

In Amazon RDS per MySQL, dall'80 al 90% della memoria disponibile in un'istanza viene allocata con i parametri predefiniti. Questa allocazione è ottimale per le prestazioni, ma se si impostano parametri che utilizzano più memoria, allora è necessario compensare modificando altri parametri in modo che utilizzino meno memoria.

È possibile calcolare l'utilizzo approssimativo della memoria dell’istanza database RDS per MySQL in questo modo:

Maximum MySQL Memory Usage = innodb_buffer_pool_size + key_buffer_size + ((read_buffer_size + read_rnd_buffer_size + sort_buffer_size + join_buffer_size) X max_connections)

Pool di buffer

I buffer e le cache globali includono componenti come Innodb_buffer_pool_size, Innodb_log_buffer_size, key_buffer_size e query_cache_size. Il parametro innodb_buffer_pool_size è l'area di memoria della RAM in cui innodb memorizza nella cache le tabelle del database e i dati relativi all'indice. Con un pool di buffer più grande, le operazioni di I/O reindirizzate sul disco sono meno numerose. Per impostazione predefinita, innodb_buffer_pool_size utilizza fino al 75% della memoria disponibile allocata all'istanza DB Amazon RDS:

innodb_buffer_pool_size = {DBInstanceClassMemory*3/4}

È necessario esaminare prima questo parametro per identificare la sorgente di utilizzo della memoria. Quindi, può essere utile ridurre il valore di innodb_buffer_pool_size modificando il valore del parametro nel proprio gruppo di parametri personalizzato.

Ad esempio, il valore predefinito di DBInstanceClassMemory*3/4 può essere ridotto a *5/8 o *1/2. Assicurati di verificare che il valore BufferCacheHitRatio dell’istanza non sia troppo basso. Se il valore BufferCacheHitRatio è basso, potrebbe essere necessario aumentare la dimensione dell'istanza per avere più RAM. Per ulteriori informazioni, consulta Procedure consigliate per la configurazione dei parametri per Amazon RDS per MySQL, parte 1: parametri relativi alle prestazioni.

Thread MySQL

La memoria viene anche allocata per ogni thread MySQL collegato a un'istanza DB MySQL. I seguenti thread richiedono l’allocazione di memoria:

• thread_stack
• net_buffer_length
• read_buffer_size
• sort_buffer_size
• join_buffer_size
• max_heap_table_size
• tmp_table_size

Inoltre, MySQL crea tabelle temporanee interne per eseguire alcune operazioni. Queste tabelle vengono create inizialmente come tabelle basate sulla memoria. Quando le tabelle raggiungono la dimensione specificata da tmp_table_size o max_heap_table_size (a seconda del valore più basso), la tabella viene convertita in una tabella basata su disco. Quando più sessioni creano tabelle temporanee interne, potrebbe verificarsi un aumento dell'utilizzo della memoria. Per ridurre l'utilizzo della memoria, evitare di usare tabelle temporanee nelle query.

**Nota:**quando si aumentano i limiti tmp_table_size e max_heap_table_size, le tabelle temporanee più grandi possono rimanere in memoria. Per confermare se è stata creata una tabella temporanea implicita, utilizza la variabile created_tmp_tables. Per ulteriori informazioni su questa variabile, consulta created_tmp_tables sul sito Web MySQL.

Operazioni JOIN e SORT

L'utilizzo della memoria aumenta se vengono allocati più buffer dello stesso tipo, come join_buffer_size o sort_buffer_size, durante un'operazione JOIN o SORT. Ad esempio, MySQL alloca un buffer JOIN per eseguire JOIN tra due tabelle. Se una query prevede JOIN in più tabelle e tutte le query richiedono un buffer JOIN, MySQL alloca un buffer JOIN in meno rispetto al numero totale di tabelle. Se le query non sono ottimizzate, la configurazione delle variabili di sessione con un valore troppo alto può causare problemi. È possibile allocare la memoria minima a variabili a livello di sessione come join_buffer_size e join_buffer_size e sort_buffer_size. Per ulteriori informazioni, consulta Utilizzo dei gruppi di parametri database.

Se si eseguono inserimenti di massa nelle tabelle MYISAM, vengono utilizzati byte di memoria bulk_insert_buffer_size. Per ulteriori informazioni, consulta Procedure consigliate per lavorare con i motori di archiviazione MySQL.

Performance Schema

La memoria può essere consumata da Performance Schema, se hai abilitato Performance Schema per Performance Insights in Amazon RDS per MySQL. Quando Performance Schema è abilitato, MySQL alloca i buffer interni all'avvio dell'istanza e durante le operazioni del server. Per ulteriori informazioni su come Performance Schema utilizza la memoria, consulta la documentazione MySQL per il modello di allocazione della memoria di Performance Schema.

Insieme alle tabelle di Performance Schema, è anche possibile utilizzare lo schema sys MySQL. Ad esempio, puoi utilizzare l'evento performance_schema per mostrare la quantità di memoria allocata ai buffer interni utilizzati da Performance Schema. Oppure puoi eseguire una query come questa per determinare la quantità di memoria allocata:

SELECT * FROM performance_schema.memory_summary_global_by_event_name WHERE EVENT_NAME LIKE 'memory/performance_schema/%';

Gli strumenti di memoria sono elencati nella tabella setup_instruments secondo il formato “memory/code_area/instrument_name”. Per abilitare la strumentazione di memoria, aggiorna la colonna ENABLED degli strumenti pertinenti nella tabella setup_instruments:

UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME LIKE 'memory/%';

Monitoraggio dell'utilizzo della memoria nell'istanza

Metriche di Amazon CloudWatch

Se la memoria disponibile è bassa, può essere utile monitorare le metriche di Amazon CloudWatch relative a DatabaseConnections, CPUUtilization, ReadIOPS e WriteIOPS.

Per DatabaseConnections, è importante notare che ogni connessione al database richiede l’allocazione di una certa quantità di memoria. Pertanto, un picco nelle connessioni al database può causare un calo della memoria liberabile. In Amazon RDS, il limite flessibile per max_connections viene calcolato in questo modo:

{DBInstanceClassMemory/12582880}

Verifica se stai superando questo limite flessibile controllando la metrica DatabaseConnections in Amazon CloudWatch.

Inoltre, verifica la pressione della memoria controllando le metriche di CloudWatch relative a SwapUsage, oltre a FreeableMemory. Se noti che viene utilizzata una grande quantità di swap e la memoria FreeableMemory è bassa, l’istanza potrebbe essere sottoposta a una forte pressione di memoria. L'elevata pressione di memoria influisce sulle prestazioni del database. È consigliabile mantenere i livelli di pressione di memoria al di sotto del 95%. Per ulteriori informazioni, consulta Perché la mia istanza Amazon RDS utilizza la memoria swap quando ho sufficiente memoria?

Monitoraggio avanzato

Per monitorare l'utilizzo delle risorse in un'istanza database, abilita il monitoraggio avanzato. Imposta una granularità di uno o cinque secondi (l'impostazione predefinita è 60 secondi). Il monitoraggio avanzato consente di monitorare la memoria liberabile e attiva in tempo reale.

Inoltre, consente di elencare i thread dell’istanza DB per identificare quelli che consumano più CPU e memoria:

mysql> select THREAD_ID, PROCESSLIST_ID, THREAD_OS_ID from performance_schema.threads;

Quindi, mappa il thread_OS_ID al 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 from EM processlist>;

Risoluzione dei problemi di memoria liberabile insufficiente

Se la memoria liberabile è insufficiente, prendi in considerazione i seguenti suggerimenti per risolvere il problema:

• Assicurati che siano state allocate al database risorse sufficienti per eseguire le query. Con Amazon RDS, la quantità di risorse allocate dipende dal tipo di istanza. Inoltre, alcune query, come le procedure archiviate, possono richiedere una quantità illimitata di memoria durante l'esecuzione.
• Evita transazioni di lunga durata suddividendo le query di grandi dimensioni in query più piccole.
• Per visualizzare tutte le connessioni e le query attive nel database, usa il comando SHOW FULL PROCESSLIST. Se è presente una query di lunga durata con operazioni JOIN o SORTS, è necessario disporre di RAM sufficiente per consentire all'ottimizzatore di calcolare il piano. Inoltre, se identifichi una query che richiede una tabella temporanea, dovrai disporre di memoria aggiuntiva da allocare alla tabella.
• Per visualizzare le transazioni di lunga durata, le statistiche sull'utilizzo della memoria e i blocchi, utilizza il comando SHOW ENGINE INNODB STATUS. Rivedi l'output e controlla le voci BUFFER POOL AND MEMORY. La voce BUFFER POOL AND MEMORY fornisce informazioni sull'allocazione della memoria per InnoDB, come “Memoria totale allocata”, “Tabelle hash interne” e “Dimensioni del pool di buffer”. InnoDB Status fornisce anche informazioni aggiuntive su latch, blocchi e deadlock.
• Se il carico di lavoro incontra spesso dei deadlock, modifica il parametro innodb_lock_wait_timeout nel tuo gruppo di parametri personalizzato. InnoDB si basa sull'impostazione innodb_lock_wait_timeout per ripristinare le transazioni quando si verifica un deadlock.
• Per ottimizzare le prestazioni del database, assicurati che le query siano ottimizzate. Altrimenti, potresti riscontrare problemi di prestazioni e tempi di attesa prolungati.
• Usa Amazon RDS Performance Insights per monitorare le istanze database e identificare eventuali query problematiche.
• Controlla i parametri di Amazon CloudWatch come l'utilizzo della CPU, gli IOPS, la memoria e l'utilizzo degli swap in modo che l'istanza non rallenti.
• Imposta un allarme CloudWatch nella metrica FreeableMemory in modo da ricevere una notifica quando la memoria disponibile raggiunge il 95%. È consigliabile mantenere libera almeno il 5% della memoria dell'istanza.
• Aggiorna regolarmente l‘istanza a una versione secondaria più recente di MySQL. È probabile che le versioni secondarie precedenti contengano bug relativi alla perdita di memoria.

---

Informazioni correlate

Panoramica sul monitoraggio di Amazon RDS

Perché la mia istanza database Amazon RDS utilizza la memoria swap quando ho memoria sufficiente?