Como converter uma tabela de 66.862.521 linhas de MyISAM para InnoDB sem ficar offline por várias horas?

Crie uma configuração Master-Master da seguinte forma:

• Criar segundo mestre, MasterB
• MasterB atua como escravo paralogTable
• Criar logTable_newcomo innodb
• Execute INSERT INTO logTable_new SELECT * FROM logTable(psuedocode) no MasterB, que envia a replicação para o MasterA
• Quando logTable_newno MasterA terminar a sincronização, troque as tabelas

Dada a restrição de:

Não me importo se as conversas demoram alguns dias ou semanas. Mas deve funcionar em segundo plano sem exigir tempo de inatividade do aplicativo e sem criar lag perceptível

Ao fazer o registro, se você tiver uma boa maneira de definir um marcador para poder dizer o que você inicia o processo, para poder reaplicar os registros ou gravar os registros em um arquivo de texto para você pode ingeri-los mais tarde comLOAD DATA INFILE

Parte do problema é que escrever em lotes menores significa que os índices precisam ser recalculados repetidamente; é melhor executar tudo de uma vez, mas isso pode causar algum atraso 'perceptível' no sistema .. mas você não precisa fazer isso no servidor de produção.

1. Pause o registro ou defina algum marcador para que você possa reaplicar os registros a partir deste ponto mais tarde.
2. Copie sua tabela MyISM para outro sistema
3. No outro sistema, crie uma tabela InnoDB com um nome diferente e migre os dados (pode até ser mais rápido despejá-los e usar LOAD DATA INFILE)
4. Copie a tabela InnoDB de volta para o sistema original
5. Defina outro marcador para o registro.
6. Reaplique todos os logs à nova tabela entre os dois últimos marcadores.
7. (repita as etapas 5 e 6 se a etapa 6 demorar mais de um minuto ou mais, até que sejam apenas alguns segundos)
8. Troque as tabelas (renomeie a antiga para table_BACKUP, a nova sob o nome da antiga)
9. Recupere os logs desde o último marcador.

Infelizmente, mesmo fazendo a cópia em pequenos lotes de 100 linhas gera um atraso significativo após algum tempo.

Você está adicionando algum atraso entre cada lote ou apenas agrupando as atualizações e executando cada lote diretamente após o anterior?

Nesse caso, tente fazer o script da conversão em seu idioma favorito com algo como:

repeat
    copy oldest 100 rows that haven't been copied yet to new table
    sleep for as long as that update took
until there are <100 rows unprocessed
stop logging service
move the last few rows
rename tables
restart logging
delete the old table when you are sure the conversion has worked

Isso deve garantir que a conversão não ocupe mais do que metade da capacidade do seu servidor, mesmo permitindo diferenças na carga imposta conforme o uso do sistema varia com o tempo.

Ou se você quiser usar o máximo de tempo possível quando o serviço estiver relativamente ocioso, mas recuar (possivelmente pausando por um longo período de tempo) quando o banco de dados precisar fazer algum trabalho para seus usuários, substitua sleep for as long as the update tookpor if the server's load is above <upper measure>, sleep for some seconds then check again, loop around the sleep/check until the load drops below <lower measure>. Isso significa que ele pode avançar em tempos de silêncio, mas fará uma pausa completa quando o servidor estiver ocupado executando sua carga de trabalho normal. A determinação da carga dependerá do seu sistema operacional - no Linux e semelhante, o valor médio de carga de 1 minuto /proc/loadavgou a saída de uptimedeve funcionar. <lower measure>e <upper measure>pode ser o mesmo valor, embora seja comum em controles como este ter uma diferença para que seu processo não continue iniciando e parando imediatamente devido ao seu próprio reinício ter influência na medida de carga.

É claro que isso não funcionaria para tabelas onde as linhas antigas podem ser modificadas, mas funcionará bem para uma tabela de log como a que você descreveu.

Você vai querer ignorar a sabedoria usual de criar índices depois de preencher a nova tabela neste caso. Embora isso seja realmente mais eficiente quando você deseja que as coisas sejam o mais rápido possível (que se dane o efeito no restante do sistema), neste caso você não deseja o grande excesso de carga no final do processo, pois o os índices são criados completamente de uma só vez, pois esse é um processo que você não pode pausar quando as coisas ficam ocupadas.

Algo assim funcionaria?

1. Pause o log (para que o $auto_incrementna sua tabela de log mytable não mude).
2. Observe o $auto_incrementvalor usando SHOW TABLE STATUS LIKE 'mytable'.
3. CREATE TABLE mytable_new LIKE mytable
4. ALTER TABLE mytable_new AUTO_INCREMENT=$auto_increment ENGINE=Innodb
5. RENAME TABLE mytable TO mytable_old, mytable_new TO mytable
6. Habilite o log novamente. A tabela Innodb agora começará a ser preenchida.
7. INSERT INTO mytable SELECT * FROM mytable_old.

Você pode executar a etapa 7 em lotes ou em uma instrução, pois não deve bloquear o registro normal.