ApsaraDB RDS:Instância do RDS for MySQL bloqueada por arquivos temporários

Sintomas

Causa

Consultas que ordenam, agrupam ou juntam tabelas podem gerar arquivos de tabela temporária que consomem todo o espaço em disco da instância. O binlog cache de transações grandes não confirmadas também pode causar esse problema. Para evitar perda de dados, o ApsaraDB RDS bloqueia automaticamente a instância. Durante o bloqueio, todas as operações de escrita são interrompidas.

Resolução

Em emergências, aumente a capacidade de armazenamento da instância. O desbloqueio ocorre cerca de cinco minutos após a expansão do armazenamento. Para mais informações, consulte Alterar configuração.

Se não for possível aumentar a capacidade de armazenamento, use o método correspondente à versão principal do mecanismo da instância:

• MySQL 5.7 ou anterior: Reiniciar instância.

• MySQL 8.0: Quando a instância é bloqueada, todas as sessões de usuário são encerradas automaticamente. Após o término das sessões, as transações começam a ser revertidas. O tempo necessário para a reversão depende das consultas em execução. O espaço de armazenamento da instância é liberado depois que todas as transações forem totalmente revertidas.

  Se a instância não for desbloqueada automaticamente após um longo período, siga estas etapas:

  1. Conecte-se à instância usando o Data Management (DMS).

  2. Execute a seguinte instrução SQL para visualizar as sessões do banco de dados:

     show processlist

  3. Nos resultados, clique em na coluna State para ordenar as sessões. Identifique sessões com estados como 'Copy to tmp table' ou 'Sending data' e anote seus IDs. O comando retorna uma lista de processos com as colunas Id, User, Host, db, Command, Time e State. Use as colunas Command e State para localizar essas sessões problemáticas.

  4. Execute a seguinte instrução SQL para encerrar uma sessão:

     kill [$ID];

     Nota

     Na instrução, [$ID] corresponde ao id da sessão anotado na etapa anterior. Importante: Antes de encerrar uma sessão, certifique-se de que essa ação não afetará seus serviços.

Prevenção

Após o desbloqueio da instância, adote as medidas abaixo para evitar a recorrência do problema:

• Configure a expansão automática de armazenamento. Esse recurso aumenta automaticamente a capacidade de armazenamento da instância quando o espaço disponível fica baixo. Para mais informações, consulte Configurar expansão automática de armazenamento.

• Otimize consultas SQL que geram arquivos temporários grandes. Evite o uso frequente de operações ORDER BY e GROUP BY. É possível aumentar os valores dos parâmetros tmp_table_size e max_heap_table_size. No entanto, não recomendamos elevar esses valores apenas para reduzir o uso de disco, pois a memória é um recurso mais valioso que o espaço em disco. Execute o comando EXPLAIN em uma instrução SQL para verificar se ela utiliza uma tabela temporária interna. Se o campo Extra na saída contiver Using temporary, a consulta usa uma tabela temporária interna.

  explain select * from alarm group by created_on order by default;

  A saída a seguir serve como exemplo. Nos resultados, um valor type igual a ALL indica uma varredura completa da tabela. A coluna Extra contém tanto Using temporary quanto Using filesort, o que sinaliza que a consulta também realiza uma operação de ordenação baseada em arquivo.

• Para gerenciar o binlog cache, reduza transações grandes, especialmente múltiplas transações grandes executadas simultaneamente em conexões diferentes. Se transações grandes forem comuns, aumente o valor do parâmetro binlog_cache_size. Contudo, não recomendamos ajustar esse parâmetro somente para economizar espaço em disco. Em vez disso, use conexões de curta duração para executar transações grandes e reduzir a sobrecarga de espaço temporário.

• Monitore o uso do disco e limpe dados ou implemente fragmentação de dados. Mantenha a utilização do disco abaixo de 80%.

Mais informações

Se não for possível limpar imediatamente os arquivos temporários para desbloquear a instância, libere espaço em disco removendo outros tipos de arquivos. Para mais detalhes, consulte os seguintes tópicos:

• O que fazer se uma instância estiver no estado Locked?

• Resolver problemas de disco cheio causados por arquivos de dados

• Resolver problemas de disco cheio causados por arquivos binlog

• Resolver problemas de disco cheio causados por arquivos de sistema

Perguntas frequentes

• P: O que fazer se o botão Restart Instance estiver indisponível na página Basic Information?

  R: Acione uma reinicialização seguindo estas etapas:

  1. Faça login no console do ApsaraDB RDS e acesse a página Instances. Na barra de navegação superior, selecione a região onde a instância RDS está localizada. Em seguida, localize a instância RDS e clique em no id da instância.

  2. No painel de navegação à esquerda, clique em Parameters.

  3. Na aba Editable Parameters, localize e modifique um parâmetro que apresente Yes na coluna Restart Required.

• P: Como localizar as instruções SQL que geram muitos arquivos temporários?

  R: Para identificar com precisão as instruções SQL que geram arquivos temporários em excesso, siga estas etapas:

  1. Ative o recurso SQL Explorer and Audit.

  2. Aguarde a recorrência do problema. Depois, use as informações de monitoramento para acompanhar a geração de arquivos temporários e analisar os dados capturados pelo SQL Explorer and Audit.

  3. Nos logs de execução do SQL Explorer and Audit, filtre as instruções SQL relacionadas à geração de arquivos temporários.