Resolver problemas de utilização elevada da CPU na Base 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 servidor único do Banco de Dados do Azure para 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 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 gargalos de recursos e problemas de desempenho no servidor. Para determinar se o servidor está tendo 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, exibir uma combinação dessas métricas fornecerá informações sobre o que pode estar causando o aumento da utilização da CPU em seu Banco de Dados do Azure para instância de servidor flexível MySQL.
Por exemplo, considere um aumento repentino nas conexões que inicia uma onda de consultas de banco de dados que fazem com que a utilização da CPU dispare.
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 alta CPU
Os picos de CPU podem ocorrer por vários motivos, principalmente devido a picos de conexões e consultas SQL mal escritas, ou uma combinação de ambos:
Pico de conexões
Um aumento nas ligações pode resultar num aumento nos threads, o que, por sua vez, pode causar um aumento na utilização da CPU, uma vez que tem que gerir estas ligações juntamente com as suas consultas e recursos. Para solucionar problemas de 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. Pode utilizar performance_schema para identificar os anfitriões e os utilizadores ligados atualmente ao servidor com os seguintes comandos:
Anfitriões ligados atualmente
select HOST,CURRENT_CONNECTIONS From performance_schema.hosts
where CURRENT_CONNECTIONS > 0
and host not in ('NULL','localhost');
Utilizadores ligados atualmente
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 dispendiosas de executar e analisam um grande número de linhas sem um índice, ou as que executam classificações temporárias juntamente com outros planos ineficientes, podem causar picos da CPU. Embora algumas consultas possam ser executadas rapidamente numa única sessão, podem causar picos da CPU quando executadas em várias sessões. Portanto, é crucial sempre explicar suas consultas que você captura da lista de processos do show e garantir que seus planos de execução sejam eficientes. Isso pode ser alcançado garantindo que eles examinem 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 EXPLICAR formato de saída.
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 flexível do servidor do Banco de Dados do Azure para MySQL. Ele também fornece detalhes sobre o estado atual e a atividade de cada sessão.
Este comando produz apenas um instantâneo do status da sessão atual e não fornece informações sobre a atividade histórica da sessão.
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 de propriedade do usuário de propriedade do cliente "adminuser", ambas do mesmo endereço IP:
- A sessão 24835 tem executado uma instrução SELECT nos últimos sete segundos.
- A sessão 24837 está executando a instrução "show full processlist".
Quando necessário, pode ser necessário encerrar uma consulta, como uma consulta de relatório ou HTAP que tenha causado o aumento do uso da CPU da carga de trabalho de produção. No entanto, sempre considere as consequências potenciais de encerrar uma consulta antes de executar a ação na tentativa de reduzir a utilização da CPU. Outras vezes, se houver consultas de longa duração identificadas que estejam levando a picos de CPU, ajuste essas consultas para que os recursos sejam utilizados da melhor forma.
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 do INFORMATION_SCHEMA. Tabela PROCESSLIST, que você também pode acessar executando o comando SHOW [FULL] PROCESSLIST.
- Metadados de transação do InnoDB do INFORMATION_SCHEMA. INNODB_TRX tabela.
Com informações de apenas uma dessas fontes, é impossível descrever o estado da conexão e da transação. Por exemplo, a lista de processos não informa se há uma transação aberta associada a alguma das sessões. Por outro lado, os metadados da transação não mostram o estado da sessão e o tempo gasto nesse estado.
A consulta de exemplo a seguir que combina informações de lista de processos com algumas das 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, está listada na tabela a seguir.
| Área | Análise |
|---|---|
| Sessão 11 | Esta sessão está atualmente ociosa (em suspensão) sem consultas em execução, e tem sido por 636 segundos. Dentro da sessão, uma transação que esteve aberta por 2908 segundos modificou 17.825.792 linhas e usa o isolamento REPEATABLE READ. |
| Sessão 12 | A sessão está atualmente executando uma instrução SELECT, que está sendo executada 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á a manter o limite de coleta de lixo enquanto for executado, a menos que esteja usando o isolamento mais relaxado READ COMMITTED . |
Observe que, se uma sessão for relatada como ociosa, ela não estará mais executando nenhuma instrução. Neste ponto, a sessão concluiu qualquer trabalho anterior e está aguardando novas declarações do cliente. No entanto, as sessões ociosas ainda são responsáveis por algum consumo de CPU e uso de memória.
Listar 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 em ordem de tempo de início da transação para que você possa identificar facilmente se há transações de longa execução e bloqueio excedendo seu tempo de execução 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 uma 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 abrindo lentamente também podem ser indicativos de outros problemas, como ter muitas tabelas sob o mesmo banco de dados.
Envio de 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. Você deve verificar os valores do innodb_buffer_pool_reads e innodb_buffer_pool_read_requests para determinar se um grande número de páginas está sendo servido do disco para a 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.
Atualização
Esse estado geralmente significa que o thread está executando uma operação de gravação. Verifique a métrica relacionada a E/S no Monitor de desempenho para entender melhor o que as sessões atuais estão fazendo.
À espera <lock_type> bloqueio
Esse estado indica que o thread está aguardando um segundo bloqueio. Na maioria dos casos, pode ser um bloqueio de metadados. Você deve rever todos os outros tópicos e ver quem está pegando o bloqueio.
Compreender e analisar eventos de espera
É importante entender os eventos de espera subjacentes no mecanismo MySQL, porque longas esperas ou um grande número de esperas em um banco de dados pode levar a uma maior utilização da CPU. O exemplo a seguir mostra o comando apropriado 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 das instruções SELECT
Se você não souber sobre o custo de execução e o tempo de execução para operações de banco de dados envolvendo consultas SELECT, qualquer SELECTs de longa execução pode levar à imprevisibilidade ou volatilidade no servidor de banco de dados. O tamanho dos extratos e transações, bem como a utilização de recursos associada, continua a crescer dependendo do crescimento do conjunto de dados subjacente. Devido a esse crescimento ilimitado, as declarações e transações do usuário final levam cada vez mais tempo, consumindo cada vez mais recursos até sobrecarregar o servidor de banco de dados. Ao usar consultas SELECT não limitadas, é recomendável configurar o parâmetro max_execution_time para que todas as consultas que excedam essa duração sejam anuladas.
Recomendações
- Certifique-se de que seu banco de dados tenha recursos suficientes alocados para executar suas consultas. Às vezes, talvez seja necessário aumentar 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 duração, dividindo-as em transações menores.
- Execute instruções SELECT em servidores de réplica de leitura quando possível.
- Use alertas em "Porcentagem de CPU do host" para receber notificações se o sistema exceder qualquer um dos limites especificados.
- Use o Query Performance Insights ou as Pastas de Trabalho do Azure para identificar consultas problemáticas ou de execução lenta e, em seguida, otimize-as.
- Para servidores de banco de dados de produção, colete diagnósticos em intervalos regulares para garantir que tudo esteja funcionando sem problemas. Caso contrário, solucione e resolva quaisquer problemas identificados.
Próximos passos
Para encontrar respostas de colegas para suas perguntas mais importantes ou para postar ou responder a uma pergunta, visite Stack Overflow.