A otimização da tabela MySql causando mais problemas do que resolve

Question

Eu tenho uma tabela altamente acessada correspondente às mensagens privadas do usuário, com mais de 10 milhões de linhas. Quando executo o processo de exclusão do usuário, as mensagens obsoletas são excluídas da tabela, geralmente com mais de 1000 linhas. O problema aumenta quando esta tabela é otimizada , porque isso leva quase 1 minuto, durante o qual a tabela é totalmente bloqueada e todas as consultas são atrasadas. O mesmo se aplica a outras grandes tabelas.

A pergunta é: posso simplesmente deixar a tabela permanentemente não otimizada sem grandes problemas de desempenho? Ou eu deveria otimizar a tabela pelo menos uma vez por semana, com um tráfego baixo para evitar problemas meus usuários on-line?

    

Answer 1

Sim, o desempenho vai se deteriorar - mas não podemos dizer com que rapidez, ou que nível de deterioração é aceitável - já que você já está fazendo isso por que não mede o impacto você mesmo .

Akber está correto em dizer que usar um cluster permitiria que você otimizasse um sistema enquanto o outro ainda oferecia dados - mas por que não configurá-los como um par master-master - então você não precisa atualizar e fazer o downgrade quando você troca. E não há necessidade de gravar registros temporários - apenas espere o atraso do servidor para recuperar e depois passar. É também uma ótima solução para backups e, claro, alta disponibilidade.

Outra solução já amplamente utilizada para esse tipo de exercício são as mudanças de esquema com tempo de inatividade zero - existem duas boas implementações que eu conheço: uma escrita em Perl pelo pessoal da Percona , e um escrito em PHP pelo pessoal do Facebook.

    

Answer 2

Suponho que você queira reorganizar as tabelas para remover o espaço ocupado pelas linhas excluídas.

• A solução mais fácil é por replicação:

  1. Replicar as tabelas ou o banco de dados inteiro para outro servidor ou instância mySQL (o escravo)
  2. Crie um usuário com privilégios somente leitura no escravo.
  3. Mude a conexão do aplicativo para o escravo com o usuário somente leitura. Mostrar mensagens apropriadas quando uma nova inserção de registro falhar.
  4. Otimize o mestre e, em seguida, volte atrás.

• Uma variação mais complicada disso pode ser usada para gravar registros temporários em outra tabela no escravo e copiá-los de volta para o mestre, mas isso envolveria alterações no nível do aplicativo.

• A solução ideal é ter uma fila de gravação e tabelas graváveis e somente leitura. Esse padrão de design é semelhante ao conhecido como CQRS . É baseado na consistência eventual e seus usuários podem nem notar um pontinho. Todas as transações de gravação serão gravadas na tabela, mas podem não estar disponíveis imediatamente, pois são mantidas na fila enquanto a tabela gravável está sendo reorganizada.