MyISAM — An old but still really useful storage engine

It’s been that INNODB has been the default MySQL storage engine for some years. For good reason, foreign keys and index level locking make it a lot more advanced. All of that functionality comes at a cost which is performance. Sadly, you don’t get functionality for free.

I have seen this first hand at work a couple of years ago. We had a table that was pretty simple, just four integers per record but was about 600 Million records long. It was also badly designed because someone felt it was necessary to index each field uniquely. It was using the INNODB storage engine. Now the use case of this table was as a log. Something would happen in the system and we’d store this event in this table. The record would not be updated or deleted in normal operation and searches against the table were usually for small sets of data. We didn't have any use of referential integrity. So it was pretty simple.

Once the table reached a certain size our application performance started to struggle. It was taking 15 minutes to insert 300k records into the table. We did some other tests and it was taking minutes to insert a single record. What was going wrong? It turns out that MySQL was creating a roll back for the statement and for a large table this roll back creation and committing of the change was really slow and expensive.

As I said, we didn't need any fancy locking as we were just appending data to the table. We weren't using foreign keys and all of the good stuff that comes with INNODB. So MyISAM was a viable option for us. One additional benefit of MyISAM is that it is great for log tables. It allows you to append data to the end of the table without requiring a full table lock.

MyISAM usually only supports table locking. So if it needs to lock, the entire table locks and any operations must wait for the connection holding the lock to release it. For a transnational table this is disastrous. This is why MyISAM is not the default and why it is not used a lot.

So we did a test of converting from INNODB to MyISAM on a test environment. the process was slow. It took the best part of day to convert the table over. Once the conversion was finished we did a few tests. It tooks 2 seconds to insert 500k records versus 15 minutes with INNODB on the same test server. WOW what a performance gain! So naturally we were sold right there and then.

How do we get that into production? We can’t have 24 hours downtime. Well we were only using it as a log table. So we knew that all of the new data was at the end of the table. Things were stored ordered by a auto incrementing primary key. So we restored a recent backup to another server, did the conversion offline. We also took the opportunity to get the indexes right. We didn't need an index for each field. So we were able to rationalise these down to two, to reduce disk space and also improve insert speeds.

We then copied the database files to production and loaded them onto the server. Then we inserted the last couple of days records from the INNODB table into MyISAM and in under two hours of downtime we had a MyISAM table.

Application performance was out of this world. We saw inserts run faster in production than they did in our testing environment. We also saw a reduction in disk utilisation as we weren't producing these large roll backs.

So after this we saw that MyISAM is really awesome for log files. It is missing some polish, but if you don’t care about transactions, foreign keys, index level locking and all of those goodies it’s fantastic. It’s a real speed demon. The other downside is that MyISAM is harder to backup and using things like mysqldump can be a hinderance due to table locks.

So from this experience my storage engine selection criteria is that I use MyISAM for log tables and for reporting tables with low write concurrency. So if you are faced with using tables for these purposes, I’d suggest considering using MyISAM.