There's nothing wrong with using multiple storage engines on the same physical machine, as long as you understand the pros and cons of each. There are performance considerations, feature limitations and use cases for all the plugin storage types.
For instance, if you have a small table that's 90% writes, you might choose MyISAM. If the data can be regenerated easily and it's a small table, say for queuing, you might choose Memory. If you have a table that's 90% reads, and the data has got to be there when you look for it, then you'd probably choose a storage engine that supports transactions and configurable atomicity, such as InnoDB. If you want accessibility through the file system w/o damaging data, you might choose CSV.
Nonetheless, you can safely use multiple storage engines within the same schema as well as the physical host.
Let me note though, that your buffers play a role in this whole mess. If you use both MyISAM and InnoDB, you will need to be careful that your key_buffer and innodb_buffer_pool do not contend. This will take careful planning on your part, but that's what we do.
The only way MyISAM can be faster that InnoDB would be under this unique circumstance
MyISAM
When read, a MyISAM table's indexes can be read once from the .MYI file and loaded in the MyISAM Key Cache (as sized by key_buffer_size). How can you make a MyISAM table's .MYD faster to read? With this:
ALTER TABLE mytable ROW_FORMAT=Fixed;
I wrote about this in my past posts
InnoDB
OK, what about InnoDB? Does InnoDB do any disk I/O for queries? Surprisingly, yes it does !! You are probably thinking I am crazy for saying that, but it is absolutely true, even for SELECT queries. At this point, you are probably wondering "How in the world is InnoDB doing disk I/O for queries?"
It all goes back to InnoDB being an ACID-complaint Transactional Storage Engine. In order for InnoDB to be Transactional, it has to support the I
in ACID
, which is Isolation. The technique for maintaining isolation for transactions is done via MVCC, Multiversion Concurrency Control. In simple terms, InnoDB records what data looks like before transactions attempt to change them. Where does that get recorded? In the system tablespace file, better known as ibdata1. That requires disk I/O.
COMPARISON
Since both InnoDB and MyISAM do disk I/O, what random factors dictate who is faster?
- Size of Columns
- Column Format
- Character Sets
- Range of Numeric Values (requiring large enough INTs)
- Rows Being Split Across Blocks (Row Chaining)
- Data Fragmentation caused by
DELETEs
and UPDATEs
- Size of Primary Key (InnoDB has a Clustered Index, requiring two key lookups)
- Size of Index Entries
- the list goes on...
Thus, in a heavy-read environment, it is possible for a MyISAM table with a Fixed Row Format to outperform InnoDB reads out of the InnoDB Buffer Pool if there is enough data being written into the undo logs contained within ibdata1 to support the transactional behavior imposed on the InnoDB data.
CONCLUSION
Plan your data types, queries, and storage engine real carefully. Once the data grows, it might become very difficult to move data around. Just ask Facebook...
Best Answer
To be honest, with a low traffic environment running on 5.1, you shouldn't have any problems with MyISAM. The biggest downfall of MyISAM is table-locking WRITES, IMO. But with limited data in the tables, this won't be noticeable.
MyISAM is probably easier to manage from a DBA standpoint with recovery and repair.
And in 5.1, I think InnoDB's benchmarks aren't really up to par. I could be wrong on this, so I'm going to look it up again!
Edit: Quick google turned up this article in 2009 using 5.1 http://www.mysqlperformanceblog.com/2009/01/12/should-you-move-from-myisam-to-innodb/
Gives some nice talking points, specifically the section on InnoDB needing tuning.
HTH