Como soluciono problemas e resolvo a alta utilização da CPU em uma instância do Amazon RDS para MySQL ou do Amazon Aurora MySQL?

Breve descrição

Os aumentos na utilização da CPU podem ser causados por vários fatores, como workloads pesadas iniciadas pelo usuário, várias consultas simultâneas ou transações de longa duração.

Para identificar a origem do uso da CPU em sua instância do Amazon RDS para MySQL, analise as seguintes abordagens:

• Monitoramento avançado
• Performance Insights
• Consultas que detectam a causa da utilização da CPU na workload
• Logs com monitoramento ativado

Depois de identificar a causa, analise e otimize sua workload para reduzir o uso da CPU.

Resolução

Usar o monitoramento avançado

O monitoramento avançado fornece uma visão no nível do sistema operacional (SO). Essa visualização pode ajudar a identificar a causa de uma carga elevada da CPU em um nível granular. Por exemplo, você pode analisar a carga média, a distribuição da CPU (system% ou nice%) e a lista de processos do sistema operacional.

Com o monitoramento avançado, é possível verificar os dados de loadAverageMinute em intervalos de 1, 5 e 15 minutos. Uma carga média maior que o número de vCPUs indica que a instância está sob carga pesada. Além disso, se a carga média for menor que o número de vCPUs da classe de instância de banco de dados, o controle de utilização da CPU pode não ser a causa da latência do aplicativo. Verifique a carga média para evitar falsos positivos ao diagnosticar a causa do uso da CPU.

Por exemplo, se você tiver uma instância de banco de dados que está usando uma classe de instância db.m5.2xlarge com 3.000 IOPS provisionadas que atinge o limite da CPU, você pode analisar os exemplos de métricas a seguir para identificar a causa raiz do alto uso da CPU. No exemplo a seguir, a classe de instância tem oito vCPUs associadas a ela. Para a mesma carga média, exceder 170 indica que a máquina está sob carga pesada durante o período de tempo medido:

Carga média por minuto

Quinze	170,25
Cinco	391,31
Um	596,74

Utilização da CPU

Usuário (%)	0,71
Sistema (%)	4,9
Nice (%)	93,92
Total (%)	99,97

Observação: o Amazon RDS dá à sua workload uma prioridade maior em relação a outras tarefas que estão sendo executadas na instância de banco de dados. Para priorizar essas tarefas, as tarefas de workloads têm um valor de Nice maior. Como resultado, no monitoramento avançado, Nice% representa a quantidade de CPU usada por sua workload no banco de dados.

Depois de ativar o monitoramento avançado, você também pode verificar a lista de processos do sistema operacional associada à instância de banco de dados. O monitoramento avançado mostra no máximo 100 processos. Isso pode ajudar você a identificar quais processos têm o maior impacto no desempenho com base no uso da CPU e da memória.

Na seção lista de processos do sistema operacional (SO) do monitoramento avançado, analise os processos do sistema operacional e os processos do RDS. Confirme a porcentagem de utilização da CPU de um processo mysqld ou Aurora. Essas métricas podem ajudar você a confirmar se o aumento na utilização da CPU é causado pelo sistema operacional ou pelos processos do RDS. Ou você pode usar essas métricas para monitorar qualquer aumento no uso da CPU causado pelo mysqld ou pelo Aurora. Você também pode ver a divisão da utilização da CPU analisando as métricas para cpuUtilization. Para obter mais informações, consulte Monitorar métricas do SO com o monitoramento avançado.

Observação: se você ativar o Performance Schema, poderá mapear o ID do thread do sistema operacional para o ID do processo do seu banco de dados. Para obter mais informações, consulte Por que minha instância de banco de dados do Amazon RDS está usando memória swap quando tenho memória suficiente?

Usar o Performance Insights

Você pode usar o Performance Insights para identificar as consultas exatas que estão em execução na instância e causando a alta utilização da CPU. Primeiro, ative o Performance Insights para MySQL. Em seguida, você pode usar o Performance Insights para otimizar sua workload. Não deixe de consultar seu DBA.

Para ver os mecanismos de banco de dados que pode usar com o Performance Insights, consulte Monitorar a carga de banco de dados com o Performance Insights no Amazon RDS.

Usar consultas para detectar a causa da utilização da CPU na workload

Antes de otimizar sua workload, você deve identificar a consulta problemática. Você pode executar as seguintes consultas enquanto o problema de alta CPU está ocorrendo para identificar a causa raiz da utilização da CPU. Em seguida, otimize sua workload para reduzir o uso da CPU.

O comando SHOW PROCESSLIST mostra os threads que estão sendo executados atualmente na sua instância do MySQL. Às vezes, o mesmo conjunto de declarações pode continuar sendo executado sem ser concluído. Quando isso acontece, as declarações subsequentes devem aguardar o término do primeiro conjunto de declarações. Isso ocorre porque o bloqueio em nível de linha do InnoDB pode estar atualizando as mesmas linhas. Para obter mais informações, consulte SHOW PROCESSLIST Statement no site do MySQL.

SHOW FULL PROCESSLIST;

Observação: execute a consulta SHOW PROCESSLIST como usuário principal do sistema. Se você não for o usuário principal do sistema, deverá ter privilégios de administração do servidor MySQL PROCESS para ver todos os threads em execução em uma instância do MySQL. Se você não tiver privilégios de administração, SHOW PROCESSLIST mostrará somente os threads associados à conta do MySQL que você estiver usando.

A tabela INNODB_TRX fornece informações sobre todas as transações do InnoDB atualmente em execução que não são transações somente para leitura.

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

A tabela INNODB_LOCKS fornece informações sobre bloqueios que uma transação do InnoDB solicitou, mas não recebeu.

Para o MySQL 5.7 ou anterior:

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

Para o MySQL 8.0:

SELECT * FROM performance_schema.data_locks;

A tabela INNODB_LOCK_WAITS fornece uma ou mais linhas para cada transação bloqueada do InnoDB.

Para o MySQL 5.7 ou anterior:

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

Para o MySQL 8.0:

SELECT * FROM performance_schema.data_lock_waits;

Você pode executar uma consulta semelhante à seguinte para ver as transações que estão aguardando e as transações que estão bloqueando as transações em espera. Para obter mais informações, consulte Using InnoDB transaction and locking information no site do MySQL.

Para o MySQL 5.7 ou anterior:

SELECT
  r.trx_id waiting_trx_id,
  r.trx_mysql_thread_id waiting_thread,
  r.trx_query waiting_query,
  b.trx_id blocking_trx_id,
  b.trx_mysql_thread_id blocking_thread,
  b.trx_query blocking_query
FROM       information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b
  ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r
  ON r.trx_id = w.requesting_trx_id;

Para o MySQL 8.0:

SELECT
  r.trx_id waiting_trx_id,
  r.trx_mysql_thread_id waiting_thread,
  r.trx_query waiting_query,
  b.trx_id blocking_trx_id,
  b.trx_mysql_thread_id blocking_thread,
  b.trx_query blocking_query
FROM       performance_schema.data_lock_waits w
INNER JOIN information_schema.innodb_trx b
  ON b.trx_id = w.blocking_engine_transaction_id
INNER JOIN information_schema.innodb_trx r
  ON r.trx_id = w.requesting_engine_transaction_id;

A consulta SHOW ENGINE INNODB STATUS fornece informações do monitor padrão do InnoDB sobre o estado do mecanismo de armazenamento InnoDB. Para obter mais informações, consulte SHOW ENGINE Statement no site do MySQL.

SHOW ENGINE INNODB STATUS;

SHOW [GLOBAL | SESSION] STATUS fornece informações sobre o status do servidor. Para obter mais informações, consulte SHOW STATUS Statement no site do MySQL.

SHOW GLOBAL STATUS;

Observação: essas consultas foram testadas no Aurora 2.x (MySQL 5.7); Aurora 1. x (MySQL 5.6); MariaDB 10.x. Além disso, a tabela INFORMATION_SCHEMA.INNODB_LOCKS não é mais compatível a partir do MySQL 5.7.14 e foi removida no MySQL 8.0. A tabela performance_schema.data_locks substitui a tabela INFORMATION_SCHEMA.INNODB_LOCKS. Para obter mais informações, consulte The data_locks Table no site do MySQL.

Analisar logs e ativar o monitoramento

Quando você analisa logs ou deseja ativar o monitoramento no Amazon RDS para MySQL, considere as seguintes abordagens:

• Analise o General Query Log do MySQL para ver o que o mysqld está fazendo em um momento específico. Você também pode ver as consultas que estão em execução na sua instância em um horário específico, incluindo informações sobre quando os clientes se conectam ou se desconectam. Para obter mais informações, consulte The General Query Log no site do MySQL.
  Observação: quando você ativa o General Query Log por longos períodos, os logs consomem armazenamento e podem aumentar a sobrecarga de desempenho.
• Analise os logs de consultas lentas do MySQL para encontrar consultas que demoram mais para serem executadas do que os segundos que você definiu para long_query_time. Você também pode examinar sua workload e analisar suas consultas para melhorar o desempenho e o consumo de memória. Para obter mais informações, consulte The Slow Query Log no site do MySQL. Dica: ao usar o log de consultas lentas ou o General Query Log, defina o parâmetro log_output como FILE.
• Use o plug-in de auditoria do MariaDB para auditar a atividade do banco de dados. Por exemplo, você pode rastrear usuários que estão fazendo login no banco de dados ou consultas que são executadas no banco de dados. Para obter mais informações, consulte Suporte ao plug-in de auditoria do MariaDB para MySQL.
• Se você usa o Aurora para MySQL, também pode usar a auditoria avançada. A auditoria pode oferecer mais controle sobre os tipos de consultas que você deseja registrar em log. Isso reduz a sobrecarga de registro em log.
• Use o parâmetro innodb_print_all_deadlocks para verificar se há impasses e bloqueios de recursos. Você pode usar esse parâmetro para registrar informações sobre impasses nas transações de usuários do InnoDB no log de erros do MySQL. Para obter mais informações, consulte innodb_print_all_deadlocks no site do MySQL.

Analisar e otimizar a alta workload da CPU

Depois de identificar a consulta que está elevando o uso da CPU, otimize sua workload para reduzir o consumo da CPU.

Se você vir uma consulta que não é necessária para sua workload, você poderá encerrar a conexão usando o seguinte comando:

CALL mysql.rds_kill(processID);

Para encontrar o processID de uma consulta, execute o comando SHOW FULL PROCESSLIST.

Se você não quiser finalizar a consulta, otimize a consulta usando EXPLAIN. O comando EXPLAIN mostra as etapas individuais envolvidas na execução de uma consulta. Para obter mais informações, consulte Optimizing Queries with EXPLAIN no site do MySQL.

Para analisar os detalhes do perfil, ative PROFILING. O comando PROFILING pode indicar o uso de recursos para declarações que estão sendo executadas durante a sessão atual. Para obter mais informações, consulte SHOW PROFILE Statement no site do MySQL.

Para atualizar qualquer estatística da tabela, use ANALYZE TABLE. O comando ANALYZE TABLE pode ajudar o otimizador a escolher um plano apropriado para executar a consulta. Para obter mais informações, consulte ANALYZE TABLE Statement no site do MySQL.

---

Informações relacionadas

Amazon RDS para MySQL

Amazon RDS para MariaDB

How do I activate and monitor logs for an Amazon RDS MySQL DB instance?

Tuning Amazon RDS for MySQL with Performance Insights