Páginas

quarta-feira, 26 de dezembro de 2012

Manutenção em Tabelas no MySQL


Hoje eu vou falar sobre operações que fazem parte da vida de um DBA MySQL, que são as manutenções de tabelas, que é um processo extremamente necessário para o desempenho e integridade do seu banco de dados, dividi esse assunto em dois posts no qual vou explicar cada processo de manutenção com suas suas devidas observações e scripts para automatizar esse processo.
Manutenção nas tabelas do mysql são necessarias para identificar e corrigir problemas de integridade, ocasionados por diversos fatores como bugs, problemas no disco e no servidor e também para fornecer mais velocidade ao MySQL, veremos o porquê, a seguir os tipos de manutenção que podemos realizar:
  • A Ferramenta check realiza uma verificação de integridade para se certificar de que a estrutura da tabela e o conteúdo não têm problemas. Esta operação poderá ser realizada em tabelas MyISAM e InnoDB.
  • A Ferramenta repair corrige problemas de integridade para restaurar a tabela para um estado conhecido utilizável. Esse comando só tem suporte para tabelas MyISAM.
  • A Ferramenta analyze, atualiza as estatísticas sobre a distribuição dos valores de índice de chave. Esta é uma informação que o otimizador pode usar para gerar melhores planos de execução para consultas sobre as tabelas .Esse comando pode ser feito tanto em tabelas MyISAM como em tabelas InnoDB.
  • A Ferramenta optimize reorganiza uma tabela para que seu conteúdo pode ser acessado de forma mais eficiente. Esse comando pode ser feito em tabelas MyISAM ,InnoDB e Archive, mas com limitações que variam entre essas storages engines.
As Ferramentas analyze e optimize são operações que você pode executar periodicamente para manter suas tabelas com sua melhor performance:
  • Quando o MySQL analisa uma tabela MyISAM ou InnoDB, ele atualiza as estatísticas de índice. O otimizador usa essas estatísticas ao processar consultas para tomar melhores decisões sobre a melhor forma de procurar registros na tabela e na ordem em que ele precisa ler tabelas em um join.
Quando o MySQL Otimiza uma tabela MyISAM, ele desfragmenta o datafile para recuperar o espaço não utilizado, classifica os índices e atualiza as estatísticas de índice. Periódicamente a desfragmentação é necessária para acelerar o acesso as tabelas que contêm colunas de comprimento variável como VARCHAR, VARBINARY, BLOB, ou TEXT. Inserções e exclusões podem resultar em muitas lacunas em tais tabelas, particularmente aqueles que são modificados com freqüência. Desfragmentando elimina essas lacunas.

CHECK TABLE

A instrução CHECK TABLE executa uma verificação de integridade de conteúdo e estrutura da tabela. Ele funciona para tabelas MyISAM e InnoDB,ARCHIVE, and CSV. Para tabelas MyISAM, ele também atualiza as estatísticas de índice. Se a tabela é uma view, CHECK TABLE verifica a definição da view. Se a saída de CHECK TABLE indica que uma tabela possui problemas, a tabela deve ser reparada.

mysql> check tables action;

+---------------------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------------------+-------+----------+----------+
| qb3_migracao.action | check | status | OK |
+---------------------+-------+----------+----------+
1 row in set (15.93 sec)

Para maiores informações consulte o link :

http://dev.mysql.com/doc/refman/5.5/en/check-table.html


REPAIR TABLE

O REPAIR TABLE corrige problemas em uma tabela que foi corrompida.
Ele só funciona para tabelas MyISAM,ARCHIVE, and CSV.
Para executa-lo é necessário ter privilégios de Select e insert sobre a tabela.
È extremamente importante antes de executar um repair table, que seja feito um backup dos dados.
Se o servidor falhar durante a execução do REPAIR TABLE, é essencial depois de reiniciá-lo de que você execute imediatamente outro REPAIR TABLE na tabela antes de executar quaisquer outras operações sobre ela. Na pior das hipóteses, você pode ter um novo arquivo de índice limpo sem informações sobre o arquivo de dados e, em seguida, a próxima operação de executar poderia substituir o arquivo de dados. Este é um cenário improvável, mas possível que ressalta o valor de fazer um backup primeiro.
Obs: O Comando Repair table por default escreve no log binario e se for um servidor master de uma replicação, vai replicar a instrução para todos os slaves, então nesse caso é necessario que se rode a opção NO_WRITE_TO_BINLOG.

Maiores informações acesse :

ANALYZE TABLE

O ANALYZE TABLE atualiza a tabela com as informações sobre a distribuição das chaves da tabela. Essas informações são usadas pelo optmizador para fazer melhores escolhas sobre os planos de execução das consultas SQL.
Para executa-lo é necessário ter privilégios de Select e insert sobre a tabela.
Obs: O Analyze table por default escreve no log binario e se for um servidor master de uma replicação, vai replicar a instrução para todos os slaves, então nesse caso é necessario que se rode a opção NO_WRITE_TO_BINLOG.
O MySQL utiliza a distribuição de chaves para decidir a ordem em que as tabelas devem ser unidas quando você executar um join em alguma coisa que não seja uma constante. Além disso, a distribuição das chaves podem ser usadas para decidir quais os índices de uma tabela será utilizado dentro de uma consulta.

mysql> analyze table action;
+---------------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------------------+---------+----------+----------+
| qb3_migracao.action | analyze | status | OK |
+---------------------+---------+----------+----------+
1 row in set (0.00 sec)

Maiores informações acesse :

OPTIMIZE TABLE

A instrução optimize table efetua a desfragmentação de uma tabela MyISAM. Ela recupera o espaço não utilizado, causado pela fragmentação das operações delete e update. Optimize table table classifica as paginas e as estatisticas do indices.
Optimize table também funciona em tabelas innodb, porém não efetua a desfragmentação das tabelas, para isso é necessario que se execute um ALTER TABLE nome_da_tabela engine=innodb;
È interessante ressaltar que essa operação é extremamente custosa, bloqueia as tabelas, e se for feita em produção, é necessario que seja realizado em um horário que que não seja o de pico.
Obs: O optimize Table por default escreve no log binario e se for um servidor master de uma replicação, vai replicar a instrução para todos os slaves, então nesse caso é necessario que se rode a opção NO_WRITE_TO_BINLOG.

mysql> OPTIMIZE TABLE action;
+---------------------+----------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------------------+----------+----------+----------+
| qb3_migracao.action | optimize | status | OK |
+---------------------+----------+----------+----------+
1 row in set (0.04 sec)

Maiores informações acesse :

Agora que falei das ferramentas vou mostrar uma procedure para automatizar esses recursos:
A procedure abaixo realiza um optimize em todas as tabelas do banco que você especificar, e dependendo da sua demanda você pode transforma-la em um evento do mysql, ou ainda criar um script para chama-la de tempo em tempo.


DELIMITER &&
CREATE PROCEDURE OPTIMIZE_TABLES()
BEGIN
DECLARE v_nome_table varchar(60);
DECLARE done INT DEFAULT 0;
DECLARE cur1 CURSOR FOR select table_name from information_schema.columns where table_schema='NOME_DO_BANCO' ;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur1;
REPEAT
FETCH cur1 INTO v_nome_table;
IF NOT done THEN
SET @v_comando := concat("OPTIMIZE TABLES nome_do_banco.", v_nome_table, ";");
PREPARE stmt FROM @v_comando;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
end if;
UNTIL done END REPEAT;
CLOSE cur1;
END
&&
DELIMITER ;

Bom pessoal, este post é nos mostra como podemos efetuar manutenções em tabelas nos schemas do MySQL, com os comandos suportados pelo nosso SGBD. Qualquer dúvida no Post, favor deixar nos comentários para que possa ajudar. Que a Graça e Paz estejam com vocês.

2 comentários:

  1. Quando você diz "periodicamente" para analize e optimize, isto séria diariamente? quinzenal? mensal? Essa periodicidade depende do tamanho do banco?

    ResponderExcluir
  2. Como posso fazeer para rodarem todas as tabelas de um determinado banco tanto o ANALYZE como o OPTIMIZE ?

    ResponderExcluir