Ottimizza l'utilizzo elevato di memoria nelle istanze

È un problema comune che le istanze consumino molta memoria o che si verifichino eventi di esaurimento della memoria. Un'istanza di database in esecuzione con un utilizzo elevato 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 carichi di lavoro delle query condividono le posizioni di 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 si chiude, la memoria utilizzata viene rilasciata al sistema.

Quando 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 di thread e processi
  • Consumo di memoria buffer
  • Consumo di memoria della cache

Consumo di memoria di thread e processi

Ogni sessione utente consuma memoria a seconda delle query in esecuzione, dei buffer o della cache utilizzata dalla 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 in un determinato momento sono in esecuzione N query, ogni query consuma memoria in base a questi parametri durante la sessione.

Consumo di memoria buffer

Questa parte della memoria è comune a tutte le query ed è controllata da parametri come innodb_buffer_pool_size, innodb_log_buffer_size e key_buffer_size.

Il pool di buffer InnoDB, configurato dal flag innodb_buffer_pool_size, occupa una quantità significativa di memoria nell'istanza Cloud SQL per MySQL e funge da cache per migliorare le prestazioni. Per ridurre il rischio di eventi di esaurimento della memoria (OOM), puoi attivare il buffer pool gestito.

Consumo di memoria della cache

La memoria cache include una cache delle query, che viene utilizzata per salvare le query e i relativi risultati per un recupero più rapido dei 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 unione e ordinamento. Se le tue query utilizzano operazioni di unione o ordinamento, queste query utilizzano la memoria in base a join_buffer_size e sort_buffer_size.

A parte questo, se abiliti lo schema delle prestazioni, consuma memoria. Per controllare la memoria utilizzata dallo 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 saperne di più, consulta la documentazione di MySQL.

Il parametro correlato a MyISAM per l'inserimento di dati collettivi è bulk_insert_buffer_size.

Per scoprire in che modo MySQL utilizza la memoria, consulta la documentazione di MySQL.

Consigli

Le sezioni seguenti offrono alcuni suggerimenti per un utilizzo ottimale della memoria utilizzata.

Abilita il pool di buffer gestito

L'attivazione del pool di buffer gestito consente di ridurre il consumo di memoria del pool di buffer InnoDB (o innodb_buffer_pool_size) quando la memoria dell'istanza è elevata. Questa riduzione libera la memoria che può essere utilizzata da altri processi del database. Se la memoria utilizzata dell'istanza è elevata, l'istanza può riscontrare eventi di esaurimento della memoria (OOM). Ti consigliamo di abilitare il buffer pool gestito sull'istanza per evitare eventi di esaurimento della memoria.

Quando la memoria utilizzata si stabilizza a un valore inferiore per 10 minuti o più, il buffer pool gestito aumenta il valore di innodb_buffer_pool_size in modo incrementale fino al suo valore originale. Puoi anche aumentare il valore del flag innodb_buffer_pool_size fino a un valore scelto dopo che la memoria utilizzata si è stabilizzata.

Criteri di idoneità

Non puoi abilitare il buffer pool gestito per le istanze con core condivisi o per MySQL 5.6 o MySQL 5.7.

Attiva la funzionalità

Per abilitare il buffer pool gestito per l'istanza, imposta il flag innodb_cloudsql_managed_buffer_pool su on. Per saperne di più sull'impostazione dei flag di database, vedi Impostare un flag di database.

La modifica del valore del flag innodb_cloudsql_managed_buffer_pool non richiede il riavvio dell'istanza Cloud SQL.

Se hai attivato il pool di buffer gestito e il consumo di memoria della tua istanza supera la percentuale di soglia predefinita della memoria allocata, allora Cloud SQL inizia a ridurre le dimensioni del innodb_buffer_pool_size. Questa percentuale di soglia predefinita varia tra il 90% e il 97% a seconda della capacità RAM della tua istanza. Per modificare la soglia, imposta il flag innodb_cloudsql_managed_buffer_pool_threshold_pct su un valore percentuale diverso. Ad esempio, per regolare la soglia al 97%, utilizza il seguente comando:

gcloud sql instances patch INSTANCE_NAME \
   --database-flags=EXISTING_FLAGS,innodb_cloudsql_managed_buffer_pool=on,\
     innodb_cloudsql_managed_buffer_pool_threshold_pct=97

Puoi impostare il flag innodb_cloudsql_managed_buffer_pool_threshold_pct su un valore intero compreso tra 50 e 99. La modifica del valore della soglia di memoria utilizzata non richiede il riavvio dell'istanza Cloud SQL.

Il buffer pool gestito aumenta il flag innodb_buffer_pool_size quando l'utilizzo della memoria si stabilizza per 10 minuti o più dopo le riduzioni precedenti. Per impostazione predefinita, la memoria utilizzata del database deve rimanere al 70% o inferiore per attivare l'aumento. Per modificare la soglia, imposta il flag innodb_cloudsql_managed_buffer_pool_tuneup_pct su un valore percentuale diverso. Ad esempio, per modificare la soglia di aumento all'80%, utilizza il seguente comando:

gcloud sql instances patch INSTANCE_NAME \
   --database-flags=EXISTING_FLAGS,innodb_cloudsql_managed_buffer_pool=on,\
     innodb_cloudsql_managed_buffer_pool_tuneup_pct=80

Logica di aggiustamento

Il pool di buffer gestito non riduce innodb_buffer_pool_size a una dimensione minima fissa e predeterminata. Riduce invece le dimensioni in modo iterativo e dinamico finché l'utilizzo totale della memoria dell'istanza non scende al di sotto della percentuale di soglia configurata (innodb_cloudsql_managed_buffer_pool_threshold_pct). Riduce il pool del buffer modificando il valore del flag innodb_buffer_pool_size, sfruttando la funzionalità di ridimensionamento del pool del buffer integrata di InnoDB.

Per evitare che innodb_buffer_pool_size si riduca a una dimensione che influisca gravemente sulle prestazioni quando la memoria utilizzata rimane elevata nonostante la riduzione, la funzionalità utilizza un limite di sicurezza interno. I valori rappresentano la percentuale della memoria totale dell'istanza che deve essere allocata al pool del buffer.

Dimensioni del contenitore MySQL Dimensioni minime del pool del buffer
1025-2048 MB 35%
2049-6528 MB 30%
6529–11315 MB 40%
11316–22630 MB 45%
Altre dimensioni (predefinite) 50%

La quantità di cui innodb_buffer_pool_size diminuisce dipende dalla capacità di memoria dell'istanza del database. La tabella seguente mostra la diminuzione percentuale per ogni dimensione del contenitore:

Dimensioni del contenitore MySQL Diminuzione percentuale
1025-2048 MB 15%
2049-6528 MB 11%
6529–11315 MB 8%
11316–22630 MB 6%
Altre dimensioni (predefinite) 5%

Dopo aver calcolato il nuovo valore ridotto, il buffer pool gestito arrotonda innodb_buffer_pool_size al multiplo più vicino dei valori innodb_buffer_pool_instances e innodb_buffer_pool_chunk_size.

Quando il buffer pool gestito apporta modifiche al valore di innodb_buffer_pool_size, le modifiche non vengono riflesse nella console Google Cloud . Per visualizzare il valore attuale di innodb_buffer_pool_size quando il pool di buffer gestito è abilitato, puoi utilizzare il client MySQL:

mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_size';

Limitazioni

La riduzione delle dimensioni del buffer pool non può impedire gli errori di memoria insufficiente in tutti i casi. Ad esempio, alcuni workload potrebbero consumare memoria in modo insostenibile o aumentare a un ritmo improvviso, alcune istanze Cloud SQL potrebbero essere sottoprovvigionate o il buffer pool potrebbe non essere riscaldato. Cloud SQL potrebbe non essere in grado di liberare memoria abbastanza rapidamente da adattarsi a improvvisi cambiamenti nel carico di lavoro della memoria. Inoltre, Cloud SQL non può gestire valori configurati in modo errato di altri flag di memoria.

Monitoraggio

Puoi monitorare il buffer pool gestito nel log degli errori MySQL. In Esplora log di Cloud Logging, puoi filtrare il log mysql.err per le voci con il prefisso Managed Buffer Pool Plugin: o Tuner Plugin: per trovare gli eventi di aggiustamento più recenti.

Quando il pool di buffer gestito viene avviato per la prima volta, emette un log simile al seguente:

[Note] [MY-000000] [Server] Managed Buffer Pool Plugin: MySQL Instance memory limit: 29533, Current MySQL memory usage: 2663641088, Max Allowed MySQL memory usage: 30732730368 ...

I seguenti log mostrano esempi di riduzioni automatiche di innodb_buffer_pool_size:

[Note] [MY-000000] [Server] Managed Buffer Pool Plugin: Decreasing InnoDB Buffer Pool Size.
[Note] [MY-000000] [Server] Managed Buffer Pool Plugin: Updated innodb_buffer_pool_size=805306368 bytes.

Puoi configurare le metriche basate sui log per monitorare gli eventi di aggiustamento del buffer pool gestito nel tempo.

Utilizza Esplora metriche di Cloud Monitoring per identificare la memoria utilizzata

Puoi esaminare l'utilizzo della memoria di un'istanza con la metrica database/memory/components.usage in Esplora metriche di Cloud Monitoring.

In generale, se hai meno del 10% di memoria in database/memory/components.cache e database/memory/components.free combinati, il rischio di un evento OOM è elevato. Per monitorare l'utilizzo della memoria e prevenire eventi di tipo OOM, ti consigliamo di configurare un'policy di avviso con una condizione di soglia della metrica in database/memory/components.usage.

La seguente tabella mostra la relazione tra la memoria dell'istanza e la soglia di avviso consigliata:

Memoria istanza Soglia di avviso consigliata
Minore o uguale a 16 GB 90%
Maggiore di 16 GB 95%

Calcolare il consumo di memoria

Calcola la memoria utilizzata massima dal tuo database MySQL per selezionare il tipo di istanza appropriato per il tuo database MySQL. Utilizza la seguente formula:

Utilizzo massimo della memoria di 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 del buffer, l'area di memoria in cui InnoDB memorizza nella cache i dati di tabelle e indici.
  • innodb_additional_mem_pool_size: le dimensioni in byte di un pool di memoria utilizzato da InnoDB per archiviare le informazioni del dizionario dei 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 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: le dimensioni del buffer utilizzato per i blocchi di indice. I blocchi di indice per le tabelle MyISAM vengono memorizzati 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 che scansiona.
  • 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 queste dimensioni. sort_buffer_size non è specifico per alcun motore di archiviazione e si applica in modo generale per l'ottimizzazione.
  • join_buffer_size: la dimensione minima del buffer utilizzato per le scansioni dell'indice semplice, le scansioni dell'indice di intervallo e i join che non utilizzano gli indici ed eseguono quindi scansioni complete delle tabelle.
  • max_connections: il numero massimo consentito di connessioni client simultanee.

Risolvi i problemi relativi all'elevato consumo di memoria

  • Esegui SHOW PROCESSLIST per visualizzare le query in corso che consumano memoria. Mostra tutti i thread connessi e le relative istruzioni SQL in esecuzione e tenta di ottimizzarli. Presta attenzione alle colonne Stato e Durata.

    mysql> SHOW [FULL] PROCESSLIST;

  • Controlla SHOW ENGINE INNODB STATUS nella sezione BUFFER POOL AND MEMORY per visualizzare l'utilizzo attuale del buffer pool e della memoria, che può 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 dei contatori, che forniscono informazioni come il numero di tabelle temporanee, il numero di thread, il numero di cache delle tabelle, pagine modificate, tabelle aperte e utilizzo del buffer pool.

    mysql> SHOW variables like 'VARIABLE_NAME'

Applica le modifiche

Dopo aver analizzato la memoria utilizzata dai 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 la memoria utilizzata è ancora elevata e ritieni che l'esecuzione di query e i valori dei flag siano ottimizzati, valuta la possibilità di aumentare le dimensioni dell'istanza per evitare OOM.

Passaggi successivi