Solucionar problemas de alta utilização da CPU no Banco de Dados do Azure para MySQL – Servidor Flexível
APLICA-SE A:
Banco de Dados do Azure para MySQL – Servidor Único Banco de Dados do Azure para MySQL – Servidor Flexível
Importante
O Banco de Dados do Azure para servidor único MySQL está no caminho de desativação. É altamente recomendável que você atualize para o Banco de Dados do Azure para o servidor flexível MySQL. Para obter mais informações sobre como migrar para o Banco de Dados do Azure para o servidor flexível MySQL, consulte O que está acontecendo com o Banco de Dados do Azure para Servidor Único MySQL?
O servidor flexível do Banco de Dados do Azure para MySQL fornece uma variedade de métricas que você pode usar para identificar afunilamentos de recursos e problemas de desempenho no servidor. Para determinar se o servidor está enfrentando alta utilização da CPU, monitore métricas como "Porcentagem de CPU do host", "Total de conexões", "Porcentagem de memória do host" e "Porcentagem de E/S". Às vezes, a exibição de uma combinação dessas métricas fornecerá insights sobre o que pode estar causando o aumento da utilização da CPU em sua instância de servidor flexível do Banco de Dados do Azure para MySQL.
Por exemplo, considere um aumento repentino nas conexões que iniciam o aumento de consultas do banco de dados que fazem com que a utilização da CPU seja disparada.
Além de capturar métricas, é importante também rastrear a carga de trabalho para entender se uma ou mais consultas estão causando o pico na utilização da CPU.
Causas de CPU alta
Picos de CPU podem ocorrer por vários motivos, principalmente devido a picos em conexões e consultas SQL mal escritas, ou uma combinação de ambos:
Pico de conexões
Um aumento nas conexões pode levar a um aumento nos threads, o que, por sua vez, pode causar um aumento no uso da CPU, pois ela precisa gerenciar essas conexões junto com suas consultas e recursos. Para solucionar um pico de conexões, verifique a métrica Total de conexões e consulte a próxima seção para obter mais detalhes sobre essas conexões. Você pode utilizar o performance_schema para identificar os hosts e usuários atualmente conectados ao servidor com os seguintes comandos:
Hosts conectados atuais
select HOST,CURRENT_CONNECTIONS From performance_schema.hosts
where CURRENT_CONNECTIONS > 0
and host not in ('NULL','localhost');
Usuários conectados atuais
select USER,CURRENT_CONNECTIONS from performance_schema.users
where CURRENT_CONNECTIONS >0
and USER not in ('NULL','azure_superuser');
Consultas SQL mal escritas
Consultas que são caras para executar e verificar um grande número de linhas sem um índice, ou aquelas que executam classificações temporárias junto com outros planos ineficientes, podem provocar picos de CPU. Embora algumas consultas possam ser executadas rapidamente em uma única sessão, elas podem causar picos de CPU quando executadas em várias sessões. Portanto, é crucial sempre explicar suas consultas que você captura da lista de processos de exibição e garantir que seus planos de execução sejam eficientes. Isso pode ser alcançado garantindo que eles verifiquem um número mínimo de linhas usando filtros/cláusula where, utilizem índices e evitem o uso de grande classificação temporária junto com outros planos de execução incorretos. Para obter mais informações sobre planos de execução, consulte EXPLAIN Output Format.
Capturando detalhes da carga de trabalho atual
O comando SHOW (FULL) PROCESSLIST exibe uma lista de todas as sessões de usuário atualmente conectadas à instância do servidor flexível do Banco de Dados do Azure para MySQL. Ele também fornece detalhes sobre o estado atual e a atividade de cada sessão.
Esse comando produz apenas um instantâneo do status da sessão atual e não fornece informações sobre a atividade de sessão histórica.
Vamos dar uma olhada na saída de exemplo da execução deste comando.
mysql> SHOW FULL PROCESSLIST;
+-------+------------------+--------------------+---------------+-------------+--------+-----------------------------+------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-------+------------------+--------------------+---------------+-------------+--------+-----------------------------+------------------------------------------+
| 1 | event_scheduler | localhost | NULL | Daemon | 13 | Waiting for next activation | NULL |
| 6 | azure_superuser | 127.0.0.1:33571 | NULL | Sleep | 115 | | NULL
|
| 24835 | adminuser | 10.1.1.4:39296 | classicmodels | Query | 7 | Sending data | select * from classicmodels.orderdetails;|
| 24837 | adminuser | 10.1.1.4:38208 | NULL | Query | 0 | starting | SHOW FULL PROCESSLIST |
+-------+------------------+--------------------+---------------+-------------+--------+-----------------------------+------------------------------------------+
5 rows in set (0.00 sec)
Observe que há duas sessões pertencentes ao cliente de propriedade do usuário "adminuser", ambas do mesmo endereço IP:
- A sessão 24835 executou uma instrução SELECT durante os últimos sete segundos.
- A sessão 24837 está executando a instrução "show full processlist".
Quando necessário, pode ser obrigatório encerrar uma consulta, como um relatório ou uma consulta HTAP que fez com que o uso da CPU da carga de trabalho de produção aumentasse. No entanto, sempre considere as possíveis consequências de encerrar uma consulta antes de tentar reduzir a utilização da CPU. Outras vezes, se consultas de execução prolongada gerando picos de CPU forem identificadas, ajuste essas consultas para que os recursos sejam utilizados de forma ideal.
Análise detalhada da carga de trabalho atual
Você precisa usar pelo menos duas fontes de informações para obter informações precisas sobre o status de uma sessão, transação e consulta:
- A lista de processos do servidor da tabela INFORMATION_SCHEMA.PROCESSLIST, que você também pode acessar executando o comando SHOW [FULL] PROCESSLIST.
- Metadados de transação do InnoDB da tabela INFORMATION_SCHEMA.INNODB_TRX.
Com informações de apenas uma dessas fontes, é impossível descrever a conexão e o estado da transação. Por exemplo, a lista de processos não informa se há uma transação aberta associada a qualquer uma das sessões. Por outro lado, os metadados da transação não exibem o estado da sessão e o tempo gasto nesse estado.
A consulta de exemplo a seguir combina informações da lista de processos com algumas partes importantes dos metadados de transação do InnoDB:
mysql> select p.id as session_id, p.user, p.host, p.db, p.command, p.time, p.state, substring(p.info, 1, 50) as info, t.trx_started, unix_timestamp(now()) - unix_timestamp(t.trx_started) as trx_age_seconds, t.trx_rows_modified, t.trx_isolation_level from information_schema.processlist p left join information_schema.innodb_trx t on p.id = t.trx_mysql_thread_id \G
O exemplo a seguir mostra a saída dessa consulta:
*************************** 1. row ***************************
session_id: 11
user: adminuser
host: 172.31.19.159:53624
db: NULL
command: Sleep
time: 636
state: cleaned up
info: NULL
trx_started: 2019-08-01 15:25:07
trx_age_seconds: 2908
trx_rows_modified: 17825792
trx_isolation_level: REPEATABLE READ
*************************** 2. row ***************************
session_id: 12
user: adminuser
host: 172.31.19.159:53622
db: NULL
command: Query
time: 15
state: executing
info: select * from classicmodels.orders
trx_started: NULL
trx_age_seconds: NULL
trx_rows_modified: NULL
trx_isolation_level: NULL
Uma análise dessas informações, por sessão, é listada na tabela a seguir.
| Área | Análise |
|---|---|
| Sessão 11 | Esta sessão está ociosa no momento (suspensa) sem consultas em execução há 636 segundos. Na sessão, uma transação que está aberta há 2908 segundos modificou 17.825.792 linhas e usa o isolamento REPEATABLE READ. |
| Sessão 12 | A sessão está executando uma instrução SELECT, que está em execução há 15 segundos. Não há nenhuma consulta em execução na sessão, conforme indicado pelos valores NULL para trx_started e trx_age_seconds. A sessão continuará mantendo o limite de coleta de lixo enquanto for executada, a menos que esteja usando o isolamento READ COMMITTED mais relaxado. |
Observe que, se uma sessão for relatada como ociosa, ela não executará outras instruções. Neste ponto, a sessão concluiu qualquer trabalho anterior e está aguardando novas instruções do cliente. No entanto, as sessões ociosas ainda são responsáveis por algum consumo de CPU e uso de memória.
Lista de transações abertas
A saída da consulta a seguir fornece uma lista de todas as transações atualmente em execução no servidor de banco de dados, na ordem da hora de início da transação, para que você possa identificar facilmente se há transações de execução prolongada e de bloqueio excedendo o runtime esperado.
SELECT trx_id, trx_mysql_thread_id, trx_state, Unix_timestamp() - ( To_seconds(trx_started) - To_seconds('1970-01-01 00:00:00') ) AS trx_age_seconds, trx_weight, trx_query, trx_tables_in_use, trx_tables_locked, trx_lock_structs, trx_rows_locked, trx_rows_modified, trx_isolation_level, trx_unique_checks, trx_is_read_only FROM information_schema.innodb_trx ORDER BY trx_started ASC;
Noções básicas sobre estados de thread
As transações que contribuem para maior utilização da CPU durante a execução podem ter threads em vários estados, conforme descrito nas seções a seguir. Use essas informações para entender melhor o ciclo de vida da consulta e vários estados de thread.
Verificando permissões/abrindo tabelas
Esse estado geralmente significa que a operação de tabela aberta está consumindo muito tempo. Normalmente, você pode aumentar o tamanho do cache da tabela para melhorar o problema. No entanto, tabelas que abrem lentamente também podem ser indicativas de outros problemas, como ter muitas tabelas no mesmo banco de dados.
Enviar dados
Embora esse estado possa significar que o thread está enviando dados pela rede, ele também pode indicar que a consulta está lendo dados do disco ou da memória. Esse estado pode ser causado por uma verificação de tabela sequencial. Verifique os valores de innodb_buffer_pool_reads e innodb_buffer_pool_read_requests para determinar se um grande número de páginas está sendo fornecido do disco na memória. Para obter mais informações, consulte Solucionar problemas de pouca memória no Banco de Dados do Azure para servidor flexível MySQL.
Atualizar
Esse estado geralmente significa que o thread está executando uma operação de gravação. Verifique a métrica relacionada à E/S no Monitor de Desempenho entender melhor o que as sessões atuais estão fazendo.
Aguardando o bloqueio <lock_type>
Esse estado indica que a thread está aguardando um segundo bloqueio. Na maioria dos casos, pode ser um bloqueio de metadados. Avalie todos os outros threads e ver qual está sendo bloqueado.
Compreendendo e analisando eventos de espera
É importante entender os eventos de espera subjacentes no mecanismo do MySQL, pois longas esperas ou um grande número de esperas em um banco de dados podem gerar um aumento na utilização da CPU. O exemplo a seguir mostra o comando adequado e a saída de exemplo.
SELECT event_name AS wait_event,
count_star AS all_occurrences,
Concat(Round(sum_timer_wait / 1000000000000, 2), ' s') AS total_wait_time,
Concat(Round(avg_timer_wait / 1000000000, 2), ' ms') AS
avg_wait_time
FROM performance_schema.events_waits_summary_global_by_event_name
WHERE count_star > 0 AND event_name <> 'idle'
ORDER BY sum_timer_wait DESC LIMIT 10;
+--------------------------------------+-----------------+-----------------+---------------+
| wait_event | all_occurrences | total_wait_time | avg_wait_time |
+--------------------------------------+-----------------+-----------------+---------------+
| wait/io/file/sql/binlog | 7090 | 255.54 s | 36.04 ms |
| wait/io/file/innodb/innodb_log_file | 17798 | 55.43 s | 3.11 ms |
| wait/io/file/innodb/innodb_data_file | 260227 | 39.67 s | 0.15 ms |
| wait/io/table/sql/handler | 5548985 | 11.73 s | 0.00 ms |
| wait/io/file/sql/FRM | 1237 | 7.61 s | 6.15 ms |
| wait/io/file/sql/dbopt | 28 | 1.89 s | 67.38 ms |
| wait/io/file/myisam/kfile | 92 | 0.76 s | 8.30 ms |
| wait/io/file/myisam/dfile | 271 | 0.53 s | 1.95 ms |
| wait/io/file/sql/file_parser | 18 | 0.32 s | 17.75 ms |
| wait/io/file/sql/slow_log | 2 | 0.05 s | 25.79 ms |
+--------------------------------------+-----------------+-----------------+---------------+
10 rows in set (0.00 sec)
Restringir o tempo de execução de instruções SELECT
Se você não tiver informações sobre o custo de execução e o tempo de execução para operações de banco de dados que envolvem consultas SELECT, quaisquer SELECTs de execução prolongada poderão causar imprevisibilidade ou volatilidade no servidor de banco de dados. O tamanho das instruções e transações, bem como a utilização de recursos associados, continua crescendo dependendo do crescimento subjacente do conjunto de dados. Devido a esse crescimento não associado, as instruções e as transações do usuário final levam mais e mais tempo, consumindo cada vez mais recursos até sobrecarregarem o servidor de banco de dados. Ao usar consultas SELECT não associadas, é recomendável configurar o parâmetro max_execution_time para que todas as consultas que excedam essa duração sejam anuladas.
Recomendações
- Verifique se o banco de dados tem recursos suficientes alocados para executar suas consultas. Às vezes, talvez seja necessário escalar verticalmente o tamanho da instância para obter mais núcleos de CPU para acomodar sua carga de trabalho.
- Evite transações grandes ou de longa execução dividindo-as em transações menores.
- Execute as instruções SELECT em servidores de réplica de leitura quando possível.
- Use alertas na "Porcentagem de CPU do Host" para que você receba notificações se o sistema exceder um dos limites especificados.
- Use a Análise de Desempenho de Consultas ou as Pastas de Trabalho do Azure para identificar consultas problemáticas ou lentas e otimizá-las.
- Para servidores de banco de dados de produção, colete diagnósticos em intervalos regulares para garantir que tudo esteja em execução sem problemas. Caso contrário, solucione e resolva os problemas que você identificar.
Próximas etapas
Para localizar respostas de pares às suas perguntas mais importantes ou publicar ou responder a uma pergunta, acesse o Stack Overflow.