Risolvere i problemi di memoria insufficiente in Database di Azure per MySQL - Server flessibile

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

Importante

Database di Azure per MySQL server singolo si trova nel percorso di ritiro. È consigliabile eseguire l'aggiornamento a Database di Azure per MySQL server flessibile. Per altre informazioni sulla migrazione a Database di Azure per MySQL server flessibile, vedere Che cosa accade 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, la memoria viene allocata per buffer e cache per migliorare le operazioni del database. Per altre informazioni, vedere Come MySQL usa la memoria.

Si noti che Database di Azure per MySQL server flessibile utilizza la memoria per ottenere il maggior numero possibile di riscontri nella cache. Di conseguenza, l'utilizzo della memoria può spesso passare dal 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.
• Query ottimali secondarie in esecuzione.
• 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, che includono 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 di memoria in cui InnoDB memorizza nella cache le tabelle di database e i dati correlati all'indice. 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 memoria.

Se si nota che l'utilizzo della memoria è aumentato improvvisamente e che la memoria disponibile viene terminata rapidamente, monitorare altre metriche, ad esempio percentuale CPU host, Connessione totali e percentuale di 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 di database può causare una carenza 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 un set di risultati temporaneo. Le tabelle temporanee possono essere riutilizzate più volte durante una sessione. Poiché tutte le tabelle temporanee create sono locali in una sessione, le diverse sessioni possono avere tabelle temporanee diverse. Nei sistemi di produzione con molte sessioni che eseguono compilazioni di set 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 dei 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 sottostanti 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 inefficienti 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 di file di tabella in modo che le tabelle possano essere aperte simultaneamente in modo indipendente da più sessioni. MySQL usa alcuni descrittori di memoria e file del sistema operativo per mantenere questa 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 di 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 Database di Azure per MySQL server flessibile dedicare più tempo all'apertura e alla chiusura delle tabelle necessarie per l'elaborazione delle query.
• L'impostazione di questo 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 di 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 per la connessione e i buffer di thread per ogni thread client e possono aumentare il valore specificato per max_allowed_packet. Se un'istruzione di query è di grandi dimensioni, ad esempio, tutti gli inserimenti/aggiornamenti hanno un valore molto elevato, 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 quando un join non può usare 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.

Sort buffer (sort_buffer_size)

Il buffer di ordinamento viene usato per eseguire ordinamenti per alcune query ORDER BY e GROUP BY. Se nell'output SHOW GLOBAL STATUS vengono visualizzati molti Sort_merge_passes al secondo, è consigliabile aumentare il valore sort_buffer_size per velocizzare le operazioni ORDER BY o GROUP BY 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 indicano diversamente. La memoria per questo 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 2M, poiché la riduzione delle prestazioni supererà eventuali vantaggi.

Cache delle query (query_cache_size)

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

Calcolo del rapporto riscontri cache 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 il rapporto di riscontri del pool di buffer InnoDB per le richieste di lettura, è possibile eseguire SHOW GLOBAL STATUS per recuperare i contatori "Innodb_buffer_pool_read_requests" e "Innodb_buffer_pool_reads" e quindi 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 del rapporto di riscontri del pool di buffer InnoDB per le 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 buffer cache hit ratio, per qualsiasi 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 scaricate per prime.

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 delle dimensioni dell'istanza è necessario per gestire le scritture in arrivo nel database.

Consigli

• Assicurarsi che il database disponga di risorse sufficienti allocate per 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 prestazioni query o cartelle di lavoro di Azure per identificare eventuali query problematiche o a esecuzione lenta e quindi ottimizzarle.
• Per i server di database di produzione, raccogliere la diagnostica a intervalli regolari per garantire che tutto funzioni senza problemi. In caso contrario, risolvere e risolvere eventuali problemi identificati.

Passaggi successivi

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