Come posso risolvere i problemi relativi a una query lenta e migliorarne le prestazioni in Amazon RDS per MySQL?

Risoluzione

Monitora le prestazioni delle risorse e del database

Utilizza Amazon CloudWatch per determinare quando una query aumenta l'utilizzo delle risorse.

Monitora le seguenti metriche di CloudWatch:

• DatabaseConnections
• NetworkReceiveThroughput
• WriteThroughput and ReadThroughput
• WriteLatency e ReadLatency
• WriteIOPS e ReadIOPS
• FreeStorageSpace
• BurstBalance

Utilizza la console Amazon RDS per determinare quando la query riduce le prestazioni del database. Inoltre, controllate lo stato dell'istanza database RDS per identificare i processi attivi o pianificati che potrebbero influire sulle prestazioni del database. Controlla gli eventi di Amazon RDS che si verificano quando le prestazioni del database sono scarse.

Utilizza Monitoraggio avanzato per visualizzare l'elenco dei sistemi operativi nel carico di lavoro e nelle metriche di sistema. Per impostazione predefinita, l'intervallo di Monitoraggio avanzato è 60 secondi. È consigliabile impostare un intervallo di 1-5 secondi per punti dati più dettagliati.

Verifica il carico di lavoro che contribuisce all'utilizzo delle risorse

Dopo aver determinato i momenti in cui la query aumenta l'utilizzo delle risorse o riduce le prestazioni, attiva Approfondimenti sulle prestazioni per identificare le query da ottimizzare.

Importante: Approfondimenti sulle prestazioni giungerà al termine del suo ciclo di vita il 30 novembre 2025. Entro tale data puoi passare alla modalità Avanzata di Database Insights. Se non esegui l'aggiornamento, i cluster di database che utilizzano Approfondimenti sulle prestazioni passeranno automaticamente alla modalità Standard di Database Insights. Solo la modalità Avanzata di Database Insights supporta i piani di esecuzione e l'analisi on demand. Se i cluster dovessero passare automaticamente alla modalità Standard, potresti non essere in grado di utilizzare queste funzionalità sulla console. Per attivare la modalità Avanzata, consulta Attivazione della modalità avanzata di Database Insights per Amazon RDS e Attivazione della modalità avanzata di Database Insights per Amazon Aurora.

Per controllare il carico di lavoro, completa i seguenti passaggi:

1. Accedi al grafico di carico del database nella dashboard di Approfondimenti delle prestazioni.
2. Controlla la colonna Carico per attese (AAS) per determinare il carico di lavoro che utilizza il maggior numero di eventi di attesa nel momento in cui l'utilizzo delle risorse aumenta o la query riduce le prestazioni.
   Nota: le bande di colore più grandi del grafico del carico mostrano i tipi di attesa che contribuiscono maggiormente al carico di lavoro. Se il carico di lavoro supera costantemente la vCPU massima, modifica la classe di istanza.
3. Nella scheda Top SQL, cerca le singole query che contribuiscono al carico di lavoro.

Per identificare le query lente, puoi anche attivare slow_query_log nel gruppo di parametri e pubblicare i log su CloudWatch Logs.

Migliora le prestazioni della query

Utilizza l'istruzione EXPLAIN

Controlla il piano operativo della query per determinare se utilizza gli indici appropriati. Utilizza EXPLAIN per ottimizzare la query e visualizzare i dettagli su come MySQL la esegue. Per ulteriori informazioni, consulta Optimizing Queries with EXPLAIN (Ottimizzazione delle query con sul EXPLAIN) sito web MySQL.

Per evitare scansioni complete della tabella, esegui l'istruzione EXPLAIN per determinare se la query utilizza un indice. Nell'output di EXPLAIN, controlla i nomi delle tabelle, le chiavi utilizzate e il numero di righe scansionate per la query. Per ulteriori informazioni, consulta EXPLAIN statement (istruzione EXPLAIN) sul sito web MySQL.

Se l'output non mostra chiavi in uso, crea un indice sulle colonne della clausola WHERE. Se la tabella ha l'indicizzazione richiesta, verifica che le statistiche della tabella siano aggiornate. Quando le statistiche sono aggiornate, l'ottimizzatore di query utilizza gli indici più selettivi con la cardinalità corretta. Di conseguenza, le prestazioni della query migliorano. Per ulteriori informazioni, consulta The INFORMATION_SCHEMA STATISTICS Table (Tabella INFORMATION_SCHEMA STATISTICS) sul sito web MySQL.

Utilizza ANALYZE TABLE

Utilizza ANALYZE TABLE per mantenere aggiornate le statistiche delle query. Per ulteriori informazioni, consulta la pagina ANALYZE TABLE statement (Istruzione ANALYZE TABLE) sul sito web MySQL.

Utilizza EXPLAIN ANALYZE

Per MySQL 8.0, utilizza EXPLAIN ANALYZE. L'istruzione EXPLAIN ANALYZE mostra dove MySQL alloca i tempi a una query e perché viene allocato quel tempo. Al completamento della query, **EXPLAIN ANALYZE ** stampa il piano e le relative misurazioni. Per ulteriori informazioni, consulta Obtaining Information with EXPLAIN ANALYZE (Informazioni fornite da EXPLAIN ANALYZE) sul sito web MySQL.

Controlla la lunghezza dell'elenco cronologico

InnoDB utilizza il controllo della simultaneità multi-versione (MVCC). MVCC conserva più copie dello stesso record per preservare la coerenza della lettura. La lunghezza dell'elenco cronologico è il numero totale di log di annullamento che contengono modifiche nell'elenco cronologico. Quando è presente una transazione di lunga durata che scrive o legge dati, la lunghezza dell'elenco cronologico aumenta fino al completamento o al rollback della transazione. Inoltre, la lunghezza dell'elenco cronologico aumenta quando altre transazioni modificano le tabelle utilizzate dalla transazione di lunga durata.

È consigliabile evitare transazioni aperte o di lunga durata sul database. Salva puttosto i dati in piccoli batch.Tuttavia, se il carico di lavoro richiede più transazioni aperte o di lunga durata, aspettati una lunga cronologia dell'elenco nel database.

Se non monitori la lunghezza dell'elenco cronologico, le prestazioni possono diminuire nel tempo. Una lunghezza elevata dell'elenco cronologico può anche causare un utilizzo elevato delle risorse, prestazioni dell'istruzione SELECT lente e incoerenti e un aumento dello spazio di archiviazione.

Nota: le transazioni di lunga durata non sono l'unica causa dei picchi di lunghezza degli elenchi cronologici. Se i thread di eliminazione non corrispondono alle modifiche nel database, la lunghezza dell'elenco cronologico rimane elevata. In casi estremi, puoi anche subire un'interruzione del database.

Per controllare la lunghezza dell'elenco cronologico, esegui questo comando:

SHOW ENGINE INNODB STATUS;

Nota: l'istruzione SHOW ENGINE INNODB STATUS ottiene informazioni sull'elaborazione delle transazioni, sugli eventi di attesa e sui deadlock. Per ulteriori informazioni, consulta la pagina SHOW ENGINE statement (Istruzione SHOW ENGINE) sul sito web MySQL.

Esempio di output:

\------------ TRANSACTIONS ------------Trx id counter 26368570695  
Purge done for trx's n:o < 26168770192 undo n:o < 0 state: running but idle History list length 1839

Per utilizzare Approfondimenti sulle prestazioni per controllare la lunghezza dell'elenco cronologico, completa i seguenti passaggi:

1. Apri la console Amazon RDS.
2. Nel pannello di navigazione, scegli Approfondimenti sulle prestazioni, quindi seleziona il database per cui desideri visualizzare le metriche.
3. Scegli la scheda Parametri.
4. Nel menu Dashboard dei parametri, scegli Pannelli di controllo personalizzati.
5. Scegli Aggiungi widget, quindi cerca e seleziona la metrica Trx Rseg History Len.
6. Scegli Aggiungi widget.

Se le scritture DML fanno aumentare la lunghezza dell'elenco cronologico, chiedi all'amministratore del database di terminare le istruzioni di scrittura.

Nota: il ripristino degli aggiornamenti per la transazione interrotta richiede molto tempo.

Risolvi le query bloccate

In MySQL 8.0, puoi trovare le attese del blocco nello schema delle prestazioni della tabella data_lock_waits. Per ulteriori informazioni, consulta Using InnoDB transaction and locking information (Utilizzo delle informazioni sulle transazioni e sui blocchi di InnoDB) sul sito web MySQL.

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, consulta Perché una query sulla mia istanza database Amazon RDS per MySQL è stata bloccata se non c'erano altre sessioni attive?

Utilizza l'istruzione SHOW PROFILE

Crea un profilo delle query più lente per individuare lo stato nel quale la sessione trascorre più tempo. Per ulteriori informazioni, consulta SHOW PROFILE statement (Istruzione SHOW PROFILE) sul sito web MySQL.

Utilizza l'istruzione SHOW FULL PROCESSLIST

Esegui l'istruzione SHOW FULL PROCESSLIST e utilizza Monitoraggio avanzato per esaminare l'elenco delle operazioni attualmente eseguite sul server di database. Per ulteriori informazioni, consulta la pagina SHOW PROCESSLIST Statement (Istruzione SHOW PROCESSLIST) sul sito web MySQL.

Informazioni correlate

Come posso risolvere i problemi relativi a un'istanza RDS per MySQL o MariaDB che indica lo spazio di archiviazione pieno?