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

Breve descrizione

In Amazon RDS, i seguenti problemi possono rallentare le prestazioni delle query:

• Problemi relativi al carico di lavoro e all'utilizzo delle risorse, ad esempio indicizzazione inadeguata e uso inefficiente del pool di buffer
• Piano di esecuzione delle query inefficiente
• Conflitto di risorse
• Transazioni bloccanti

Per risolvere questi problemi, identifica i colli di bottiglia nelle prestazioni consultando le metriche di Amazon CloudWatch, Performance Insights, Database Insights e Monitoraggio avanzato. Quindi elimina il collo di bottiglia e ottimizza le prestazioni delle query.

Risoluzione

Importante: Performance Insights giungerà al termine del suo ciclo di vita il 30 giugno 2026. Entro tale data puoi passare alla modalità Avanzata di Database Insights. Se non esegui l'aggiornamento, i cluster di database che utilizzano Performance Insights 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.

Nota: se ricevi errori quando esegui i comandi dell'Interfaccia della linea di comando AWS (AWS CLI), consulta Risoluzione degli errori per AWS CLI. Inoltre, assicurati di utilizzare la versione più recente di AWS CLI.

Monitora le prestazioni delle risorse e del database

Per risolvere i problemi relativi alle prestazioni delle query, determina la causa del problema consultando le metriche di Amazon CloudWatch. Per determinare quando una query aumenta l'utilizzo di una risorsa specifica o riduce le prestazioni del database, monitora le seguenti metriche utilizzando la console CloudWatch o AWS CLI:

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

Se le prestazioni del database sono insoddisfacenti, controlla lo stato dell'istanza database RDS per i processi attivi o pianificati che potrebbero influire sulle prestazioni. Inoltre, controlla gli eventi di Amazon RDS per individuare quelli che possono influire sulle prestazioni del database.

Esamina il carico di lavoro e l'utilizzo delle risorse

Se le prestazioni delle query sono lente, esamina le altre query nel carico di lavoro per capire se influiscono sulle prestazioni delle query. Per identificare le query da ottimizzare, puoi attivare la modalità Avanzata di Database Insights per Amazon RDS o Amazon Aurora.

Se si riavvia, l'istanza database potrebbe perdere i dati memorizzati nella cache e rallentare le prestazioni delle query. Per evitare il problema del cold cache, configura i seguenti parametri per accelerare il riscaldamento del pool di buffer dopo un riavvio:

• innodb_buffer_pool_dump_at_shutdown
• innodb_buffer_pool_load_at_startup
• innodb_buffer_pool_dump_pct

Per ottimizzare le prestazioni delle query, è consigliabile monitorare quanto l'istanza database utilizza il pool di buffer di InnoDB. Per ulteriori informazioni, consulta Buffer pool (Pool di buffer) sul sito web MySQL. Per monitorare lo stato del pool di buffer di InnoDB, esamina i seguenti contatori del database di Performance Insights:

• Per il numero di richieste di lettura logiche, consulta il contatore Innodb_buffer_pool_read_requests.
• Per il numero di letture logiche che InnoDB non può soddisfare dal pool di buffer e ha dovuto leggere direttamente dal disco, consulta Innodb_buffer_pool_reads.
• Utilizza Innodb_buffer_pool_hit_ratio per la percentuale di letture che InnoDB può soddisfare dal pool di buffer.
• Esamina Innodb_buffer_pool_usage per la percentuale del pool di buffer di InnoDB che contiene pagine di dati.

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

Ottimizza le prestazioni delle query

Per ottimizzare le prestazioni delle query, esegui questi comandi in base alle esigenze del relativo piano di esecuzione. Per ulteriori informazioni, consulta EXPLAIN output format (Formato di output di EXPLAIN) sul sito web MySQL.

Utilizza EXPLAIN per ottimizzare le query

Per visualizzare i dettagli sulle prestazioni della query e sul motivo per cui la query potrebbe essere in ritardo, esegui il comando EXPLAIN. Per ulteriori informazioni, consulta Optimizing Queries with EXPLAIN (Ottimizzazione delle query con EXPLAIN) sul sito web MySQL.

Per determinare se la query utilizza un indice, esegui la query EXPLAIN. Nell'output di EXPLAIN, esamina i nomi delle tabelle, le chiavi in uso e il numero di righe scansionate dalla query. Per ulteriori informazioni, consulta EXPLAIN statement (Istruzione EXPLAIN) sul sito web MySQL. Esamina l'output, quindi intraprendi le seguenti azioni:

• Se l'output non mostra le chiavi in uso, crea un indice delle colonne della clausola WHERE.
• Se la tabella ha l'indicizzazione richiesta, assicurati che le statistiche della tabella siano aggiornate. Per ulteriori informazioni, consulta The INFORMATION_SCHEMA STATISTICS Table (Tabella INFORMATION_SCHEMA STATISTICS) sul sito web MySQL.

Utilizza ANALYZE TABLE per aggiornare le statistiche della query

Se le statistiche della tabella non sono aggiornate, la query può avere prestazioni insoddisfacenti. Per aggiornare le statistiche della query, esegui il comando ANALYZE TABLE. Per ulteriori informazioni, consulta la pagina ANALYZE TABLE statement (Istruzione ANALYZE TABLE) sul sito web MySQL.

Utilizza EXPLAIN ANALYZE per vedere come le query allocano il tempo

Per determinare quale parte dell'esecuzione della query è lenta, esegui la query EXPLAIN ANALYZE per vedere come MySQL alloca il tempo alla query. Al completamento della query, la 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. Puoi anche utilizzare SHOW PROFILE per profilare le query più lente e individuare lo stato in cui la sessione trascorre più tempo. Per ulteriori informazioni, consulta SHOW PROFILE statement (Istruzione SHOW PROFILE) sul sito web MySQL.

Utilizza SHOW FULL PROCESSLIST e Monitoraggio avanzato per esaminare le operazioni

Esegui il comando SHOW FULL PROCESSLIST per visualizzare l'elenco delle operazioni eseguite sul server di database. Puoi anche utilizzare Monitoraggio avanzato per esaminare questo elenco. Per ulteriori informazioni, consulta la pagina SHOW PROCESSLIST Statement (Istruzione SHOW PROCESSLIST) sul sito web MySQL.

Controlla la lunghezza dell'elenco della cronologia

Il sistema di transazioni di InnoDB esegue il Multi-Version Concurrency Control (MVCC). Se il carico di lavoro richiede più transazioni aperte o di lunga durata, aspettati una lunghezza dell'elenco della cronologia elevata nel database. È consigliabile evitare transazioni aperte o di lunga durata nel database. Per ulteriori informazioni, consulta La lunghezza dell'elenco della cronologia di InnoDB è aumentata in modo significativo.

Se non controlli la dimensione della lunghezza dell'elenco della cronologia, le prestazioni diminuiscono nel tempo. Una lunghezza elevata dell'elenco della cronologia può anche causare un utilizzo elevato delle risorse, prestazioni di 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 dell'elenco della cronologia. Se i thread di eliminazione non corrispondono alle modifiche nel database, la lunghezza dell'elenco della cronologia rimane elevata. In casi estremi, puoi anche subire un'interruzione del database.

Il comando SHOW ENGINE INNODB STATUS mostra informazioni sull'elaborazione delle transazioni, sugli eventi di attesa e sui deadlock. Per ulteriori informazioni, consulta SHOW ENGINE statement (Istruzione SHOW ENGINE) sul sito web MySQL. Esegui la query SHOW ENGINE INNODB STATUS per controllare la lunghezza dell'elenco della cronologia:

SHOW ENGINE INNODB STATUS;

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 controllare la lunghezza dell'elenco della cronologia utilizzando Performance Insights, completa i seguenti passaggi:

1. Apri la console Amazon RDS.
2. Nel pannello di navigazione, scegli Performance Insights, quindi seleziona il database di cui desideri visualizzare le metriche.
3. Scegli Metriche.
4. Nella pagina Dashboard delle metriche, scegli Custom dashboard (Dashboard personalizzate).
5. Scegli Aggiungi widget, quindi seleziona la metrica Trx Rseg History Len.
6. Scegli Aggiungi widget.

Nota: se le scritture DML (Data Manipulation Language) aumentano la lunghezza dell'elenco della cronologia, chiedi all'amministratore del database di terminare le query di scrittura.

Risolvi i problemi delle query bloccate

Se la query è in esecuzione per un periodo di tempo prolungato, è possibile che un'altra query la stia bloccando. In MySQL 8.0, puoi trovare le attese dei blocchi 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. Esegui questa query per identificare le transazioni bloccanti:

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;

Informazioni correlate

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

Perché una query sulla mia istanza database Amazon RDS per MySQL è stata bloccata se non c'erano altre sessioni attive?