Ottimizza il consumo elevato di memoria nelle istanze

È un problema comune che le istanze consumano molta memoria o riscontrino problemi di esaurimento della memoria. Un'istanza di database in esecuzione con un utilizzo elevato della memoria spesso causa problemi di prestazioni, blocchi o persino tempi di inattività del database.

Alcuni blocchi di memoria MySQL vengono utilizzati a livello globale. Ciò significa che tutti i carichi di lavoro delle query condividono le posizioni della memoria, sono sempre occupati e vengono rilasciati solo all'arresto del processo MySQL. Alcuni blocchi di memoria sono basati sulla sessione, il che significa che non appena la sessione si chiude, anche la memoria utilizzata dalla sessione viene rilasciata al sistema.

Ogni volta che si verifica un elevato utilizzo della memoria da parte di un'istanza Cloud SQL per MySQL, Cloud SQL consiglia di identificare la query o il processo che utilizza molta memoria e di rilasciarla. Il consumo di memoria da parte di MySQL è suddiviso in tre parti principali:

Consumo di memoria per thread e processi
Consumo di memoria nel buffer
Consumo memoria cache

Consumo di memoria per thread e processi

Ogni sessione utente consuma memoria a seconda delle query in esecuzione, del buffer o della cache utilizzate da quella sessione ed è controllata dai parametri di sessione di MySQL. I parametri principali includono:

thread_stack
net_buffer_length
read_buffer_size
read_rnd_buffer_size
sort_buffer_size
join_buffer_size
max_heap_table_size
tmp_table_size

Se è presente un numero N di query in esecuzione in un determinato momento, ogni query consuma memoria in base a questi parametri durante la sessione.

Consumo di memoria nel buffer

Questa parte della memoria è comune per tutte le query ed è controllata da parametri come Innodb_buffer_pool_size, Innodb_log_buffer_size e key_buffer_size.

Consumo 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ù rapidamente i dati delle stesse query successive. Include inoltre la cache binlog per contenere le modifiche apportate al log binario mentre la transazione è in esecuzione ed è controllato 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 unione o ordinamento, queste query utilizzano la memoria sulla base di join_buffer_size e sort_buffer_size.

Inoltre, se attivi lo schema delle prestazioni, questa operazione consuma memoria. Per controllare l'utilizzo della memoria in base allo schema delle prestazioni, 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 ulteriori informazioni, consulta la documentazione di MySQL.

Il parametro relativo a MyISAM per l'inserimento collettivo dei dati è bulk_insert_buffer_size.

Per informazioni su come MySQL utilizza la memoria, consulta la documentazione di MySQL.

Suggerimenti

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 hai meno del 5% di memoria in database/memory/components.cache e database/memory/components.free insieme, il rischio di un evento OOM è elevato. Per monitorare l'utilizzo della memoria ed evitare gli eventi OOM, ti consigliamo di configurare un criterio 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 tuo database MySQL per selezionare il tipo di istanza appropriato per il tuo database MySQL. Utilizza la seguente formula:

Utilizzo massimo di 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)

Ecco 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 di tabella e indice.
innodb_additional_mem_pool_size: le dimensioni in byte di un pool di memoria che InnoDB utilizza per archiviare le informazioni del dizionario dati e altre strutture di dati interne.
innodb_log_buffer_size: le dimensioni in byte del buffer che InnoDB utilizza per scrivere nei file di log su disco.
tmp_table_size: la dimensione massima delle tabelle temporanee in memoria interne create dal motore di archiviazione MEMORY e, a partire da MySQL 8.0.28, anche dal motore di archiviazione TempTable.
Key_buffer_size: la dimensione del buffer utilizzato per i blocchi di indice. I blocchi di indice per le tabelle MyISAM vengono eseguiti nel buffer e sono condivisi da tutti i thread.
Read_buffer_size: ogni thread che esegue una scansione sequenziale per una tabella MyISAM alloca un buffer di queste dimensioni (in byte) per ogni tabella analizzata.
Read_rnd_buffer_size: questa variabile viene utilizzata per le letture dalle tabelle MyISAM, per qualsiasi motore di archiviazione e per l'ottimizzazione della lettura multi-intervallo.
Sort_buffer_size: ogni sessione che deve eseguire un ordinamento alloca un buffer di questa dimensione. sort_buffer_size non è specifica per alcun motore di archiviazione e si applica in modo generico per l'ottimizzazione.
Join_buffer_size: la dimensione minima del buffer utilizzato per le scansioni degli indici normali, le scansioni dell'indice degli intervalli e i join che non utilizzano indici ed eseguono quindi scansioni complete delle tabelle.
Max_connections: il numero massimo consentito di connessioni client simultanee.

Risolvere i problemi relativi al consumo elevato di memoria

Esegui SHOW PROCESSLIST per visualizzare le query in corso che utilizzano attualmente memoria. Visualizza tutti i thread connessi e le relative istruzioni SQL attualmente in esecuzione. Presta attenzione alle colonne relative a stato e durata.
```
mysql> SHOW [FULL] PROCESSLIST;
```

Controlla SHOW ENGINE INNODB STATUS nella sezione BUFFER POOL AND MEMORY per vedere il pool di buffer attuale e l'utilizzo della memoria, che possono aiutarti a impostare le dimensioni del buffer pool.

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 0

Utilizza il comando SHOW variables di MySQL per controllare i valori del contatore, che forniscono informazioni come numero di tabelle temporanee, numero di thread, numero di cache delle tabelle, pagine dirty, tabelle aperte e 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 Flag, seleziona il flag e inserisci il nuovo valore.

Infine, se l'utilizzo della memoria è ancora elevato e ritieni che l'esecuzione di query e valori dei flag siano ottimizzati, valuta la possibilità di aumentare le dimensioni dell'istanza per evitare la OOM.