Risoluzione dei problemi delle prestazioni delle query per i database Aurora MySQL - Amazon Aurora
Comprendere il tempo impiegato dalle interrogazioniRevisione delle impostazioni dell'ottimizzatore 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 dell'ottimizzatore di query tramite variabili di sistema che influiscono sul modo in cui vengono valutati i piani di query, ottimizzazioni commutabili, suggerimenti sull'ottimizzatore e sull'indice e il modello di costo dell'ottimizzatore. 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 attuali 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.

  • Trova 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'EXPLAIN ANALYZEistruzione è uno strumento di profilazione che mostra dove MySQL dedica il tempo alla tua query e perché. ConEXPLAIN 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 dell'interrogazione.

  • Mantieni aggiornate le statistiche dello schema utilizzando l'ANALYZEistruzione. L'ottimizzatore di 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 last_update colonna 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, vedere Stima della complessità di ANALYZE TABLE per le tabelle InnoDB e le statistiche dell'istogramma 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, la profilazione è disabilitata. Abilita la profilazione, 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 nella documentazione di MySQL, la lettura e l'elaborazione delle righe per SELECT un'istruzione è spesso lo stato di esecuzione più lungo durante la durata di una determinata query. È possibile utilizzare l'EXPLAINistruzione per capire come MySQL esegue questa query.

  2. Esamina il log delle query lente rows_sent per valutare rows_examined e assicurarti che il carico di lavoro sia simile in ogni ambiente. Per ulteriori informazioni, consulta Registrazione 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 cron processo 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 dainformation_schema, che fornisce informazioni sullo schema sull'istanza DB. Per ulteriori informazioni, consulta Panoramica dello schema delle prestazioni per Performance Insights su Aurora MySQL, Amazon RDS MariaDB o MySQL.

Traccia dell'ottimizzatore di query

Per capire perché è stato scelto un particolare piano di query per l'esecuzione, puoi configurare l'accesso optimizer_trace all'ottimizzatore di query MySQL.

Esegui una traccia dell'ottimizzatore per mostrare informazioni complete 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 dell'ottimizzatore delle query

Aurora MySQL versione 3 (compatibile con MySQL 8.0 Community Edition) presenta molte modifiche relative all'ottimizzatore rispetto alla versione 2 di Aurora MySQL (compatibile con MySQL 5.7 Community Edition). Se disponi di valori personalizzati per il, ti consigliamo di esaminare le differenze tra le impostazioni predefinite optimizer_switch e di impostare i valori più adatti al tuo carico di lavoro. optimizer_switch Si consiglia 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 della community di 20 per il parametro innodb_stats_persistent_sample_pages.

È possibile utilizzare il optimizer_switch seguente comando per mostrare i valori:

SELECT @@optimizer_switch\G;

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

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 Ottimizzazioni commutabili (MySQL 5.7) e Ottimizzazioni commutabili (MySQL 8.0) nella documentazione di MySQL.