Should I change my DB from MyISAM to InnoDB? (AWS notification)

3
votes

I am developing a browser app that connects to several MySQL-databases through php-sites. These databases are set on a single AWS RDS instance. When I set up these databases I was not very well informed about the different db engines. After reading about the different types I decided to use InnoDB for low IOPS demands and MyISAM for high IOPS demands as I read it was faster in certain tasks.

Amazon Web Services tells me that:

DB Instance test-1 contains MyISAM tables that have not been migrated to InnoDB. These tables can impact your ability to perform point-in-time restores. Consider converting these tables to InnoDB. Please refer to http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Overview.BackingUpAndRestoringAmazonRDSInstances.html#Overview.BackupDeviceRestrictions

Should I recreate the MyISAM db using InnoDB?

1
mysqlamazon-web-services
The fact that MyISAM is faster is only really relevant if you large data throughput requirements - for most CRUD applications doing typical web-application workloads you're much better-off with InnoDB for its stronger resiliancy and greater feature-set. – Dai

1 Answers

6
votes

The trope about MyISAM being faster than InnoDB is a holdover from code that was current in the mid-2000's.

MyISAM is not faster than InnoDB anymore, for most types of queries. Look at the benchmarks in this blog from 2007: https://www.percona.com/blog/2007/01/08/innodb-vs-myisam-vs-falcon-benchmarks-part-1/

InnoDB has just gotten better, faster, and more reliable since then. MyISAM is not being developed.

Update: In MySQL 8.0, even the system tables have been converted to InnoDB. There is clearly an intention to phase out MyISAM. I expect that it will be deprecated and then removed in future versions of MySQL (but I can't say how many years from now that will be).

There were a couple of edge cases where MyISAM might be faster, like table-scans. But you really shouldn't be optimizing your database for table-scans. You should be creating the right indexes to avoid table-scans.

Update Feb 2018: MyISAM just suffered an additional 40% performance hit from the recent fix for the Meltdown CPU bug, and this affects table-scans. Assuming you are responsible and patch your systems to fix the Meltdown vulnerability, MyISAM is now a performance liability. See current tests of MyISAM performance with the patch: https://mariadb.org/myisam-table-scan-performance-kpti/

But what trumps that is the fact that InnoDB supports ACID behavior, and MyISAM doesn't support any of the four qualities of ACID. See my answer to MyISAM versus InnoDB

Failing to support ACID isn't just an academic point. It translates into things like table-locks during updates, and global locks during backups.