Como você lida com a tarefa de alterar o esquema de um banco de dados MySQL de produção?

Question

Uma das maiores reclamações que ouvi sobre o MySQL é que ele bloqueia uma tabela se você tentar alterar seu esquema como adicionar uma coluna ou adicionar um índice.

Por "trancar a mesa", significa que não consigo ler nem escrever na tabela? Às vezes por horas?

Isso parece ser uma limitação bastante severa. Eu ia usar o MySQL para o meu novo projeto, mas isso me dá uma pausa.

Existe uma solução para isso? Como você lida com a tarefa de alterar o esquema de seu banco de dados MySQL de produção?

A propósito, alguém me disse que o Postgresql não tem esse problema. Isso é verdade - eu posso ler e escrever em uma tabela do Postgresql enquanto modifico seu esquema? Existe alguma penalidade de desempenho incorrida?

Adoraria ouvir suas experiências.

    

Answer 1

Sim, o MySQL bloqueia a tabela completamente enquanto faz a instrução ALTER TABLE. A maior parte desse tempo é gasta em copiar fisicamente a tabela, e é por isso que é recomendável colocar todas as alterações necessárias na instrução ALTER TABLE.

Existem várias abordagens para atenuar esse problema em um banco de dados ativo, se você não conseguir uma janela de manutenção decente.

Primeiro de tudo, muitos ambientes têm timeout de vários minutos esperando que uma tabela fique disponível para a consulta e não sabem (e é realmente difícil verificar) por que a tabela está bloqueada. Eu usei essa peculiaridade de um site ao vivo para fazer alterações nas tabelas. Em um site que eu costumava cuidar, achei que tínhamos uma permissão de cerca de 7 minutos antes de alguém começar a perceber. :-) Isso ajuda a garantir que seu chefe esteja do seu lado.

Outra maneira de fazer isso é fazer o truque de inserir e renomear. Isso funciona bem se a tabela tiver uma frequência UPDATE razoavelmente baixa ou se for apenas o destino de INSERT s. As etapas básicas são copiar o esquema da tabela, fazer as alterações necessárias, elaborar uma instrução para fazer um INSERT...SELECT do antigo para o novo e renomear as tabelas (fazer a renomeação em uma instrução). Você também precisa preparar antecipadamente uma declaração para copiar qualquer "novo" registro adicionado ou atualizado entre o SELECT e o RENAME . Eu também fiz isso algumas vezes em um trabalho anterior.

No entanto, existem ressalvas:

• Você irá quase certamente ter problemas se a tabela de origem for MyISAM, a menos que talvez a tabela quase nunca seja gravada. Isso é devido ao modo como as tabelas MyISAM são bloqueadas. Funciona melhor com tabelas InnoDB, porque ainda pode ser lido enquanto seu grande INSERT...SELECT está sendo executado.
• Você precisa de uma maneira infalível de descobrir registros que foram adicionados ou alterados entre o SELECT e o RENAME . Para tabelas que são usadas apenas para INSERT , use a coluna auto_increment. Para tabelas que recebem UPDATE s, você precisará de uma coluna confiável da última modificação.

Outras maneiras de lidar com esse problema envolvem a modificação de escravos e o failover da aplicação. Isso está mais intimamente ligado ao modo como seus bancos de dados são replicados. Eu também não fiz isso sozinho, então não posso descrever as etapas exatas.

Finalmente, há uma dúzia de configurações de servidor que você pode alterar e várias outras que são muito mais difíceis de alterar, afetando o tempo necessário para copiar uma tabela. O buffer de ordenação é um, mas também a quantidade de memória que o MySQL pode usar é outra. (Lembre-se que você pode definir muitos desses por conexão, ao invés de configurar alguns deles globalmente.) Ao lidar com muitos dados, o MySQL tem um efeito de 'ponto de inflexão' onde as coisas são razoavelmente lineares até certo ponto. tamanho, e depois ir para o inferno de repente. Ele geralmente apresenta consultas complexas que trabalham com muitos dados e está relacionado a tamanhos de tabelas temporárias internas e à quantidade de memória que é permitido usar, mas pode resultar em alterações na tabela, pois envolvem a reindexação dos dados. Essa é uma razão pela qual dar mais memória a um banco de dados é quase sempre uma coisa boa.

    

Answer 2

Essa é a maior queixa que você já ouviu falar sobre o MySQL? Nossa, eu tenho um balde cheio de maiores do que isso ... (história para outro dia, talvez)

Sim, o MySQL bloqueia completamente uma tabela quando você está executando um ALTER TABLE nele; sem leituras ou gravações durante a duração, e as consultas que tentam fazer isso são pausadas até que sejam concluídas. Na ocasião estranha eu tenho que modificar o esquema de uma grande tabela MySQL (os pequenos completam sua alteração com rapidez suficiente para que não cause um problema perceptível). Eu normalmente apenas programo uma janela de manutenção e faço isso então. Não é difícil avaliar exatamente quanto tempo essa mudança levará para uma determinada tabela na réplica.

Se você tem uma daquelas gerentes de idiotas que se recusam a permitir que você tenha janelas de manutenção razoáveis (e, em caso afirmativo, corram para outro emprego), então ouvi falar de pessoas fazendo coisas como fazer uma cópia de uma tabela. esquema, modificando a tabela vazia, em seguida, fazendo uma cópia de seleção para a nova tabela (com um bloqueio de gravação no lugar para evitar alterações), em seguida, renomear as tabelas. Soa como muito risco para o meu gosto. O mk-table-sync do Maatkit tem um modo para fazer isso, se você preferir capaz de culpar alguém quando morre.

Embora o PostgreSQL não tenha o "bloqueio gigante da desgraça", você ainda tem um impacto significativo no desempenho para modificar o esquema de uma tabela no PgSQL - é um monte de E / S de disco. Eu não posso imaginar como qualquer RDBMS será capaz de evitar isso.