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, che aiuta a comprendere l'allocazione della memoria di 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, ad esempio il server MySQL e gli agenti di servizio che forniscono servizi di supporto, come logging e 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. Per informazioni sull'allocazione della memoria in MySQL, consulta questo articolo.

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 code_area è un valore come sql o Innodb 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 fase di runtime conteggerà solo gli oggetti di memoria creati dopo aver abilitato la strumentazione. Consigliamo quindi 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 visualizzazioni sono disponibili in MySQL 5.7 e 8.0 per visualizzare l'allocazione della memoria a livelli diversi.

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 visualizzazione sys.memory_global_by_current_bytes mostra l'utilizzo corrente 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 in base all'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 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/job (consigliato), questa visualizzazione può aiutarti a capire se un'applicazione o un job particolare sta consumando molta memoria.

Questa visualizzazione riassume l'utilizzo della memoria raggruppato 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 la memoria utilizzata 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.

Risolvere i problemi di utilizzo elevato della memoria è un compito impegnativo. 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 un'elevata quantità di memoria, puoi limitare le dimensioni delle tabelle temporanee utilizzando tmp_table_size o ridurre il valore di 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 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.
• Esamina le note di rilascio di MySQL delle versioni secondarie successive della stessa serie per verificare se sono stati corretti eventuali bug con un comportamento simile. 
• Esegui l'upgrade di MySQL alla versione secondaria più recente 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.