Reduzindo o tamanho da tabela MyISAM

Question

Eu tenho uma tabela MyISAM que atualmente contém cerca de 54 milhões de registros e tem 20 GB de tamanho. Escolhas erradas foram feitas quando este banco de dados foi projetado. Não sou um profissional de banco de dados, mas como essa tabela é constantemente gravada e raramente consultada, parece que o InnoDB teria sido uma escolha melhor. O problema é que esta tabela deve estar disponível para gravação quase sempre. Um dos campos é um campo com carimbo de data/hora. Os registros remontam a 5 anos - só preciso manter os últimos 6 meses. Eu tentei algumas exclusões, demora cerca de 20 minutos para excluir 200.000 linhas durante as quais a tabela está bloqueada e não pode ser gravada. Alguém tem sugestões de como posso reduzir o tamanho desta tabela sem que ela fique bloqueada por várias horas? Existe uma maneira mais rápida de excluir as linhas desnecessárias? Nunca converti uma tabela MyISAM em uma tabela InnoDB. Este é um processo demorado?

Answer 1

Finalmente tive tempo de revisitar este projeto e, para completar, queria postar o que acabei fazendo. Acredito que o problema original de DELETE FROM demorar tanto está relacionado a como eu estava especificando as linhas a serem excluídas. Eu estava usando uma declaração semelhante a

DELETE FROM table WHERE starttime < 20150101 limit 200000;

O campo starttime é na verdade um DATETIME. Acabei criando uma nova tabela usando

CREATE TABLE new_table LIKE existing_table;

e então mudando o mecanismo da nova tabela

ALTER TABLE new_table ENGINE=InnoDB;

e depois renomear

RENAME TABLE existing_table TO old_table, new_table TO existing_table;

Depois disso, eu precisava transferir dados de 6 meses da tabela antiga para a nova tabela

INSERT INTO existing_table SELECT * FROM old_table WHERE starttime BETWEEN DATE_SUB(NOW(), INTERVAL 6 MONTH) AND NOW();

Obrigado Martinho pela sugestão. Sou novo no serverfault, então não tenho certeza de como marcaria o comentário dele como uma resposta.

Answer 2

MyISAM só é bom para SELECT. Innodb possui filas READ e WRITE separadas. Portanto, quando você excluir 200.000 linhas, as tabelas não serão bloqueadas. Os bloqueios de tabela são a grande limitação do MyISAM quando se trata de produção. O InnoDB é mais lento que o MyISAM para a maioria dos usos, mas pode funcionar mais rápido em certas condições devido a um melhor mecanismo de bloqueio; MyISAM bloqueia toda a tabela para leitura enquanto inserções/atualizações estão sendo executadas. InnoDB pode fazer bloqueio em nível de linha, permitindo assim múltiplas gravações e leituras simultâneas na tabela

http://kakadba.blogspot.in/2014/02/innodb-vs-myisam.html

Convertendo MyISAM para INNODB

Você pode fazer isso tabela por tabela

ALTER TABLE table_name ENGINE=InnoDB;

Ou você pode tentar o script para fazer tudo de uma vez

https://stackoverflow.com/questions/3856435/how-to-convert-all-tables-from-myisam-into-innodb