How do you choose a MySQL database engine
In particular, how do you choose between MyISAM and InnoDB, when neither is missing a required feature (e.g. you don't need foreign keys).
Does it always come down to trying both and measuring? Or are there good rules of thumb regarding the number and frequency of reads versus writes, and other measures like that? Does the size of the table have any effect on the typical choice?
Solution 1:
The answer is you should always measure, preferably with your own data and workload if at all possible.
Since the data access patterns can vary greatly from app to app, it's hard to say and in all likelihood impossible to determine a "best" storage engine for all workloads.
However, there are very encouraging developments in the MySQL space having attended MySQLConf/Percona Performance Conf last week.
Some of the alternative storage engines:
- XtraDB (fork of InnoDB)
- InnoDB plugin
- PBXT
- TokuDB
Additionally, Percona, Google, etc. have contributed patches that help greatly with InnoDB performance. Personally, I run an OurDelta build. It works nicely for me and I encourage checking out the OurDelta and Percona builds.
Solution 2:
If it's just a simple store / report system I use MyISAM for its raw performance.
I'd use InnoDB if I was concerned about multiple concurrent accesses with lots of writes, to take advantage of row-level locking.
Solution 3:
There are a good number of benchmarks out there for different MySQL database engines. There's a decent one comparing MyISAM, InnoDB and Falcon on the Percona MySQL Performance Blog, see here.
Another thing to consider between the two aforementioned engines (MyISAM and InnoDB) are their approaches to locking. MyISAM performs table-locking, whilst InnoDB performs row-locking. There are a variety of things to consider, not only downright performance figures.