Ottimizza l'utilizzo elevato della CPU nelle istanze

Un utilizzo elevato della CPU in un'istanza può essere causato da vari motivi, come l'aumento dei carichi di lavoro, le transazioni pesanti, le query lente e le transazioni a lunga esecuzione.

Il motore per suggerimenti di istanze sottodimensionate analizza l'utilizzo della CPU. Se i livelli di utilizzo della CPU raggiungono o superano il 95% per un periodo di tempo significativo negli ultimi 30 giorni, il recommender ti avvisa e fornisce approfondimenti aggiuntivi per aiutarti a risolvere il problema.

Questo documento spiega come esaminare e ottimizzare un'istanza Cloud SQL per MySQL se viene identificata dal Recommender di istanze sottodimensionate come avente un elevato utilizzo della CPU.

Consigli

L'utilizzo della CPU aumenta proporzionalmente al carico di lavoro. Per ridurre l'utilizzo della CPU, controlla le query in esecuzione e ottimizzale. Ecco alcuni passaggi per controllare il consumo della CPU.

  1. Controlla Threads_running e Threads_connected

    Utilizza la seguente query per visualizzare il numero di thread attivi:

    > SHOW STATUS like 'Threads_%';

    Threads_running è un sottoinsieme di Threads_connected. Il resto dei thread è inattivo. Un aumento di Threads_running contribuirebbe a un aumento dell'utilizzo della CPU. Ti consiglio di controllare cosa viene eseguito su questi thread.

  2. Controllare gli stati delle query

    Esegui il comando SHOW PROCESSLIST per visualizzare le query in corso. Restituisce tutti i thread collegati in ordine e il relativo istruzione SQL attualmente in esecuzione. 

    mysql> SHOW [FULL] PROCESSLIST;

    Presta attenzione alle colonne Stato e Durata. Controlla se ci sono molte query bloccate nello stesso stato.

    • Se molti thread mostrano Updating, potrebbe esserci una contesa per il blocco dei record. Vedi il passaggio successivo.
    • Se molti thread mostrano Waiting per il blocco dei metadati della tabella, controlla la query per conoscere la tabella e poi cerca un DDL (ad esempio ALTER TABLE) che potrebbe contenere il blocco dei metadati. Un comando DDL potrebbe anche essere in attesa del blocco dei metadati della tabella se è in corso una query precedente, ad esempio un'esecuzione prolungata di SELECT query.

  3. Verifica la contesa per il blocco dei record

    Quando le transazioni detengono i blocchi sui record degli indici più utilizzati, bloccano altre transazioni che richiedono gli stessi blocchi. Ciò potrebbe causare un effetto a catena e bloccare un numero di richieste, con un aumento del valore di Threads_running. Per diagnosticare la contesa dei blocchi, utilizza la tabella information_schema.innodb_lock_waits.

    La seguente query elenca ogni transazione di blocco e il numero di transazioni bloccate associate. 

    SELECT 
  t.trx_id, 
  t.trx_state, 
  t.trx_started, 
  COUNT(distinct w.requesting_trx_id) AS blocked_trxs
FROM 
  information_schema.innodb_lock_waits w 
INNER JOIN information_schema.innodb_trx t
   ON t.trx_id = w.blocking_trx_id 
GROUP BY t.trx_id,t.trx_state, t.trx_started
ORDER BY t.trx_id;

    Sia un'unica DML di grandi dimensioni sia molte DML di piccole dimensioni concorrenti potrebbero causare conflitti di blocco delle righe. Per ottimizzare questo aspetto dal lato dell'applicazione, segui questi passaggi:

    • Evita transazioni lunghe perché i blocchi delle righe vengono mantenuti fino al termine della transazione.
    • Suddividi un'unica DML di grandi dimensioni in DML di piccole dimensioni.
    • Esegui batch di una singola riga DML in piccoli blocchi.
    • Riduci al minimo le contese tra i thread. Ad esempio, se il codice dell'applicazione utilizza un pool di connessioni, assegna un intervallo di ID allo stesso thread.

  4. Trovare transazioni in esecuzione da molto tempo

    • Utilizza SHOW ENGINE INNODB STATUS

      Nella sezione TRANSAZIONI, puoi vedere tutte le transazioni aperte ordinate dalla più recente alla più vecchia. 

      mysql> SHOW ENGINE INNODB STATUS\G
……
------------
TRANSACTIONS
------------
…
---TRANSACTION 245762, ACTIVE 262 sec
2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 9210, OS thread handle 140262286128896, query id 202218 localhost root

      Inizia con le transazioni più vecchie e trova le risposte alle seguenti domande:

      • Da quanto tempo sono in esecuzione queste transazioni?
      • Quante strutture di blocco e blocchi di righe sono presenti?
      • Quante voci di log di annullamento sono presenti?
      • Che cosa sono gli utenti e gli host che si connettono?
      • Qual è l'istruzione SQL in corso?

    • Utilizza information_schema.innodb_trx

      Se SHOW ENGINE INNODB STATUS è stato troncato, un modo alternativo per esaminare tutte le transazioni aperte è utilizzare la tabella information_schema.innodb_trx:

      SELECT 
 trx_id, trx_state, 
 timestampdiff(second, trx_started, now()) AS active_secs, 
 timestampdiff(second, trx_wait_started, now()) AS wait_secs, trx_tables_in_use,
 trx_tables_locked, 
 trx_lock_structs, 
 trx_rows_locked, 
 trx_rows_modified, 
 trx_query 
FROM information_schema.innodb_trx

    Se le transazioni mostrano gli attuali statement di lunga esecuzione, puoi decidere di interromperle per ridurre la pressione sul server o attendere il completamento delle transazioni critiche. Se le transazioni precedenti non mostrano attività, vai al passaggio successivo per trovare la cronologia delle transazioni.

  5. Controlla le istruzioni SQL delle transazioni a lunga esecuzione

    • Utilizza performance_schema

      Per utilizzare performance_schema, devi prima attivarlo. Si tratta di una modifica che richiede il riavvio dell'istanza. Dopo aver attivato performance_schema, controlla che gli strumenti e i consumatori siano attivati:

      SELECT * FROM setup_consumers where name like 'events_statements_history';
SELECT * FROM setup_instruments where name like 'statement/sql/%';

      Se non sono attivati, attivali:

      UPDATE setup_instruments SET ENABLED = 'YES', timed = 'YES' WHERE NAME LIKE 'statement/%';
UPDATE setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE 'events_statements%';

      Per impostazione predefinita, ogni thread conserva gli ultimi 10 eventi definiti da performance_schema_events_statements_history_size. In genere sono sufficienti per individuare la transazione nel codice dell'applicazione. Questo parametro non è dinamico.

      Con mysql thread id, ovvero processlist_id, esegui una query sugli eventi della cronologia:

      SELECT 
 t.thread_id, 
 event_name, 
 sql_text, 
 rows_affected, 
 rows_examined, 
 processlist_id, 
 processlist_time, 
 processlist_state 
FROM events_statements_history h 
INNER JOIN threads t 
ON h.thread_id = t.thread_id 
WHERE processlist_id = <mysql thread id>
ORDER BY event_id;

    • Utilizzare il log delle query lente

      Per il debug, puoi acquisire tutte le query che hanno richiesto più di N secondi nel log delle query lente. Puoi attivare i log delle query lente modificando le impostazioni dell'istanza nella pagina dell'istanza della console Google Cloud o gcloud CLI e poi visualizzare i log utilizzando il visualizzatore dei log nella console Google Cloud o gloud CLI.

  6. Controlla la contesa dei semafori

    In un ambiente concorrente, i mutex e i latch di lettura/scrittura sulle risorse condivise potrebbero essere il punto di contesa, rallentando le prestazioni del server. Inoltre, se il tempo di attesa del semaforo è superiore a 600 secondi, il sistema può arrestarsi in modo anomalo per uscire dalla situazione di blocco.

    Per visualizzare la contesa del semiforo, utilizza il seguente comando:

    mysql> SHOW ENGINE INNODB STATUS\G
----------
SEMAPHORES
----------
...
  --Thread 140396021667584 has waited at row0purge.cc line 862 for 241.00 seconds the semaphore:
  S-lock on RW-latch at 0x30c03e8 created in file dict0dict.cc line 1183
  a writer (thread id 140395996489472) has reserved it in mode  exclusive
  number of readers 0, waiters flag 1, lock_word: 0
  Last time read locked in file row0purge.cc line 862
  Last time write locked in file /build/mysql-5.7-FFKPr6/mysql-5.7-5.7.22/storage/innobase/dict/dict0stats.cc line 2376
...

    Con ogni attesa del semaphore, la prima riga mostra il thread in attesa, il semaphore specifico e la durata dell'attesa. Se si verificano frequenti attese del semaphore quando esegui ripetutamente SHOW ENGINE INNODB STATUS, in particolare attese di più di qualche secondo, significa che il sistema sta riscontrando colli di bottiglia della concorrenza.

    Esistono punti di contesa diversi in configurazioni e carichi di lavoro diversi.

    Quando i semiconduttori sono spesso su btr0sea.c, l'indicizzazione ad hash adattiva potrebbe essere la fonte della contesa. Prova a disattivarlo utilizzando la console Google Cloud o gcloud CLI.

  7. Ottimizzare le query SELECT lunghe

    Innanzitutto, rivedi la query. Identifica lo scopo della query e il modo migliore per ottenere i risultati. Il piano di query migliore è quello che riduce al minimo l'accesso ai dati.

    • Controlla il piano di esecuzione della query:
    mysql> EXPLAIN <the query>;

    Consulta la documentazione di MySQL per scoprire come interpretare l'output e valutare l'efficienza della query.

    • Utilizza l'indice corretto

    Controlla la colonna della chiave per vedere se viene utilizzato l'indice previsto. In caso contrario, aggiorna le statistiche dell'indice:

    mysql> analyze table <table_name>

    Aumenta il numero di pagine di esempio utilizzate per calcolare le statistiche dell'indice. Per saperne di più, consulta la documentazione di MySQL.

    • Sfrutta al meglio l'indice

    Quando utilizzi un indice a più colonne, controlla le colonne key_len per verificare se l'indice viene sfruttato completamente per filtrare i record. Le colonne più a sinistra devono essere confronti di uguaglianza e l'indice può essere utilizzato fino alla prima condizione di intervallo inclusa.

    • Utilizzare i suggerimenti dello strumento per l'ottimizzazione

    Un altro modo per assicurarti l'indice corretto è utilizzare l'indice di suggerimento e l'suggerimento per l'ordine di unione delle tabelle.

  8. Evita un lungo elenco della cronologia con READ COMMITTED

    L'elenco della cronologia è l'elenco delle transazioni non eliminate nel tablespace di annullamento. Il livello di isolamento predefinito di una transazione è REPEATABLE READ, che richiede che una transazione legga lo stesso snapshot per tutta la sua durata. Di conseguenza, una query SELECT blocca l'eliminazione definitiva dei record del log di annullamento creati dall'inizio della query (o della transazione). Un elenco della cronologia lungo rallenta quindi le prestazioni delle query. Un modo per evitare di creare un elenco di cronologia lungo è modificare il livello di isolamento delle transazioni in LETTURA ACCONTATA. Con READ COMMITTED, non è più necessario mantenere l'elenco della cronologia per una visualizzazione di lettura coerente. Puoi modificare il livello di isolamento delle transazioni a livello globale per tutte le sessioni, per una singola sessione o per la singola transazione successiva. Per saperne di più, consulta la documentazione di MySQL.

  9. Ottimizzare la configurazione del server

    C'è molto da dire sulla configurazione del server. Sebbene la storia completa non rientri nell'ambito di questo documento, vale la pena ricordare che il server registra anche varie variabili di stato che forniscono suggerimenti sull'efficacia delle configurazioni correlate. Ad esempio:

    • Modifica thread_cache_size se Threads_created/Connections è grande. Una cache dei thread adeguata ridurrebbe il tempo di creazione dei thread e agevolerebbe il carico di lavoro altamente concorrente.
    • Modifica table_open_cache se Table_open_cache_misses/Table_open_cache_hits non è banale. La presenza di tabelle nella cache delle tabelle consente di risparmiare tempo di esecuzione delle query e potrebbe fare la differenza in un ambiente ad alta concorrenza.

  10. Terminare una connessione indesiderata

    Puoi interrompere la query se sembra non valida o non è più richiesta. Per scoprire come identificare e terminare il thread MySQL, consulta Gestire le connessioni al database.

Infine, se l'utilizzo della CPU è ancora elevato e le query generano il traffico necessario, valuta la possibilità di aumentare le risorse della CPU nell'istanza per evitare arresti anomali o tempi di riposo del database.