È un problema comune avere istanze che consumano molta memoria o che incorrono in eventi di esaurimento della memoria. Un'istanza di database in esecuzione con un elevato utilizzo della memoria spesso causa problemi di prestazioni, arresti o anche tempi di inattività del database.
Alcuni blocchi di memoria MySQL vengono utilizzati a livello globale. Ciò significa che tutti i workload di query condividono le posizioni della memoria, sono occupati tutto il tempo e vengono rilasciati solo quando il processo MySQL si arresta. Alcuni blocchi di memoria sono basati sulla sessione, il che significa che, non appena la sessione viene chiusa, la memoria utilizzata dalla sessione viene restituita al sistema.
Ogni volta che un'istanza Cloud SQL per MySQL utilizza molta memoria, Cloud SQL consiglia di identificare la query o il processo che utilizza molta memoria e di rilasciarla. Il consumo di memoria di MySQL è suddiviso in tre parti principali:
- Consumo di memoria da parte di thread ed elaborazione
- Consumo della memoria buffer
- Consumo della memoria cache
Consumo di memoria da parte di thread ed elaborazione
Ogni sessione utente consuma memoria a seconda delle query in esecuzione, dei buffer o della cache utilizzati dalla sessione ed è controllata dai parametri di sessione di MySQL. I parametri principali includono:
thread_stacknet_buffer_lengthread_buffer_sizeread_rnd_buffer_sizesort_buffer_sizejoin_buffer_sizemax_heap_table_sizetmp_table_size
Se in un determinato momento sono in esecuzione N query, ciascuna di queste consuma memoria in base a questi parametri durante la sessione.
Consumo della memoria buffer
Questa parte di memoria è comune a tutte le query ed è controllata da parametri come Innodb_buffer_pool_size, Innodb_log_buffer_size e key_buffer_size.
Consumo della memoria cache
La memoria cache include una cache delle query, che viene utilizzata per salvare le query e i relativi risultati al fine di recuperare più velocemente i dati delle stesse query successive. Include anche la cache binlog per conservare le modifiche apportate al log binario durante l'esecuzione della transazione ed è controllata da binlog_cache_size.
Altro consumo di memoria
La memoria viene utilizzata anche dalle operazioni di join e ordinamento. Se le query utilizzano operazioni di join o di ordinamento, utilizzano la memoria in base a join_buffer_size e sort_buffer_size.
Inoltre, se attivi lo schema delle prestazioni, questo consuma memoria. Per controllare l'utilizzo della memoria da parte dello schema di rendimento, utilizza la seguente query:
SELECT *
FROM
performance_schema.memory_summary_global_by_event_name
WHERE EVENT_NAME LIKE 'memory/performance_schema/%';
In MySQL sono disponibili molti strumenti che puoi configurare per monitorare l'utilizzo della memoria tramite lo schema delle prestazioni. Per saperne di più, consulta la documentazione di MySQL.
Il parametro relativo a MyISAM per l'inserimento collettivo dei dati è bulk_insert_buffer_size.
Per scoprire in che modo MySQL utilizza la memoria, consulta la documentazione di MySQL.
Consigli
Utilizzare Metrics Explorer per identificare l'utilizzo della memoria
Puoi esaminare l'utilizzo della memoria di un'istanza con la metrica database/memory/components.usage in Metrics Explorer.
Se la memoria in database/memory/components.cache e
database/memory/components.free combinate è inferiore al 5%, il rischio di un evento OOM è elevato.
Per monitorare l'utilizzo della memoria e per evitare eventi OOM,
ti consigliamo di configurare un regolamento di avviso
con una condizione di soglia della metrica pari o superiore al 95% in database/memory/components.usage.
La tabella seguente mostra la relazione tra la memoria dell'istanza e la soglia di avviso consigliata:
| Memoria istanza | Soglia di avviso consigliata |
|---|---|
| Fino a 100 GB | 95% |
| Da 100 GB a 200 GB | 96% |
| Da 200 GB a 300 GB | 97% |
| Più di 300 GB | 98% |
Calcolare il consumo di memoria
Calcola l'utilizzo massimo della memoria da parte del database MySQL per selezionare il tipo di istanza appropriato per il database MySQL. Utilizza la seguente formula:
Utilizzo massimo della memoria MySQL = innodb_buffer_pool_size + innodb_additional_mem_pool_size + innodb_log_buffer_size + tmp_table_size + key_buffer_size + ((read_buffer_size + read_rnd_buffer_size + sort_buffer_size + join_buffer_size) x max_connections)
Di seguito sono riportati i parametri utilizzati nella formula:
innodb_buffer_pool_size: le dimensioni in byte del pool di buffer, l'area di memoria in cui InnoDB memorizza nella cache i dati delle tabelle e degli indici.innodb_additional_mem_pool_size: le dimensioni in byte di un pool di memoria utilizzato da InnoDB per memorizzare le informazioni del dizionario dati e altre strutture di dati interne.innodb_log_buffer_size: le dimensioni in byte del buffer utilizzato da InnoDB per scrivere nei file di log sul disco.tmp_table_size: la dimensione massima delle tabelle temporanee interne in memoria create dal motore di archiviazione MEMORY e, a partire da MySQL 8.0.28, dal motore di archiviazione TempTable.Key_buffer_size: la dimensione del buffer utilizzato per i blocchi di indici. I blocchi di indici per le tabelle MyISAM vengono memorizzati nella memoria intermedia e sono condivisi da tutti i thread.Read_buffer_size: ogni thread che esegue una scansione sequenziale di una tabella MyISAM alloca un buffer di queste dimensioni (in byte) per ogni tabella che analizza.Read_rnd_buffer_size: questa variabile viene utilizzata per le letture dalle tabelle MyISAM, per qualsiasi motore di archiviazione e per l'ottimizzazione delle letture in più intervalli.Sort_buffer_size: ogni sessione che deve eseguire un'ordinamento alloca un buffer di queste dimensioni. sort_buffer_size non è specifico per nessun motore di archiviazione e si applica in modo generale per l'ottimizzazione.Join_buffer_size: le dimensioni minime del buffer utilizzato per le scansioni di indici semplici, le scansioni di indici di intervallo e i join che non utilizzano gli indici e quindi eseguono scansioni complete della tabella.Max_connections: il numero massimo consentito di connessioni client simultanee.
Risolvere i problemi di consumo elevato di memoria
Esegui
SHOW PROCESSLISTper visualizzare le query in corso che stanno attualmente consumando memoria. Mostra tutti i thread collegati e i relativi statement SQL in esecuzione e tenta di ottimizzarli. Presta attenzione alle colonne Stato e Durata.mysql> SHOW [FULL] PROCESSLIST;Seleziona
SHOW ENGINE INNODB STATUSnella sezioneBUFFER POOL AND MEMORYper visualizzare l'utilizzo corrente del pool di buffer e della memoria, che può aiutarti a impostare le dimensioni del pool di buffer.mysql> SHOW ENGINE INNODB STATUS \G ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 398063986; in additional pool allocated 0 Dictionary memory allocated 12056 Buffer pool size 89129 Free buffers 45671 Database pages 1367 Old database pages 0 Modified db pages 0Utilizza il comando
SHOW variablesdi MySQL per controllare i valori del contatore, che forniscono informazioni come il numero di tabelle temporanee, il numero di thread, il numero di cache delle tabelle, le pagine sporche, le tabelle aperte e l'utilizzo del pool di buffer.mysql> SHOW variables like 'VARIABLE_NAME'
Applica le modifiche
Dopo aver analizzato l'utilizzo della memoria da parte di diversi componenti, imposta il flag appropriato nel database MySQL. Per modificare il flag nell'istanza Cloud SQL per MySQL, puoi utilizzare la console Google Cloud o gcloud CLI. Per modificare il valore del flag utilizzando la console Google Cloud, modifica la sezione Flags (Indicatori), seleziona l'indicatore e inserisci il nuovo valore.
Infine, se l'utilizzo della memoria è ancora elevato e ritieni che le query in esecuzione e i valori dei flag siano ottimizzati, valuta la possibilità di aumentare le dimensioni dell'istanza per evitare OOM.