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
eThreads_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 deThreads_connected
. O restante das linhas de execução está inativo. Um aumento noThreads_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 (comoALTER 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 umaSELECT query
de execução longa, estiver retendo-a.
- Se muitas linhas de execução mostrarem
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 tabelainformation_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 tabelainformation_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 queperformance_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 dogcloud CLI
e confira os registros usando o visualizador de registros no console do Google Cloud ou nogloud 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
longasPrimeiro, 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. Exemplo:
- Ajuste
thread_cache_size
seThreads_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
seTable_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.
- Ajuste
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.