Perché la query viene eseguita lentamente in Amazon RDS per MySQL?

Breve descrizione

Per migliorare le prestazioni delle query, considerare i seguenti fattori:

• Utilizzo delle risorse (ad esempio CPU, memoria e storage)
• Analisi del carico di lavoro
• Ottimizzazione e monitoraggio delle query

Risoluzione

Utilizzo delle risorse (ad esempio CPU, memoria e storage)

Per comprendere la causa principale di eventuali problemi di prestazioni del database, controllare tutte le risorse a livello di server utilizzate dall'istanza. È possibile monitorare il carico di lavoro e il rilevamento di quando le prestazioni della query erano normali rispetto a quando la query ha iniziato a richiedere troppo tempo per essere eseguita.

Utilizza i parametri di Amazon CloudWatch per monitorare queste risorse in un periodo di tempo che include giorni in cui le prestazioni sono state considerate normali. È inoltre possibile visualizzare i parametri delle prestazioni nella console di Amazon RDS per monitorare le prestazioni del database.

È inoltre possibile controllare lo stato dell'istanza per identificare eventuali altri processi attivi o pianificati che potrebbero influire sulle prestazioni del database. Nella console di Amazon RDS, controlla gli eventi che si sono verificati mentre il tuo database non funzionava correttamente.

Analisi del carico di lavoro

Per analizzare il carico di lavoro che contribuisce al consumo di risorse, utilizza Performance Insights. Performance Insights fornirà un'analisi grafica di tutte le query e di eventuali attese che contribuiscono a un maggiore consumo di risorse.

Performance Insights utilizza il carico di lavoro come parametro principale anziché utilizzare il numero di vCPU per un'istanza. Se il carico di lavoro corrente supera il limite di vCPU, il server è sovraccarico. Se il server è sovraccarico, controlla le query che contribuiscono al carico di lavoro e identifica i modi per ottimizzare le query. Quindi, valuta la possibilità di modificare la classe di istanza.

Il carico di lavoro di Performance Insights può anche essere suddiviso in eventi di attesa. Analizza le attese che consumano più risorse suddividendone il carico del database in base al numero di eventi di attesa. Le bande colorate più spesse nel grafico di caricamento indicano i tipi di attesa che contribuiscono maggiormente al carico di lavoro. Per ulteriori informazioni, consulta Monitoraggio del carico del database con Performance Insights su Amazon RDS.

È inoltre possibile utilizzare il registro delle query lente (abilitato nel gruppo di parametri personalizzato) per identificare le query a esecuzione lenta.

Quindi, puoi utilizzare li parametri di Amazon CloudWatch per verificare se la quantità di lavoro svolto sull'istanza è aumentata. Ad esempio:

• Connessioni al database: il numero di sessioni client connesse all'istanza database.
• Velocità effettiva di ricezione di rete (MB/secondo): la velocità del traffico di rete da e verso l'istanza database.
• Velocità effettiva di scrittura e lettura: il numero medio di megabyte letti o scritti su disco al secondo.
• Latenza di scrittura e lettura: il tempo medio per un'operazione di lettura o scrittura in millisecondi.
• IOPS (lettura e scrittura): il numero medio di operazioni di lettura o scrittura su disco al secondo.
• Spazio di archiviazione libero (MB): la quantità di spazio su disco non attualmente utilizzata dall'istanza DB.

I parametri di latenza indicano il tempo necessario per completare l'operazione di I/O del disco in lettura o scrittura. La correlazione dei parametri di latenza con l'aumento delle connessioni al database o con i parametri della velocità effettiva potrebbe indicare il carico di lavoro come motivo dell'esecuzione lenta delle query. Per ulteriori informazioni sull'identificazione dei fattori di utilizzo, consulta Come si visualizza l'utilizzo dello storage in un'istanza database di Amazon RDS che esegue MySQL?

È inoltre possibile utilizzare il monitoraggio avanzato per recuperare l'elenco dei sistemi operativi coinvolti nel carico di lavoro e i parametri di sistema sottostanti. Per impostazione predefinita, l'intervallo di monitoraggio per il monitoraggio avanzato è di 60 secondi. È consigliabile impostare questo intervallo su intervalli di 1-5 secondi per punti dati più granulari.

Ottimizzazione delle query

Dopo che la query a esecuzione prolungata è stata identificata da un registro di query lento o da Performance Insights, prendere in considerazione i modi per migliorare le prestazioni delle query. Per ottimizzare una query, prendere in considerazione i seguenti approcci:

• Per trovare gli stati in cui viene trascorso più tempo, profila le query più lente. Per ulteriori informazioni, vedere l'istruzione SHOW PROFILE sul sito Web MySQL.
• Eseguire il comando SHOW FULL PROCESSLIST insieme al monitoraggio avanzato. Se utilizzati insieme, è possibile rivedere l'elenco delle operazioni attualmente eseguite sul server di database.
• Utilizzare il comando SHOW ENGINE INNODB STATUS per ottenere informazioni sull'elaborazione delle transazioni, sulle attese e sui deadlock.
• Trova eventuali query di blocco e risolvi il blocco. Per ulteriori informazioni, consulta Perché una query sulla mia istanza database Amazon RDS per MySQL è stata bloccata quando non sono presenti altre sessioni attive?
• Pubblica log MySQL su Amazon CloudWatch. I registri vengono ruotati ogni ora per mantenere il 2% della soglia di spazio di archiviazione allocata. Vengono quindi eliminati se hanno più di due settimane o se la loro dimensione combinata supera la soglia del 2%.
• Imposta un allarme Amazon CloudWatch in modo da poter monitorare l'utilizzo delle risorse e ricevere avvisi ogni volta che vengono superate le soglie.
• Individuare il piano di esecuzione per la query e verificare se la query utilizza gli indici appropriati. È possibile ottimizzare la query utilizzando il piano EXPLAIN e rivedere i dettagli su come MySQL esegue la query.
• Mantieni aggiornate le statistiche delle query con l'istruzione della tabella ANALYZE. A volte gli ottimizzatori di query possono scegliere piani di esecuzione scadenti a causa di statistiche obsolete. Ciò può comportare prestazioni insoddisfacenti di una query a causa di stime di cardinalità imprecise sia della tabella che degli indici.
• MySQL 8.0 ora utilizza un'istruzione EXPLAIN ANALYZE. L'istruzione EXPLAIN ANALYZE è uno strumento di profilazione per le tue query che mostra dove MySQL trascorre il tempo sulla tua query e perché. Con EXPLAIN ANALYZE, MySQL pianifica, strumentalizza ed esegue la query contando le righe e misurando il tempo trascorso in vari punti del piano di esecuzione. Al termine della query, EXPLAIN ANALYZE stamperà il piano e le relative misurazioni anziché il risultato della query.
• In MySQL versione 8, tieni presente che le attese di blocco sono elencate nello schema delle prestazioni della tabella data_lock_waits. Ad esempio:

SELECT
  r.trx_id waiting_trx_id,
  r.trx_mysql_thread_id waiting_thread,
  r.trx_query waiting_query,
  b.trx_id blocking_trx_id,
  b.trx_mysql_thread_id blocking_thread,
  b.trx_query blocking_query
FROM       performance_schema.data_lock_waits w
INNER JOIN information_schema.innodb_trx b
  ON b.trx_id = w.blocking_engine_transaction_id
INNER JOIN information_schema.innodb_trx r
  ON r.trx_id = w.requesting_engine_transaction_id;

Per ulteriori informazioni, vedere Utilizzo delle transazioni InnoDB e informazioni di blocco sul sito Web MySQL.

---

Informazioni correlate

Ottimizzazione di Amazon RDS for MySQL con Performance Insights

In che modo è possibile identificare e risolvere il problema dell'elevato utilizzo della CPU in relazione alle istanze Amazon RDS for MySQL, MariaDB o Aurora per MySQL?