Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

In my current use of MyISAM the databases are extremely small and kept for a few weeks after which time they are deleted and completely rebuilt with a clean slate. The script uses MYISAM tables and other than lack of support in the latest MYSQL versions, I have never had a problem with the way they work.
Changing to INNODB is something that would be time consuming and provide a zero benefit. However, I am concerned that eventually there may come a time when either the Older MySQL version is not available or some other process forces the move.

My question is: Does anyone know of a specific reason to change from MyISAM to MyinnoDB just to keep up with MySQL.

If I can keep using what works for my own specific use, I see no reason to worry with testing and possible errors when there are more pressing issues that do need attention that have problems now. MyISAM does not have a problem at this time.

share|improve this question
    
use myisam for data warehouse, use innodb for transactional/operational database. –  Neil McGuigan Jul 6 '13 at 18:30

1 Answer 1

Plenty of good reasons to move to InnoDB.

  • MyISAM has no crash recovery mechanism. A power break may render your tables useless. They can be corrupted (I've seen this happen plenty of times) and you may be able to salvage data -- or you may not be able to salvage data (and I've witnessed this as well multiple times). With InnoDB you have crash recovery mechanism.

  • MyISAM has no "hot backup", with the possible exception of file system/lvm/disk snapshots, which do require you to flush all tables to disk. You say your databases are extremely small, so this may not appeal to you too much; but the time it takes to flush your entire database to disk can be quite long for larger datasets. You have multiple ways of hot-backing up an InnoDB database.

  • MyISAM has poor index-write performance. Because of its lack of crash recovery mechanism, any write to an idnex page is immediately flushed to disk. I once consulted to a company where they had this MyISAM table being written to intensively -- it was so hard on the disk they had to put it on its very own server. Changed to InnoDB and Voila! All was relaxed: the transaction log takes care of all that for you.

  • MyISAM is very poor on highly concurrent apps. You can't write to the same table from two connections at the same time. This quickly leads to a bottleneck on heavy traffic sites, for example.

  • Likewise, once you do get a write lock on a table, no one else is then able to read from it (with a minor exception for specific INSERT types). With InnoDB you can have concurrent writes and non blocking reads.

  • MyISAM is hardly under any type of development. I don't recall any blog post by Oracle or anyone else saying "we've just improved foobar on MyISAM". InnoDB is under intensive development. You're likely to get new features and bugs fixes in InnoDB; not in MyISAM.

The above off the top of my head. I'm sure plenty more reasons will pop up once I submit; but these provide the general feeling for the disadvantages of MyISAM.

But - you said your databases are extremely small. Why then would it be so time consuming to migrate to InnoDB?

share|improve this answer
1  
+1 Another scenario I use to persuade people away from MyISAM: issue a long-running UPDATE and hit Ctrl-C halfway through. Some of the rows in your table will be updated, some will not be updated. MyISAM has no support for Atomic changes -- in addition to no support for Consistency, Isolation, and Durability. –  Bill Karwin Jul 8 '13 at 23:53
    
@BillKarwin indeed so. There is just no data guarantee whatsoever. –  Shlomi Noach Jul 9 '13 at 6:14
    
+1 for all the reasons, especially "hardly under any type of development". –  RolandoMySQLDBA Sep 30 '13 at 17:51

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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