Otimize a utilização elevada da CPU em instâncias

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

O recomendador de instâncias com aprovisionamento insuficiente analisa a utilização da CPU. Se os níveis de utilização da CPU estiverem iguais ou superiores a 95% durante um período significativo nos últimos 30 dias, o motor de recomendações envia-lhe um alerta e fornece estatísticas adicionais para ajudar a resolver o problema.

Este documento explica como rever e otimizar uma instância do Cloud SQL para MySQL se essa instância for identificada pelo recomendador de instâncias com aprovisionamento insuficiente como tendo uma utilização elevada da CPU.

Use as estatísticas de consultas para identificar consultas com um elevado consumo da CPU

As estatísticas de consultas ajudam a detetar, diagnosticar e evitar problemas de desempenho das consultas que possam causar um elevado consumo de CPU para bases de dados do Cloud SQL.

Use a auditoria da base de dados MySQL

Use a auditoria da base de dados MySQL para ver a memória e o consumo da sua instância.

Recomendações

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

  1. Consulte Threads_running e Threads_connected

    Use a seguinte consulta para ver o número de discussões ativas:

    > SHOW STATUS like 'Threads_%';

    Threads_running é um subconjunto de Threads_connected. Os restantes threads estão inativos. Um aumento no Threads_running contribui para um aumento na utilização da CPU. É recomendável verificar o que está a ser executado nesses threads.

  2. Verifique os estados das consultas

    Execute o comando SHOW PROCESSLIST para ver as consultas em curso. Devolve todas as threads ligadas por ordem e a respetiva declaração SQL em execução ativa.

    mysql> SHOW [FULL] PROCESSLIST;

    Preste atenção às colunas de estado e duração. Verifique se existem muitas consultas bloqueadas no mesmo estado.

    • Se muitos threads apresentarem Updating, pode haver contenção de bloqueio de registo. Consulte o passo seguinte.
    • Se muitos threads apresentarem Waiting para o bloqueio de metadados da tabela, verifique a consulta para saber a tabela e, em seguida, procure um DDL (como ALTER TABLE) que possa manter o bloqueio de metadados. Uma DDL também pode estar à espera de um bloqueio de metadados da tabela se uma consulta inicial, como um SELECT query de execução prolongada, a estiver a reter.

  3. Verifique a existência de contestações de bloqueio de registos

    Quando as transações retêm bloqueios em registos de índice populares, bloqueiam outras transações que pedem os mesmos bloqueios. Isto pode gerar um efeito em cadeia e fazer com que vários pedidos fiquem bloqueados, bem como aumentar o valor de Threads_running. Para diagnosticar a contenção de bloqueios, use a tabela information_schema.innodb_lock_waits.

    A consulta seguinte lista cada transação de bloqueio e os números 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 um único DML grande como muitos DMLs pequenos simultâneos podem causar contestações de bloqueio de linhas. Pode otimizar esta situação do lado da aplicação através dos seguintes passos:

    • Evite transações longas, uma vez que os bloqueios de linhas são mantidos até ao fim da transação.
    • Divida um único DML grande em DMLs mais pequenos.
    • Agrupe uma única linha DML em pequenos blocos.
    • Minimize a contenção entre threads; por exemplo, se o código da aplicação usar um conjunto de ligações, atribua um intervalo de IDs ao mesmo thread.

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

    • Usar SHOW ENGINE INNODB STATUS

      Na secção TRANSAÇÕES, pode ver todas as transações em aberto ordenadas da mais antiga para a mais 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

      Comece pelas transações mais antigas e encontre respostas às seguintes perguntas:

      • Há quanto tempo é que estas transações estão em execução?
      • Quantas estruturas de bloqueio e bloqueios de linhas estão presentes?
      • Quantas entradas de registo de anulação existem?
      • Quais são os anfitriões e os utilizadores que estão a estabelecer ligação?
      • Qual é a declaração SQL em curso?

    • Usar information_schema.innodb_trx

      Se SHOW ENGINE INNODB STATUS tiver sido truncado, uma forma 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 declarações de execução prolongada atuais, pode decidir parar estas transações para reduzir a pressão sobre o servidor ou aguardar que as transações críticas sejam concluídas. Se as transações mais antigas não apresentarem atividades, avance para o passo seguinte para encontrar o histórico de transações.

  5. Verifique as declarações SQL das transações de execução prolongada

    • Usar performance_schema

      Para usar o performance_schema, tem de o ativar primeiro. É uma alteração que requer o reinício de uma instância. Depois de performance_schema estar 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 ativadas, ative-as:

      UPDATE setup_instruments SET ENABLED = 'YES', timed = 'YES' WHERE NAME LIKE 'statement/%';
UPDATE setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE 'events_statements%';

      Por predefinição, cada thread mantém os últimos 10 eventos definidos por performance_schema_events_statements_history_size. Geralmente, são suficientes para localizar a transação no código da aplicação. 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;

    • Use o registo de consultas lentas

      Para a depuração, pode capturar todas as consultas que demoraram mais de N segundos no registo de consultas lentas. Pode ativar os registos de consultas lentas editando as definições da instância na página da instância daGoogle Cloud consola ou gcloud CLI e, em seguida, ver os registos através do visualizador de registos naGoogle Cloud consola ou gloud CLI.

  6. Verifique a contenção de semáforos

    Num ambiente concorrente, o mutex e o bloqueio de leitura/escrita em recursos partilhados podem ser o ponto de contenção, o que abranda o desempenho do servidor. Além disso, se o tempo de espera do semáforo for superior a 600 segundos, o sistema pode falhar para sair do impasse.

    Para ver a contenção de semáforos, 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
...

    Com cada espera de semáforo, a primeira linha mostra a thread que está à espera, o semáforo específico e o tempo que esperou. Se houver esperas de semáforo frequentes quando executa repetidamente SHOW ENGINE INNODB STATUS, especialmente esperas de mais de alguns segundos, significa que o sistema está a deparar-se com gargalos de concorrência.

    Existem 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 hash adaptativa pode ser a origem da contenção. Experimente desativá-lo através da Google Cloud consola ougcloud CLI.

  7. Otimize as consultas longas SELECT

    Primeiro, reveja a consulta. Identificar o objetivo da consulta e a melhor forma de obter os resultados. O melhor plano de consulta é o 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 o resultado e avaliar a eficiência da consulta.

    • Use o índice certo

    Verifique a coluna da chave para ver se é usado o índice esperado. Caso contrário, atualize as estatísticas do índice:

    mysql> analyze table <table_name>

    Aumentar o número de páginas de amostra usadas para calcular as estatísticas do índice. Para saber mais, consulte a documentação do MySQL.

    • Tire o máximo partido do índice

    Quando usar um índice de várias colunas, verifique as colunas key_len para ver se o índice é totalmente usado para filtrar os registos. As colunas mais à esquerda têm de ser comparações iguais, e o índice pode ser usado até à primeira condição de intervalo, inclusive.

    • Use sugestões do otimizador

    Outra forma de garantir que é usado o índice correto é aplicar a sugestão de índice e a sugestão para a ordem de junção de tabelas.

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

    A lista de histórico é a lista de transações não anuladas no espaço de tabelas de anulação. O nível de isolamento predefinido de uma transação é REPEATABLE READ, que requer que uma transação leia a mesma imagem instantânea durante toda a sua duração. Assim, uma consulta SELECT bloqueia a limpeza dos registos do histórico de anulação feitos desde que a consulta (ou a transação) foi iniciada. Uma lista de histórico longa retarda o desempenho das consultas. Uma forma de evitar a criação de uma longa lista de histórico é alterar o nível de isolamento da transação para READ COMMITTED. Com o READ COMMITTED, já não é necessário manter a lista de histórico para uma vista de leitura consistente. 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 única. Para saber mais, consulte a documentação do MySQL.

  9. Ajuste a configuração do servidor

    Há muito a dizer sobre a configuração do servidor. Embora a história completa esteja fora do âmbito deste documento, vale a pena mencionar que o servidor também comunica várias variáveis de estado que dão indicações sobre o desempenho das configurações relacionadas. Por exemplo:

    • Ajuste thread_cache_size se Threads_created/Connections for grande. Uma cache de threads adequada reduz o tempo de criação de threads e ajuda a carga de trabalho altamente concorrente.
    • Ajuste table_open_cache se Table_open_cache_misses/Table_open_cache_hits não for trivial. Ter tabelas na cache de tabelas poupa tempo de execução de consultas e pode fazer a diferença num ambiente altamente concorrencial.

  10. Termine uma associação indesejada

    Pode parar a consulta se esta parecer inválida ou já não for necessária. Para saber como identificar e terminar a discussão do MySQL, consulte o artigo Faça a gestão das ligações à base de dados.

Por último, se a utilização da CPU continuar elevada e as consultas formarem tráfego necessário, considere aumentar os recursos da CPU na sua instância para evitar falhas ou indisponibilidade da base de dados.

O que se segue?