Otimize o alto uso da CPU nas instâncias

O alto uso da CPU em uma instância pode ser causado 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âncias subprovisionadas analisa o uso da CPU. Se os níveis de uso 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 alto uso da CPU.

Recomendações

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

Verificar Threads_running e Threads_connected

Use a consulta a seguir para verificar o número de linhas de execução ativas:
```
> SHOW STATUS like 'Threads_%';
```
Threads_running é um subconjunto de Threads_connected. O restante das linhas de execução está inativo. Um aumento no Threads_running contribui para um aumento no uso da CPU. É recomendável verificar o que está sendo executado nas linhas de execução.
Verificar os estados da consulta

Execute o comando SHOW PROCESSLIST para verificar as consultas em andamento. Ele retorna todas as linhas conectadas em ordem e a instrução SQL em execução.
```
mysql> SHOW [FULL] PROCESSLIST;
```
Veja com atenção as colunas Estado e Duração. Verifique se há muitas consultas 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 identificar a tabela e procure um DDL (como ALTER TABLE) que possa manter o bloqueio de metadados. Uma DDL também pode estar aguardando o bloqueio de metadados da tabela se uma consulta inicial, como uma SELECT query de execução longa, estiver retendo-a.
Verificar a contenção de bloqueio de registro

Quando as transações mantêm bloqueios em registros de índice populares, elas bloqueiam outras transações que solicitam os mesmos bloqueios. Isso pode causar um efeito em cascata e fazer com que várias solicitações fiquem presas, além de aumentar 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 DML grande e muitas DMLs pequenas simultâneas podem causar contenções de bloqueio de linhas. É possível otimizá-las no aplicativo seguindo estas etapas:
- Evite transações longas, porque os bloqueios da linha são mantidos até o término da transação.
- Divida uma DML grande em DMLs menores.
- Agrupe uma DML de linha única 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, verifique todas as transações abertas da mais recente à 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 pelas 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 bloqueios de linha existem?
  - Quantas entradas de registro de reversão existem?
  - O que são os hosts e usuários conectados?
  - Qual é a instrução SQL em andamento?
- Usar information_schema.innodb_trx
  
  Se SHOW ENGINE INNODB STATUS foi 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 execução de longa duração atuais, interrompa essas transações para reduzir a pressão no servidor ou aguardar a conclusão das transações importantes. Se as transações mais antigas não estiverem mostrando atividades, siga para a próxima etapa para encontrar o histórico de transações.
Verificar as instruções SQL das transações de longa duração
- Usar performance_schema
  
  Para usar o performance_schema, primeiro é necessário ativá-lo. Essa é uma mudança que requer uma reinicialização da instância. Depois que performance_schema estiver ativado, verifique se os instrumentos e os consumidores estão ativados:
```
SELECT * FROM setup_consumers where name like 'events_statements_history';
SELECT * FROM setup_instruments where name like 'statement/sql/%';
```
  Se não estiverem, ative-os:
```
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. Geralmente, isso é suficiente para localizar a transação no código do aplicativo. Esse parâmetro não é dinâmico.
  
  Com o 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 o registro de consulta lenta
  
  Para depuração, é possível capturar todas as consultas que levaram mais de N 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 do gcloud CLI e confira os registros usando o visualizador de registros no console do Google Cloud ou no 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á pontos de contenção diferentes 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, analise a consulta. Identifique o objetivo da consulta e a melhor maneira de conseguir os resultados. O melhor plano de consulta é aquele que minimiza o acesso aos dados.
- Confira 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.
- Usar o índice certo
Verifique a coluna de chave para saber 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 o índice por completo
Ao usar um índice de várias colunas, verifique as colunas key_len para saber se o índice é aproveitado por completo para filtrar os registros. As colunas mais à esquerda precisam ser comparações iguais, e o índice pode ser usado até a primeira condição de intervalo.
- Use as dicas do otimizador
Outra maneira de garantir o índice correto é usar a dica de índice e a dica para a ordem de mesclagem de tabelas.
Evite uma lista de histórico longa com CONFIRMADO POR LEITURA

A lista de histórico é a lista de transações não excluídas permanentemente no tablespace de reversão. O nível de isolamento padrão de uma transação é LEITURA REPETÍVEL, que exige que uma transação leia o mesmo snapshot durante toda a duração. Portanto, uma consulta SELECT bloqueia a exclusão permanente de registros de reversão feitos desde o início da consulta (ou transação). Uma lista de histórico longa, portanto, diminui a performance da consulta. Uma maneira de evitar a criação de uma lista de histórico longa é mudar o nível de isolamento da transação para CONFIRMADO POR LEITURA. Com CONFIRMADO POR LEITURA, não é mais necessário manter a lista de histórico para uma visualização de leitura consistente. É possível mudar o nível de isolamento das transações 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

Existem muitas informações sobre a configuração do servidor. Embora a história completa não conste neste documento, é importante citar que o servidor também informa diferentes variáveis de status que dão dicas sobre a qualidade das configurações relacionadas. Por exemplo:
- Ajuste thread_cache_size se Threads_created/Connections for grande. Um cache de linha de execução adequado reduz o tempo de criação de linhas de execução e ajuda a carga de trabalho extremamente simultânea.
- 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 extremamente simultâneo.
Encerrar uma conexão indesejada

Você pode interromper a consulta se ela 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, aumente os recursos da CPU na instância para evitar falha ou inatividade do banco de dados.