Otimize o alto uso da CPU em 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 execução.

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

Este documento explica como revisar e otimizar uma instância do Cloud SQL para MySQL se essa instância for identificada pelo recomendador de instância subprovisionada como tendo alta utilização de CPU.

Recomendações

A utilização da CPU aumenta proporcionalmente à carga de trabalho. Para reduzir a utilização da CPU, verifique as consultas em execução e otimize-as. Aqui estão alguns passos para verificar o consumo de CPU.

  1. Verifique Threads_running e Threads_connected

    Use a seguinte consulta para ver o número de threads ativos:

    > SHOW STATUS like 'Threads_%';

    Threads_running é um subconjunto de Threads_connected . As demais threads estão ociosas. Um aumento em Threads_running contribuiria para um aumento no uso da CPU. É uma boa ideia verificar o que está sendo executado nessas threads.

  2. Verifique os estados da consulta

    Execute o comando SHOW PROCESSLIST para visualizar as consultas em andamento. Ele retorna todos os threads conectados em ordem e suas instruções SQL em execução.

    mysql> SHOW [FULL] PROCESSLIST;

    Preste atenção às colunas de estado e duração. Verifique se há muitas consultas travadas no mesmo estado.

    • Se muitas threads exibirem Updating , pode haver contenção de bloqueio de registro. Veja a próxima etapa.
    • Se muitas threads exibirem a mensagem " Waiting bloqueio de metadados da tabela", verifique a consulta para identificar a tabela e, em seguida, procure uma DDL (como ALTER TABLE ) que possa conter o bloqueio de metadados. Uma DDL também pode estar aguardando bloqueio de metadados da tabela se uma consulta anterior, como uma SELECT query de longa duração, a estiver contendo.

  3. Verifique se há contenção de bloqueio de registro

    Quando transações mantêm bloqueios em registros de índice populares, elas bloqueiam outras transações que solicitam os mesmos bloqueios. Isso pode gerar um efeito encadeado e causar o travamento de várias solicitações e um aumento no valor de Threads_running . Para diagnosticar a contenção de bloqueios, 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;

    Tanto uma única DML grande quanto muitas DMLs pequenas simultâneas podem causar contenções de bloqueio de linha. Você pode otimizar isso do lado do aplicativo seguindo os seguintes passos:

    • Evite transações longas porque os bloqueios de linha são mantidos até que a transação termine.
    • Divida um único DML grande em DMLs pequenos.
    • Divida uma única linha DML em pequenos pedaços.
    • Minimize a contenção entre threads; por exemplo, se o código do aplicativo usar um pool de conexões, atribua um intervalo de ID ao mesmo thread.

  4. Encontre transações de longa duração

    • Use SHOW ENGINE INNODB STATUS

      Na seção TRANSAÇÕES , você pode ver todas as transações abertas ordenadas da mais antiga para a 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 andamento?
      • Quantas estruturas de bloqueio e bloqueios de linha estão presentes?
      • Quantas entradas de log de desfazer existem?
      • Quais são os hosts e usuários de conexão?
      • 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 os extratos de execução longa atuais, você pode optar por interrompê-las para reduzir a pressão sobre o servidor ou aguardar a conclusão das transações críticas. 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.

  5. Verifique as instruções SQL das transações de longa duração

    • Usar performance_schema

      Para usar performance_schema , você precisa ativá-lo primeiro. É uma alteração que requer a reinicialização da instância. Após ativar performance_schema , verifique se os instrumentos e consumidores estão habilitados:

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

      Se não estiverem habilitados, habilite-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 thread manteria os últimos 10 eventos definidos por performance_schema_events_statements_history_size . Geralmente, eles são suficientes para localizar a transação no código do aplicativo. Este 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 log de consulta lenta

      Para depuração, você pode capturar todas as consultas que levaram mais de N segundos no log de consultas lentas. Você pode habilitar os logs de consultas lentas editando as configurações da instância na página da instância.Google Cloud console ou gcloud CLI e, em seguida, veja os logs usando o visualizador de logs noGoogle Cloud console ou gloud CLI .

  6. Verifique a contenção do semáforo

    Em um ambiente concorrente, mutex e travas de leitura/escrita em recursos compartilhados podem ser o ponto de contenção, o que reduz o desempenho do servidor. Além disso, se o tempo de espera do semáforo for superior a 600 segundos, o sistema pode travar para sair do estado de espera.

    Para visualizar a contenção do semáforo, 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
...

    A cada espera de semáforo, a primeira linha mostra a thread que está aguardando, o semáforo específico e o tempo de espera. Se houver esperas frequentes de semáforo ao executar repetidamente SHOW ENGINE INNODB STATUS , especialmente esperas que duram 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 semáforos estão frequentemente em btr0sea.c, a indexação de hash adaptável pode ser a fonte de contenção. Tente desativá-la usando Google Cloud console ou gcloud CLI .

  7. Otimize consultas SELECT longas

    Primeiro, revise a consulta. Identifique o objetivo da consulta e a melhor maneira de obter 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-chave para ver se o índice esperado está sendo 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 .

    • Aproveite ao máximo o índice

    Ao usar um índice com várias colunas, verifique as colunas key_len para ver se o índice está totalmente aproveitado 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, inclusive.

    • Use dicas do otimizador

    Outra maneira de garantir o índice correto é usar a dica de índice e a dica para a ordem de junção de tabela .

  8. Evite uma longa lista de histórico com READ COMMITTED

    A lista de histórico é a lista de transações não purgadas no tablespace de undo. O nível de isolamento padrão de uma transação é REPEATABLE READ , que requer que uma transação leia o mesmo snapshot durante toda a sua duração. Portanto, uma consulta SELECT bloqueia a purga de registros de log de undo que foram feitos desde o início da consulta (ou transação). Uma lista de histórico longa, portanto, reduz o desempenho da consulta. Uma maneira de evitar a criação de uma lista de histórico longa é alterar o nível de isolamento da transação para READ COMMITTED . Com READ COMMITTED , não há mais a necessidade de manter a lista de histórico para uma visualização de leitura consistente. Você pode alterar o nível de isolamento da 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 .

  9. 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 mencionar que o servidor também relata diversas 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 threads adequado reduziria o tempo de criação de threads e ajudaria na carga de trabalho altamente simultânea.
    • Ajuste table_open_cache se Table_open_cache_misses/Table_open_cache_hits não for trivial. Ter tabelas no cache de tabelas economiza tempo de execução de consultas e pode fazer a diferença em um ambiente altamente concorrente.

  10. Encerrar uma conexão indesejada

    Você pode interromper a consulta se ela parecer inválida ou se não for mais necessária. Para saber como identificar e encerrar a thread do MySQL, consulte Gerenciar conexões de banco de dados .

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

O que vem a seguir