Risoluzione dei problemi delle prestazioni delle query per i database Aurora MySQL - Amazon Aurora
Comprendere il tempo impiegato dalle interrogazioniRevisione delle impostazioni dello strumento di ottimizzazione delle query

Le traduzioni sono generate tramite traduzione automatica. In caso di conflitto tra il contenuto di una traduzione e la versione originale in Inglese, quest'ultima prevarrà.

Risoluzione dei problemi delle prestazioni delle query per i database Aurora MySQL

MySQL fornisce il controllo di ottimizzazione delle query tramite variabili di sistema che influiscono sul modo in cui vengono valutati i piani di query, ottimizzazioni commutabili, suggerimenti sullo strumento di ottimizzazione e sugli indici e il modello di costo dello strumento di ottimizzazione. Questi punti dati possono essere utili non solo per confrontare diversi ambienti MySQL, ma anche per confrontare i piani di esecuzione delle query precedenti con i piani di esecuzione correnti e per comprendere l’esecuzione complessiva di una query MySQL in qualsiasi momento.

Le prestazioni delle query dipendono da molti fattori, tra cui il piano di esecuzione, lo schema e le dimensioni della tabella, le statistiche, le risorse, gli indici e la configurazione dei parametri. L’ottimizzazione delle query richiede l’identificazione dei punti critici e l’ottimizzazione del percorso di esecuzione.

  • Trovare il piano di esecuzione per la query e verifica se la query utilizza gli indici appropriati. È possibile ottimizzare la query utilizzando EXPLAIN e rivedendo i dettagli di ciascun piano.

  • Aurora MySQL versione 3 (compatibile con MySQL 8.0 Community Edition) utilizza un’istruzione EXPLAIN ANALYZE. L’istruzione EXPLAIN ANALYZE è uno strumento di profiling che mostra il punto della query in cui MySQL trascorre tempo e perché. Con EXPLAIN ANALYZE, Aurora MySQL pianifica, prepara ed esegue la query contando le righe e misurando il tempo impiegato in vari punti del piano di esecuzione. Al termine della query, EXPLAIN ANALYZE stampa il piano e le relative misurazioni anziché il risultato della query.

  • Mantenere aggiornate le statistiche dello schema utilizzando l’istruzione ANALYZE. Lo strumento di ottimizzazione delle query a volte può scegliere piani di esecuzione scadenti a causa di statistiche obsolete. Ciò può comportare una riduzione delle prestazioni di una query a causa di stime imprecise della cardinalità sia delle tabelle che degli indici. La colonna last_update della tabella innodb_table_stats mostra l’ultima volta che le statistiche dello schema sono state aggiornate, il che è un buon indicatore di “stallo”.

  • Possono verificarsi altri problemi, come la distorsione della distribuzione dei dati, che non vengono presi in considerazione per la cardinalità della tabella. Per ulteriori informazioni, consulta Estimating ANALYZE TABLE complexity for InnoDB tables e Histogram statistics in MySQL nella documentazione MySQL.

Comprendere il tempo impiegato dalle interrogazioni

Di seguito sono riportati i modi per determinare il tempo impiegato dalle query:

Profiling

Per impostazione predefinita, il profiling è disabilitato. Abilita il profiling, quindi esegui la query lenta e rivedi il profilo.

SET profiling = 1;
Run your query.
SHOW PROFILE;

  1. Identifica la fase in cui viene impiegato più tempo. In base agli stati generali dei thread presenti nella documentazione MySQL, la lettura e l’elaborazione delle righe di SELECT un’istruzione sono spesso lo stato di esecuzione più lungo per tutta la durata di una determinata query. È possibile utilizzare l’istruzione EXPLAIN per capire come MySQL esegue questa query.

  2. Esamina il log delle query lente per valutare rows_examined e rows_sent per assicurarti che il carico di lavoro sia simile in ogni ambiente. Per ulteriori informazioni, consulta Registrazione di log per i database Aurora MySQL.

  3. Esegui il comando seguente per le tabelle che fanno parte della query identificata:

    SHOW TABLE STATUS\G;

  4. Acquisisci i seguenti output prima e dopo l’esecuzione della query in ogni ambiente:

    SHOW GLOBAL STATUS;

  5. Esegui i seguenti comandi in ogni ambiente per vedere se ci sono altre query/sessioni che influenzano le prestazioni di questa query di esempio.

    SHOW FULL PROCESSLIST;

SHOW ENGINE INNODB STATUS\G;

    A volte, quando le risorse del server sono occupate, ciò influisce su tutte le altre operazioni sul server, comprese le query. È inoltre possibile acquisire informazioni periodicamente quando vengono eseguite delle query o impostare un processo cron per acquisire informazioni a intervalli utili.

Performance Schema

Lo schema delle prestazioni fornisce informazioni utili sulle prestazioni di runtime del server, con un impatto minimo su tali prestazioni. Questo è diverso da information_schema, che fornisce informazioni sullo schema sull’istanza database. Per ulteriori informazioni, consulta Panoramica dello schema di prestazioni per Approfondimenti sulle prestazioni su Aurora MySQL.

Traccia dell’ottimizzatore delle query

Per capire perché è stato scelto un particolare piano di query per l’esecuzione, puoi configurare optimizer_trace per l’accesso allo strumento di ottimizzazione delle query MySQL.

Esegui una traccia dell’ottimizzatore per mostrare informazioni dettagliate su tutti i percorsi disponibili per l’ottimizzatore e sulla sua scelta.

SET SESSION OPTIMIZER_TRACE="enabled=on"; 
SET optimizer_trace_offset=-5, optimizer_trace_limit=5;

-- Run your query.
SELECT * FROM table WHERE x = 1 AND y = 'A';

-- After the query completes:
SELECT * FROM information_schema.OPTIMIZER_TRACE;
SET SESSION OPTIMIZER_TRACE="enabled=off";

Revisione delle impostazioni dello strumento di ottimizzazione delle query

Aurora MySQL versione 3 (compatibile con MySQL 8.0 Community Edition) presenta molte modifiche relative allo strumento di ottimizzazione rispetto ad Aurora MySQL versione 2 (compatibile con MySQL 5.7 Community Edition). In caso di valori personalizzati per optimizer_switch, ti consigliamo di esaminare le differenze tra le impostazioni predefinite e di impostare i valori optimizer_switch più adatti al tuo carico di lavoro. Consigliamo inoltre di testare le opzioni disponibili per Aurora MySQL versione 3 per esaminare le prestazioni delle query.

Nota

Aurora MySQL versione 3 utilizza il valore predefinito 20 della versione Community per il parametro innodb_stats_persistent_sample_pages.

Puoi utilizzare il seguente comando per visualizzare i valori optimizer_switch:

SELECT @@optimizer_switch\G;

La tabella seguente mostra i valori predefiniti optimizer_switch per Aurora MySQL versioni 2 e 3.

Impostazione Aurora MySQL versione 2 Aurora MySQL versione 3
batched_key_access off off
block_nested_loop on on
condition_fanout_filter on on
derived_condition_pushdown – on
derived_merge on on
duplicateweedout on on
engine_condition_pushdown on on
firstmatch on on
hash_join off on
hash_join_cost_based on –
hypergraph_optimizer – off
index_condition_pushdown on on
index_merge on on
index_merge_intersection on on
index_merge_sort_union on on
index_merge_union on on
loosescan on on
materialization on on
mrr on on
mrr_cost_based on on
prefer_ordering_index on on
semijoin on on
skip_scan – on
subquery_materialization_cost_based on on
subquery_to_derived – off
use_index_extensions on on
use_invisible_indexes – off

Per ulteriori informazioni, consulta Switchable optimizations (MySQL 5.7) e Switchable optimizations (MySQL 8.0) nella documentazione MySQL.