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
Il server singolo del Database di Azure per MySQL è in fase di ritiro. È consigliabile eseguire l'aggiornamento al server flessibile del Database di Azure per MySQL. Per altre informazioni sulla migrazione a Database di Azure per MySQL - Server flessibile, vedere Cosa succede 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?
- Un set di query specifico?
- Una query specifica?
Tenere inoltre presente che eventuali modifiche recenti alla struttura o ai dati sottostanti delle tabelle su cui si sta eseguendo la query possono influire sulle prestazioni.
Abilitazione della funzionalità di registrazione
Prima di analizzare le singole query, è necessario definire i relativi benchmark. 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 con esecuzione lenta, è possibile usare la diagnostica MySQL per risolvere i problemi di queste query.
Prima di iniziare a tracciare query con esecuzione prolungata, è necessario abilitare il parametro slow_query_log usando il portale di Azure o l'interfaccia della riga di comando di Azure. Con questo parametro abilitato, è anche necessario configurare il valore del parametro long_query_time per specificare il numero di secondi durante i quali le query possono essere eseguite prima di essere identificate come query "con esecuzione lenta". Il valore predefinito del parametro è 10 secondi, ma è possibile modificarlo per soddisfare le esigenze del contratto di servizio dell'applicazione.
Nonostante il log delle query lente sia 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 può:
- influire negativamente sulle prestazioni se il numero di query è molto elevato o se l'istruzione di esecuzione della query è molto grande. Modificare il valore del parametro
long_query_timedi conseguenza. - Potrebbe non essere utile se è stato abilitato anche il parametro
log_queries_not_using_index, 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é questo non limita il numero di righe restituite.
Recupero delle informazioni dai log
I log sono disponibili per un massimo di sette giorni dalla data di creazione. È possibile elencare e scaricare i log di query lente usando il portale o l'interfaccia della riga di comando di Azure. Nel portale di Azure passare al server, in Monitoraggio, selezionare Log del servere poi selezionare la freccia verso il basso accanto a una voce per scaricare i log associati alla data e all'ora in cui si sta effettuando l’analisi.
Inoltre, se i log delle query lente sono integrati con i log di Monitoraggio di Azure tramite i log di diagnostica, è possibile eseguire le 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 ottenere altri esempi su come iniziare a diagnosticare i log delle 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 per 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 soltanto pochi Rows_examined, questo spesso indica la presenza di un rallentamento della risorsa. In questi casi, è consigliabile verificare i rallentamenti sono nell’IO o nell’utilizzo della CPU.
Profilatura di una query
Dopo aver identificato una query con esecuzione lenta specifica, è possibile usare il comando EXPLAIN e la profilatura per acquisire ulteriori 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 nel dettaglio l'uso di questi comandi per profilare una query. Prima di tutto, abilitare la profilatura per la sessione corrente eseguendo il comando SET PROFILING = 1:
mysql> SET PROFILING = 1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Eseguire quindi una query subottimale che esegue una scansione 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)
Poi visualizzare un elenco di tutti i profili di query disponibili eseguendo il comando SHOW PROFILES:
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 della query 1, eseguire il comando SHOW PROFILE FOR QUERY 1.
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 del 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 rilevare 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 rilevare se una query presenta un aumento improvviso del numero di esecuzioni 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 ottenere il benchmark delle query eseguite più in alto nel server del database e determinare se è stata apportata una modifica nelle query principali o se la durata dell’esecuzione delle query esistenti nel benchmark è aumentata.
Elencare le 10 query più rilevanti in termini di tempo di esecuzione totale
L'output della query seguente fornisce informazioni sulle prime 10 query in esecuzione sul server del database e sul numero di esecuzioni nel server del database. Fornisce anche altre informazioni utili, come ad esempio latenze delle query, periodi di blocco, numero di tabelle temporanee create nel contesto del runtime delle query e così via. Usare l’output della query per tenere traccia delle query principali nel database e delle modifiche apportate a fattori come le latenze, che potrebbero indicare la possibilità di ritoccare 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 del Garbage Collection InnoDB
Quando il Garbage Collection InnoDB è bloccato o rallentato, 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 della cronologia dei segmenti di rollback di InnoDB misura il numero di record delle modifiche (HLL) archiviati nel registro degli annullamenti. Un valore HLL crescente indica che i thread di Garbage Collection di InnoDB (thread di eliminazione) non sono in linea con il carico di lavoro in scrittura o che l'eliminazione viene bloccata da una query o da una transazione con esecuzione prolungata.
Un numero eccessivo di ritardi nell'operazione di Garbage Collection può avere conseguenze gravi e negative:
- Lo spazio della tabella del sistema InnoDB si espande, accelerando così la crescita del volume di archiviazione sottostante. A volte, lo spazio delle tabelle di sistema può aumentare 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 delle tabelle InnoDB e impedire al motore di riutilizzare lo spazio di archiviazione occupato da tali 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 dei valori HLL
È possibile trovare il valore HLL eseguendo il comando "Mostra stato mototre InnoDB". 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 |
|---|---|
| Minore 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 correzioni |
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. Benché non sia previsto che il database sia privo di ritardo di eliminazione, il ritardo non deve essere lasciato aumentare in modo incontrollato.
Per ottenere informazioni sulle transazioni dalla tabella information_schema.innodb_trx, ad esempio, eseguire i comandi seguenti:
select * from information_schema.innodb_trx
order by trx_started asc\G
I dettagli nella colonna trx_started 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 tabella information_schema.processlist. 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
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 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 ciascun ambiente per misurare la variazione del comportamento del motore.
Usare gli avvisi in "Percentuale CPU host" e "Connessioni totali" 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.
Passaggi successivi
Per trovare risposte di peer alle domande più importanti o per pubblicare o rispondere a una domanda, visitare Stack Overflow.