Come faccio a risolvere i problemi relativi all'utilizzo elevato della CPU sull'istanza database di Amazon RDS per MySQL o di Aurora compatibile con 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 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, disponi di un'istanza database che utilizza una classe di istanza db.m5.2xlarge con una capacità di IOPS allocata pari a 3000 che raggiunge il limite della CPU. Alla classe di istanza sono associate otto vCPU. Un carico medio superiore a 170 indica un uso intensivo della macchina 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 assegnare 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, controlla l'elenco dei processi del sistema operativo associato all'istanza DB. Monitoraggio avanzato mostra un massimo di 100 processi. Questo consente di identificare quali processi hanno il maggiore impatto sulle prestazioni in base all'utilizzo della CPU e della 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 aiutare a confermare se l'aumento dell'utilizzo della CPU è causato dal sistema operativo o dai processi RDS. Oppure, utilizza queste metriche per monitorare eventuali aumenti dell'utilizzo della CPU causati da mysqld o Aurora. Per vedere la divisione 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: attivando lo schema delle prestazioni, è possibile mappare l'ID thread del sistema operativo all'ID processo del database. Per ulteriori informazioni, consulta Perché la mia istanza database Amazon RDS utilizza la memoria di swap quando la memoria è sufficiente?

Utilizzo di Approfondimenti sulle prestazioni

Puoi utilizzare Approfondimenti sulle prestazioni per identificare le query in esecuzione sull'istanza database che causano un elevato utilizzo della CPU. Innanzitutto, attiva Approfondimenti sulle prestazioni per MySQL. Quindi, utilizza Approfondimenti sulle prestazioni per ottimizzare il carico di lavoro. Se necessario, collabora con l'amministratore del database per identificare la causa principale del problema.

Per vedere i motori di database che puoi utilizzare con Approfondimenti sulle prestazioni, consulta Amazon RDS DB engine, AWS Region, and instance class support for Performance Insights (Motore DB Amazon RDS, Regione AWS e supporto delle classi di istanza per Approfondimenti sulle prestazioni.

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

Prima di ottimizzare il carico di lavoro, è necessario identificare la query problematica. Per identificare la causa principale dell'utilizzo della CPU, esegui le seguenti query quando si verifica il problema dell'utilizzo elevato di CPU.

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

SHOW FULL PROCESSLIST;

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

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 13.7.5.29 SHOW PROCESSLIST statement sul sito web di 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 la seguente query:

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 visualizzare la tabella INNODB\ _LOCKS, esegui la seguente query:

• Per 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 24.4.14 The INFORMATION_SCHEMA.INNODB_LOCKS table e 10.13.1 The data_locks table sul sito web di MySQL.

La tabella INNODB_LOCK_WAITS fornisce una o più righe per ogni transazione InnoDB bloccata. Per visualizzare la tabella INNODB\ _LOCKS\ _WAITS, esegui la seguente query.

• Per 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:

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

• 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 su come interpretare l'output di questa query, consulta 17.15.2.1 Using InnoDB transaction and locking information sul sito web di MySQL.

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

SHOW ENGINE INNODB STATUS;

Per ulteriori informazioni, consulta 13.7.5.15 SHOW ENGINE statement sul sito web di MySQL.

Per visualizzare lo stato del server, esegui il seguente comando.

SHOW GLOBAL STATUS;

Per ulteriori informazioni, consulta 15.7.7.37 SHOW STATUS statement sul sito web di MySQL.

Analisi dei log e attivazione del 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 7.4.3 The general query log sul sito web di MySQL.

Importante: 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 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, è una best practice impostare il parametro log_output su FILE.

Utilizza il Plug-in di audit MariaDB per verificare l'attività del database. Ad esempio, tieni traccia degli utenti che accedono al database o delle query eseguite sul database.

Se utilizzi Aurora MySQL, puoi anche utilizzare l'audit avanzato. L'audit avanzato offre un maggiore controllo sui tipi di query che si desidera 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.

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 viene visualizzata una query non necessaria per il carico di lavoro, esegui il comando seguente per terminare la connessione:

CALL mysql.rds_kill(processID);

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 si esegue una query. Per ulteriori informazioni, consulta la pagina 10.8.1 Optimizing queries with EXPLAIN sul sito web di 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 la pagina 15.7.7.30 SHOW PROFILE statement sul sito web di MySQL.

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

Informazioni correlate

Amazon RDS per MySQL

Amazon RDS per MariaDB

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

Tuning Amazon RDS for MySQL with Performance Insights