I have a MyISAM table that currently contains about 54 million records and is 20 GB in size. Poor choices were made when this database was designed. I'm no DB pro but since this table is constantly being written to and infrequently being queried, it seems InnoDB would have been a better choice. The problem is this table has to be available for writing to almost always. One of the fields is a timestamped field. Records go back 5 years--I only need to keep the last 6 months. I've tried some deletes, it takes about 20 minutes to delete 200,000 rows during which the table is locked and cannot be written to. Does anyone have suggestions on how I can reduce the size of this table without it being locked for several hours? Is there a faster way to delete the unneeded rows? I have never converted a MyISAM table to an InnoDB table. Is this a lengthy process?
-
Do you really need to delete 200,000 rows at once?– Michael HamptonMar 4, 2015 at 18:30
-
The conversion process can be tricky depending on your constraints and you have a very large table size; some details: dev.mysql.com/doc/refman/5.0/en/…– MartinMar 4, 2015 at 18:32
-
I really need to delete about 50 million rows in total. I've been doing this a little and a long during maintenance windows. I have only one 2 hour maintenance window per week that I'm authorized to do this work and can delete about 1 million rows during this time. By the time next week rolls around, there has been almost 500,000 new rows entered.If I was able to convince management that 5-6 hours and its done, I can get the window extended. (This is my first post on here, thank you for the quick response).– Matt SouthwardMar 4, 2015 at 18:35
-
2If it's infrequently being queried is it possible for you to make a new innodb table with same definitions, copy a maintenance windows worth of data to it and switch them to writing to that table instead? You could copy of 6 months of data over time ?– MartinMar 4, 2015 at 19:04
-
Martin, I like this solution and I'm looking into implementing it.– Matt SouthwardMar 5, 2015 at 13:45
2 Answers
I finally had time to revisit this project and for completeness sake wanted to post what I ended up doing. The original problem with the DELETE FROM taking so long, I believe, is related to how I was specifying rows to be deleted. I was using a statement similar to
DELETE FROM table WHERE starttime < 20150101 limit 200000;
The starttime field is actually a DATETIME. I ended up creating a new table using
CREATE TABLE new_table LIKE existing_table;
and then changing the new table's engine
ALTER TABLE new_table ENGINE=InnoDB;
and then renaming
RENAME TABLE existing_table TO old_table, new_table TO existing_table;
After this I needed to get 6 months worth of data from the old table into the new table
INSERT INTO existing_table SELECT * FROM old_table WHERE starttime BETWEEN DATE_SUB(NOW(), INTERVAL 6 MONTH) AND NOW();
Thank you Martin for the suggestion. I'm new on serverfault so I'm not sure how I would mark his comment as an answer.
MyISAM is only good for SELECT. Innodb has separate READ and WRITE queue. So when you are deleting 200,000 rows tables will not be locked. Table locks is the one big limitation of MyISAM when comes to production. InnoDB is slower than MyISAM for most uses, but can perform faster in certain conditions due to a better locking mechanism; MyISAM locks the whole table for reading while inserts/updates are executing. InnoDB can do row-level locking, thus allowing multiple concurrent writes and read on the table
http://kakadba.blogspot.in/2014/02/innodb-vs-myisam.html
Converting MyISAM to INNODB
You can do it table by table
ALTER TABLE table_name ENGINE=InnoDB;
Or you may try script to do it all at once
https://stackoverflow.com/questions/3856435/how-to-convert-all-tables-from-myisam-into-innodb