Risolvere i problemi di utilizzo elevato della CPU per 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?
Database di Azure per MySQL server flessibile offre una serie di metriche che è possibile usare per identificare i colli di bottiglia delle risorse e i problemi di prestazioni nel server. Per determinare se il server sta riscontrando un utilizzo elevato della CPU, monitorare le metriche, ad esempio "Percentuale CPU host", "Total Connessione ions", "Host Memory Percent" e "I/O Percent". A volte, la visualizzazione di una combinazione di queste metriche fornirà informazioni dettagliate su ciò che potrebbe causare l'aumento dell'utilizzo della CPU nell'istanza del server flessibile Database di Azure per MySQL.
Si consideri, ad esempio, un aumento improvviso delle connessioni che avvia un aumento delle query di database che causano l'aumento dell'utilizzo della CPU.
Oltre ad acquisire le metriche, è importante tracciare anche il carico di lavoro per capire se una o più query causano il picco di utilizzo della CPU.
Cause elevate della CPU
I picchi di CPU possono verificarsi per diversi motivi, principalmente a causa di picchi di connessioni e query SQL scritte in modo non corretto o di una combinazione di entrambi:
Picco nelle connessioni
Un aumento delle connessioni può comportare un aumento dei thread, che a sua volta può causare un aumento dell'utilizzo della CPU perché deve gestire queste connessioni insieme alle query e alle risorse. Per risolvere un picco di connessioni, è necessario controllare la metrica Total Connessione ions e fare riferimento alla sezione successiva per altri dettagli su queste connessioni. È possibile utilizzare il performance_schema per identificare gli host e gli utenti attualmente connessi al server con i comandi seguenti:
Host connessi correnti
select HOST,CURRENT_CONNECTIONS From performance_schema.hosts
where CURRENT_CONNECTIONS > 0
and host not in ('NULL','localhost');
Utenti connessi correnti
select USER,CURRENT_CONNECTIONS from performance_schema.users
where CURRENT_CONNECTIONS >0
and USER not in ('NULL','azure_superuser');
Query SQL scritte in modo non adeguato
Le query che sono costose per eseguire e analizzare un numero elevato di righe senza un indice o quelle che eseguono ordinamenti temporanei insieme ad altri piani inefficienti possono causare picchi della CPU. Anche se alcune query possono essere eseguite rapidamente in una singola sessione, possono causare picchi di CPU durante l'esecuzione in più sessioni. Pertanto, è fondamentale spiegare sempre le query acquisite dall'elenco dei processi di visualizzazione e assicurarsi che i piani di esecuzione siano efficienti. Ciò può essere ottenuto assicurandosi di analizzare un numero minimo di righe usando filtri/clausola where, utilizzare indici ed evitare l'uso di un ordinamento temporaneo di grandi dimensioni insieme ad altri piani di esecuzione non valida. Per altre informazioni sui piani di esecuzione, vedere FORMATO di output EXPLAIN.
Acquisizione dei dettagli del carico di lavoro corrente
Il comando SHOW (FULL) PROCESSLIST visualizza un elenco di tutte le sessioni utente attualmente connesse all'istanza del server flessibile Database di Azure per MySQL. Fornisce anche informazioni dettagliate sullo stato corrente e sull'attività di ogni sessione.
Questo comando genera solo uno snapshot dello stato della sessione corrente e non fornisce informazioni sull'attività della sessione cronologica.
Di seguito viene esaminato l'output di esempio dell'esecuzione di questo comando.
mysql> SHOW FULL PROCESSLIST;
+-------+------------------+--------------------+---------------+-------------+--------+-----------------------------+------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-------+------------------+--------------------+---------------+-------------+--------+-----------------------------+------------------------------------------+
| 1 | event_scheduler | localhost | NULL | Daemon | 13 | Waiting for next activation | NULL |
| 6 | azure_superuser | 127.0.0.1:33571 | NULL | Sleep | 115 | | NULL
|
| 24835 | adminuser | 10.1.1.4:39296 | classicmodels | Query | 7 | Sending data | select * from classicmodels.orderdetails;|
| 24837 | adminuser | 10.1.1.4:38208 | NULL | Query | 0 | starting | SHOW FULL PROCESSLIST |
+-------+------------------+--------------------+---------------+-------------+--------+-----------------------------+------------------------------------------+
5 rows in set (0.00 sec)
Si noti che esistono due sessioni di proprietà dell'utente di proprietà del cliente "adminuser", entrambe dello stesso indirizzo IP:
- La sessione 24835 ha eseguito un'istruzione edizione Standard LECT per gli ultimi sette secondi.
- La sessione 24837 esegue l'istruzione "show full processlist".
Quando necessario, potrebbe essere necessario terminare una query, ad esempio una query di report o HTAP che ha causato il picco dell'utilizzo della CPU del carico di lavoro di produzione. Tuttavia, considerare sempre le potenziali conseguenze della terminazione di una query prima di eseguire l'azione nel tentativo di ridurre l'utilizzo della CPU. Altre volte se sono presenti query a esecuzione prolungata identificate che comportano picchi di CPU, ottimizzare queste query in modo che le risorse vengano usate in modo ottimale.
Analisi dettagliata del carico di lavoro
È necessario usare almeno due origini di informazioni per ottenere informazioni accurate sullo stato di una sessione, una transazione e una query:
- Elenco di processi del server dal INFORMATION_SCHEMA. Tabella PROCESSLIST, accessibile anche eseguendo il comando SHOW [FULL] PROCESSLIST.
- Metadati delle transazioni di InnoDB dal INFORMATION_SCHEMA. INNODB_TRX tabella.
Con informazioni provenienti solo da una di queste origini, è impossibile descrivere la connessione e lo stato della transazione. Ad esempio, l'elenco di processi non indica se è presente una transazione aperta associata a una delle sessioni. D'altra parte, i metadati della transazione non mostrano lo stato della sessione e il tempo trascorso in tale stato.
La query di esempio seguente che combina le informazioni sull'elenco di processi con alcune delle parti importanti dei metadati delle transazioni InnoDB:
mysql> select p.id as session_id, p.user, p.host, p.db, p.command, p.time, p.state, substring(p.info, 1, 50) as info, t.trx_started, unix_timestamp(now()) - unix_timestamp(t.trx_started) as trx_age_seconds, t.trx_rows_modified, t.trx_isolation_level from information_schema.processlist p left join information_schema.innodb_trx t on p.id = t.trx_mysql_thread_id \G
L'esempio seguente mostra l'output di questa query:
*************************** 1. row ***************************
session_id: 11
user: adminuser
host: 172.31.19.159:53624
db: NULL
command: Sleep
time: 636
state: cleaned up
info: NULL
trx_started: 2019-08-01 15:25:07
trx_age_seconds: 2908
trx_rows_modified: 17825792
trx_isolation_level: REPEATABLE READ
*************************** 2. row ***************************
session_id: 12
user: adminuser
host: 172.31.19.159:53622
db: NULL
command: Query
time: 15
state: executing
info: select * from classicmodels.orders
trx_started: NULL
trx_age_seconds: NULL
trx_rows_modified: NULL
trx_isolation_level: NULL
Un'analisi di queste informazioni, per sessione, è elencata nella tabella seguente.
| Area | Analisi |
|---|---|
| Sessione 11 | Questa sessione è attualmente inattiva (inattiva) senza query in esecuzione ed è stata per 636 secondi. All'interno della sessione, una transazione aperta per 2908 secondi ha modificato 17.825.792 righe e usa l'isolamento REPEATABLE READ. |
| Sessione 12 | La sessione esegue attualmente un'istruzione edizione Standard LECT, che è stata eseguita per 15 secondi. Non esiste alcuna query in esecuzione all'interno della sessione, come indicato dai valori NULL per trx_started e trx_age_seconds. La sessione continuerà a contenere il limite di Garbage Collection finché viene eseguito, a meno che non usi l'isolamento READ COMMITTED più rilassato. |
Si noti che se una sessione viene segnalata come inattiva, non esegue più istruzioni. A questo punto, la sessione ha completato qualsiasi lavoro precedente ed è in attesa di nuove istruzioni dal client. Tuttavia, le sessioni inattive sono comunque responsabili di un utilizzo della CPU e della memoria.
Elenco delle transazioni aperte
L'output della query seguente fornisce un elenco di tutte le transazioni attualmente in esecuzione sul server di database in ordine di ora di inizio della transazione, in modo da poter identificare facilmente se sono presenti transazioni a esecuzione prolungata e bloccano il runtime previsto.
SELECT trx_id, trx_mysql_thread_id, trx_state, Unix_timestamp() - ( To_seconds(trx_started) - To_seconds('1970-01-01 00:00:00') ) AS trx_age_seconds, trx_weight, trx_query, trx_tables_in_use, trx_tables_locked, trx_lock_structs, trx_rows_locked, trx_rows_modified, trx_isolation_level, trx_unique_checks, trx_is_read_only FROM information_schema.innodb_trx ORDER BY trx_started ASC;
Informazioni sugli stati dei thread
Le transazioni che contribuiscono a un utilizzo maggiore della CPU durante l'esecuzione possono avere thread in vari stati, come descritto nelle sezioni seguenti. Usare queste informazioni per comprendere meglio il ciclo di vita delle query e i vari stati del thread.
Controllo delle autorizzazioni/Apertura di tabelle
Questo stato indica in genere che l'operazione di tabella aperta richiede molto tempo. In genere, è possibile aumentare le dimensioni della cache delle tabelle per migliorare il problema. Tuttavia, le tabelle che si aprono lentamente possono anche essere indicative di altri problemi, ad esempio la presenza di troppe tabelle nello stesso database.
Invio di dati
Anche se questo stato può significare che il thread invia dati attraverso la rete, può anche indicare che la query sta leggendo i dati dal disco o dalla memoria. Questo stato può essere causato da un'analisi di tabella sequenziale. È necessario controllare i valori del innodb_buffer_pool_reads e innodb_buffer_pool_read_requests per determinare se un numero elevato di pagine viene servito dal disco nella memoria. Per altre informazioni, vedere Risolvere i problemi di memoria insufficiente in Database di Azure per MySQL server flessibile.
Aggiornamento
Questo stato indica in genere che il thread esegue un'operazione di scrittura. Controllare la metrica correlata alle operazioni di I/O nel Monitor prestazioni per ottenere una migliore comprensione delle operazioni eseguite dalle sessioni correnti.
In attesa del <blocco lock_type>
Questo stato indica che il thread è in attesa di un secondo blocco. Nella maggior parte dei casi, potrebbe trattarsi di un blocco di metadati. È consigliabile esaminare tutti gli altri thread e vedere chi sta prendendo il blocco.
Informazioni e analisi degli eventi di attesa
È importante comprendere gli eventi di attesa sottostanti nel motore MySQL, perché un numero elevato di attese o un numero elevato di attese in un database può causare un aumento dell'utilizzo della CPU. L'esempio seguente mostra il comando e l'output di esempio appropriati.
SELECT event_name AS wait_event,
count_star AS all_occurrences,
Concat(Round(sum_timer_wait / 1000000000000, 2), ' s') AS total_wait_time,
Concat(Round(avg_timer_wait / 1000000000, 2), ' ms') AS
avg_wait_time
FROM performance_schema.events_waits_summary_global_by_event_name
WHERE count_star > 0 AND event_name <> 'idle'
ORDER BY sum_timer_wait DESC LIMIT 10;
+--------------------------------------+-----------------+-----------------+---------------+
| wait_event | all_occurrences | total_wait_time | avg_wait_time |
+--------------------------------------+-----------------+-----------------+---------------+
| wait/io/file/sql/binlog | 7090 | 255.54 s | 36.04 ms |
| wait/io/file/innodb/innodb_log_file | 17798 | 55.43 s | 3.11 ms |
| wait/io/file/innodb/innodb_data_file | 260227 | 39.67 s | 0.15 ms |
| wait/io/table/sql/handler | 5548985 | 11.73 s | 0.00 ms |
| wait/io/file/sql/FRM | 1237 | 7.61 s | 6.15 ms |
| wait/io/file/sql/dbopt | 28 | 1.89 s | 67.38 ms |
| wait/io/file/myisam/kfile | 92 | 0.76 s | 8.30 ms |
| wait/io/file/myisam/dfile | 271 | 0.53 s | 1.95 ms |
| wait/io/file/sql/file_parser | 18 | 0.32 s | 17.75 ms |
| wait/io/file/sql/slow_log | 2 | 0.05 s | 25.79 ms |
+--------------------------------------+-----------------+-----------------+---------------+
10 rows in set (0.00 sec)
Limitare il tempo di esecuzione delle istruzioni edizione Standard LECT
Se non si conoscono i costi di esecuzione e il tempo di esecuzione per le operazioni di database che coinvolgono query edizione Standard LECT, qualsiasi edizione Standard LET a esecuzione prolungata può causare imprevedibilità o volatilità nel server di database. Le dimensioni delle istruzioni e delle transazioni, nonché l'utilizzo delle risorse associato, continuano a crescere a seconda della crescita del set di dati sottostante. A causa di questa crescita non associato, le istruzioni e le transazioni degli utenti finali richiedono più tempo e più tempo, consumando sempre più risorse fino a quando non sovraccaricano il server di database. Quando si usano query edizione Standard LECT non associate, è consigliabile configurare il parametro max_execution_time in modo che tutte le query che superano questa durata vengano interrotte.
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 più core CPU per supportare il carico di lavoro.
- Evitare transazioni di grandi dimensioni o con esecuzione prolungata suddividendole in transazioni più piccole.
- Eseguire istruzioni edizione Standard LECT nei server di replica in lettura, quando possibile.
- Usare gli avvisi in "Percentuale CPU 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.