25

I've got a single database of about 4.5GB running on a server with 8GB RAM. The vast majority of the tables are MyIsam (about 4.3GB), but I'm soon going to be converting some of them to InnoDB. (It's going to be a slow process, focusing on the most write-intensive tables at first).

Is there anything wrong with running a dedicated server where both types of storage engines exist?

2
  • Is there anything wrong with running a dedicated server where both types of storage engines exist? Maybe rephrase to Multiple types?
    – John
    Commented Jan 7, 2011 at 16:46
  • The only reason I said 'both' is because this question is about the two main engines that are configured to be 'finely tuned'. I'm trying to ignore other types of engines like MEMORY or MERGE, which are uncommon enough that afaik aren't the object of performance tuning. Commented Jan 7, 2011 at 16:55

3 Answers 3

20

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.

5
  • 4
    +1 Another common use case is MyISAM for tables requiring full text search and InnoDB for all other tables.
    – Asaph
    Commented Dec 19, 2011 at 18:52
  • @Aseph: Even at the time of your comment, InnoDB supported Fulltext indices. Commented May 29, 2013 at 21:13
  • 2
    ^^^^ "MySQL 5.6 features" was not GA until 2013-02-05
    – randomx
    Commented May 30, 2013 at 18:56
  • 1
    @randymelder Nice answer. Would you be able to elaborate on what you meant by "You will need to be careful that your key_buffer and innodb_buffer_pool do not contend"?
    – Neel
    Commented Oct 16, 2014 at 20:25
  • 1
    I want to be proved wrong, but the logic looks opposite of this stackoverflow.com/a/6796566/5645769 here. Commented Jan 11, 2016 at 4:36
7

I can't tell you if this is a common practice. I can say about my own experience.

I always use the best tool for the job, so I mix engines all the time. Most of my projects use MyISAM as the default engine.

When I need special features just available on InnoDB, I go for it.

When a table is mostly read-only, I choose Archive engine before I could blink.

Knowing that machine server has enough memory, all my temp data are store on Heap tables.

I saw in the past some slowdowns mixing MyISAM and InnoDB but this is not a specific MySQL problem. It's a design problem not seeming when you use just one engine. Actually using the wrong engine causes more slowdown doesn't matter if it is just MyISAM, just InnoDB or a mix of both. It's hard to define a formula to know when the slowdown would happen. Just actual tests could say it to you.

Of course, you couldn't preserve integrity and consistency mixing InnoDB and MyISAM on an unique query.

1
  • archive engine doesn't support indexing so why you store it in archive.
    – user4951
    Commented Jun 14, 2012 at 8:24
0

I try to avoid mixing MyISAM and InnoDB tables in the same database, but this is for sanity rather than practical reasons. However, I find it useful to have a database with MyISAM tables for fulltext search so I can run that on sites. Keeping it in a separate database with a foreign key for each entry makes it easy for any other developers working on the DB to see what's going on.

2
  • How can you have a foreign keys when you use MyISAM ?
    – user1822
    Commented Apr 7, 2014 at 18:17
  • Wrong terminology, but works like a foreign key. I store the id number of the item from the InnoDB table and use that to do the page lookup on search result.
    – Kenzo
    Commented Apr 7, 2014 at 18:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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