Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

(Although I've tagged this as subjective, since it seems like it is, I'd prefer as objective answers as possible).

share|improve this question

5 Answers 5

up vote 6 down vote accepted

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.

share|improve this answer
    
If you are interested in benchmarks, try sysbench or iibench. –  Jauder Ho Apr 30 '09 at 9:39
    
Are any of the alternative storage engines stable enough that they are suitable for a production site? –  Tony Meyer Apr 30 '09 at 10:09
    
Don MacAskill is looking at putting XtraDB into production. YMMV. –  Jauder Ho Apr 30 '09 at 12:05
    
Performance is not the only requirement - consider operational issues as well (in fact they are usually more important, in my experience) –  MarkR May 18 '09 at 9:26
    
Obviously, performance is not the only requirement although I believe the original request was asking more about perf. Other considerations such as operational (as you point out) and features will all play a part in the selection process. –  Jauder Ho May 19 '09 at 20:09

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.

share|improve this answer
1  
For any workload that mixes reads and writes InnoDB is the only option (that is currently shipping). –  Dave Cheney Apr 30 '09 at 10:45

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.

share|improve this answer

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.
share|improve this answer
2  
Do you really think that the database engine is the developer's decision, not the administrators? As a developer, I want to say "I have this data, that I will be doing this with", and leave optimising (and backing up, and ...) of the database to the administrator. –  Tony Meyer May 18 '09 at 10:20
1  
Yes, the developer needs to be able to develop and test their app against a specific engine; they behave vastly differently both functionally and in performance. –  MarkR May 27 '09 at 21:46

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.

share|improve this answer
    
Only select count() from table is faster in MyISAM. Select count() from table where column=value has similar performance both on MyISAM and InnoDB. mysqlperformanceblog.com/2006/12/01/count-for-innodb-tables –  sumar Mar 3 '10 at 22:28

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.