Otimizar a alta utilização da CPU nas instâncias

A alta utilização da CPU em uma instância pode ser causada por vários motivos, como aumento de cargas de trabalho, transações pesadas, consultas lentas e transações de longa duração.

O recomendador de instância desprovisionado analisa a utilização da CPU. Se os níveis de utilização da CPU estiverem em até ou acima de 95% por um período significativo nos últimos 30 dias, o recomendador alerta você e fornece mais insights para ajudar a resolver o problema.

Neste documento, você verá como analisar e otimizar uma instância do Cloud SQL para MySQL se ela for identificada pelo recomendador de instâncias subprovisionadas como tendo alta utilização da CPU.

Recomendações

O uso da CPU aumenta proporcionalmente com a carga de trabalho. Para reduzir o uso da CPU, verifique e otimize as consultas em execução. Veja algumas etapas para verificar o consumo da CPU.

Verificar Threads_running e Threads_connected

Use a consulta a seguir para ver o número de linhas de execução ativas:
```
> SHOW STATUS like 'Threads_%';
```
Threads_running é um subconjunto de Threads_connected. As demais linhas estão inativas. Um aumento em Threads_running contribuiria para um aumento no uso da CPU. É uma boa ideia conferir o que está sendo executado nessas linhas de execução.
Verificar estados da consulta

Execute o comando SHOW PROCESSLIST para ver as consultas em andamento. Ela retorna todas as linhas de execução conectadas em ordem e a instrução SQL em execução no momento.
```
mysql> SHOW [FULL] PROCESSLIST;
```
Preste atenção nas colunas de estado e duração. Verifique se há muitas consultas travadas no mesmo estado.
- Se muitas linhas de execução mostrarem Updating, pode haver contenção de bloqueio de registro. Para isso, veja a próxima etapa.
- Se muitas linhas de execução mostrarem Waiting para o bloqueio de metadados da tabela, verifique a consulta para saber a tabela. Em seguida, procure uma DDL (como ALTER TABLE) que possa conter o bloqueio de metadados. Uma DDL também pode aguardar o bloqueio de metadados da tabela se estiver sendo mantida por uma consulta inicial, como uma SELECT query de longa duração.
Verificar a contenção de bloqueio de registro

Quando as transações mantêm bloqueios em registros de índice conhecidos, elas bloqueiam outras transações que solicitam os mesmos bloqueios. Isso pode encadear um efeito e fazer com que várias solicitações fiquem travadas e aumentem o valor de Threads_running. Para diagnosticar a contenção de bloqueio, use a tabela information_schema.innodb_lock_waits.

A consulta a seguir lista cada transação de bloqueio e o número de transações bloqueadas associadas.
```
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;
```
Uma única DML grande e muitas DMLs pequenas simultâneas podem causar contenções de bloqueio de linhas. É possível otimizar esse processo no aplicativo seguindo estas etapas:
- Evite transações longas porque os bloqueios de linha são mantidos até o fim da transação.
- Separe uma grande DML em pequenas DMLs.
- Agrupe em lote uma única DML em pequenos blocos.
- Reduzir a contenção entre linhas de execução. Por exemplo, se o código do aplicativo usar um pool de conexões, atribua um intervalo de IDs à mesma linha de execução.
Encontrar transações de longa duração
- Usar SHOW ENGINE INNODB STATUS
  
  Na seção TRANSAÇÕES, é possível ver todas as transações abertas ordenadas da mais antiga à mais antiga.
```
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
```
  Comece com as transações mais antigas e encontre respostas para as seguintes perguntas:
  - Há quanto tempo essas transações estão em execução?
  - Quantas estruturas de bloqueio e de bloqueio de linha estão presentes?
  - Quantas entradas de registro desfeitas há?
  - Quais são os hosts e usuários que se conectam?
  - Qual é a instrução SQL em andamento?
- Usar information_schema.innodb_trx
  
  Se SHOW ENGINE INNODB STATUS estiver truncado, uma maneira alternativa de examinar todas as transações abertas é usar a tabela 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 as transações mostrarem as instruções de longa duração atuais, será possível interromper essas transações para reduzir a pressão no servidor ou esperar que as transações críticas sejam concluídas. Se as transações mais antigas não mostrarem nenhuma atividade, vá para a próxima etapa para encontrar o histórico de transações.
Verificar instruções SQL das transações de longa duração
- Usar performance_schema
  
  Para usar o app performance_schema, ele precisa ser ativado primeiro. Essa é uma alteração que requer que uma instância seja reiniciada. Depois que a performance_schema estiver ativada, verifique se os instrumentos e consumidores estão ativados:
```
SELECT * FROM setup_consumers where name like 'events_statements_history';
SELECT * FROM setup_instruments where name like 'statement/sql/%';
```
  Se eles não estiverem ativados, faça o seguinte:
```
UPDATE setup_instruments SET ENABLED = 'YES', timed = 'YES' WHERE NAME LIKE 'statement/%';
UPDATE setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE 'events_statements%';
```
  Por padrão, cada linha de execução mantém os últimos 10 eventos definidos por performance_schema_events_statements_history_size. Em geral, essas informações são suficientes para localizar a transação no código do aplicativo. Esse parâmetro não é dinâmico.
  
  Com mysql thread id, que é processlist_id, consulte os eventos do histórico:
```
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;
```
- Usar registro de consulta lenta
  
  Para depuração, é possível capturar todas as consultas que levaram mais de N número de segundos no registro de consultas lentas. Para ativar os registros de consulta lenta, edite as configurações da instância na página da instância do console do Google Cloud ou gcloud CLI e veja os registros usando o visualizador de registros no console do Google Cloud ou em gloud CLI.
Verificar a contenção de semáforos

Em um ambiente simultâneo, o mutex e a trava de leitura/gravação nos recursos compartilhados podem ser o ponto de contenção, o que atrasa o desempenho do servidor. Além disso, se o tempo de espera do semaphore for maior que 600 segundos, o sistema poderá falhar para sair da parada.

Para ver a contenção de semaphore, use o seguinte 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
...
```
Para cada espera de semaphore, a primeira linha mostra a linha de execução que está em espera, o semaphore específico e o tempo de espera. Se houver um mapeamento semaphore frequente aguardando repetidamente a execução de SHOW ENGINE INNODB STATUS, especialmente aqueles que aguardam mais de alguns segundos, significa que o sistema está enfrentando gargalos de simultaneidade.

Há diferentes pontos de contenção em diferentes cargas de trabalho e configurações.

Quando os semaphores costumam estar em btr0sea.c, a indexação de hash adaptável pode ser a origem da contenção. Tente desativá-lo usando o console do Google Cloud ou gcloud CLI.
Otimizar consultas SELECT longas

Primeiro, revise a consulta. Identifique a meta da consulta e a melhor maneira de conseguir os resultados. O melhor plano de consulta é aquele que minimiza o acesso aos dados.
- Verifique o plano de execução da consulta:
```
mysql> EXPLAIN <the query>;
```
Consulte a documentação do MySQL para saber como interpretar a saída e avaliar a eficiência da consulta.
- Use o índice correto
Verifique a coluna da chave para ver se o índice esperado é usado. Caso contrário, atualize as estatísticas do índice:
```
mysql> analyze table <table_name>
```
Aumente o número de páginas de amostra usadas para calcular estatísticas de índice. Para saber mais, consulte a documentação do MySQL.
- Use todo o índice
Ao usar um índice de várias colunas, verifique as colunas key_len para ver se ele é totalmente aproveitado para filtrar os registros. As colunas mais à esquerda precisam ser comparações iguais, e o índice pode ser usado até e incluindo a condição do primeiro intervalo.
- Usar as dicas do otimizador
Outra maneira de garantir o índice correto é usar dica de índice e dica para a ordem de mesclagem de tabelas.
Evite uma longa lista de histórico com READ COMMITTED

A lista de histórico é a lista de transações não limpas no espaço de tabela de desfazer. O nível de isolamento padrão de uma transação é REPEATABLE READ, que exige que uma transação leia o mesmo snapshot durante toda a duração. Portanto, uma consulta SELECT bloqueia a limpeza de registros de desfazer que foram feitos desde o início da consulta (ou transação). Portanto, uma lista de histórico longa diminui o desempenho da consulta. Uma maneira de evitar a criação de uma longa lista de histórico é mudar o nível de isolamento da transação para READ COMMITTED. Com READ COMMITTED, não é mais necessário manter a lista de histórico para uma visualização de leitura consistente. É possível alterar o nível de isolamento de transação globalmente para todas as sessões, para uma única sessão ou para a próxima transação. Para saber mais, consulte a documentação do MySQL.
Ajustar a configuração do servidor

Há muito a dizer sobre a configuração do servidor. Embora a história completa esteja além do escopo deste documento, vale a pena mencionar que o servidor também informa várias variáveis de status que dão dicas sobre o desempenho das configurações relacionadas. Exemplo:
- Ajuste thread_cache_size se Threads_created/Connections for grande. Um cache de linha de execução adequado reduziria o tempo de criação de linha de execução e ajudaria com cargas de trabalho altamente simultâneas.
- Ajuste table_open_cache se Table_open_cache_misses/Table_open_cache_hits não for trivial. Ter tabelas no cache da tabela economiza o tempo de execução da consulta e pode fazer a diferença em um ambiente altamente simultâneo.
Encerrar uma conexão indesejada

A consulta pode ser interrompida se parecer inválida ou não for mais necessária. Para saber como identificar e encerrar a linha de execução do MySQL, consulte Gerenciar conexões de banco de dados.

Por fim, se o uso da CPU ainda estiver alto e as consultas formarem o tráfego necessário, considere aumentar os recursos da CPU na instância para evitar falhas ou inatividade do banco de dados.