Julius S. Julius S. - 14 days ago 5
MySQL Question

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

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?

Answer

The trope about MyISAM being faster than InnoDB is a holdover from code that was current about ten years ago.

MyISAM is not faster 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.

There are 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.

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.

Comments