Risolvere i problemi di utilizzo elevato della CPU per Database di Azure per MySQL - Server flessibile

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 riscontra un utilizzo elevato della CPU, monitorare le metriche, ad esempio "Percentuale CPU host", "Connessioni totali", "Percentuale memoria host" e "Percentuale I/O". 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 di un elevato utilizzo 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 oppure a causa di una combinazione di entrambi i fattori:

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 Totale connessioni e fare riferimento alla sezione successiva per altri dettagli relativi a tali 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 potrebbero essere eseguite rapidamente in una singola sessione, possono causare picchi di CPU quando vengono eseguite 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 validi. Per altre informazioni sui piani di esecuzione, vedere EXPLAIN Output Format.

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.

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) |

Esistono due sessioni di proprietà dell'utente di proprietà del cliente "adminuser", entrambe dello stesso indirizzo IP:

• La sessione 24835 ha eseguito un'istruzione SELECT per gli ultimi sette secondi.
• La sessione 24837 esegue l'istruzione "show full processlist".

Laddove sia il caso, 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

Nell'esempio seguente viene descritto 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.

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.

Inserzione di transazioni aperte

L'output della query seguente fornisce un elenco di tutte le transazioni attualmente in esecuzione sul server di database ordinate per 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 dei thread.

Controllo delle autorizzazioni/Apertura di tabelle

Questo stato indica in genere che l'operazione di apertura della tabella richiede molto tempo. In genere, è possibile aumentare le dimensioni della cache delle tabelle per risolvere il problema. Tuttavia, le tabelle che si aprono lentamente possono anche essere indicative di altri problematiche, 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 una scansione di tabella sequenziale. È necessario controllare i valori del innodb_buffer_pool_reads e innodb_buffer_pool_read_requests per stabilire 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 IO in Performance Monitor per ottenere una migliore comprensione delle operazioni eseguite dalle sessioni correnti.

In attesa del blocco di <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 provocando il blocco.

Comprensione e analisi degli eventi in 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. Il comando e l’output campione appropriati sono illustrati nell'esempio seguente:

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 SELECT

Se non si conoscono i costi di esecuzione e il tempo di esecuzione per le operazioni di database che coinvolgono query SELECT, qualsiasi selet 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 associata, le istruzioni e le transazioni degli utenti finali richiedono sempre più tempo, consumando sempre più risorse fino a quando non sovraccaricano il server di database. Quando si usano query SELECT non associate, è consigliabile configurare il parametro max_execution_time in modo che tutte le query che superano questa durata vengano interrotte.

Elementi consigliati

• Accertarsi che il database disponga di risorse sufficienti ad eseguire le query. A volte potrebbe essere necessario aumentare le dimensioni dell'istanza per ottenere più core della CPU per supportare il carico di lavoro.
• Evitare transazioni di grandi dimensioni o con esecuzione prolungata suddividendole in transazioni più piccole.
• Eseguire istruzioni SELECT 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 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.

Contenuto correlato

Stack Overflow