segunda-feira, 17 de novembro de 2014

Alterações a quente de tabelas Innodb no Mysql

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:
SELECT *
FROM information_schema.tables
WHERE table_schema = 'NomeDoBanco'
AND table_name IN ('NomeDaTabela');
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.

Abraços e até a próxima!

2 comentários:

  1. Marcelo, gostei muito da abordagem!! Vale ressaltar mesmo que antes de qualquer alteração em variáveis dinâmicas, é necessário verificar se há recurso de máquina disponível para ser utilizado afim de evitar problemas com o conhecido SIGNAL 11 e trambém, porque qualçquer lateração realizada no em variáveis dinâmicas no MySQL, requer processamebnto adicional, o que não será interessante para ambientes com grande pressão de processamento, ou mesmo, CPU Bound. Com respeito às variáveis de ambiente, quando se deseja voltar o valor original das mesmas, basta utilizar a keywork DEFAULT, como exemplifico abaixo:

    ndbx mysql> select format((@@innodb_online_alter_log_max_size/1024/1024),0) as innodb_online_alter_log_max_size\G
    *************************** 1. row ***************************
    innodb_online_alter_log_max_size: 128
    1 row in set (0.00 sec)

    ndbx mysql> SET GLOBAL innodb_online_alter_log_max_size=256*1024*1024;
    Query OK, 0 rows affected (0.01 sec)

    ndbx mysql> select format((@@innodb_online_alter_log_max_size/1024/1024),0) as innodb_online_alter_log_max_size\G
    *************************** 1. row ***************************
    innodb_online_alter_log_max_size: 256
    1 row in set (0.00 sec)

    ndbx mysql> SET GLOBAL innodb_online_alter_log_max_size=DEFAULT;
    Query OK, 0 rows affected (0.00 sec)

    ndbx mysql> select format((@@innodb_online_alter_log_max_size/1024/1024),0) as innodb_online_alter_log_max_size\G
    *************************** 1. row ***************************
    innodb_online_alter_log_max_size: 128
    1 row in set (0.00 sec)

    Happy MySQL' ing!!

    ResponderExcluir
  2. Grande Wagner!
    Muito obrigado. É sempre bom contar com seus conhecimentos!

    ResponderExcluir