In che modo MySQL utilizza la memoria

MySQL alloca i buffer globali all'avvio del server che vengono poi condivisi tra tutte le connessioni. La maggior parte della memoria di MySQL viene utilizzata dai buffer globali, ad esempio innodb_buffer_pool_size, innodb_log_buffer_size, key_buffer_size ecc.

Il pool di buffer InnoDB è in genere quello che consuma più memoria in un'istanza MySQL. Viene configurato utilizzando il parametro innodb_buffer_pool_size. Viene usato per memorizzare nella cache i dati e gli indici delle tabelle, il buffer delle modifiche, l'indice hash adattivo e altre strutture interne. Cloud SQL per MySQL, l'offerta MySQL gestita di Google Cloud, configura innodb_buffer_pool_size fino al 72% della memoria dell'istanza a seconda delle dimensioni dell'istanza. 

InnoDB riserva memoria aggiuntiva per i buffer e le strutture di dati associate: la memoria allocata totale è circa il 10% in più rispetto alle dimensioni del pool di buffer specificate. Puoi controllare la memoria utilizzata dal pool di buffer InnoDB nell'output show engine innodb status\G.

mysql> show engine innodb status\G

…

----------------------

BUFFER POOL AND MEMORY

----------------------

Total large memory allocated 11511349248

…

Il buffer dei log InnoDB viene utilizzato per conservare le modifiche da scrivere nei file dei redo log InnoDB sul disco. Viene configurato utilizzando innodb_log_buffer_size. Il valore predefinito della community MySQL è 16 MB, che è lo stesso usato da Cloud SQL per MySQL.

Il buffer delle chiavi viene utilizzato da MySQL per memorizzare nella cache gli indici MyISAM in memoria. Viene configurato utilizzando key_buffer_size. Il valore predefinito della community MySQL è 8 MB, che è lo stesso usato da Cloud SQL per MySQL. Poiché Cloud SQL per MySQL non supporta le tabelle MyISAM, conviene lasciare impostato il valore predefinito.

La variabile di configurazione query_cache_size definisce la quantità di memoria allocata per la memorizzazione nella cache dei risultati delle query. Per impostazione predefinita, è disabilitata nella community MySQL 5.7 e in Cloud SQL per MySQL 5.7. 

La cache delle query era nota per i gravi problemi di scalabilità, pertanto è stata deprecata in MySQL 5.7.20 e rimossa in MySQL 8.0. Se la usi ancora per la tua istanza MySQL 5.7, controlla se è davvero utile per il tuo carico di lavoro. Per saperne di più, consulta questo blog. 

MySQL alloca cache globali condivise tra tutte le connessioni, che vengono allocate in modo dinamico mentre le variabili di configurazione ne definiscono il limite massimo.

MySQL utilizza la cache delle tabelle per accelerare l'apertura delle tabelle. La cache delle tabelle MySQL è separata in due parti, una cache per le tabelle aperte e una cache per le definizioni delle tabelle, configurate utilizzando rispettivamente table_open_cache e table_definition_cache. 

table_open_cache è una memoria cache per l'archiviazione del descrittore dei file delle tabelle aperte da parte di tutti i thread collegati. Incrementando questo valore aumenta il numero dei descrittori di file richiesti dal programma mysqld, noto anche come server MySQL. Assicurati che il tuo sistema operativo possa gestire il numero di descrittori di file aperti impliciti nell'impostazione dell'impostazione table_open_cache.

Più sessioni client possono accedere contemporaneamente alla tabella specificata e questa viene aperta in modo indipendente da ogni sessione client simultanea. Questo è il motivo per cui potresti visualizzare un numero di tabelle aperte superiore al numero di quelle nel server. Quando la cache delle tabelle è piena, il server rilascia le tabelle attualmente non in uso, a partire da quella utilizzata più di recente.

table_definition_cach è una memoria cache per l'archiviazione delle definizioni delle tabelle. È globale e condiviso tra tutte le connessioni.

Cloud SQL per MySQL 5.7 utilizza 2000 e 1400 come valori predefiniti per table_open_cache e table_definition_cache.

Cloud SQL per MySQL 8.0 utilizza 4000 e 2000 come valori predefiniti per table_open_cache e table_definition_cache.

Per ogni connessione client, MySQL assegna un thread dedicato che esegue tutte le query e restituisce il risultato al client finché questo non si disconnette. MySQL memorizza nella cache i thread in modo da non doverli creare ed eliminare per ogni connessione. Il numero di thread nella cache dei thread viene configurato utilizzando la variabile thread_cache_size.

Cloud SQL per MySQL utilizza 48 come valore predefinito per thread_cache_size.

InnoDB dispone di una propria cache per l'archiviazione delle definizioni delle tabelle, che è diversa dalla cache delle tabelle aperte e dalla cache delle definizioni delle tabelle. Puoi controllare la memoria allocata per il dizionario dei dati InnoDB nell'output show engine innodb status\G.

----------------------

BUFFER POOL AND MEMORY

----------------------

…

Dictionary memory allocated 65816817

L'impostazione table_definition_cache definisce un limite flessibile per il numero di istanze della tabella nella cache del dizionario dei dati InnoDB. Se il numero delle istanze della tabella nella cache del dizionario dei dati InnoDB supera il limite di table_definition_cache, il meccanismo LRU inizia a contrassegnare le istanze della tabella per l'eliminazione e alla fine le rimuove dalla cache.

Significa che il numero di istanze della tabella nella cache del dizionario dei dati InnoDB sarà sempre inferiore al limite di table_definition_cache? In caso contrario, le istanze della tabella con relazioni di chiave esterna non vengono inserite nell'elenco LRU. Rimangono memorizzate nella cache e fanno sì che le istanze della tabella superino il limite di table_definition_cache, portando a un utilizzo aggiuntivo di memoria. La memoria utilizzata dalle tabelle con relazioni di chiave esterna viene rilasciata solo all'arresto o al riavvio di MySQL. Questo problema si presenta sia in MySQL 5.7 che in MySQL 8.0 ed esiste un bug verificato noto.

Se l'istanza MySQL ha un numero elevato di tabelle con relazioni di chiave esterna, la cache del dizionario dei dati InnoDB potrebbe utilizzare più GB di memoria. Spesso questo aspetto viene trascurato durante la configurazione dei buffer o delle cache MySQL e potrebbe essere una delle cause di utilizzo imprevisto della memoria o di problemi di esaurimento della memoria.

Ecco un output di esempio che mostra una piccola istanza che utilizza 4,16 GB per la cache del dizionario dei dati InnoDB.

$ mysql -e "show engine innodb status\G" | grep -i memory

BUFFER POOL AND MEMORY

Total large memory allocated 7696023552

Dictionary memory allocated 4465193358

Un'altra funzionalità di MySQL che consuma memoria sono i buffer delle sessioni. Questi buffer vengono allocati in base alla sessione e, in alcuni casi, possono essere allocati per una singola query (in particolare join_buffer_size).

• sort_buffer_size 
• join_buffer_size
• read_buffer_size
• read_rnd_buffer_size

Questi buffer vengono allocati solo quando una query ne ha bisogno (per ordinamento, unioni, scansioni di tabelle complete/indicizzate e altro ancora), ma alla loro grandezza originale anche se ne serve solo una parte. L'impostazione di questi buffer su un valore alto può comportare uno spreco di memoria.

I valori predefiniti sono gli stessi nella community MySQL e in Cloud SQL per MySQL.

MySQL utilizza la cache del log binario per conservare le modifiche apportate al log binario durante l'esecuzione di una transazione. Viene configurato utilizzando binlog_cache_size. Viene allocato per ciascun client se è abilitato il logging binario (log_bin=ON).

Il valore predefinito per binlog_cache_size è lo stesso nella community MySQL e in Cloud SQL per MySQL.

MySQL crea tabelle temporanee interne per archiviare il risultato intermedio durante l'elaborazione di alcuni tipi di query come GROUP BY, ORDER BY, DISTINCT e UNION. Queste tabelle temporanee interne vengono create prima in memoria e convertite in tabelle su disco al raggiungimento della dimensione massima. La dimensione massima delle tabelle temporanee interne è definita come il valore minimo delle variabili tmp_table_size e max_heap_table_size. 

I valori predefiniti per tmp_table_size e max_heap_table_size sono gli stessi nella community MySQL e in Cloud SQL per MySQL.

Nota: poiché i buffer per sessione e le tabelle temporanee in memoria allocano la memoria separatamente per ogni connessione, la quantità complessiva di memoria utilizzata può essere molto elevata se richiesta da un numero elevato di connessioni. Ti consigliamo di non impostare valori troppo alti e di fare delle prove per trovare il valore migliore per il tuo carico di lavoro.

Ogni thread richiede poca memoria per gestire la connessione client. Le seguenti variabili controllano le relative dimensioni.

• thread_stack: le dimensioni dello stack per ogni thread; il valore predefinito è 256 kB.
• net_buffer_length: ogni client è associato a un buffer di connessione e al buffer dei risultati di net_buffer_length. Ciò può incrementare ulteriormente le dimensioni di max_allowed_packet.

Se performance_schema è abilitato, contribuisce a monitorare l'esecuzione del server MySQL a basso livello. performance_schema alloca la memoria in modo dinamico e viene liberato solo alla chiusura o al riavvio di MySQL.

Cloud SQL per MySQL consente di abilitare performance_schema sulle istanze con dimensioni RAM pari o superiori a 15 GB; è abilitato per impostazione predefinita a partire dalla versione MySQL 8.0.26. performance_schema è disabilitato per impostazione predefinita per le versioni 5.6, 5.7 e 8.0.18 di MySQL; può essere abilitato utilizzando i flag di database.

Cloud SQL per MySQL configura automaticamente i parametri relativi alla memoria per prestazioni pronte all'uso. La tua istanza può comunque incorrere in problemi di esaurimento della memoria se il carico di lavoro richiede cache più grandi per supportare un numero elevato di tabelle e/o connessioni. Anche molti thread che allocano buffer delle sessioni contemporaneamente possono causare problemi di memoria. Più memoria viene allocata per buffer/cache globali, meno memoria sarà disponibile per connessioni/buffer delle sessioni e viceversa, quindi è fondamentale trovare un buon equilibrio.

Se il tuo carico di lavoro richiede buffer/cache di dimensioni maggiori, puoi ridurre le dimensioni del buffer globale con innodb_buffer_pool_size. Puoi modificare i parametri di configurazione delle tue istanze Cloud SQL per MySQL utilizzando i flag di database. Se continui a riscontrare problemi di prestazioni o di esaurimento della memoria, puoi eseguire l'upgrade delle dimensioni dell'istanza per aumentare la memoria.