The 5 minute DBA: Storage Engine Selection

I was soooo tempted to post a 1 word post on this, just posting “Innodb”. It would be very easy, and its the advice that I am going to give in the end anyways. After all we need to get back to the real reason behind these 5 minute DBA topics. These are supposed to be a short tidbit of advice to those who are not going to spend more then 5 minutes making a decision. What’s easier then a single word. Right? But that would be lazy… so. Here goes.

Currently there are only 2 main storage engines that you can consider for your general purpose DB’s. Innodb or MyISAM. While these two storage engines are included with your MySQL installation, there are several storage engines that are in some “pre”-release state that could shake things up in the future.  Because of their relative youth and official release status of these engines, I would probably hold off deploying them unless you are you sure you need them for something.  So in the end  Your probably going to be looking at Innodb or MyISAM.

If your setting up your DB in 5 minutes and you don’t know what the database is going to be used for, my gut says stick with Innodb.  Innodb is going to be the safest choice for your multi-purpose database.  Typically MyISAM does a bang up job in environments where you have a lot of inserts & selects, but little if any deletes & updates (think data warehouse or reporting databases). I could go through a long litany of things that MyISAM does slightly better, but in the end most people who are deploying Web & OLTP systems are just going to want the features in Innodb. Others have written about this, in fact Peter Z. wrote an outstanding article on choosing between MyIsam and Innodb and why he would recommend Innodb so I will point you there and save some typing.

But if you wanted my two second criteria of when to use each :

MyISAM

  • Lots of Selects/inserts
  • Little Updates/deletes
  • No need for transactions
  • OLAP
  • lots of Select count(*) type queries
  • Need native full-text searching
INNODB

  • Mixed workload ( selects/updates/deletes/inserts )
  • OLTP type
  • need transactions
  • general purpose
  • Excellent database recovery ( crash consistancy )

Bottom line most database deployments I see going in should be Innodb… so for you five minute dba’ers stick with that. And for those MyIsam fans out there, no disrespect. MyISAM has its place, its just if I was going to have non-dba’s choose a storage engine I think Innodb is a better fit for them.

As for other storage engines. As they reach more maturity and get officially released I will come back and refresh this ( there are some really outstanding features in some of the open source storage engines ). What are the Some of the other engines?

  • PBXT – its in release candidate stage

  • Innodb plugin – according to the web in “early adopter release”

  • XtraDB – built from the innodb plugin with lots of extra features and performance enhancements from lots of different sources. ( An honest question here, since the innodb plugin is an early adopter release is XtraDB an earlier adopter release as well? )

  • Maria – Currently Alpha

  • Falcon – Currently alpha

This entry was posted in 5 minute dba, mysql. Bookmark the permalink.

One Response to The 5 minute DBA: Storage Engine Selection

  1. Michel says:

    With Yves’s suggestion, we took out of NDBCluster all the smallest lookup tables from our code (For example Sex, containing Undefined, Male, Female).

    We tried InnoDB, that simply borked with the load we made it suffer through. However, MyISAM was excellent an way quicker than InnoDB, and especially more stable at high loads.

    It’s interesting to have many different tools in the same MySQL database, as sometimes, it’s better to use a fly swatter to kill a fly, instead of using a sledgehammer. True, the fly is dead on both, but the sledgehammer has a ever-so-slightly longer recoil time. :)