Perché la mia query SELECT viene eseguita lentamente sul mio cluster Amazon Aurora MySQL DB?

Breve descrizione

Esistono diversi motivi per cui le query SELECT potrebbero essere eseguite lentamente sul tuo cluster DB compatibile con Aurora MySQL:

• Le tue risorse di sistema Amazon Relational Database Service (Amazon RDS) sono sovrautilizzate. Ciò può verificarsi a causa di una CPU elevata, di una memoria insufficiente o di un carico di lavoro superiore a quello che il tipo di istanza database è in grado di gestire.
• Il database si sta bloccando e gli eventi di attesa risultanti stanno causando un cattivo funzionamento delle query SELECT.
• La query SELECT sta eseguendo scansioni complete della tabella su tabelle di grandi dimensioni oppure la query non dispone degli indici necessari.
• La lunghezza dell'elenco cronologico di InnoDB (HLL) è cresciuta notevolmente a causa di transazioni a lungo termine.

Risoluzione

Monitora le risorse del tuo sistema Amazon RDS utilizzando le metriche

È consigliabile monitorare sempre l'utilizzo della CPU e la memoria libera sul cluster Amazon Aurora. Sebbene i picchi occasionali della CPU siano normali, una CPU costantemente elevata per lunghi periodi di tempo può rallentare l'esecuzione delle query SELECT. Utilizza i seguenti strumenti per determinare come e dove utilizzi la CPU:

1. I parametri di Amazon CloudWatch sono il modo più semplice per monitorare l'utilizzo della CPU. Per ulteriori informazioni sulle metriche disponibili per Aurora, consulta Metriche di CloudWatch per Aurora.

2. Il monitoraggio avanzato fornisce un'analisi dettagliata delle metriche a livello di sistema operativo con una granularità inferiore. La suddivisione dettagliata mostra come i processi utilizzano la CPU.

3. Performance Insights determina con precisione il carico del tuo DB. Attiva Performance Insights per la tua istanza database, quindi controlla se il carico supera la vCPU massima. È inoltre possibile monitorare le query di carico e gli SQL in base alle attese e identificare gli utenti che causano il numero massimo di attese.

****Le query SELECT possono anche essere eseguite lentamente a causa delle ricerche su disco. Per ridurre al minimo l'I/O del disco, il motore del database tenta di memorizzare nella cache il blocco letto dal disco. Ciò significa che la prossima volta che il database avrà bisogno dello stesso blocco di dati, quel blocco viene recuperato dalla memoria anziché dal disco.

Usa queste metriche per verificare se stai eseguendo una determinata query dal disco o dalla memoria:

• **Volume Leggi SIOPS:**Questa metrica indica il numero di operazioni di lettura del [disco] a livello di volume fatturate. È consigliabile assicurarsi che questo valore sia il più basso possibile.
• Rapporto di riscontri nella cache del buffer:Questa metrica è la percentuale di richieste soddisfatte dalla cache del buffer. È consigliabile assicurarsi che questo valore sia il più alto possibile. Se BufferCacheHitRatio si interrompe e l’istruzioneSELECT è lenta, stai elaborando la query dai volumi sottostanti.

Un'altra risorsa importante per identificare le istruzioni SELECT lente è lo slow query log. Attiva la registrazione lenta delle query per il tuo cluster di database per registrare queste query e intervenire in seguito. Per la versione compatibile con MySQL 5.6, utilizza My SQL Performance Schema per monitorare le prestazioni delle query su base continuativa.

Identifica le situazioni di stallo e attendi gli eventi

Amazon RDS blocca i dati nel tuo database in modo che solo una sessione utente possa scrivere o aggiornare una riga alla volta. Qualsiasi altra transazione che richiede questa riga viene mantenuta in sospeso. In un lucchetto condiviso, le transazioni di scrittura/aggiornamento vengono mantenute in attesa mentre le transazioni di lettura leggono i dati. Se una query è in attesa di accedere a una riga bloccata da un'altra query, ciò può causare un deadlock.

Per identificare i deadlock sul tuo database, abilita il parametro innodb_print_all_deadlocks nei tuoi gruppi di parametri. Quindi, monitora mysql-error.log dalla console RDS/CLI/API.

Oppure, accedi a MySQL con un account amministratore, quindi esegui questo comando per identificare i deadlock dall'output del comando nella sezione Latest Detected Deadlock:

mysql> SHOW ENGINE INNODB STATUS\G;

Controlla se la tua ricerca utilizza un indice

Se una query non dispone di un indice o esegue scansioni complete della tabella, la query viene eseguita più lentamente. Gli indici aiutano a velocizzare le interrogazioni SELECT.

Per verificare se la tua query utilizza un indice, usa la query EXPLAIN. Si tratta di uno strumento utile per la risoluzione dei problemi relativi alle query lente. Nell'output EXPLAIN, controlla i nomi delle tabelle, la chiave utilizzata e il numero di righe scansionate durante la query. Se l'output non mostra alcuna chiave in uso, crea un indice sulle colonne utilizzate nella clausola WHERE.

Se la tabella ha l'indicizzazione richiesta, controlla se le statistiche della tabella sono aggiornate. Assicurarsi che le statistiche siano accurate significa che l'ottimizzatore delle query utilizza gli indici più selettivi con la cardinalità corretta. Ciò migliora le prestazioni delle query.

Controlla la lunghezza dell'elenco cronologico (HLL)

InnoDB utilizza un concetto chiamato Multi-version-Concurrency Control (MVCC). MVCC conserva più copie dello stesso record per preservare la coerenza della lettura. Ciò significa che quando esegui una transazione, InnoDB elimina le copie più vecchie. Ma quando una transazione non viene confermata per molto tempo a causa della crescita dei segmenti di annullamento, la lunghezza dell'elenco cronologico (HLL) aumenta. La lunghezza dell'elenco della cronologia di InnoDB rappresenta il numero di modifiche non cancellate.

Se il carico di lavoro richiede più transazioni aperte o di lunga durata, puoi aspettarti di vedere un HLL elevato nel database.

Nota: Le transazioni a lungo termine non sono l'unica causa dei picchi di HLL. Anche se i thread di eliminazione non sono in grado di tenere il passo con le modifiche sul DB, l'HLL può rimanere elevato.

Se non monitorate le dimensioni dell'HLL, le prestazioni regrediscono nel tempo. Una dimensione crescente dell'HLL può anche causare un maggiore consumo di risorse, prestazioni dell'istruzione SELECT più lente e incoerenti e un aumento dello spazio di archiviazione. In casi estremi, ciò può causare l'interruzione del database.

Per controllare la lunghezza dell'elenco della cronologia, esegui il seguente comando:

SHOW ENGINE INNODB STATUS;

Uscita:

------------ 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 Aurora MySQL, a causa della natura dei volumi dello storage condiviso, la lunghezza dell'elenco della cronologia è a livello di cluster e non a livello di singola istanza. Connettiti al tuo scrittore ed esegui la seguente query:

SELECT server_id, IF(session_id = 'master_session_id', 'writer', 'reader') AS ROLE, replica_lag_in_msec,
       oldest_read_view_trx_id , oldest_read_view_lsn
       from mysql.ro_replica_status;

Questa query consente di comprendere il ritardo di replica tra i nodi reader e il nodo writer. Descrive inoltre l'LSN più vecchio utilizzato dall'istanza database per la lettura dallo storage e l'ID TRX di visualizzazione di lettura più vecchio dell'istanza database. Usa queste informazioni per verificare se uno dei lettori ha una vecchia vista di lettura (rispetto allo stato del motore InnoDB sullo scrittore).

Nota: A partire da Aurora MySQL 1.19 e 2.06, puoi monitorare HLL utilizzando la metrica RollbackSegmentHistoryListLength in CloudWatch. Oppure, nelle versioni precedenti, usa trx_rseg_history_len per controllare HLL usando il seguente comando:

select NAME AS RollbackSegmentHistoryListLength,
COUNT from INFORMATION_SCHEMA.INNODB_METRICS where NAME = 'trx_rseg_history_len';

Se Performance Insights è attivato per le tue istanze di Aurora MySQL, puoi controllare RollbackSegmentHistoryListLength. Vai allo scrittore Performance Insight ed esegui le seguenti operazioni:

1. Seleziona Gestisci metriche, quindi seleziona Metriche del database.

2. Seleziona la metrica trx_rseg_history_len, quindi seleziona Aggiorna grafico.

Utilizza i seguenti metodi per risolvere i problemi relativi alla crescita di HLL:

• Se DML (scritture) causa la crescita di HLL: L'annullamento o la cessazione di questa dichiarazione comporta un ripristino della transazione interrotta. Questa operazione richiede molto tempo perché tutti gli aggiornamenti effettuati fino a questo momento vengono annullati.
• Se un READ causa la crescita dell'HLL: Terminare la query utilizzando mysql.rds_kill_query.
• A seconda della durata della query, collabora con il tuo DBA per verificare se è possibile terminarla utilizzando la stored procedure.

È consigliabile evitare la crescita monitorando l'HLL utilizzando questi metodi ed evitare transazioni aperte o di lunga durata sul database. Inoltre, è consigliabile salvare i dati in batch più piccoli.

Importante: Non riavviare il cluster o l'istanza del database. È più efficiente eliminare l'HLL quando può accedere ai dati in memoria nel buffer pool. Se si riavvia il database, la cache delle pagine sopravvissute potrebbe andare persa. Quando ciò accade, è necessario leggere le pagine di dati del volume del cluster per eliminare l'HLL. Questa operazione è più lenta rispetto all'esecuzione in memoria e comporta costi di fatturazione I/O aggiuntivi.

---

Informazioni correlate

Monitora i log di Amazon Aurora MySQL, Amazon RDS per MySQL e MariaDB con Amazon CloudWatch