Come posso risolvere i problemi relativi all'utilizzo elevato della CPU in un'istanza database Amazon RDS per MySQL o Aurora compatibile con MySQL?

Breve descrizione

Diversi fattori possono causare un aumento dell'utilizzo della CPU, come carichi di lavoro pesanti avviati dall'utente, più query concorrenti o transazioni di lunga durata.

Per identificare l'origine dell'utilizzo della CPU in un'istanza database, controlla le seguenti risorse:

• Monitoraggio avanzato
• Approfondimenti sulle prestazioni
• Query che rilevano la causa dell'utilizzo della CPU nel carico di lavoro
• Log con monitoraggio attivato

Dopo aver identificato l'origine, analizza e ottimizza il carico di lavoro per ridurre l'utilizzo della CPU.

Risoluzione

Utilizzo di Monitoraggio avanzato

Il monitoraggio avanzato fornisce una vista a livello di sistema operativo (OS) per identificare la causa di un uso intensivo della CPU. Ad esempio, è possibile esaminare il carico medio, l'elenco dei processi del sistema operativo e la distribuzione della CPU System (%) o Nice (%).

Utilizza Monitoraggio avanzato per controllare i dati loadAverageMinute a intervalli di 1, 5 e 15 minuti. Un carico medio superiore al numero di vCPU indica che il carico sull'istanza è pesante. Se il carico medio è inferiore al numero di vCPU per la classe di istanza database, la limitazione (della larghezza di banda della rete) della CPU potrebbe non causare la latenza dell'applicazione. Controlla il carico medio per evitare falsi positivi quando esegui la diagnostica della causa dell'utilizzo della CPU.

Ad esempio, hai un'istanza database che utilizza una classe di istanza db.m5.2xlarge che raggiunge il limite della CPU. Alla classe di istanza sono associate 8 vCPU. Un carico medio superiore a 170 indica un uso intensivo del computer durante l'intervallo di tempo misurato:

Carico medio al minuto:

• Quindici: 170,25
• Cinque: 391,31
• Uno: 596,74

Utilizzo della CPU:

• Utente (%): 0,71
• Sistema (%): 4,9
• Nice (%): 93,92
• Totale (%): 99,97

Nota: Amazon RDS assegna al carico di lavoro una priorità più elevata rispetto ad altre attività in esecuzione sull'istanza database. Per dare priorità alle attività correlate alla gestione, le attività del carico di lavoro hanno un valore Nice diverso. Di conseguenza, in Monitoraggio avanzato, Nice (%) rappresenta la quantità di CPU utilizzata dal carico di lavoro rispetto al database.

Dopo aver attivato Monitoraggio avanzato, controlla l'elenco dei processi del sistema operativo associati all'istanza database. Monitoraggio avanzato mostra un massimo di 100 processi. L'elenco può aiutarti a identificare i processi che influiscono maggiormente sulle prestazioni di CPU e memoria.

Nella sezione dell'elenco dei processi del sistema operativo di Monitoraggio avanzato, esamina i processi del sistema operativo e i processi di RDS. Queste metriche possono aiutarti a capire se l'aumento dell'utilizzo della CPU dipende dai processi del sistema operativo o dai processi di RDS . In alternativa, utilizza queste metriche per monitorare la percentuale di CPU utilizzata dai processi mysqld o aurora. Se Aurora Storage Daemon mostra un utilizzo elevato della CPU in un'istanza Aurora, ciò significa che l'istanza ha un carico di lavoro di lettura/scrittura elevato. Questo utilizzo elevato della CPU può anche suggerire che la dimensione dell'istanza potrebbe essere troppo piccola per il volume di archiviazione e il carico di lavoro correnti. Oppure in background si svolgono operazioni complesse.

Per vedere la ripartizione dell'utilizzo della CPU, esamina le metriche per cpuUtilization. Per ulteriori informazioni, consulta Monitoring OS metrics with Enhanced Monitoring (Monitoraggio dei parametri del sistema operativo con Monitoraggio avanzato).

Nota: se attivi Performance Schema (Schema delle prestazioni), puoi mappare l'ID del thread del sistema operativo sull'ID del processo solo per l'istanza database RDS MySQL. Non puoi mappare invece l'ID del thread del sistema operativo sull'ID del processo per l'istanza database Aurora MySQL. Per ulteriori informazioni, consulta Perché la mia istanza database Amazon RDS utilizza la memoria swap quando ho memoria sufficiente?

Utilizza Database insights

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. Inoltre, consulta Attivazione della modalità avanzata di Database Insights per Amazon Aurora.

Puoi utilizzare Database Insights per identificare le query eseguite sull'istanza database che causano un utilizzo elevato della CPU.

Prima di tutto, attiva Database Insights nell'istanza MySQL. Quindi utilizza Database Insights per ottimizzare il carico di lavoro. Puoi anche rivolgerti all'amministratore del database per identificare insieme la causa principale del problema.

Per informazioni sul supporto del motore, della Regione AWS e della classe di istanza, consulta Supporto del motore di database, della Regione e della classe di istanza di Amazon Aurora per Database Insights. Inoltre, consulta Supporto del motore di database, della Regione e della classe di istanza di Amazon Aurora per Database Insights.

Utilizza query per rilevare la causa dell'utilizzo elevato della CPU nel carico di lavoro

Prima di ottimizzare il carico di lavoro, devi identificare la query problematica. Per identificare la causa principale dell'utilizzo elevato della CPU, esegui queste query nel momento in cui riscontri il problema.

Per visualizzare i thread in esecuzione sull'istanza MySQL, esegui il comando SHOW FULL PROCESSLIST:

SHOW FULL PROCESSLIST;

Nota: esegui la query SHOW PROCESSLIST come utente principale del sistema. Devi avere le autorizzazioni di amministrazione del server MySQL PROCESS per visualizzare tutti i thread in esecuzione su un'istanza MySQL. Se non hai le autorizzazioni di amministrazione, il comando SHOW PROCESSLIST mostra solo i thread associati all'account MySQL che utilizzi.

A volte, la stessa serie di istruzioni può continuare a essere eseguita senza essere completata. Quando ciò accade, le istruzioni successive devono attendere il completamento della prima serie di istruzioni. Questo perché il blocco a livello di riga di InnoDB potrebbe aggiornare le stesse righe. Per ulteriori informazioni, consulta la pagina SHOW PROCESSLIST Statement (Istruzione SHOW PROCESSLIST) sul sito web MySQL.

La tabella INNODB_TRX fornisce informazioni su tutte le transazioni InnoDB in esecuzione che non sono transazioni di sola lettura. Per visualizzare la tabella INNODB\ _TRX, esegui questa query:

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

La tabella INNODB_LOCKS fornisce informazioni sui blocchi che una transazione InnoDB richiede ma non riceve. Per visualizzare la tabella INNODB\ _LOCKS, esegui questa query:

MySQL 5.7 o versioni precedenti:

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

MySQL 8.0:

SELECT * FROM performance_schema.data_locks;

Per ulteriori informazioni, consulta The INFORMATION_SCHEMA.INNODB_LOCKS table (La tabella INFORMATION_SCHEMA.INNODB_LOCKS) nella sezione per la versione di MySQL 5.7 e The data_locks table (La tabella data_locks) nella sezione per la versione di MySQL 8.0 sul sito web MySQL.

La tabella INNODB_LOCK_WAITS fornisce una o più righe per ogni transazione InnoDB bloccata. Per visualizzare la tabella INNODB_LOCKS_WAITS, esegui questa query.

MySQL 5.7 o versioni precedenti:

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

MySQL 8.0:

SELECT * FROM performance_schema.data_lock_waits;

Per vedere le transazioni in attesa e quelle che bloccano le transazioni in attesa, esegui una query simile all'esempio seguente:

MySQL 5.7 o versioni precedenti:

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       information_schema.innodb_lock_waits w  
INNER JOIN information_schema.innodb_trx b  
  ON b.trx_id = w.blocking_trx_id  
INNER JOIN information_schema.innodb_trx r  
  ON r.trx_id = w.requesting_trx_id;

MySQL 8.0:

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 sull'interpretazione dell'output di questa query, consulta Using InnoDB transaction and locking information (Utilizzo delle informazioni sulle transazioni e sui blocchi di InnoDB) nella sezione per la versione di MySQL 8.0 sul sito web MySQL.

Per ottenere informazioni dal monitor standard di InnoDB sullo stato del motore di archiviazione InnoDB, esegui questa query:

SHOW ENGINE INNODB STATUS;

Per ulteriori informazioni, consulta la pagina SHOW ENGINE statement (Istruzione SHOW ENGINE) nella sezione per la versione di MySQL 8.0 sul sito web MySQL.

Per visualizzare lo stato del server, esegui questo comando.

SHOW GLOBAL STATUS;

Per ulteriori informazioni, consulta la pagina SHOW STATUS statement (Istruzione STATUS ENGINE) nella sezione per la versione di MySQL 8.0 sul sito web MySQL.

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

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

Se il carico di lavoro richiede più transazioni aperte o di lunga durata, il database potrebbe avere un valore HLL elevato. Inoltre, se i thread di eliminazione non riescono a tenere il passo con le modifiche del database, il valore HLL potrebbe risultare elevato. Un valore HLL elevato causa un maggiore utilizzo delle risorse e prestazioni lente e non uniformi dell'istruzione SELECT.

In un'istanza di scrittura Aurora MySQL, utilizza la metrica di CloudWatch RollbackSegmentHistoryListLength per monitorare il valore HLL.

Se l'istanza ha un valore HLL elevato, rivedi l'istruzione SQL. Questo problema si verifica quando applichi START TRANSACTION e non c'è alcun COMMIT. Poiché il thread è entrato in uno stato SLEEP, non puoi vedere l'istruzione SQL precedente.

Per risolvere il problema, esegui questo comando.

SELECT event_id, current_schema, sql_text, lock_time
 FROM performance_schema.events_statements_history
 WHERE thread_id=<thread_id>  
 ORDER BY event_id DESC;

Analizza i log e attiva il monitoraggio

Analizza il log delle query generale di MySQL per vedere cosa sta facendo mysqld in un momento specifico. Puoi anche visualizzare le query in esecuzione sull'istanza in un momento specifico (ad esempio, le informazioni su quando i client si connettono o si disconnettono). Per ulteriori informazioni, consulta la pagina The General Query Log (Log delle query generale) sul sito web di MySQL.

Importante: quando attivi il log delle query generale per lunghi periodi, i log consumano spazio di archiviazione e possono aumentare il sovraccarico incidendo sulle prestazioni.

Analizza i log delle query lente di MySQL per individuare le query che richiedono più tempo per essere eseguite rispetto ai secondi impostati in long_query_time. Puoi anche rivedere il carico di lavoro e analizzare le query per migliorare le prestazioni e il consumo di memoria. Per ulteriori informazioni, consulta 7.4.5 The slow query log sul sito web di MySQL.

Nota: quando utilizzi il log delle query lente o il log delle query generale, è consigliabile impostare il parametro log_output su FILE.

Utilizza il Plugin di audit MariaDB per controllare l'attività del database in Amazon RDS per MySQL o Amazon RDS per MariaDB. Ad esempio, tieni traccia degli utenti che accedono al database o delle query eseguite sul database.

Se utilizzi Aurora compatibile con MySQL, puoi utilizzare Audit avanzato. Audit avanzato offre un maggiore controllo sui tipi di query che desideri registrare e riduce il costo generale per la registrazione.

Utilizza il parametro innodb_print_all_deadlocks per verificare la presenza di deadlock e il blocco delle risorse. È possibile utilizzare questo parametro per registrare informazioni sui deadlock nelle transazioni utente di InnoDB nel log degli errori di MySQL. Per ulteriori informazioni, consulta la pagina innodb_print_all_deadlocks sul sito web di MySQL.

Analizza e ottimizza il carico di lavoro elevato della CPU

Dopo aver identificato la query che aumenta l'utilizzo della CPU, ottimizza il carico di lavoro per ridurne il consumo.

Se viene visualizzata una query non necessaria per il carico di lavoro, esegui questo comando per terminare la connessione:

CALL mysql.rds_kill(processID);

Importante: quando termini la scrittura DML (Data Manipulation Language) su un'istanza, viene ripristinata la transazione interrotta. Il ripristino degli aggiornamenti può richiedere molto tempo. Se una query resta in esecuzione per molto tempo, rivolgiti all'amministratore database per verificare se puoi interromperla.

Per trovare il processID di una query, esegui il comando SHOW FULL PROCESSLIST.

Se non desideri terminare la query, utilizza il comando EXPLAIN per ottimizzarla. EXPLAIN mostra i singoli passaggi necessari quando esegui una query. Per ulteriori informazioni, consulta Optimizing Queries with EXPLAIN (Ottimizzazione delle query con sul EXPLAIN) sito web MySQL.

Per visualizzare i dettagli del profilo, attiva il profiling. Il comando SHOW PROFILE mostra l'utilizzo delle risorse per le istruzioni in esecuzione durante la sessione corrente. Per ulteriori informazioni, consulta SHOW PROFILE statement (Istruzione SHOW PROFILE) sul sito web MySQL.

Per visualizzare e ottimizzare le statistiche delle tabelle, utilizza la query ANALYZE TABLE. Per ulteriori informazioni, consulta la pagina ANALYZE TABLE statement (Istruzione ANALYZE TABLE) sul sito web MySQL.

Informazioni correlate

Regolazione di Aurora MySQL con eventi di attesa

Come posso attivare e monitorare i log per un'istanza database Amazon RDS per MySQL?

Amazon CloudWatch Database Insights applied in real scenarios (Amazon CloudWatch Database Insights applicato in scenari reali)