Ottimizza l'utilizzo elevato della CPU nelle istanze

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

Il motore per suggerimenti 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 motore per suggerimenti ti avvisa e fornisce ulteriori insight per aiutarti a risolvere il problema.

Questo documento spiega come esaminare e ottimizzare un'istanza Cloud SQL per MySQL se viene identificata dal motore per suggerimenti istanze con provisioning insufficiente della CPU.

Suggerimenti

L'utilizzo della CPU aumenta proporzionalmente in base al carico di lavoro. Per ridurre l'utilizzo della CPU, controlla le query in esecuzione e ottimizzale. Di seguito sono riportati alcuni passaggi per verificare il consumo della CPU.

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. È una buona idea verificare cosa è in esecuzione nei thread.
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 attualmente in esecuzione.
```
mysql> SHOW [FULL] PROCESSLIST;
```
Presta attenzione alle colonne relative a stato e durata. Controlla se ci sono molte query bloccate sullo stesso stato.
- Se molti thread mostrano Updating, potrebbe esserci un conflitto tra il blocco del record. Vedi il passaggio successivo.
- Se in molti thread viene visualizzato 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 è in possesso di una query in anteprima, ad esempio un SELECT query a lunga esecuzione.
Verificare la contesa del blocco del record

Quando le transazioni bloccano i blocchi sui record degli indici più diffusi, bloccano le altre transazioni che richiedono gli stessi blocchi. Ciò potrebbe avere un effetto concatenato e causare il blocco di alcune 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 singolo DML di grandi dimensioni sia molti DML di piccole dimensioni simultanei potrebbero causare contese del blocco delle righe. Puoi ottimizzare questo processo dal lato dell'applicazione seguendo questa procedura:
- Evita transazioni lunghe perché i blocchi di riga vengono conservati fino al termine della transazione.
- Interrompi un singolo DML di grandi dimensioni in DML di dimensioni minime.
- Raggruppa un DML a riga singola 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 ID allo stesso thread.
Trovare le transazioni a lunga esecuzione
- Utilizza SHOW ENGINE INNODB STATUS
  
  Nella sezione TRANSAZIONI puoi visualizzare tutte le transazioni aperte ordinate a partire da quella 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 meno recenti e trova le risposte alle seguenti domande:
  - Da quanto tempo sono in corso queste transazioni?
  - Quanti struct di blocco e blocchi di riga sono presenti?
  - Quante voci di log di annullamento sono disponibili?
  - 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 lunga esecuzione attuali, puoi decidere di interromperle per ridurre la pressione sul server o attendere il completamento delle transazioni critiche. Se le transazioni precedenti non mostrano alcuna attività, vai al passaggio successivo per trovare la cronologia delle transazioni.
Controlla le istruzioni SQL delle transazioni a lunga esecuzione
- Utilizza performance_schema
  
  Per utilizzare performance_schema, devi prima attivarla. È una modifica che richiede il riavvio dell'istanza. Dopo l'attivazione di performance_schema, verifica che gli strumenti e i consumatori 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 manterrà gli ultimi 10 eventi definiti da performance_schema_events_statements_history_size. Questi sono in genere 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;
```
- Usa log di query lento
  
  Per il debug, puoi acquisire nel log delle query lente tutte le query che hanno richiesto più di N di secondi. Puoi abilitare i log delle query lenti modificando le impostazioni dell'istanza nella pagina dell'istanza della console Google Cloud o su gcloud CLI, quindi visualizzare i log utilizzando il visualizzatore log nella console Google Cloud o gloud CLI.
Controllare la contesa dei semafori

In un ambiente simultaneo, il blocco di mutex e lettura/scrittura sulle risorse condivise potrebbe essere il punto di contesa, 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 dallo stallo.

Per visualizzare la contesa dei semafori, utilizza il comando seguente:
```
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, la prima riga mostra il thread in attesa, il semaforo specifico e il tempo di attesa. Se si verificano frequenti attese di semaforo durante l'esecuzione ripetuta di SHOW ENGINE INNODB STATUS, in particolare per più di qualche secondo, significa che il sistema è in esecuzione in colli di bottiglia della contemporaneità.

Esistono diversi punti di contesa a seconda dei carichi di lavoro e delle configurazioni.

Quando i semafori sono spesso su btr0sea.c, l'indicizzazione dell'hash adattivo potrebbe essere la fonte di un conflitto. Prova a disattivarlo utilizzando la console Google Cloud o gcloud CLI.
Ottimizza le query SELECT lunghe

Innanzitutto, esamina la query. Identifica l'obiettivo 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>;
```
Per informazioni su come interpretare l'output e valutare l'efficienza delle query, consulta la documentazione di MySQL.
- Usa l'indice corretto
Controlla la colonna della chiave per verificare se è stato 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 ulteriori informazioni, consulta la documentazione di MySQL.
- Sfrutta al massimo l'indice
Quando utilizzi un indice a più colonne, controlla le colonne key_len per vedere se l'indice viene sfruttato al meglio per filtrare i record. Le colonne più a sinistra devono essere confronti uguali e l'indice può essere utilizzato fino alla condizione del primo intervallo inclusa.
- Utilizzare i suggerimenti dello strumento di ottimizzazione
Un altro modo per assicurarti che l'indice sia corretto consiste nell'utilizzare il suggerimento indice e il suggerimento per l'ordine di unione delle tabelle.
Evita un lungo elenco di 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 una transazione per leggere lo stesso snapshot per tutta la durata. Di conseguenza, una query SELECT blocca l'eliminazione definitiva dei record di log di annullamento creati dall'inizio della query (o della transazione). Un elenco cronologico lungo rallenta quindi le prestazioni della 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 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 ulteriori informazioni, consulta la documentazione di MySQL.
Ottimizza la configurazione del server

C'è molto da dire sulla configurazione del server. Anche se la storia completa esula dall'ambito di questo documento, vale la pena ricordare che il server segnala anche varie variabili di stato che forniscono indizi sull'efficacia delle configurazioni correlate. Ad esempio:
- Modifica thread_cache_size se Threads_created/Connections è grande. Una cache dei thread adeguata ridurrebbe i tempi di creazione dei thread e aiuterà un carico di lavoro altamente simultaneo.
- 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 ridurre i tempi di esecuzione delle query e può fare la differenza in un ambiente altamente simultaneo.
Terminare una connessione indesiderata

Puoi interromperla se non è più valida o se non è più necessaria. Per informazioni su come identificare e terminare il thread MySQL, vedi 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 nell'istanza per evitare arresti anomali o tempi di inattività del database.