Risoluzione dei problemi relativi alla memoria di MySQL

Una condizione di esaurimento della memoria (OOM) si verifica quando un processo tenta di allocare memoria e ha esito negativo perché il server non ha memoria disponibile. Questo spesso determina l'esecuzione del Linux OOM Killer, un processo utilizzato dal kernel Linux quando la memoria del sistema è molto ridotta che termina il processo del database. 

Un arresto anomalo del database a causa di una condizione OOM non è l'ideale, perché il processo del database viene interrotto utilizzando la direttiva SIGKILL, che non permette all'applicazione di arrestarsi normalmente causando i seguenti problemi:

• Il server MySQL è inattivo (mentre completa il ripristino dall'arresto anomalo, che può richiedere diversi minuti) e ha un impatto sugli utenti e sull'attività
• Il server MySQL richiede più tempo per il warm up completo dei buffer e delle cache per raggiungere il massimo delle prestazioni
• Possibilità di danneggiare i dati a causa di un arresto anomalo imprevisto

MySQL ha aggiunto la strumentazione della memoria in performance_schema a partire dalla versione 5.7, il che consente di comprendere l'allocazione della memoria MySQL. Questo articolo ti aiuterà a risolvere i problemi relativi alla memoria di MySQL utilizzando performance_schema.

Ogni istanza Cloud SQL è alimentata da una macchina virtuale (VM) in esecuzione su un server Google Cloud host. Ogni VM gestisce il programma di database, come il server MySQL e gli agenti di servizio che forniscono servizi di supporto, come il logging e il monitoraggio. Cloud SQL riserva una parte di memoria per il sistema operativo e gli agenti di servizio, e alloca la memoria rimanente per i programmi di database come mysqld. 

La tabella seguente mostra l'allocazione della memoria per il processo mysqld e la configurazione predefinita del pool di buffer InnoDB per ciascun tipo di macchina.

Per istanze con 10 GB di RAM e oltre, il processo mysqld è limitato all'utilizzo del 92% della RAM dell'istanza e il pool di buffer InnoDB è configurato nell'intervallo del 75-80% della memoria disponibile per il processo mysqld. Oltre al pool di buffer InnoDB, MySQL alloca diversi buffer e cache per migliorare le prestazioni. Consulta questo articolo per comprendere l'allocazione della memoria in MySQL.

Quando l'utilizzo della memoria mysqld supera il limite configurato del processo mysqld, Linux OOM Killer termina il processo mysqld perché consuma la maggior parte della memoria dell'istanza.

performance_schema è una funzionalità MySQL per il monitoraggio dell'esecuzione del server a un basso livello. Cloud SQL per MySQL consente di abilitare performance_schema sulle istanze con dimensioni RAM pari o superiori a 3 GB. È abilitato per impostazione predefinita nelle istanze con almeno 15 GB di RAM, a partire dalla versione 8.0 di MySQL. 

performance_schema è disabilitato per impostazione predefinita per le versioni 5.6 e 5.7 di MySQL; può essere abilitato utilizzando i flag di database. 

L'abilitazione di performance_schema comporta un overhead delle prestazioni e implicazioni per la memoria. Per comprendere l'allocazione della memoria, consulta il modello di allocazione della memoria dello schema delle prestazioni. 

La strumentazione della memoria performance_schema aiuta a monitorare l'utilizzo della memoria in MySQL. Gli strumenti di memoria hanno nomi nel formato memory/code_area/instrument_name, dove memory/code_area/instrument_name è un valore come memory/code_area/instrument_name o memory/code_area/instrument_name e instrument_name è il dettaglio dello strumento.

MySQL 5.7 abilita alcuni strumenti di memoria per impostazione predefinita, ma non tutti. Per abilitarli, aggiorna la colonna ENABLED della tabella performance_schema.setup_instruments. 

mysql> select version();

+-------------------+

| version()    |

+-------------------+

| 5.7.39-google-log |

+-------------------+

1 row in set (0.00 sec)

mysql> SELECT enabled, count(*) FROM performance_schema.setup_instruments WHERE NAME LIKE '%memory%' group by enabled;

+---------+----------+

| enabled | count(*) |

+---------+----------+

| YES  |   70 |

| NO   |   306 |

+---------+----------+

2 rows in set (0.00 sec)

mysql> UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME LIKE 'memory/%';

Query OK, 306 rows affected (0.00 sec)

Rows matched: 376 Changed: 306 Warnings: 0

mysql> SELECT enabled, count(*) FROM performance_schema.setup_instruments WHERE NAME LIKE '%memory%' group by enabled;

+---------+----------+

| enabled | count(*) |

+---------+----------+

| YES  |   376 |

+---------+----------+

1 row in set (0.00 sec)

L'abilitazione degli strumenti di memoria in runtime conteggia solo gli oggetti di memoria creati dopo l'abilitazione della strumentazione. Pertanto, consigliamo di attendere che il server raccolga dati sufficienti per la risoluzione dei problemi.

MySQL 8.0 abilita tutti gli strumenti di memoria per impostazione predefinita.

mysql> select version();

+---------------+

| version()  |

+---------------+

| 8.0.28-google |

+---------------+

1 row in set (0.00 sec)

mysql> SELECT enabled, count(*) FROM performance_schema.setup_instruments WHERE NAME LIKE '%memory%' group by enabled;

+---------+----------+

| enabled | count(*) |

+---------+----------+

| YES  |   490 |

+---------+----------+

1 row in set (0.00 sec)

Lo schema sys contiene molte visualizzazioni che riassumono i dati di performance_schema in moduli facilmente comprensibili. La maggior parte di queste visualizzazioni è a coppie, in cui entrambe hanno lo stesso nome a cui viene aggiunto il prefisso x$ a una. Le seguenti viste sono disponibili in MySQL 5.7 e 8.0 per visualizzare l'allocazione della memoria a diversi livelli.

mysql> use sys;

Database changed

mysql> show tables like '%memory%';

+-------------------------------------+

| Tables_in_sys (%memory%)      |

+-------------------------------------+

| memory_by_host_by_current_bytes  |

| memory_by_thread_by_current_bytes |

| memory_by_user_by_current_bytes  |

| memory_global_by_current_bytes   |

| memory_global_total        |

| x$memory_by_host_by_current_bytes |

| x$memory_by_thread_by_current_bytes |

| x$memory_by_user_by_current_bytes |

| x$memory_global_by_current_bytes  |

| x$memory_global_total       |

+-------------------------------------+

10 rows in set (0.02 sec)

La visualizzazione sys.memory_global_total riassume l'utilizzo totale della memoria in un formato leggibile.

mysql> select * from sys.memory_global_total;

+-----------------+

| total_allocated |

+-----------------+

| 12.43 GiB   |

+-----------------+

1 row in set (0.44 sec)

Il parametro sys.x$memory_global_total mostra le stesse informazioni, ma in byte.

mysql> select * from sys.x$memory_global_total;

+-----------------+

| total_allocated |

+-----------------+

|  13351482992 |

+-----------------+

1 row in set (1.39 sec)

La visualizzazione senza il prefisso x$ ha lo scopo di fornire un output più facile da usare e da leggere. La visualizzazione con il prefisso x$ che mostra gli stessi valori in formato non elaborato è destinata principalmente all'utilizzo con altri strumenti che eseguono la propria elaborazione sui dati.

La vista sys.memory_global_by_current_bytes mostra l'utilizzo attuale della memoria raggruppato per tipo di allocazione (event_name), ad esempio memory/innodb/buf_buf_pool (pool di buffer InnoDB). Per impostazione predefinita, le righe vengono ordinate in ordine decrescente in base alla quantità di memoria utilizzata. 

Prima di esaminare in dettaglio ogni tipo di allocazione, è utile avere una panoramica dell'utilizzo della memoria per ogni area di codice, ad esempio Innodb, sql, performance_schema e altre. 

La seguente query aggrega la memoria attualmente allocata per area di codice.

SELECT SUBSTRING_INDEX(event_name,'/',2) AS code_area,

FORMAT_BYTES(SUM(current_alloc)) AS current_alloc

FROM sys.x$memory_global_by_current_bytes

GROUP BY SUBSTRING_INDEX(event_name,'/',2)

ORDER BY SUM(current_alloc) DESC;

+---------------------------+---------------+

| code_area        | current_alloc |

+---------------------------+---------------+

| memory/innodb      | 11.28 GiB  |

| memory/performance_schema | 520.02 MiB  |

| memory/temptable     | 514.00 MiB  |

| memory/sql        | 106.12 MiB  |

| memory/mysys       | 43.25 MiB  |

| memory/vio        | 372.15 KiB  |

| memory/myisam      | 696 bytes  |

| memory/csv        | 88 bytes  |

| memory/blackhole     | 88 bytes  |

+---------------------------+---------------+

9 rows in set (1.02 sec)

Questo output mostra che innodb utilizza la maggior parte della memoria, seguito da performance_schema e dalle tabelle temporanee.

Ora, l'esecuzione di una query su sys.memory_global_by_current_bytes mostra direttamente l'utilizzo della memoria per le strutture interne di MySQL, come descritto nella colonna event_name. Se stai cercando di risolvere i problemi relativi a un aumento anomalo della memoria, puoi identificare l'origine osservando l'output di questa visualizzazione.

mysql> select event_name,current_alloc,high_alloc from sys.memory_global_by_current_bytes limit 5;

+-------------------------------+---------------+------------+

| event_name          | current_alloc | high_alloc |

+-------------------------------+---------------+------------+

| memory/innodb/buf_buf_pool  | 10.72 GiB  | 10.72 GiB |

| memory/temptable/physical_ram | 514.00 MiB  | 515.00 MiB |

| memory/innodb/hash0hash   | 292.69 MiB  | 292.69 MiB |

| memory/innodb/memory     | 191.77 MiB  | 197.94 MiB |

| memory/sql/TABLE       | 60.39 MiB  | 62.35 MiB |

+-------------------------------+---------------+------------+

5 rows in set (0.45 sec)

Di seguito sono riportati i dettagli di ciascun tipo di allocazione.

Questa visualizzazione riassume l'utilizzo della memoria raggruppato per utente. Se un utente ha più connessioni, somma la memoria utilizzata per tutte le istanze dell'utente.

mysql> select user,current_allocated from memory_by_user_by_current_bytes;

+-----------------+-------------------+

| user      | current_allocated |

+-----------------+-------------------+

| sbtest     | 60.30 MiB    |

| background   | 2.68 MiB     |

| root      | 1.52 MiB     |

| event_scheduler | 16.38 KiB    |

+-----------------+-------------------+

4 rows in set (1.16 sec)

Se utilizzi un utente diverso per ogni applicazione/lavoro (opzione consigliata), questa visualizzazione può aiutarti a identificare se una determinata applicazione o job consuma molta memoria.

Questa visualizzazione riassume la memoria utilizzata raggruppata per nome host.

mysql> select host,current_allocated from memory_by_host_by_current_bytes;

+------------+-------------------+

| host   | current_allocated |

+------------+-------------------+

| 10.128.0.8 | 62.10 MiB    |

| background | 2.70 MiB     |

| 127.0.0.1 | 311.01 KiB    |

| localhost | 16.38 KiB    |

+------------+-------------------+

4 rows in set (1.58 sec)

Questa visualizzazione è utile per identificare l'utilizzo della memoria per host client. È molto comune al giorno d'oggi avere più server client/delle applicazioni, per cui questa visualizzazione ti aiuterà a identificare se un particolare client o carico di lavoro sta causando un elevato utilizzo della memoria. Puoi anche eseguire il deployment della modifica su un solo client/server delle applicazioni, monitorarne l'impatto sulla memoria (confrontando l'utilizzo della memoria con quello di altri host client) e prendere decisioni consapevoli.

Questa visualizzazione riassume l'utilizzo della memoria raggruppato per thread. È utile per trovare i thread che occupano la maggior parte della memoria.

mysql> select thread_id,user,current_allocated from sys.memory_by_thread_by_current_bytes limit 5;

+-----------+-------------------+-------------------+

| thread_id | user       | current_allocated |

+-----------+-------------------+-------------------+

|  12999 | sbtest@10.128.0.8 | 3.80 MiB     |

|  12888 | sbtest@10.128.0.8 | 3.75 MiB     |

|  12760 | sbtest@10.128.0.8 | 3.68 MiB     |

|  13128 | sbtest@10.128.0.8 | 3.67 MiB     |

|  13221 | sbtest@10.128.0.8 | 3.47 MiB     |

+-----------+-------------------+-------------------+

5 rows in set (2.29 sec)

Se scopri che un determinato thread consuma molta memoria, puoi filtrare i log delle query MySQL lente o i log generali utilizzando thread_id e identificare le sessioni/query che hanno portato all'aumento della memoria.

La risoluzione dei problemi relativi a un utilizzo elevato di memoria è un'attività impegnativa. Per fortuna, la strumentazione della memoria performance_schema aiuta gli utenti a capire in che modo la memoria viene utilizzata dalle strutture MySQL interne. Le visualizzazioni dello schema sys semplificano l'interpretazione delle informazioni. Se riscontri problemi relativi alla memoria, questa funzionalità ti aiuterà a trovarne l'origine e potrai correggerli apportando modifiche alla configurazione/alla query. 

Ad esempio:

1. Se le tabelle temporanee interne richiedono una quantità elevata di memoria, puoi limitare la dimensione delle tabelle temporanee utilizzando tmp_table_size o ridurre il valore innodb_buffer_pool_size per fare spazio ai buffer per sessione
2. Se un determinato thread consuma molta memoria, puoi filtrare i log delle query lente utilizzando thread_id per identificare le query problematiche e ottimizzarle in modo da ridurre l'utilizzo di memoria per quel thread/sessione.

Se ti imbatti in una situazione in cui un'istanza MySQL utilizza più memoria del dovuto e l'utilizzo della memoria aumenta continuamente fino a quando il server non esaurisce la memoria e performance_schema mostra un divario tra memoria totale e memoria instrumentata o non segnala dove sta andando la memoria, potrebbe indicare una perdita di memoria. Se sospetti che si sia verificata una perdita di memoria, puoi procedere nel seguente modo:

• Esamina attentamente la configurazione MySQL e verifica che nessun buffer/cache abbia un'allocazione eccessiva. Questo articolo ti aiuterà a capire in che modo MySQL alloca la memoria per buffer/cache diversi.
• Identifica la funzionalità/configurazione/query che causa il problema di memoria e prova a riprodurlo.
• Consulta le note di rilascio di MySQL delle versioni secondarie successive della stessa serie per verificare se sono stati risolti eventuali bug con comportamento simile.
• Esegui l'upgrade di MySQL all'ultima versione secondaria per vedere se il problema di memoria viene risolto.
• Esegui una ricerca nel database dei bug di MySQL per verificare se un problema simile è stato segnalato da altri utenti della community.
• Crea un nuovo bug con uno scenario di test riproducibile se non esiste un bug per lo stesso problema.