Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Is there anything wrong with running a dedicated server where both types of storage engines exist? Maybe rephrase to Multiple types? –  John Jan 7 '11 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. –  Derek Downey Jan 7 '11 at 16:55
add comment

3 Answers 3

up vote 11 down vote accepted

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.

share|improve this answer
3  
+1 Another common use case is MyISAM for tables requiring full text search and InnoDB for all other tables. –  Asaph Dec 19 '11 at 18:52
    
@Aseph: Even at the time of your comment, InnoDB supported Fulltext indices‌​. –  BlueRaja - Danny Pflughoeft May 29 '13 at 21:13
1  
^^^^ "MySQL 5.6 features" was not GA until 2013-02-05 –  randy melder May 30 '13 at 18:56
add comment

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.

share|improve this answer
    
archive engine doesn't support indexing so why you store it in archive. –  Jim Thio Jun 14 '12 at 8:24
add comment

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.

share|improve this answer
    
How can you have a foreign keys when you use MyISAM ? –  a_horse_with_no_name Apr 7 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 Apr 7 at 18:18
add comment

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.