Otimização e Alterações a quente de tabelas Innodb no Mysql 5.6 acima
Olá leitor, tudo bem?
Em resumo, com a compra da MySQL pela Oracle, alguns de nós usuários e fãs do Mysql pensamos que poderia ser seu fim. Ou que problemas estariam à vista em pouco tempo. E é com muito prazer que hoje nós usuários do banco de dados MySQL percebemos que o que ocorreu é o contrário. O banco de dados MySQL está muito bem e cada vez mais forte.
A Oracle colocou na versão 5.6 em diante do Mysql vários recursos realmente importantes vindos do oracle knowledge base, entre eles a otmização e alteração de tabelas innodb a quente.
É claro! Não é mágico! Não basta instalar a nova versão e sair mandando ver! Por isso segue uma dica do seu amigo aqui!
Seguem as etapas: Planejamento, Execução, Guardando as ferramentas!
Planejamento:
Escolha a tabela que pretende otimizar, busque-a no banco “information_schema” com o seguinte comando:
[codesyntax lang=”sql”]
SELECT *
FROM information_schema.tables
WHERE table_schema = 'NomeDoBanco'
AND table_name IN ('NomeDaTabela');[/codesyntax]
Usei o “IN” no nome da tabela pois costumo eleger mais de uma para manutenção.
Anote os seguintes campos retornados da tabela: DATA_LENGTH e INDEX_LENGTH, some os 2.
Esse é o tamanho total da tabela em disco para o MySQL.
Precisamos agora alterar uma nova variável do MySQL da versão 5.6, que é a innodb_online_alter_log_max_size.
Busque-a assim por exemplo: SHOW VARIABLES LIKE ‘innodb_online%’; ou SELECT @@innodb_online_alter_log_max_size; E anote seu valor. Será necessário voltar mais tarde ao valor original. O valor padrão é 128Mb.
Pegue sua maior tabela a ser alterada, no meu caso a tabela tinha 1,080Gb de dados e mais 800Mb de índice. Totalizando quase 1,9Gb. Então setei essa variável com 2Gb de espaço, com o seguinte comando: SET GLOBAL innodb_online_alter_log_max_size = (2*1024*1024*1024);
Aproveito e deixo a dica: O Mysql armazena esses valores em bytes. Por isso fica chato ficar calculando. Seguindo o conceito matemático de como calcular Kb, Mb e Gb, use os multiplicadores (1024) para subir cada nível da conta. Assim de forma simples num exemplo, 800Mb = 800 * 1024(Mb) * 1024(Kb). Certo?
ALERTA: Esta operação online não é suportada para tabelas com índices FULLTEXT.
Execução:
Respire fundo e digite o comando: OPTIMIZE TABLE banco.nomeDaTabela;
Respire fundo novamente e execute.
Por que respirar fundo? Operações Alter Table e Optimize até a versão 5.5 faziam cópia, alteração e resconstrução das tabelas (ainda fazem se a variável innodb_online_alter_log_max_size não for grande o suficiente para guardar a tabela e suas alterações de uso (DML) enquanto o comando é executado.
Caso algo esteja errado e algum cliente do seu sistema tente usar um recurso que escreve ou faz JOIN para essa tabela, o sistema vai parar, conexão a conexão. O Número de conexões vai subir e um restart do banco será necessário caso não seja possível esperar o fim do comando enquanto vai matando as conexões clientes que chegam e competem pela atenção do banco. Enfim.. não é simples!
Por isso tenha bastante calma, não se afobe e execute esses comando com sabedoria e cautela.
Aqui no meu caso, enquanto executava o Optimize fiz testes de select, join e update nas tabelas e deu tudo certo. Mas são 23 horas! Não tenho nenhum cliente usando o sistema! Uma boa hora para testar a funcionalidade no banco produção.
Guardando as ferramentas:
Como perceberam, precisei retirar a variável innodb_online_alter_log_max_size de seu valor original para 2Gb. Isso não pode ficar assim, tanta memória é necessária ao servidor no dia a dia. Por isso é necessário voltar a variavel ao patamar padrão dela:
Usando o comando: SET GLOBAL innodb_online_alter_log_max_size = (128*1024*1024); /*128Mb*/.
Conclusão:
O uso do recurso de alteração e otimização on-line de tabelas é muito útil. Mas deve sempre que possível ser feita a manutenção fora do horário de trabalho, em sua janela de manutenção. É para ser usado quando sua janela está escassa.
Aqui na Móveis Simonetti faço uso de recursos que me permitem automatizar alterações em tabelas como eventos e CRON. Além é claro de tentar antever essas alterações sempre que possível.
Mas, em caso de emergência, ou de um novo recurso no sistema que vale a pena o risco, informe seus superiores e faça! E conte conosco sempre que precisar de uma dica.
[Crédito da Imagem: Tabelas InnoDD – ShutterStock]