ApsaraDB RDS:Alta utilização de CPU em instâncias do ApsaraDB RDS for MySQL ou MariaDB

Descrição do problema

A utilização de CPU da sua instância do ApsaraDB RDS for MySQL ou MariaDB está elevada e, às vezes, atinge 100%.

Causas

Quando uma aplicação envia uma consulta ou modificação de dados, o sistema executa diversas operações de leitura lógica que acessam muitas linhas de dados em uma tabela. O sistema consome recursos significativos de CPU para manter a consistência dos dados entre o armazenamento e a memória. Este tópico explica duas causas comuns para a utilização de CPU em 100% e suas respectivas soluções: alta carga da aplicação, medida em consultas por segundo (QPS), e alto custo de consulta devido a instruções SQL lentas. Frequentemente, a alta utilização de CPU no MySQL resulta de instruções SQL lentas que acessam um grande volume de linhas de dados, o que eleva o custo das consultas.

• Alta carga da aplicação (QPS elevado):

  • Características: A instância apresenta QPS elevado. As consultas são simples e eficientes, com pouca margem para otimização.

  • Sintomas: Não há consultas lentas ou elas não representam a causa principal. As curvas de QPS e de utilização de CPU se comportam de forma similar.

  • Cenários comuns: Essa situação é frequente em sistemas de processamento de transações online (OLTP) otimizados, como sistemas de pedidos, aplicações web populares com altas taxas de leitura e ferramentas de teste de estresse de terceiros, como o Sysbench.

• Alto custo de consulta devido a instruções SQL lentas (muitas linhas de dados acessadas):

  • Características: A instância apresenta QPS baixo. A execução das consultas é ineficiente e exige a varredura de grandes volumes de dados da tabela. Há margem significativa para otimização.

  • Sintomas: Existem consultas lentas. As curvas de QPS e de utilização de CPU não apresentam correspondência.

  • Análise da causa: Consultas ineficientes precisam acessar grandes quantidades de dados para retornar os resultados esperados, gerando média elevada de E/S lógica. Como resultado, a utilização de CPU pode ficar alta mesmo quando o QPS é baixo, como em um site com pouco tráfego.

Soluções

Escolha uma solução adequada à sua situação específica.

Alta carga da aplicação (QPS elevado)

Se a alta utilização de CPU for causada por carga elevada da aplicação, há pouca margem para otimização de SQL. Resolva o problema ajustando a arquitetura da aplicação ou o tipo da instância. Considere os seguintes métodos:

• Atualize o tipo da instância para adicionar mais recursos de CPU. Para obter mais informações, consulte Change instance configurations.

• Adicione instâncias somente leitura. Transfira consultas que não exigem consistência rigorosa de dados, como buscas por categorias de product ou horários de trens, para as instâncias somente leitura. Isso reduz a carga na instância primária. Para obter mais informações, consulte Create an ApsaraDB RDS for MySQL read-only instance.

• Use o PolarDB-X, banco de dados distribuído nativo da nuvem da Alibaba Cloud. Ele realiza sharding automaticamente para distribuir a carga de consultas entre várias instâncias do RDS.

• Use o Alibaba Cloud Memcache ou o Tair (compatível com Redis OSS). Recupere resultados de consultas frequentes diretamente do cache para aliviar a carga na instância do RDS.

• Para aplicações que consultam dados relativamente estáticos, possuem alta repetição de consultas e conjuntos de resultados menores que 1 MB, considere habilitar o cache de consultas.

  Nota

  Teste se a ativação do cache de consultas traz benefícios para sua aplicação. Para obter mais informações sobre as configurações, consulte Set and use the query cache for ApsaraDB RDS for MySQL.

• Arquive dados históricos periodicamente. Use sharding ou particionamento para reduzir o volume de dados acessado pelas consultas. Otimize as consultas para diminuir os custos de execução e melhorar a escalabilidade da aplicação.

Alto custo de consulta devido a instruções SQL lentas

Para resolver esse problema, localize consultas ineficientes, otimize a eficiência de execução e reduza os custos operacionais.

1. Localize consultas ineficientes das seguintes maneiras:

   • Execute a seguinte instrução SQL para visualizar as consultas em execução no momento.

     show processlist;
     show full processlist;

     O sistema exibe uma saída semelhante à seguinte:

     mysql> show processlist;
     +----------+-------+-------------------+---------+---------+-------+--------------+----------------------------------------------+
     | Id       | User  | Host              | db      | Command | Time  | State        | Info                                         |
     +----------+-------+-------------------+---------+---------+-------+--------------+----------------------------------------------+
     |101031643 | jacky | xxx.xxx.xxx.xx:xx | my_test | Query   | 10760 | Sending data | select b.* from perf_test_no_idx_01 a, ...   |
     |117731567 | jacky | xxx.xxx.xxx.xx:xx | my_test | Query   | 10760 | Sending data | select b.* from perf_test_no_idx_01 a, ...   |
     |134298793 | jacky | xxx.xxx.xxx.xx:xx | my_test | Query   | 10760 | Sending data | select b.* from perf_test_no_idx_01 a, ...   |
     |134384670 | jacky | xxx.xxx.xxx.xx:xx | my_test | Query   |     0 | Init         | show processlist                             |
     |234891284 | jacky | xxx.xxx.xxx.xx:xx | my_test | Query   | 10760 | Sending data | select b.* from perf_test_no_idx_01 a, ...   |
     |235125098 | jacky | xxx.xxx.xxx.xx:xx | my_test | Query   | 10760 | Sending data | select b.* from perf_test_no_idx_01 a, ...   |
     |235200576 | jacky | xxx.xxx.xxx.xx:xx | my_test | Query   | 10760 | Sending data | select b.* from perf_test_no_idx_01 a, ...   |
     |235633985 | jacky | xxx.xxx.xxx.xx:xx | my_test | Query   | 10760 | Sending data | select b.* from perf_test_no_idx_01 a, ...   |
     |235887773 | jacky | xxx.xxx.xxx.xx:xx | my_test | Query   | 10760 | Sending data | select b.* from perf_test_no_idx_01 a, ...   |
     |251990394 | jacky | xxx.xxx.xxx.xx:xx | my_test | Query   | 10760 | Sending data | select b.* from perf_test_no_idx_01 a, ...   |
     |252662718 | jacky | xxx.xxx.xxx.xx:xx | my_test | Query   | 10760 | Sending data | select b.* from perf_test_no_idx_01 a, ...   |
     +----------+-------+-------------------+---------+---------+-------+--------------+----------------------------------------------+
     11 rows in set (0.00 sec)

     Sessões de consulta longas com estado Sending data, Copying to tmp table, Copying to tmp table on disk, Sorting result ou Using filesort podem indicar problemas de desempenho.

     • Em cenários onde QPS elevado causa alta utilização de CPU, as consultas costumam ser executadas rapidamente. Isso dificulta a captura delas com o comando show processlist; ou pela visualização das sessões da instância. Nesse caso, execute a seguinte instrução SQL:

       explain [$SQL]

       Nota

       [$SQL] representa a consulta SQL com problemas de desempenho.

     • Interrompa uma sessão de longa duração executando um comando como kill [$ID];. Para obter mais informações sobre como interromper sessões, consulte How to stop sessions on an ApsaraDB RDS for MySQL instance.

       Nota

       [$ID] corresponde ao ID da sessão associada à consulta.

   • Visualize as consultas em execução usando o Database Autonomy Service (DAS):

     1. Faça login no console do DAS.

     2. No painel de navegação à esquerda, clique em Intelligent O&M Center > Instance Monitoring.

     3. Localize a instância desejada e clique em no ID da instância para abrir a página de detalhes.

     4. No painel de navegação à esquerda, clique em Instance Sessions.

     5. Clique em no texto da consulta na coluna SQL para exibir a consulta completa e seu plano de execução.

2. Após identificar as consultas que precisam de otimização, use o SQL Diagnostics no console do DMS para obter sugestões de melhoria. O relatório de diagnóstico também ajuda a solucionar problemas históricos de alta utilização de CPU:

   1. Faça login na instância pelo console do DMS. Para obter mais informações, consulte Log on to a database instance.

   2. Na parte superior da página, clique em SQL Window e selecione o banco de dados alvo.

   3. Cole a consulta na janela SQL e clique em SQL Diagnostics para receber sugestões de otimização.

3. Siga as sugestões de otimização conforme necessário. Por exemplo, adicionar um índice pode reduzir significativamente os custos de execução da consulta.

Mais informações

Recursos para solução de problemas de desempenho

O Data Management (DMS) oferece diversos recursos para ajudar você a diagnosticar e resolver problemas de desempenho da instância. O relatório de diagnóstico é a melhor ferramenta para investigar falhas de desempenho em instâncias MySQL e MariaDB. Independentemente da causa, verifique sempre o relatório de diagnóstico primeiro. Preste atenção especial às seções de otimização de SQL, lista de sessões e resumo de SQL lento.

• Diagnostic report

• SQL diagnostics

• Session management

Princípios para evitar utilização de CPU em 100%

Siga estes princípios para evitar que a utilização da CPU atinja 100%:

• Configure alertas de utilização de CPU para garantir que sua instância mantenha margem suficiente de recursos de processamento.

• Durante o projeto e desenvolvimento da aplicação, considere a otimização de consultas. Siga os princípios gerais de otimização para MySQL visando reduzir a E/S lógica e melhorar a escalabilidade da aplicação.

• Antes de lançar um novo recurso ou módulo, realize testes de estresse com dados de produção .

• Antes de colocar um novo recurso ou módulo em produção, execute testes de regressão utilizando dados reais.

Algoritmo de recursos do sistema

O modelo simplificado a seguir explica a relação entre os recursos do sistema, os custos de execução de instruções SQL e as consultas por segundo (QPS):

• Condição: O modelo da aplicação permanece constante, ou seja, sem modificações.

• avg_lgc_io: Média de E/S lógica necessária para executar cada consulta.

• total_lgc_io: Total de E/S lógica que os recursos de CPU da instância conseguem processar por unidade de tempo.

• Fórmula: total_lgc_io = avg_lgc_io × QPS. Isso significa Total de recursos de CPU por unidade de tempo = Média de E/S lógica por consulta × Número de consultas por unidade de tempo.

Referências

Resolve high CPU utilization on an ApsaraDB RDS for MySQL instance using the autonomy service

Versões aplicáveis

• ApsaraDB RDS for MySQL

• ApsaraDB RDS for MariaDB