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

O Banco de Dados do Azure para servidor flexível 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 conseguido garantindo que eles digitalizem 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.

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 falta de 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 ao aumento da 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.