Eu tenho uma tabela MySQL com dados de 3GB e o ALTER TABLE para criar um novo campo dura cerca de 15 minutos na pré-produção.
O software pode fazer consultas de leitura e gravação com o novo campo ou sem ele, para que eu possa atualizar o banco de dados sem alterar o software.
Existe alguma maneira de executar isso de alguma forma como "background" e deixar que todos os ciclos de CPU atendam primeiro as "outras consultas" e somente se houver CPU livre, execute o comando ALTER?
Qual é a maneira correta de fazer uma "tabela de alteração longa" na produção?
Sim, você pode. Uma simples pesquisa no Google encontrou esta resposta no site da irmã SE: link
You can use the LOW_PRIORITY or HIGH_PRIORITY in your queries depending on the type of query you execute:
INSERT [LOW_PRIORITY | HIGH_PRIORITY] INTO ... SELECT [HIGH_PRIORITY] * FROM ... UPDATE [LOW_PRIORITY] table ...
A CPU raramente é o verdadeiro gargalo. Geralmente é o disco, com a quantidade de memória disponível para o buffer pool também sendo uma restrição.
O problema é que os recursos necessários para fazer um ALTER TABLE geralmente não são seu problema real ... é o fato de que a tabela provavelmente será bloqueada durante a operação.
Não há como des-priorizar as consultas no MySQL, mas se houvesse, provavelmente prolongaria sua agonia, já que o bloqueio no nível da tabela persistiria por mais tempo.
Tente isto:
ALTER TABLE t1
ADD COLUMN c2 BIGINT UNSIGNED NOT NULL AFTER c1,
LOCK=NONE,
ALGORITHM=INPLACE;
Se o servidor não permitir que você faça isso - o que deve resolver seu problema -, a mensagem de erro deverá explicar o motivo.
Se o motivo estiver relacionado a chaves estrangeiras, mas sua consulta ALTER não fará nada para perturbar a integridade da chave estrangeira, você poderá desabilitar as verificações que impediriam a execução de uma alteração in-loco sem bloqueios.
SET @@FOREIGN_KEY_CHECKS = 0; -- before ALTER
SET @@FOREIGN_KEY_CHECKS = 1; -- after ALTER
Isso faz com que não desative as restrições de chave estrangeira na tabela. Ele apenas isenta qualquer consulta que você execute da sua conexão atual da necessidade de tomar medidas para evitar a violação da integridade relacional. Contanto que você não esteja fazendo nada para perturbar a integridade relacional, é uma estratégia segura de usar, mas somente quando você precisar.