Modificando colunas de tabelas mysql muito grandes com pouco ou nenhum tempo de inatividade

Question

Eu periodicamente preciso fazer alterações em tabelas no mysql 5.1, principalmente adicionando colunas. Muito simples com o comando alter table. Mas minhas tabelas têm até 40 milhões de linhas agora e elas estão crescendo rapidamente ... Então, esses comandos alter table demoram várias horas. Daqui a alguns meses eles vão demorar dias para adivinhar.

Desde que eu estou usando o RDS amazon, não posso ter servidores escravos para brincar e depois promover para dominar. Então, minha pergunta é se há uma maneira de fazer isso com o mínimo de tempo de inatividade? Eu não me importo uma operação demorando horas ou até dias, se os usuários ainda podem usar o banco de dados é claro ... Eles podem, pelo menos, ler enquanto as colunas estão sendo adicionadas? O que acontece se meu aplicativo tentar escrever? Inserir ou atualizar? Se falhar imediatamente, na verdade não é tão ruim, se simplesmente parar e causar problemas para o servidor db, o que é um grande problema.

Isso deve ser um problema de escala bastante comum. Todos precisam adicionar colunas. O que geralmente é feito em um banco de dados de produção? Escravo - > migração mestre?

Atualização - esqueci de mencionar que estou usando o mecanismo de armazenamento innodb

    

Answer 1

I periodically need to make changes to tables in mysql 5.1, mostly adding columns.

Não. Não mesmo. Apenas não faça. Deve ser uma ocasião muito rara quando isto é sempre necessário.

Supondo que seus dados estejam realmente normalizados para começar, a maneira correta de resolver o problema é adicionar uma nova tabela com uma relação 1: 1 à tabela base (não obrigatória na nova tabela).

Ter que adicionar colunas regularmente é geralmente um indicador de um banco de dados que não é normalizado - se o seu esquema não estiver normalizado, então esse é o problema que você precisa consertar.

Por fim, se o seu esquema estiver realmente normalizado e você realmente precisar continuar adicionando colunas:

1. Verifique se você tem uma coluna de timestamp no banco de dados ou se está gerando logs de replicação
2. Crie uma cópia (B) da tabela (A)
3. adicione as novas colunas a B (isso ainda bloqueará com myisam)
4. desativar transações
5. renomeie a tabela original (A) como algo diferente (backup)
6. renomeie a nova tabela (B) com o nome da tabela original (A)
7. reproduzir as transações do início da operação a partir do log de replicação ou da tabela de backup
8. ativar transações.

Answer 2

Eu só tive que fazer isso recentemente. O que a Amazon recomendou foi usar o Percona Toolkit. Eu fiz o download e consegui executar algo como:

./pt-online-schema-change h=databasenameHostName,D=databasename,t=tablename --recursion-method=none --execute --user username --password password --alter "MODIFY someColumn newDataType"

e funciona muito bem. Ele informa quanto tempo restante no processo.

Na verdade, ele cria uma nova tabela com a nova coluna e, em seguida, copia os dados existentes. Além disso, ele cria um gatilho para que novos dados também sejam colocados na nova tabela. Em seguida, ele renomeia as tabelas automaticamente, elimina a tabela antiga e você está pronto para rodar com a nova coluna e sem tempo de inatividade enquanto espera pelas atualizações.

    

Answer 3

symcbean fornece algumas recomendações sólidas .

Para responder à sua pergunta, a maneira mais fácil e melhor de reduzir o impacto é ter vários bancos de dados sendo replicados. Mestre duplo com um procedimento de failover apropriado, impedindo a replicação no ativo, o que permite uma alteração no inativo sem afetar o ativo.

Você poderia fazer isso em um único banco de dados ativo e minimizar o impacto usando um procedimento semelhante àquele detalhado em esta resposta . Evidentemente, isso é semelhante ao que syccbean descreveu, mas inclui detalhes técnicos. Você também pode usar um campo auto_increment e não apenas timestamp.

Por fim, se o conjunto de dados estiver crescendo muito, você também precisará considerar o arquivamento entre OLTP e bases de dados OLAP . Seu conjunto de dados de transação não precisa ser tão grande se você projetar adequadamente.

    

Answer 4

Do manual: link

In most cases, ALTER TABLE makes a temporary copy of the original table. MySQL incorporates the alteration into the copy, then deletes the original table and renames the new one. While ALTER TABLE is executing, the original table is readable by other sessions. Updates and writes to the table are stalled until the new table is ready, and then are automatically redirected to the new table without any failed updates.

Então, a leitura funcionará bem. As gravações serão interrompidas, mas executadas depois. Se você quiser evitar isso, terá que modificar seu software.

    

Answer 5

Eu estou em situação semelhante, onde eu tenho que alterar 1 da minha tabela de transação, que é quase 65GB. Eu ouço 2 soluções

1. Use o comando direto ALTER e deixe rodar (números X de horas ou dia)
2. Verifique se você tem uma coluna de carimbo de data / hora no banco de dados ou se está gerando logs de replicação
   • Crie uma cópia (B) da tabela (A)
   • adicione as novas colunas a B (isso ainda bloqueará com myisam)
   • desativar transações
   • renomeie a tabela original (A) como algo diferente (backup)
   • renomeie a nova tabela (B) com o nome da tabela original (A)