Risolvere i problemi di memoria insufficiente nel server flessibile del Database di Azure per MySQL

SI APPLICA A:Database di Azure per MySQL - Server singolo Database di Azure per MySQL - Server flessibile

Importante

Il server singolo del Database di Azure per MySQL è in fase di ritiro. È consigliabile eseguire l'aggiornamento al server flessibile del Database di Azure per MySQL. Per altre informazioni sulla migrazione a Database di Azure per MySQL - Server flessibile, vedere Cosa succede a Database di Azure per MySQL - Server singolo?

Per garantire che un'istanza del server flessibile Database di Azure per MySQL funzioni in modo ottimale, è molto importante disporre dell'allocazione e dell'utilizzo della memoria appropriati. Per impostazione predefinita, quando si crea un'istanza di Database di Azure per MySQL server flessibile, la memoria fisica disponibile dipende dal livello e dalle dimensioni selezionate per il carico di lavoro. Inoltre, viene allocata memoria per i buffer e per i cache in modo da migliorare le operazioni del database. Per ulteriori informazioni, vedere Come MySQL usa la memoria.

Si noti che Database di Azure per MySQL server flessibile utilizza memoria per ottenere il maggior numero possibile di riscontri nella cache. Di conseguenza, l'utilizzo della memoria può spesso passare dall’80 al 90% della memoria fisica disponibile di un'istanza. A meno che non si verifichi un problema con lo stato di avanzamento del carico di lavoro della query, non si tratta di un problema. Tuttavia, è possibile che si verifichino problemi di memoria insufficiente per motivi come quelli disponibili:

• Buffer troppo grandi configurati.
• Esecuzioni di query non ottimali.
• Query che eseguono join e ordinamento di set di dati di grandi dimensioni.
• Impostare le connessioni massime in un server di database troppo elevato.

La maggior parte della memoria di un server viene usata dai buffer e dalle cache globali di InnoDB, inclusi componenti come innodb_buffer_pool_size, innodb_log_buffer_size, key_buffer_size e query_cache_size.

Il valore del parametro innodb_buffer_pool_size specifica l’area della memoria in cui InnoDB memorizza le tabelle del database e i dati relativi agli indici. MySQL tenta di supportare il maggior numero possibile di dati correlati a tabelle e indici nel pool di buffer. Un pool di buffer di dimensioni maggiori richiede un minor numero di operazioni di I/O deviate sul disco.

Monitoraggio dell'utilizzo della memoria

Database di Azure per MySQL server flessibile offre una gamma di metriche per misurare le prestazioni dell'istanza del database. Per comprendere meglio l'utilizzo della memoria per il server di database, visualizzare le metriche Percentuale memoria host o Percentuale di memoria.

Se si nota che l'utilizzo della memoria è aumentato improvvisamente e che la memoria disponibile termina rapidamente, monitorare altre metriche, come ad esempio Percentuale CPU host, Connessioni totali e Percentuale I/O, per determinare se un picco improvviso nel carico di lavoro è l'origine del problema.

È importante notare che ogni connessione stabilita con il server di database richiede l'allocazione di una quantità di memoria. Di conseguenza, un aumento delle connessioni al database può causare carenze di memoria.

Cause dell'utilizzo elevato della memoria

Verranno ora esaminate altre cause dell'utilizzo elevato della memoria in MySQL. Queste cause dipendono dalle caratteristiche del carico di lavoro.

Aumento delle tabelle temporanee

MySQL usa "tabelle temporanee", ovvero un tipo speciale di tabella progettato per archiviare una serie di risultati temporanea. Le tabelle temporanee possono essere riutilizzate più volte durante una sessione. Dato che tutte le tabelle temporanee create sono locali in una sessione, sessioni differenti possono avere tabelle temporanee differenti. Nei sistemi di produzione con molte sessioni che eseguono compilazioni di serie di risultati temporanei di grandi dimensioni, è consigliabile controllare regolarmente il contatore dello stato globale created_tmp_tables, che tiene traccia del numero di tabelle temporanee create durante le ore di punta. Un numero elevato di tabelle temporanee in memoria può portare rapidamente a memoria insufficiente in un'istanza di Database di Azure per MySQL server flessibile.

Con MySQL, le dimensioni temporanee della tabella sono determinate dai valori di due parametri, come descritto nella tabella seguente.

Parametro	Descrizione
tmp_table_size	Specifica le dimensioni massime delle tabelle temporanee interne in memoria.
max_heap_table_size	Specifica le dimensioni massime in base alle quali le tabelle MEMORY create dall'utente possono aumentare.

Nota

Quando si determinano le dimensioni massime di una tabella temporanea interna e in memoria, MySQL considera la parte inferiore della serie di valori impostati per i parametri tmp_table_size e max_heap_table_size.

Consigli

Per risolvere i problemi di memoria insufficiente relativi alle tabelle temporanee, prendere in considerazione le raccomandazioni seguenti.

• Prima di aumentare il valore tmp_table_size, verificare che il database sia indicizzato correttamente, in particolare per le colonne coinvolte in join e raggruppate per operazioni. L'utilizzo degli indici appropriati nelle tabelle soggiacenti limita il numero di tabelle temporanee create. L'aumento del valore di questo parametro e del parametro max_heap_table_size senza verificare gli indici può consentire l'esecuzione di query inefficaci senza indici e creare più tabelle temporanee di quelle necessarie.
• Ottimizzare i valori dei parametri max_heap_table_size e tmp_table_size per soddisfare le esigenze del carico di lavoro.
• Se i valori impostati per i parametri max_heap_table_size e tmp_table_size sono troppo bassi, le tabelle temporanee possono passare regolarmente all'archiviazione, aggiungendo latenza alle query. È possibile tenere traccia della distribuzione delle tabelle temporanee su disco usando il contatore di stato globale created_tmp_disk_tables. Confrontando i valori delle variabili created_tmp_disk_tables e created_tmp_tables, si visualizza il numero di tabelle temporanee interne su disco create con il numero totale di tabelle temporanee interne create.

Cache delle tabelle

Come sistema multithread, MySQL gestisce una cache di descrittori dei file della tabella in modo che le tabelle possano essere aperte simultaneamente in modo indipendente da più sessioni. MySQL utilizza una certa quantità di memoria e di descrittori dei file del sistema operativo per mantenere la cache delle tabelle. La variabile table_open_cache definisce le dimensioni della cache della tabella.

Consigli

Per risolvere i problemi di memoria insufficiente relativi alla cache delle tabelle, prendere in considerazione le raccomandazioni seguenti.

• Il parametro table_open_cache specifica il numero di tabelle aperte per tutti i thread. L'aumento di questo valore aumenta il numero dei descrittori di file richiesti da mysqld. È possibile verificare se è necessario aumentare la cache delle tabelle controllando la variabile di stato opened_tables nel contatore dello stato globale. Aumentare il valore di questo parametro in incrementi per supportare il carico di lavoro.
• L'impostazione di table_open_cache troppo bassa può causare la necessità di dedicare più tempo Database di Azure per MySQL server flessibile all'apertura e alla chiusura delle tabelle necessarie per l'elaborazione delle query.
• L'impostazione di un valore troppo alto può causare un utilizzo maggiore di memoria e il sistema operativo in esecuzione di descrittori di file che causano connessioni rifiutate o che non riescono a elaborare le query.

Altri buffer e cache della query

Durante la risoluzione dei problemi relativi alla memoria insufficiente, è possibile usare altri buffer e una cache per facilitare la risoluzione.

Buffer netto (net_buffer_length)

Il buffer netto è la dimensione dei buffer di connessione e di thread per ogni thread client e può crescere fino al valore specificato per max_allowed_packet. Se l'istruzione di esecuzione di una query è di grandi dimensioni, ad esempio, tutti gli inserimenti/aggiornamenti hanno un valore molto elevato, pertanto l'aumento del valore del parametro net_buffer_length consentirà di migliorare le prestazioni.

Buffer di join (join_buffer_size)

Il buffer di join viene allocato alle righe della tabella della cache qualora il join non possa utilizzare un indice. Se il database include molti join eseguiti senza indici, prendere in considerazione l'aggiunta di indici per join più veloci. Se non è possibile aggiungere indici, è consigliabile aumentare il valore del parametro join_buffer_size, che specifica la quantità di memoria allocata per ogni connessione.

Buffer di ordinamento (sort_buffer_size)

Il buffer di ordinamento viene usato per classificare le query secondo i criteri ORDINA PER e RAGGRUPPA PER. Se nell'output MOSTRA STATO GLOBALE vengono visualizzati molti Sort_merge_passes al secondo, è consigliabile aumentare il valore sort_buffer_size per velocizzare le operazioni ORDINA PER o RAGGRUPPA PER che non possono essere migliorate usando l'ottimizzazione delle query o un'indicizzazione migliore.

Evitare di aumentare arbitrariamente il valore sort_buffer_size, a meno che non siano presenti informazioni correlate che diano indicazioni differenti. La memoria per tale buffer viene assegnata per ogni connessione. Nella documentazione di MySQL, l'articolo Variabili di sistema del server indica che in Linux sono presenti due soglie, 256 kB e 2 MB e che l'uso di valori più grandi può rallentare significativamente l'allocazione della memoria. Di conseguenza, evitare di aumentare il valore sort_buffer_size oltre 2 M, in quanto la riduzione delle prestazioni supererà eventuali vantaggi.

Cache della query (query_cache_size)

La cache della query è un'area della memoria usata per memorizzare nella cache le serie di risultati della query. Il parametro query_cache_size determina la quantità di memoria allocata per la memorizzazione dei risultati della query nella cache. La cache delle query è disabilitata per impostazione predefinita. Inoltre, la cache delle query è stata deprecata nella versione MySQL 5.7.20 e rimossa nella versione MySQL 8.0. Se la cache delle query è abilitata nella soluzione, verificare che non siano presenti query basate su di essa prima di disabilitarla.

Calcolo della percentuale dei riscontri nella cache del buffer

Il rapporto di riscontri nella cache del buffer è importante nell'ambiente server flessibile Database di Azure per MySQL per comprendere se il pool di buffer può soddisfare o meno le richieste del carico di lavoro e, come regola generale, è consigliabile avere sempre un rapporto di riscontri nella cache del pool di buffer superiore al 99%.

Per calcolare la percentuale di riscontri del pool di buffer InnoDB alle richieste di lettura, è possibile eseguire MOSTRA STATO GLOBALE per recuperare i contatori "Innodb_buffer_pool_read_requests" e "Innodb_buffer_pool_reads" e poi calcolare il valore usando la formula illustrata di seguito.

InnoDB Buffer pool hit ratio = Innodb_buffer_pool_read_requests / (Innodb_buffer_pool_read_requests + Innodb_buffer_pool_reads) * 100

Si consideri l'esempio seguente.

mysql> show global status like "innodb_buffer_pool_reads";
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| Innodb_buffer_pool_reads | 197   |
+--------------------------+-------+
1 row in set (0.00 sec)

mysql> show global status like "innodb_buffer_pool_read_requests";
+----------------------------------+----------+
| Variable_name                    | Value    |
+----------------------------------+----------+
| Innodb_buffer_pool_read_requests | 22479167 |
+----------------------------------+----------+
1 row in set (0.00 sec)

Usando i valori precedenti, il calcolo della percentuale di riscontri del pool di buffer InnoDB alle richieste di lettura restituisce il risultato seguente:

InnoDB Buffer pool hit ratio = 22479167/(22479167+197) * 100 

Buffer hit ratio = 99.99%

Oltre a selezionare le istruzioni della percentuale di riscontri della cache del buffer, per ciascuna istruzione DML, le scritture nel pool di buffer InnoDB vengono eseguite in background. Tuttavia, se è necessario leggere o creare una pagina e non sono disponibili pagine pulite, è anche necessario attendere che le pagine vengano dapprima scaricate.

Il contatore Innodb_buffer_pool_wait_free conta il numero di volte in cui si è verificato questo problema. Innodb_buffer_pool_wait_free maggiore di 0 è un indicatore sicuro che il pool di buffer InnoDB è troppo piccolo e l'aumento delle dimensioni del pool di buffer o dell'istanza è necessario per gestire le scritture in arrivo nel database.

Consigli

• Accertarsi che il database disponga di risorse sufficienti ad eseguire le query. A volte, potrebbe essere necessario aumentare le dimensioni dell'istanza per ottenere una maggiore memoria fisica, in modo che i buffer e le cache siano in grado di supportare il carico di lavoro.
• Evitare transazioni di grandi dimensioni o con esecuzione prolungata suddividendole in transazioni più piccole.
• Usare gli avvisi "Percentuale memoria host" in modo da ricevere notifiche se il sistema supera una delle soglie specificate.
• Usare informazioni dettagliate sulle prestazioni delle query o cartelle di lavoro di Azure per identificare eventuali query problematiche o con esecuzione lenta e quindi ottimizzarle.
• Per i server di database di produzione, acquisire la diagnostica a intervalli regolari per garantire che tutto funzioni correttamente. In caso contrario, risolvere eventuali problemi identificati.

Passaggi successivi

Per trovare risposte di peer alle domande più importanti o per pubblicare o rispondere a una domanda, visitare Stack Overflow.