Come faccio a risolvere i problemi relativi all'utilizzo elevato della CPU sull'istanza Amazon RDS per MySQL o Amazon Aurora MySQL?

Breve descrizione

L'aumento dell'utilizzo della CPU può essere causato da diversi fattori, come carichi di lavoro pesanti avviati dall'utente, diverse query simultanee o transazioni di lunga durata.

Per identificare l'origine dell'utilizzo della CPU nell'istanza Amazon RDS per MySQL, esamina i seguenti approcci:

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

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

Risoluzione

Utilizzo del monitoraggio avanzato

Il Monitoraggio avanzato fornisce una visualizzazione a livello di sistema operativo (OS). Questa visualizzazione può aiutare a identificare la causa di un carico elevato della CPU a livello granulare. Ad esempio, è possibile esaminare il carico medio, la distribuzione della CPU (system% o nice%) e l'elenco dei processi del sistema operativo.

Con il Monitoraggio avanzato puoi controllare i dati loadAverageMinute a intervalli di 1, 5 e 15 minuti. Un carico medio superiore al numero di vCPU indica che l'istanza è sottoposta a un carico pesante. Se invece il carico medio è inferiore al numero di vCPU per la classe di istanza database, la limitazione della CPU potrebbe non causare la latenza dell'applicazione. Quando diagnostichi la causa dell'utilizzo della CPU, controlla il carico medio per evitare falsi positivi.

Ad esempio, se hai un'istanza database che utilizza una classe di istanza db.m5.2xlarge con 3000 Provisioned IOPS che raggiunge il limite della CPU, puoi esaminare le seguenti metriche di esempio per identificare la causa principale dell'elevato utilizzo della CPU. Nell'esempio seguente, alla classe di istanza sono associate otto vCPU. A parità di carico medio, un valore superiore a 170 indica che la macchina è sottoposta a un carico pesante durante il periodo di tempo misurato:

Load Average Minute

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 dà al carico di lavoro una priorità più elevata rispetto ad altre attività in esecuzione sull'istanza database. Per dare priorità a queste attività, le attività del carico di lavoro hanno un valore Nice più elevato. Di conseguenza, in Monitoraggio avanzato Nice% rappresenta la quantità di CPU utilizzata dal carico di lavoro rispetto al database.

Dopo aver attivato Monitoraggio avanzato puoi anche controllare l'elenco dei processi del sistema operativo associato all'istanza database. Il monitoraggio avanzato mostra un massimo di 100 processi. Questo può aiutarti a identificare quali processi hanno il maggiore impatto sulle prestazioni in base all'uso di CPU e memoria.

Nella sezione Elenco dei processi del sistema operativo (OS) di Monitoraggio avanzato, esamina i processi del sistema operativo e i processi RDS. Conferma la percentuale di utilizzo della CPU di un processo mysqld o Aurora. Queste metriche possono aiutarti a confermare se l'aumento dell'utilizzo della CPU è causato dal sistema operativo o dai processi RDS. Oppure puoi utilizzare queste metriche per monitorare eventuali aumenti dell'utilizzo della CPU causati da mysqld o Aurora. Puoi anche vedere la suddivisione dell'utilizzo della CPU esaminando le metriche per cpuUtilization. Per ulteriori informazioni, consulta la pagina Monitoraggio dei parametri del sistema operativo con il monitoraggio avanzato.

Nota: attivando Performance Schema è possibile mappare l'ID thread del sistema operativo all'ID processo del database. Per ulteriori informazioni, consulta la pagina Perché la mia istanza database Amazon RDS utilizza la memoria di swap quando la memoria è sufficiente?

Utilizzo di Informazioni dettagliate sulle prestazioni

È possibile utilizzare Informazioni dettagliate sulle prestazioni per identificare le query esatte in esecuzione sull'istanza che causano un elevato utilizzo della CPU. Innanzitutto, attiva Informazioni dettagliate sulle prestazioni per MySQL. Quindi, puoi utilizzare Informazioni dettagliate sulle prestazioni per ottimizzare il carico di lavoro. Assicurati di consultare il tuo DBA.

Per vedere i motori di database che puoi utilizzare con Informazioni dettagliate sulle prestazioni, consulta la pagina Monitoraggio del carico DB con Performance Insights su Amazon RDS.

Utilizzo delle query per rilevare la causa dell'utilizzo della CPU nel carico di lavoro

Prima di poter ottimizzare il carico di lavoro è necessario identificare la query problematica. Per identificarne la causa principale dell'utilizzo elevato della CPU puoi eseguire le query seguenti mentre si verifica il problema. Quindi, ottimizza il carico di lavoro per ridurre l'utilizzo della CPU.

Il comando SHOW PROCESSLIST mostra i thread attualmente in esecuzione sull'istanza MySQL. A volte, la stessa serie di istruzioni potrebbe continuare a funzionare 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 sul sito web di MySQL.

SHOW FULL PROCESSLIST;

Nota: esegui la query SHOW PROCESSLIST come utente principale del sistema. Se non sei l'utente principale del sistema, devi disporre dei privilegi di amministratore del server MySQL PROCESS per vedere tutti i thread in esecuzione su un'istanza MySQL. Se non disponi dei privilegi di amministratore, il comando SHOW PROCESSLIST mostrerà solo i thread associati all'account MySQL che stai utilizzando.

La tabella INNODB_TRX fornisce informazioni su tutte le transazioni InnoDB attualmente in esecuzione che non sono transazioni di sola lettura.

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

La tabella INNODB_LOCKS fornisce informazioni sui blocchi che una transazione InnoDB ha richiesto ma non ha ricevuto.

Per MySQL 5.7 o versioni precedenti:

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

Per MySQL 8.0:

SELECT * FROM performance_schema.data_locks;

La tabella INNODB_LOCK_WAITS fornisce una o più righe per ogni transazione InnoDB bloccata.

Per MySQL 5.7 o versioni precedenti:

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

Per MySQL 8.0:

SELECT * FROM performance_schema.data_lock_waits;

Puoi eseguire una query simile alla seguente per visualizzare le transazioni in attesa e le transazioni che bloccano le transazioni in attesa. Per ulteriori informazioni, consulta la pagina Using InnoDB transaction and locking information sul sito web di MySQL.

Per 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;

Per 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;

La query SHOW ENGINE INNODB STATUS fornisce informazioni ottenute dal monitoraggio InnoDB standard sullo stato del motore di archiviazione InnoDB. Per ulteriori informazioni, consulta la pagina SHOW ENGINE statement sul sito web di MySQL.

SHOW ENGINE INNODB STATUS;

La query SHOW\ [GLOBAL | SESSION] STATUS fornisce informazioni sullo stato del server. Per ulteriori informazioni, consulta la pagina SHOW STATUS statement sul sito web di MySQL.

SHOW GLOBAL STATUS;

Nota: queste query sono state testate su Aurora 2.x (MySQL 5.7); Aurora 1. x (MySQL 5.6); MariaDB 10.x. Inoltre, la tabella INFORMATION_SCHEMA.INNODB_LOCKS non è più supportata a partire da MySQL 5.7.14 ed è stata rimossa in MySQL 8.0. La tabella performance_schema.data_locks sostituisce la tabella INFORMATION_SCHEMA.INNODB_LOCKS. Per ulteriori informazioni, consulta la pagina The data_locks table sul sito web di MySQL.

Analisi dei log e attivazione del monitoraggio

Quando analizzi i log o desideri attivare il monitoraggio in Amazon RDS per MySQL, considera i seguenti approcci:

• 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, incluse le informazioni su quando i client si connettono o si disconnettono. Per ulteriori informazioni, consulta la pagina The General Query Log sul sito web di MySQL.
  Nota: quando si attiva il Log delle query generale per lunghi periodi, i log consumano spazio di archiviazione e possono aumentare il sovraccarico delle prestazioni.
• Analizza i Log delle query lente di MySQL per trovare le query che richiedono più tempo per essere eseguite rispetto ai secondi impostati per long_query_time. Puoi anche rivedere il carico di lavoro e analizzare le tue query per migliorare le prestazioni e il consumo di memoria. Per ulteriori informazioni, consulta la pagina The Slow Query Log sul sito web di MySQL. Suggerimento: quando utilizzi il Log delle query lente o il Log delle query generale, imposta il parametro log_output su FILE.
• Usa il MariaDB Audit Plugin per controllare l'attività del database. Ad esempio, è possibile monitorare gli utenti che accedono al database o le query eseguite sul database. Per ulteriori informazioni, consulta la pagina Supporto per MySQL del plug-in per audit MariaDB.
• Se usi Aurora per MySQL, puoi anche usare l'Audit avanzato. L'audit può darti un maggiore controllo sui tipi di query che desideri registrare. In questo modo si riduce il sovraccarico per la registrazione.
• Usa 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.

Analisi e ottimizzazione dell'elevato carico di lavoro della CPU

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

Se vedi una query non richiesta per il tuo carico di lavoro, puoi terminare la connessione utilizzando il seguente comando:

CALL mysql.rds_kill(processID);

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

Se non desideri terminare la query, ottimizzala utilizzando il comando EXPLAIN. Il comando EXPLAIN mostra le singole fasi coinvolte nell'esecuzione di una query. Per ulteriori informazioni, consulta la pagina Optimizing Queries with EXPLAIN sul sito web di MySQL.

Per rivedere i dettagli del profilo, attiva il comando PROFILING. Il comando PROFILING può indicare l'utilizzo delle risorse per le istruzioni in esecuzione durante la sessione corrente. Per ulteriori informazioni, consulta la pagina SHOW PROFILE statement sul sito web di MySQL.

Per aggiornare le statistiche della tabella, usa il comando ANALYZE TABLE. Il comando ANALYZE TABLE può aiutare l'ottimizzatore a scegliere un piano appropriato per eseguire la query. Per ulteriori informazioni, consulta la pagina ANALYZE TABLE statement sul sito web di MySQL.

---

Informazioni correlate

Amazon RDS per MySQL

Amazon RDS for MariaDB

How do I activate and monitor logs for an Amazon RDS MySQL DB instance?

Tuning Amazon RDS for MySQL with Performance Insights