How do you choose a MySQL database engine

Question

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?

Answer 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:

1. XtraDB (fork of InnoDB)
2. InnoDB plugin
3. PBXT
4. 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.

Answer 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.

Answer 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.

Answer 4

There are features that you will find very useful, for operational reasons, even if your application doesn't absolutely require them:

• InnoDB has MVCC which means you can take non-blocking consistent backups
• InnoDB has automatic recovery which means no lengthy REPAIR TABLE operations after an unclean shutdown
• With InnoDB, readers never block writers and vice versa, meaning (generally speaking) greater concurrency (this need not mean better performance in the general case though)
• InnoDB clusters its rows on the primary key, which MAY mean fewer IO operations for read operations IF the primary key was chosen sufficiently well.

So notwithstanding foreign key constraints, you probably want to use InnoDB anyway.

of course this is ServerFault, not Stack Overflow, so the proper answer is:

• You must always use the engine that the application developers have chosen
• If they haven't chosen a specific engine, they aren't very serious about using MySQL, and probably don't know how to use it properly.
• You can't switch to a different engine to the one which your application was tested on, it may introduce bugs.

Answer 5

My hosting provider advised us to get rid of MyISAM completely and switch to InnoDB, unless it is not possible.

In our case we were having severe data corruption which started to show from a few times to a few times per day, always requiring REPAIR TABLE and related commands, which took ages on large tables.

Once we converted (or: were converted) to InnoDB, the problems instantly went away. Downsides/caveats we had:

• Can't convert tables with FULLTEXT index (this problem went away over time by itself; it was replaced with a Solr/Lucene based solution which has much better quality anyway)
• Big tables with millions of rows which often require COUNT(*) were very slow, we were not able to switch those either.

But note: this is all specific to our environment, etc., so it may not generally apply.