Compartilhar via

Solucionar problemas de alta utilização da CPU no Banco de Dados do Azure para MySQL – Servidor Flexível

  • Artigo

O Servidor Flexível do Banco de Dados do Azure para MySQL oferece 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á 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 na sua instância do 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 da alta utilização de CPU

Picos de CPU podem ocorrer por diversos 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 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 problemas relacionados a picos de conexões, você deve verificar a métrica de Total de Conexões e consultar a próxima seção para 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 analisar suas consultas capturadas do comando show processlist e garantir que seus planos de execução sejam eficientes. Isso pode ser feito garantindo que elas escaneiem o menor número possível de linhas usando cláusulas where/filtros, usando índices e evitando o uso de grandes classificações temporárias, junto com outros planos de execução inadequados. Para obter mais informações sobre planos de execução, consulte Formato de saída do comando EXPLAIN.

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.

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) |

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.

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, veja Solucionar problemas de baixa memória no Banco de Dados do Azure para MySQL - Servidor flexível.

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.

Conteúdo relacionado

Stack Overflow