Risolvere i problemi di prestazioni delle query nel server flessibile di Database di Azure per MySQL
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?
Le prestazioni delle query possono essere influenzate da più fattori, quindi è prima importante esaminare l'ambito dei sintomi riscontrati nell'istanza del server flessibile Database di Azure per MySQL. Ad esempio, le prestazioni delle query sono lente per:
- Tutte le query in esecuzione nell'istanza del server flessibile Database di Azure per MySQL?
- Set specifico di query?
- Una query specifica?
Tenere inoltre presente che eventuali modifiche recenti alla struttura o ai dati sottostanti delle tabelle su cui si sta eseguendo query possono influire sulle prestazioni.
Abilitazione della funzionalità di registrazione
Prima di analizzare le singole query, è necessario definire benchmark di query. Con queste informazioni, è possibile implementare la funzionalità di registrazione nel server di database per tracciare le query che superano una soglia specificata in base alle esigenze dell'applicazione.
Database di Azure per MySQL server flessibile, è consigliabile usare la funzionalità di log delle query lente per identificare le query che richiedono più tempo di N secondi da eseguire. Dopo aver identificato le query dal log delle query lente, è possibile usare la diagnostica MySQL per risolvere i problemi di queste query.
Prima di iniziare a tracciare le query con esecuzione prolungata, è necessario abilitare il slow_query_log parametro usando il portale di Azure o l'interfaccia della riga di comando di Azure. Con questo parametro abilitato, è anche necessario configurare il valore del long_query_time parametro per specificare il numero di secondi che le query possono essere eseguite prima di essere identificate come query "a esecuzione lenta". Il valore predefinito del parametro è 10 secondi, ma è possibile modificare il valore per soddisfare le esigenze del contratto di servizio dell'applicazione.
Anche se il log delle query lente è uno strumento ideale per tracciare query con esecuzione prolungata, esistono alcuni scenari in cui potrebbe non essere efficace. Ad esempio, il log delle query lente:
- Influisce negativamente sulle prestazioni se il numero di query è molto elevato o se l'istruzione di query è molto grande. Modificare il valore del
long_query_timeparametro di conseguenza. - Potrebbe non essere utile se è stato abilitato anche il
log_queries_not_using_indexparametro , che specifica di registrare le query previste per recuperare tutte le righe. Le query che eseguono un'analisi completa dell'indice sfruttano un indice, ma vengono registrate perché l'indice non limita il numero di righe restituite.
Recupero di informazioni dai log
I log sono disponibili per un massimo di sette giorni dalla data di creazione. È possibile elencare e scaricare log di query lente tramite il portale di Azure o l'interfaccia della riga di comando di Azure. Nella portale di Azure passare al server, in Monitoraggio selezionare Log del server e quindi selezionare la freccia verso il basso accanto a una voce per scaricare i log associati alla data e all'ora in cui si sta esaminando.
Inoltre, se i log di query lente sono integrati con i log di Monitoraggio di Azure tramite i log di diagnostica, è possibile eseguire query in un editor per analizzarle ulteriormente:
AzureDiagnostics
| where Resource == '<your server name>'
| where Category == 'MySqlSlowLogs'
| project TimeGenerated, Resource , event_class_s, start_time_t , query_time_d, sql_text_s
| where query_time_d > 10
Nota
Per altri esempi per iniziare a diagnosticare i log di query lente tramite i log di diagnostica, vedere Analizzare i log nei log di Monitoraggio di Azure.
Lo snapshot seguente illustra una query lenta di esempio.
# Time: 2021-11-13T10:07:52.610719Z
# User@Host: root[root] @ [172.30.209.6] Id: 735026
# Query_time: 25.314811 Lock_time: 0.000000 Rows_sent: 126 Rows_examined: 443308
use employees;
SET timestamp=1596448847;
select * from titles where DATE(from_date) > DATE('1994-04-05') AND title like '%senior%';;
Si noti che la query è stata eseguita in 26 secondi, esaminata oltre 443.000 righe e ha restituito 126 righe di risultati.
In genere, è consigliabile concentrarsi sulle query con valori elevati per Query_time e Rows_examined. Tuttavia, se si notano query con un Query_time elevato, ma solo pochi Rows_examined, questo spesso indica la presenza di un collo di bottiglia della risorsa. Per questi casi, è consigliabile verificare se sono presenti limitazioni di I/O o utilizzo della CPU.
Profilatura di una query
Dopo aver identificato una query a esecuzione lenta specifica, è possibile usare il comando EXPLAIN e la profilatura per raccogliere altri dettagli.
Per controllare il piano di query, eseguire il comando seguente:
EXPLAIN <QUERY>
Nota
Per altre informazioni sull'uso delle istruzioni EXPLAIN, vedere Come usare EXPLAIN per profilare le prestazioni delle query in Database di Azure per MySQL server flessibile.
Oltre a creare un piano EXPLAIN per una query, è possibile usare il comando SHOW PROFILE, che consente di diagnosticare l'esecuzione di istruzioni eseguite all'interno della sessione corrente.
Per abilitare la profilatura e profilare una query specifica in una sessione, eseguire il set di comandi seguente:
SET profiling = 1;
<QUERY>;
SHOW PROFILES;
SHOW PROFILE FOR QUERY <X>;
Nota
La profilatura di singole query è disponibile solo in una sessione e le istruzioni cronologiche non possono essere profilate.
Esaminiamo in dettaglio l'uso di questi comandi per profilare una query. Prima di tutto, abilitare la profilatura per la sessione corrente, eseguire il SET PROFILING = 1 comando :
mysql> SET PROFILING = 1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Eseguire quindi una query non ottimale che esegue un'analisi completa della tabella:
mysql> select * from sbtest8 where c like '%99098187165%';
+----+---------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
| id | k | c | pad |
+----+---------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
| 10 | 5035785 | 81674956652-89815953173-84507133182-62502329576-99098187165-62672357237-37910808188-52047270287-89115790749-78840418590 | 91637025586-81807791530-84338237594-90990131533-07427691758 |
+----+---------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
1 row in set (27.60 sec)
Visualizzare quindi un elenco di tutti i profili di query disponibili eseguendo il SHOW PROFILES comando :
mysql> SHOW PROFILES;
+----------+-------------+----------------------------------------------------+
| Query_ID | Duration | Query |
+----------+-------------+----------------------------------------------------+
| 1 | 27.59450000 | select * from sbtest8 where c like '%99098187165%' |
+----------+-------------+----------------------------------------------------+
1 row in set, 1 warning (0.00 sec)
Infine, per visualizzare il profilo per la query 1, eseguire il SHOW PROFILE FOR QUERY 1 comando .
mysql> SHOW PROFILE FOR QUERY 1;
+----------------------+-----------+
| Status | Duration |
+----------------------+-----------+
| starting | 0.000102 |
| checking permissions | 0.000028 |
| Opening tables | 0.000033 |
| init | 0.000035 |
| System lock | 0.000018 |
| optimizing | 0.000017 |
| statistics | 0.000025 |
| preparing | 0.000019 |
| executing | 0.000011 |
| Sending data | 27.594038 |
| end | 0.000041 |
| query end | 0.000014 |
| closing tables | 0.000013 |
| freeing items | 0.000088 |
| cleaning up | 0.000020 |
+----------------------+-----------+
15 rows in set, 1 warning (0.00 sec)
Elenco delle query più usate nel server di database
Ogni volta che si esegue la risoluzione dei problemi relativi alle prestazioni delle query, è utile comprendere quali query vengono eseguite più spesso nell'istanza del server flessibile Database di Azure per MySQL. È possibile usare queste informazioni per misurare se una delle query principali richiede più tempo del solito per l'esecuzione. Inoltre, uno sviluppatore o un amministratore di database potrebbe usare queste informazioni per identificare se una query ha un aumento improvviso del numero di esecuzioni di query e della durata.
Per elencare le prime 10 query eseguite sull'istanza del server flessibile Database di Azure per MySQL, eseguire la query seguente:
SELECT digest_text AS normalized_query,
count_star AS all_occurrences,
Concat(Round(sum_timer_wait / 1000000000000, 3), ' s') AS total_time,
Concat(Round(min_timer_wait / 1000000000000, 3), ' s') AS min_time,
Concat(Round(max_timer_wait / 1000000000000, 3), ' s') AS max_time,
Concat(Round(avg_timer_wait / 1000000000000, 3), ' s') AS avg_time,
Concat(Round(sum_lock_time / 1000000000000, 3), ' s') AS total_locktime,
sum_rows_affected AS sum_rows_changed,
sum_rows_sent AS sum_rows_selected,
sum_rows_examined AS sum_rows_scanned,
sum_created_tmp_tables,
sum_select_scan,
sum_no_index_used,
sum_no_good_index_used
FROM performance_schema.events_statements_summary_by_digest
ORDER BY sum_timer_wait DESC LIMIT 10;
Nota
Usare questa query per eseguire il benchmark delle query eseguite più in alto nel server di database e determinare se è stata apportata una modifica nelle query principali o se le query esistenti nel benchmark iniziale sono aumentate nella durata di esecuzione.
Elencare le 10 query più costose in base al tempo di esecuzione totale
L'output della query seguente fornisce informazioni sulle prime 10 query in esecuzione sul server di database e sul numero di esecuzioni nel server di database. Fornisce anche altre informazioni utili, ad esempio latenze di query, tempi di blocco, numero di tabelle temporanee create come parte del runtime di query e così via. Usare questo output di query per tenere traccia delle prime query nel database e delle modifiche apportate a fattori quali latenze, che potrebbero indicare la possibilità di ottimizzare ulteriormente la query per evitare eventuali rischi futuri.
SELECT REPLACE(event_name, 'statement/sql/', '') AS statement,
count_star AS all_occurrences ,
Concat(Round(sum_timer_wait / 1000000000000, 2), ' s') AS total_latency,
Concat(Round(avg_timer_wait / 1000000000000, 2), ' s') AS avg_latency,
Concat(Round(sum_lock_time / 1000000000000, 2), ' s') AS total_lock_time ,
sum_rows_affected AS sum_rows_changed,
sum_rows_sent AS sum_rows_selected,
sum_rows_examined AS sum_rows_scanned,
sum_created_tmp_tables, sum_created_tmp_disk_tables,
IF(sum_created_tmp_tables = 0, 0, Concat( Truncate(sum_created_tmp_disk_tables /
sum_created_tmp_tables * 100, 0))) AS
tmp_disk_tables_percent,
sum_select_scan,
sum_no_index_used,
sum_no_good_index_used
FROM performance_schema.events_statements_summary_global_by_event_name
WHERE event_name LIKE 'statement/sql/%'
AND count_star > 0
ORDER BY sum_timer_wait DESC
LIMIT 10;
Monitoraggio di Garbage Collection innoDB
Quando il Garbage Collection innoDB è bloccato o ritardato, il database può sviluppare un notevole ritardo di eliminazione che può influire negativamente sull'utilizzo dell'archiviazione e sulle prestazioni delle query.
La lunghezza dell'elenco di cronologia dei segmenti di rollback di InnoDB misura il numero di record delle modifiche archiviati nel log di annullamento. Un valore HLL crescente indica che i thread di Garbage Collection di InnoDB (thread di ripulitura) non sono in linea con il carico di lavoro di scrittura o che l'eliminazione viene bloccata da una query o una transazione a esecuzione prolungata.
Un numero eccessivo di ritardi nell'operazione di Garbage Collection può avere conseguenze gravi e negative:
- Lo spazio di tabella del sistema InnoDB si espande, accelerando così la crescita del volume di archiviazione sottostante. A volte, lo spazio delle tabelle di sistema può gonfiarsi di diversi terabyte in seguito a un'eliminazione bloccata.
- I record contrassegnati dall'eliminazione non verranno rimossi in modo tempestivo. Ciò può causare l'aumento degli spazi di tabella innoDB e impedisce al motore di riutilizzare lo spazio di archiviazione occupato da questi record.
- Le prestazioni di tutte le query potrebbero peggiorare e l'utilizzo della CPU potrebbe aumentare a causa della crescita delle strutture di archiviazione InnoDB.
Di conseguenza, è importante monitorare valori, modelli e tendenze HLL.
Ricerca di valori HLL
È possibile trovare il valore HLL eseguendo il comando show engine innodb status .You can find the HLL value by running the show engine innodb status command. Il valore verrà elencato nell'output, sotto l'intestazione TRANSACTIONS:
mysql> show engine innodb status\G
*************************** 1. row ***************************
(...)
------------
TRANSACTIONS
------------
Trx id counter 52685768
Purge done for trx's n:o < 52680802 undo n:o < 0 state: running but idle
History list length 2964300
(...)
È anche possibile determinare il valore HLL eseguendo una query sulla tabella information_schema.innodb_metrics:
mysql> select count from information_schema.innodb_metrics
-> where name = 'trx_rseg_history_len';
+---------+
| count |
+---------+
| 2964300 |
+---------+
1 row in set (0.00 sec)
Interpretazione dei valori HLL
Quando si interpretano i valori HLL, prendere in considerazione le linee guida elencate nella tabella seguente:
| valore | Note |
|---|---|
| Meno di ~10.000 | Valori normali, che indicano che garbage collection non è in ritardo. |
| Tra ~10.000 e ~1.000.000 | Questi valori indicano un ritardo secondario in Garbage Collection. Tali valori potrebbero essere accettabili se rimangono stabili e non aumentano. |
| Maggiore di ~1.000.000 | Questi valori devono essere esaminati e potrebbero richiedere azioni correttive |
Indirizzamento di valori HLL eccessivi
Se HLL mostra picchi di grandi dimensioni o presenta un modello di crescita periodica, esaminare immediatamente le query e le transazioni in esecuzione nell'istanza del server flessibile Database di Azure per MySQL. È quindi possibile risolvere eventuali problemi del carico di lavoro che potrebbero impedire lo stato di avanzamento del processo di Garbage Collection. Anche se non è previsto che il database sia privo di ritardo di eliminazione, non è necessario lasciare che il ritardo cresce in modo noncontrollo.
Per ottenere informazioni sulle transazioni dalla information_schema.innodb_trx tabella, ad esempio, eseguire i comandi seguenti:
select * from information_schema.innodb_trx
order by trx_started asc\G
I dettagli nella trx_started colonna consentono di calcolare l'età delle transazioni.
mysql> select * from information_schema.innodb_trx
-> order by trx_started asc\G
*************************** 1. row ***************************
trx_id: 8150550
trx_state: RUNNING
trx_started: 2021-11-13 20:50:11
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 0
trx_mysql_thread_id: 19
trx_query: select * from employees where DATE(hire_date) > DATE('1998-04-05') AND first_name like '%geo%';
(…)
Per informazioni sulle sessioni di database correnti, incluso il tempo trascorso nello stato corrente della sessione, controllare la information_schema.processlist tabella. L'output seguente, ad esempio, mostra una sessione che esegue attivamente una query per gli ultimi 1462 secondi:
mysql> select user, host, db, command, time, info
-> from information_schema.processlist
-> order by time desc\G
*************************** 1. row ***************************
user: test
host: 172.31.19.159:38004
db: employees
command: Query
time: 1462
info: select * from employees where DATE(hire_date) > DATE('1998-04-05') AND first_name like '%geo%';
(...)
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 e memoria aggiuntiva per supportare il carico di lavoro.
Evitare transazioni di grandi dimensioni o con esecuzione prolungata suddividendole in transazioni più piccole.
Configurare innodb_purge_threads in base al carico di lavoro per migliorare l'efficienza per le operazioni di eliminazione in background.
Nota
Testare le modifiche apportate a questa variabile server per ogni ambiente per misurare la modifica del comportamento del motore.
Usare gli avvisi in "Percentuale CPU host", "Percentuale memoria host" e "Total Connessione ions" 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.