Tengo una tabla MyISAM que actualmente contiene alrededor de 54 millones de registros y tiene un tamaño de 20 GB. Se tomaron malas decisiones al diseñar esta base de datos. No soy un profesional de DB, pero dado que esta tabla se escribe constantemente y rara vez se consulta, parece que InnoDB habría sido una mejor opción. El problema es que esta tabla tiene que estar disponible para escribir casi siempre. Uno de los campos es un campo con marca de tiempo. Los registros se remontan a 5 años atrás; solo necesito conservar los últimos 6 meses. Probé algunas eliminaciones, se necesitan unos 20 minutos para eliminar 200.000 filas durante las cuales la tabla está bloqueada y no se puede escribir en ellas. ¿Alguien tiene alguna sugerencia sobre cómo puedo reducir el tamaño de esta tabla sin que quede bloqueada durante varias horas? ¿Existe una forma más rápida de eliminar las filas innecesarias? Nunca convertí una tabla MyISAM en una tabla InnoDB. ¿Es este un proceso largo?
Respuesta1
Finalmente tuve tiempo de volver a visitar este proyecto y, para estar completo, quería publicar lo que terminé haciendo. Creo que el problema original con que DELETE FROM demore tanto está relacionado con la forma en que especificaba las filas que se eliminarían. Estaba usando una declaración similar a
DELETE FROM table WHERE starttime < 20150101 limit 200000;
El campo de hora de inicio es en realidad DATETIME. Terminé creando una nueva tabla usando
CREATE TABLE new_table LIKE existing_table;
y luego cambiando el motor de la nueva mesa
ALTER TABLE new_table ENGINE=InnoDB;
y luego cambiar el nombre
RENAME TABLE existing_table TO old_table, new_table TO existing_table;
Después de esto, necesitaba obtener datos de 6 meses de la tabla anterior a la tabla nueva.
INSERT INTO existing_table SELECT * FROM old_table WHERE starttime BETWEEN DATE_SUB(NOW(), INTERVAL 6 MONTH) AND NOW();
Gracias Martín por la sugerencia. Soy nuevo en serverfault, así que no estoy seguro de cómo marcaría su comentario como respuesta.
Respuesta2
MyISAM sólo sirve para SELECT. Innodb tiene una cola de LECTURA y ESCRITURA separada. Entonces, cuando elimine 200.000 filas, las tablas no se bloquearán. Los bloqueos de tabla son la gran limitación de MyISAM cuando se trata de producción. InnoDB es más lento que MyISAM para la mayoría de los usos, pero puede funcionar más rápido en ciertas condiciones debido a un mejor mecanismo de bloqueo; MyISAM bloquea toda la tabla para lectura mientras se ejecutan inserciones/actualizaciones. InnoDB puede realizar bloqueos a nivel de fila, lo que permite múltiples escrituras y lecturas simultáneas en la tabla.
http://kakadba.blogspot.in/2014/02/innodb-vs-myisam.html
Conversión de MyISAM a INNODB
Puedes hacerlo mesa por mesa.
ALTER TABLE table_name ENGINE=InnoDB;
O puedes probar el script para hacerlo todo a la vez.
https://stackoverflow.com/questions/3856435/how-to-convert-all-tables-from-myisam-into-innodb