copiar para a tabela tmp que altera a tabela em incremento automático

Question

Temos um problema com o MySql (5.5.x) e espero que você possa nos ajudar. Em algum momento do dia, noto um processo com o estado "copiar para a tabela tmp" e esta consulta:

ALTER TABLE `MyTableName` AUTO_INCREMENT=200000001

Depois disso, todas as outras consultas recebem um "Bloqueio de metadados de espera da tabela", e todas as consultas são congeladas e nada é processado.

Preciso matar esse processo e, a partir desse ponto, todas as consultas reiniciadas.

Por quê? Como posso resolver este problema?

Answer 1

No MySQL 5.5, um ALTER TABLE como o que você executou faz uma cópia de toda a tabela. Quanto maior a mesa, mais tempo leva. Especialmente se você tiver armazenamento lento.

Qual é o tamanho da sua mesa (você pode pegar isso SHOW TABLE STATUS LIKE 'MyTableName'\Ge olhar para o data_length + index_length)?

Acabei de fazer um teste no meu laptop. Preenchi uma tabela em uma instância do MySQL 5.5, até que o tamanho da tabela fosse cerca de 175 MB. A execução de uma tabela de alteração para definir o valor de incremento automático leva cerca de 5 a 6 segundos. Seus resultados podem ser diferentes, dependendo da potência do seu servidor e da velocidade de armazenamento.

Enquanto a tabela alter está em execução, o encadeamento que faz essa operação mantém um bloqueio de metadados na tabela, que bloqueia todas as outras consultas, mesmo instruções SELECT somente leitura.

ALTER TABLE foi aprimorado em 2013, como um recurso do MySQL 5.6. Alguns tipos de alterações foram otimizados para serem feitos "no local", de forma que não precisem copiar a tabela inteira se não for necessário. Alterar o AUTO_INCREMENT é uma dessas operações. Não importa o tamanho da tabela, se você alterar a tabela para alterar o AUTO_INCREMENT, é rápido porque apenas altera um atributo da tabela, sem exigir a cópia de nenhuma linha de dados.

Vejo https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl-operations.html

No MySQL 5.5, essas otimizações não foram implementadas. Portanto, qualquer tabela de alteração leva muito tempo, proporcional ao tamanho da tabela.

Eu recomendaria que a melhor maneira de corrigir esse problema no seu caso é atualizar para uma versão mais recente. O MySQL 5.5 está além do fim de sua vida útil. Até o MySQL 5.6 está chegando ao fim de sua vida útil em fevereiro de 2021. É hora de atualizar.

Se você não pode atualizar, você deve investigar qual cliente está fazendo esta instrução ALTER TABLE. Você disse que percebeu em algum momento durante o dia. Rastreie isso. Na lista de processos, ele informará o host do cliente de onde a instrução SQL está sendo executada. Ele também informará o usuário MySQL com o qual ele efetuou login. Você também pode precisar fazer uma pesquisa em seu código-fonte de quaisquer aplicativos ou scripts que usam este banco de dados. Ou pergunte aos seus companheiros de equipe.

Depois de encontrar o cliente que está fazendo esse ALTER TABLE, tente alterar a hora em que o cliente executa esta instrução para uma hora do dia em que ALTER TABLE não bloqueie consultas importantes. Ou pergunte ao desenvolvedor responsável se é realmente necessário fazer essa tabela de alteração com tanta frequência?

Answer 2

O problema pode ser devido a uma reinicialização do servidor , já que o InnoDb armazena o último índice de incremento automático na memória e o recalcula na reinicialização do servidor ( Inicialização do contador InnoDB AUTO_INCREMENT ):

Se você especificar uma coluna AUTO_INCREMENT para uma tabela InnoDB, o identificador de tabela no dicionário de dados InnoDB contém um contador especial denominado contador de incremento automático que é usado na atribuição de novos valores para a coluna. Este contador é armazenado apenas na memória principal, não no disco.

Para inicializar um contador de incremento automático após a reinicialização do servidor, o InnoDB executa o equivalente à seguinte instrução na primeira inserção em uma tabela contendo uma coluna AUTO_INCREMENT.

SELECT MAX (ai_col) FROM nome_tabela FOR UPDATE;

InnoDB incrementa o valor recuperado pela instrução e o atribui à coluna e ao contador de incremento automático para a tabela. Por padrão, o valor é incrementado em 1. Esse padrão pode ser substituído pela definição de configuração auto_increment_increment.

Se a tabela estiver vazia, InnoDB usa o valor 1. Este padrão pode ser sobrescrito pela definição de configuração auto_increment_offset.

Olhe os logs do mysql e tente descobrir se o servidor está reiniciando , fazendo com que a tabela ALTER reinicie o contador de incremento automático.

Tente reiniciar o servidor mysql para ver se você consegue este comportamento .

Se for esse o caso, você pode tentar:

• Evite reiniciar o servidor mysql, talvez haja um processo cron que o reinicia uma vez por dia
• Atualize sua versão do mysql para 8 ( Autoicremento salvo nos metadados da tabela ):

No MySQL 8.0, esse comportamento foi alterado. O valor máximo atual do contador de incremento automático é gravado no log de redo cada vez que ele muda e é salvo em uma tabela de sistema privado do mecanismo em cada ponto de verificação. Na reinicialização do servidor após um desligamento normal, o InnoDB inicializa o contador de incremento automático na memória usando o valor de incremento automático máximo atual armazenado na tabela do sistema do dicionário de dados.

• Você pode tentar acelerar as operações de "copiar para a tabela tmp", pular a cópia para a tabela tmp no disco mysql

Referências

Como fazer a tabela InnoDB não resetar o incremento automático na reinicialização do servidor?

https://serverfault.com/questions/228690/mysql-auto-increment-fields-resets-by-itself