It is rather typical for systems to start as MyISAM but as system growths to move to Innodb. The reason of the move could be just desire for better data consistency guaranty or being bitten repairing multiple GB MyISAM table few times, though Table Locks is probably the most important issue – with modern multi core servers not only the fact you can’t well mix SELECTs and UPDATEs but also the fact only one update can be happening at the time can be the problem, not to mention Key Cache which often becomes serious contention issue.
The problem we often run into during migration is Full Text Search indexes which are not supported for Innodb tables. So what can you do ?
Leave Tables as MyISAM The beauty of MySQL storage engines is you do not have to convert all tables at once. In some cases full text search is used on secondary small tables which do not cause problems with contention or anything else. So this can be valid choice. Unfortunately in many cases the tables you want to do full text search on are intensively used and this may not be the option.
Use MyISAM Slaves In some cases it may be justified to keep table as MyISAM on one or several of slaves and use it for full text search queries. This approach is helpful if migration has to be performed very quickly and it takes a lot of time to implement any significant changes to schema or queries. In general cross storage engine replication is not my favorite approach but sometimes it is less of the evils.
Use “Shadow” MyISAM Table You can keep main data in Innodb but build a “shadow” MyISAM table which is used for full text search. In certain cases you can just use MySQL triggers to maintain such table, in other cases this would not work as this would add a lot of contention on the writes. In this case you can just rebuild the table from Innodb source on regular basics. In case you can afford to have stale data in search results it can work pretty well.
Use Sphinx or other external full text search engine This is what we use a lot especially if performance of full text search is an issue to. This eliminates the need to have MyISAM table anywhere. We use Sphinx in a lot of cases as it is very easy to get going. For simple applications it often takes just couple of hours to get the full text search running Sphinx instead of build in full text search.
If one doesn’t mind changing the structure of the table too much, maybe carve/partition out a MyISAM table which has just the column(s) that need the FT indexing and the key that binds the row back to the new InnoDB/XtraDB table row. Major caveat is that the application would need to manage two tables instead of one (insert/delete into both tables as needed). Performance wise though, insertions/deletions may be bottle-necked by the MyISAM table (because of the table locks). As an advantage though, lookups on data -not- involving the FT/text columns may improve.
To add to the above though, I still prefer to use Sphinx for any InnoDB table warranting FT indexing.
Mixing InnoDB and MyISAM on a single server is not a good idea, due to memory allocation issues. So the only realistic option is a MyISAM slave. Or go Lucene/Solr.
Imran,
Indeed if you create Shadow MyISAM table you would rarely keep it same as original table. You probably will not need a lot of indexes as well as many columns.
Jan,
Mixing MyISAM and Innodb on the same server add balancing challenge but I think it is far from “not a good idea”. You just need to consider your table sizes and usage pattern to balance between Innodb Buffer Pool size, Key Cache and OS Cache.
Peter, I assume your suggestion would be to e.g. give each database engine half the ram. Of course, that would work perfectly, but you are wasting precious memory. Having InnoDB and MyISAM “share” memory does not work very well.
So yes, you can do it, but I think other methods are better.
Since you’re adding another item to the database environment you increase the complexity for day-to-day things, but also migrations. The benefits gained are certainly nice, but I’m curious if anyone has any good practice recommendations for migration Sphinx from one box to another when you need to move MySQL.
Jan,
Do not forget MyISAM is pretty much always used for on disk temporary tables if you have Innodb so you never get off balancing problem completely.
Also you need to balance memory between innodb buffer pool and OS cache which is needed for some things, query cache etc.
Also setting it 50-50 is not a good method. The size and usage is what should drive the value. If there is single full text search table remains I would like to keep key_buffer same as .MYI takes (or expected to take) for the table.
Chritopher,
Yes. Everything has a cost. I would see it same as running memcache or other caching system with MySQL – you increase complexity you get performance.
About Migrations – Sphinx is a separate server and can be moved separately from MySQL.
Also because it pulls data from MySQL you can often move MySQL, when get the new sphinx installation running off that MySQL and when stop using old one.
how about just using mysql cluster? that should the prblem
Unless I’m missing your point, that would solve absolutely nothing.
I found a good match with moving full text searching to Solr.