Ottimizza l'utilizzo elevato della CPU nelle istanze

L'elevato utilizzo della CPU in un'istanza può essere causato da vari motivi come l'aumento dei carichi di lavoro, transazioni complesse, query lente e 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 rivedere e ottimizzare un'istanza Cloud SQL per MySQL se questa viene identificata dal motore per suggerimenti delle istanze con underprovisioning in quanto ha un utilizzo elevato 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. Di seguito sono riportati 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. È una buona idea controllare cosa viene eseguito in questi thread.

  2. Controlla gli stati delle query

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

    mysql> SHOW [FULL] PROCESSLIST;

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

    • Se molti thread mostrano Updating, potrebbe esserci un conflitto di blocco del record. Vedi il passaggio successivo.
    • Se molti thread mostrano Waiting per il blocco dei metadati della tabella, controlla la query per conoscere la tabella, quindi cerca un DDL (ad esempio ALTER TABLE) che potrebbe contenere il blocco dei metadati. Un DDL potrebbe anche essere in attesa del blocco dei metadati della tabella se una query precedente, ad esempio un SELECT query a lunga esecuzione, lo sta mantenendo.

  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 più piccole.
    • Raggruppa in piccoli blocchi un DML di una singola riga.
    • Ridurre al minimo i conflitti tra i thread; Ad esempio, se il codice dell'applicazione usa un pool di connessioni, assegnare un intervallo di ID allo stesso thread.

  4. Trovare transazioni a lunga esecuzione

    • Utilizza SHOW ENGINE INNODB STATUS

      Nella sezione TRANSAZIONI, puoi vedere tutte le transazioni aperte ordinate dal più recente al meno recente. 

      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?
      • Quanti blocchi struct e righe sono presenti?
      • Quante voci di log di annullamento sono presenti?
      • Cosa sono gli host e gli utenti 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 è utilizza 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 le attuali istruzioni a lunga esecuzione, puoi decidere di interromperle per ridurre la pressione sul server o attendere il completamento delle transazioni più importanti. Se le transazioni precedenti non mostrano alcuna attività, vai al passaggio successivo per trovare la cronologia transazioni.

  5. Controllare le istruzioni SQL delle transazioni a lunga esecuzione

    • Utilizza performance_schema

      Per usare la funzionalità performance_schema, devi prima attivarla. È una modifica che richiede il riavvio dell'istanza. Dopo aver attivato performance_schema, controlla che gli strumenti e i consumer siano abilitati:

      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, che è processlist_id, esegui 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 di gcloud CLI e poi visualizzare i log utilizzando il visualizzatore dei log nella console Google Cloud o in 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 stallo.

    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
...

    Ad ogni attesa di un semaforo, la prima riga mostra il thread in attesa, il semaforo specifico e il tempo di attesa. Se si verificano frequenti tempi di attesa durante l'esecuzione ripetuta di SHOW ENGINE INNODB STATUS, in particolare per alcuni secondi, significa che nel sistema si stanno verificando colli di bottiglia relativi alla contemporaneità.

    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 disabilitarlo utilizzando la console Google Cloud o gcloud CLI.

  7. Ottimizzare le query SELECT lunghe

    Innanzitutto, rivedi la query. Identifica l'obiettivo della query e il modo migliore per ottenere i risultati. Il piano di query migliore è quello che minimizza l'accesso ai dati.

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

    Per informazioni su come interpretare l'output e valutare l'efficienza della query, consulta la documentazione di MySQL.

    • Utilizza l'indice corretto

    Controlla la colonna delle chiavi per verificare 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 uguali e l'indice può essere utilizzato fino alla prima condizione di intervallo inclusa.

    • Utilizza i suggerimenti di 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 di cronologie 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 di 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 conservare 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 successiva singola transazione. 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 segnala anche variabili di stato che forniscono suggerimenti sull'efficacia delle configurazioni correlate. Ad esempio:

    • Regola 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 salva tempi di esecuzione delle query e questo potrebbe fare la differenza in un ambiente altamente simultaneo.

  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 di database.

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