Algumas palavras sobre Alter Table, ou como não fazê-lo

Provavelmente não é um artigo, mas uma breve nota sobre alguns recursos do trabalho com tabelas grandes no MySQL.

O motivo da escrita foi a adição aparentemente diária de uma nova coluna à mesa. Mas tudo acabou não sendo tão simples como o esperado.

Então, uma noite, para não incomodar nossos queridos clientes, precisamos adicionar uma coluna à tabela.

Para deixar mais claro, as características da tabela e da base:

• tamanho da tabela 110Gb
• número de linhas: 7,5 milhões
• mecanismo de armazenamento: InnoDB
• existem dois servidores sql conectados de acordo com o esquema mestre-escravo, enquanto o mestre está no SSD e o escravo no HDD

Parece ser uma solução óbvia para adicionar uma coluna - Alter Table.

alter table table_name add source varchar(32) 

Nós o usamos (sim, entendemos que era ruim, mas nesse caso em particular, os riscos eram mínimos).

Os resultados foram bastante desagradáveis:

• no assistente, o processo de adição de uma coluna levou cerca de uma hora (!)
• no escravo, começou após o término do processo no mestre e durou cerca de 8 horas (!!)
• durante a tabela de alteração, a replicação de dados (!!!) parou completamente no escravo

Mas há um lado positivo: um pequeno bônus foi que, após adicionar uma coluna, o tamanho da tabela diminuiu 10%.

Nos gráficos abaixo, é claramente visível.


Gráfico de carga da CPU no assistente.


Gráfico de carga da CPU no escravo.


Atraso na replicação.

Que problemas aguardam aqueles que fazem isso nas mesas de batalha?

Primeiro, durante a duração da tabela alter, você não pode gravar dados na tabela (mas pode lê-los). De fato, depende da versão do MySQL; no último, não é, mas, no entanto, você precisa entender do que exatamente sua versão é capaz para evitar problemas.

Portanto, se a tabela for grande, o tempo de indisponibilidade será significativo (como conosco, ao usar um SSD demorou uma hora e em um disco normal - 8 horas), o que é improvável que seus clientes esperem.

Em segundo lugar, como no nosso caso, durante a execução da Alter Table, a sincronização de todas as tabelas , não apenas a que mudamos, parou completamente no escravo. Portanto, se seus dados no segundo servidor são críticos e devem ser atualizados - você corre o risco de ficar sem atualizações com todas as conseqüências resultantes.

Outro ponto não óbvio que encontramos ao adicionar uma coluna (mas essa foi outra vez) - é necessário espaço em disco adicional .

O fato é que algumas alterações nas tabelas recriam a tabela do zero, portanto, você não precisa de menos espaço do que uma tabela existente. Para tabelas grandes, respectivamente, é necessário muito espaço, para dizer o mínimo. De acordo com a documentação, uma tabela temporária é criada no mesmo diretório que o original.

Além disso, durante a execução de todos os tipos de Alter Table, todas as alterações são gravadas no arquivo de log, para que, após as alterações, os dados possam ser rolados durante o tempo em que a operação foi executada. E também aqui uma surpresa desagradável pode aguardar: se a tabela mudar por um longo período de tempo e o volume de operações for grande, então não apenas o espaço em disco poderá terminar, mas também o limite de tamanho do arquivo especificado nas configurações SQL poderá ser excedido. De qualquer forma, "a operação DDL on-line falha e as operações DML simultâneas não confirmadas são revertidas" espera por você.

Fomos confrontados com o fato de o diretório para arquivos temporários ser pequeno, como resultado, tivemos que redefinir innodb_tmpdir .

Para ver para onde a variável está apontando no momento, você pode fazer isso:

 select @@GLOBAL.innodb_tmpdir; 

Lembre-se de que o tamanho do diretório temporário também pode ser necessário, do tamanho de uma tabela + índices. Em geral, estoque de espaço.

Para não repetir a documentação, leia com mais detalhes em https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-space-requirements.html

Mas como fazer isso? De fato, não existe uma receita única para todas as ocasiões.

Uma das opções possíveis, como fazemos para tabelas que não são críticas para atualização:

• Crie uma nova tabela com a estrutura desejada
• Preencha os campos da tabela antiga
• Excluir ou renomear uma tabela antiga
• Renomeie o novo

Repito que isso funciona para tabelas de atualização não críticas. E, ao mesmo tempo, evita o bloqueio de replicação. Deve-se ter em mente que o preenchimento de uma nova tabela deve ser feito de forma a permitir a replicação continuar e, como é executado sequencialmente, você não pode fazer isso com uma única expressão sql, deve dividi-la em várias pequenas consultas entre as quais a replicação de outros dados ocorrerá. Em outros casos, outras opções são possíveis, talvez alguém compartilhe os comentários.

UPD Syavadee sugeriu o uso da mudança de esquema on-line percona. De fato, ele implementa o algoritmo descrito acima com vantagens adicionais.

UPD A Arheops recomenda ativar a replicação paralela / gtid para resolver problemas de replicação.

Bem, aliás, às vezes, para entender o tamanho da tabela e quantas linhas nela, você precisa aprender como ensinar

 select count(*) from table_name 

Mas em tabelas grandes e carregadas, essa também não é a operação mais rápida, especialmente quando você tem meio milhão de linhas ou mais.

Portanto, para uma estimativa aproximada do volume, você pode usar o seguinte método:

 SHOW TABLE STATUS FROM express where name='table_name' 

Infelizmente, no mecanismo do InnoDB, o tamanho resultante pode diferir em 50% (no nosso caso, com a tabela acima, o número real de registros é de cerca de 7,5 milhões e esse método mostrou apenas 5 milhões), mas isso é adequado para uma estimativa indicativa.

Isso é tudo, espero que esta nota ajude alguém a evitar grandes problemas com comandos SQL supostamente inofensivos.