3

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 Hampton Mar 4 '15 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/… – Martin Mar 4 '15 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 Southward Mar 4 '15 at 18:35
  • 2
    If 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 ? – Martin Mar 4 '15 at 19:04
  • Martin, I like this solution and I'm looking into implementing it. – Matt Southward Mar 5 '15 at 13:45
1

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.

1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.