MyISAM vs InnoDB

[Nathan]

An application I've been troubleshooting has had major performance issues last week.  I spent a few hours on it banging my head against a wall.  Finally after doing some research I found that a lot of the database tables were created years ago in the MyISAM storage engine.  After reading further I saw that for this application at least, InnoDB made much more sense.  I'm actually not sure why you would ever use MyISAM anymore really...

The server itself was showing massive MySQL connections at any give time, 20k-30k!  There were not near that many people online so I was confused as to what was happening.  I knew the page that was causing a majority of the issues though as it was showing massive load times, approximately 40 seconds.  I tracked down the query and it was this:

select lat, lon, createDate
from activityTracking
where userID = 404
order by id desc
limit 1

There were already indexes on the userID field and id is a primary key on the table.  The query would run for about 4 seconds and it was in a PHP loop for multiple records that were displaying on the page, so 4 seconds about 10 times = 40 second page load time.  The query at first look seems to be very efficient, it has indexes, and order by and limit.  So I randomly removed the limit 1 from the query just to see what would happen.

select lat, lon, createDate
from activityTracking
where userID = 404
order by id desc

This time it executed in .0099 seconds.  How can that be?  Well the table itself have records inserted every second, probably 100 records per second, so there must be some table locking going on or some index being rebuilt way too often that's causing my issue I figured.  That's when I stumbled upon the MyISAM and InnoDB storage engine questions and explanations.  

InnoDB:
The InnoDB storage engine in MySQL.

• Support for transactions (giving you support for the ACID property).
• Row-level locking. Having a more fine grained locking-mechanism gives you higher concurrency compared to, for instance, MyISAM.
• Foreign key constraints. Allowing you to let the database ensure the integrity of the state of the database, and the relationships between tables.
• InnoDB is more resistant to table corruption than MyISAM.
• Support for large buffer pool for both data and indexes. MyISAM key buffer is only for indexes.
• MyISAM is stagnant; all future enhancements will be in InnoDB. This was made abundantly clear with the roll out of Version 8.0.

MyISAM:
The MyISAM storage engine in MySQL.

• Simpler to design and create, thus better for beginners. No worries about the foreign relationships between tables.
• Faster than InnoDB on the whole as a result of the simpler structure thus much less costs of server resources. -- Mostly no longer true.
• Full-text indexing. -- InnoDB has it now
• Especially good for read-intensive (select) tables. -- Mostly no longer true.
• Disk footprint is 2x-3x less than InnoDB's. -- As of Version 5.7, this is perhaps the only real advantage of MyISAM.

MyISAM Limitations:

• No foreign keys and cascading deletes/updates
• No transactional integrity (ACID compliance)
• No rollback abilities
• 4,284,867,296 row limit (2^32) -- This is old default. The configurable limit (for many versions) has been 2**56 bytes.
• Maximum of 64 indexes per table

InnoDB Limitations:

• No full text indexing (Below-5.6 mysql version)
• Cannot be compressed for fast, read-only (5.5.14 introduced ROW_FORMAT=COMPRESSED)
• You cannot repair an InnoDB table

So that was my issue, my select is getting queued up behind the inserts that were firing and causing the long query times.  I logged into phpmyadmin and modified the table to InnoDB and all of a sudden my queries were running in .0004 seconds.  The page then loaded in less than a second and everything was all set.  I'm still not sure why you would ever use MyISAM tables.  InnoDB is something I'll be using going forward for sure.

Here's a little more info on MyISAM vs InnoDB - http://blog.danyll.com/myisam-vs-innodb/

[Tony]

You said ~100 inserts per second? Are those part of a batch operation or is an insert script being called 100 times per second?

[Nathan]

30 minutes ago, Tony said:

You said ~100 inserts per second? Are those part of a batch operation or is an insert script being called 100 times per second?

Not a batch.  They are API calls that come in from different clients/sources in different intervals.

[2G_]

Thank you for the article, you have answered my question on what format is more appropriate to my application.

However, I must add that there are instances where MyISAM would be more appropriate.

For instance, I have static data that is dumped into my tables once a month, but the data is queried constantly, as it is usually the case with archival or analysis data.
Since there are no insert, update or delete transactions that run other than at the beginning of the month, MyISAM is actually a better choice for this types of cases.

Edited January 16, 2019 by 2G_