Ottimizza l'utilizzo elevato della CPU nelle istanze

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

Il motore per suggerimenti per istanze con provisioning insufficiente analizza l'utilizzo della CPU. Se i livelli di utilizzo della CPU sono pari o superiori al 95% per un periodo di tempo significativo negli ultimi 30 giorni, il sistema di suggerimenti ti avvisa e fornisce ulteriori approfondimenti per aiutarti a risolvere il problema.

Questo documento spiega come esaminare e ottimizzare un'istanza Cloud SQL per MySQL se questa istanza viene identificata dal suggeritore di istanze con provisioning insufficiente come avente 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. Ecco alcuni passaggi per controllare il consumo di 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. Gli altri thread sono inattivi. Un aumento di Threads_running contribuirebbe a un aumento dell'utilizzo della CPU. È consigliabile 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 connessi in ordine e l'istruzione SQL in esecuzione attiva.

    mysql> SHOW [FULL] PROCESSLIST;

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

    • Se molti thread mostrano Updating, potrebbe esserci un conflitto di 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'istruzione DDL (ad esempio ALTER TABLE) che potrebbe contenere il blocco dei metadati. Un'istruzione DDL potrebbe anche essere in attesa del blocco dei metadati della tabella se una query precedente, ad esempio un'istruzione SELECT query a lunga esecuzione, lo sta mantenendo.

  3. Controllare la contesa del blocco dei record

    Quando le transazioni bloccano i record di indice più utilizzati, bloccano altre transazioni che richiedono gli stessi blocchi. Ciò potrebbe causare un effetto a catena e bloccare un certo numero di richieste, nonché aumentare il valore di Threads_running. Per diagnosticare la contesa di 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 singolo DML di grandi dimensioni sia molti DML di piccole dimensioni simultanei potrebbero causare contese di blocco delle righe. Puoi ottimizzare questo aspetto dal lato dell'applicazione seguendo questi passaggi:

    • Evita transazioni lunghe perché i blocchi a livello di riga vengono mantenuti fino al termine della transazione.
    • Dividi una singola istruzione DML di grandi dimensioni in istruzioni DML più piccole.
    • Raggruppa una singola riga DML in piccoli blocchi.
    • Riduci al minimo la contesa 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 le transazioni a lunga esecuzione

    • Utilizza SHOW ENGINE INNODB STATUS

      Nella sezione TRANSAZIONI, puoi visualizzare 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 dalle transazioni più vecchie e trova le risposte alle seguenti domande:

      • Da quanto tempo vengono eseguite queste transazioni?
      • Quante struct di blocco e quanti blocchi di righe sono presenti?
      • Quante voci di log di annullamento sono presenti?
      • Che 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 è 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 le istruzioni a esecuzione prolungata correnti, puoi decidere di interrompere queste transazioni per ridurre il carico 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, verifica che gli strumenti e i consumatori siano attivi:

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

      Se non sono attivi, 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 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 consoleGoogle Cloud o di gcloud CLI e poi visualizzare i log utilizzando il visualizzatore log nella consoleGoogle Cloud o in gloud CLI.

  6. Controlla la contesa dei semafori

    In un ambiente simultaneo, il mutex e il latch di lettura/scrittura sulle risorse condivise potrebbero essere il punto di contesa, il che rallenta 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 semaforo, 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
...

    A ogni attesa del semaforo, la prima riga mostra il thread in attesa, il semaforo specifico e il tempo di attesa. Se si verificano attese frequenti del semaforo durante l'esecuzione ripetuta di SHOW ENGINE INNODB STATUS, in particolare attese di più di qualche secondo, significa che il sistema sta riscontrando colli di bottiglia di concorrenza.

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

    Quando i semafori si trovano spesso su btr0sea.c, l'indicizzazione hash adattiva potrebbe essere la fonte di contesa. Prova a disattivarlo utilizzando la console Google Cloud o gcloud CLI.

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

    • Utilizzare l'indice giusto

    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 campione utilizzate per calcolare le statistiche dell'indice. Per saperne di più, consulta la documentazione di MySQL.

    • Sfruttare al meglio l'indice

    Quando utilizzi un indice a più colonne, controlla le colonne key_len per verificare se l'indice viene sfruttato appieno 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 assicurarsi che venga utilizzato l'indice corretto è applicare l'hint per l'indice e l'hint 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 nello spazio delle tabelle 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. Pertanto, una query SELECT blocca l'eliminazione definitiva dei record del log di annullamento creati dall'inizio della query (o della transazione). Un lungo elenco della cronologia rallenta quindi le prestazioni delle query. Un modo per evitare di creare un lungo elenco della cronologia è modificare il livello di isolamento delle transazioni in READ COMMITTED. 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 singola transazione successiva. Per scoprire 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 varie variabili di stato che forniscono indicazioni sull'efficacia delle configurazioni correlate. Ad esempio:

    • Modifica thread_cache_size se Threads_created/Connections è grande. Una cache dei thread corretta ridurrebbe il tempo di creazione dei thread e aiuterebbe i carichi di lavoro con un'elevata concorrenza.
    • 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 può fare la differenza in un ambiente con un elevato livello di concorrenza.

  10. Terminare una connessione indesiderata

    Puoi interrompere la query se sembra non valida o non è più necessaria. 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 formano il traffico necessario, valuta la possibilità di aumentare le risorse della CPU nella tua istanza per evitare arresti anomali o tempi di inattività del database.

Passaggi successivi