MyISAM versus InnoDB [closed]

I'm working on a projects which involves a lot of database writes, I'd say (70% inserts and 30% reads). This ratio would also include updates which I consider to be one read and one write. The reads can be dirty (e.g. I don't need 100% accurate information at the time of read).
The task in question will be doing over 1 million database transactions an hour.

I've read a bunch of stuff on the web about the differences between MyISAM and InnoDB, and MyISAM seems like the obvious choice to me for the particular database/tables that I'll be using for this task. From what I seem to be reading, InnoDB is good if transactions are needed since row level locking is supported.

Does anybody have any experience with this type of load (or higher)? Is MyISAM the way to go?

Solution 1:

I have briefly discussed this question in a table so you can conclude whether to go with InnoDB or MyISAM.

Here is a small overview of which db storage engine you should use in which situation:

                                                 MyISAM   InnoDB
----------------------------------------------------------------
Required full-text search                        Yes      5.6.4
----------------------------------------------------------------
Require transactions                                      Yes
----------------------------------------------------------------
Frequent select queries                          Yes      
----------------------------------------------------------------
Frequent insert, update, delete                           Yes
----------------------------------------------------------------
Row locking (multi processing on single table)            Yes
----------------------------------------------------------------
Relational base design                                    Yes

Summary

• In almost all circumstances, InnoDB is the best way to go
• But, frequent reading, almost no writing, use MyISAM
• Full-text search in MySQL <= 5.5, use MyISAM

Solution 2:

I'm not a database expert, and I do not speak from experience. However:

MyISAM tables use table-level locking. Based on your traffic estimates, you have close to 200 writes per second. With MyISAM, only one of these could be in progress at any time. You have to make sure that your hardware can keep up with these transaction to avoid being overrun, i.e., a single query can take no more than 5ms.

That suggests to me you would need a storage engine which supports row-level locking, i.e., InnoDB.

On the other hand, it should be fairly trivial to write a few simple scripts to simulate the load with each storage engine, then compare the results.

Solution 3:

People often talk about performance, reads vs. writes, foreign keys, etc. but there's one other must-have feature for a storage engine in my opinion: atomic updates.

Try this:

1. Issue an UPDATE against your MyISAM table that takes 5 seconds.
2. While the UPDATE is in progress, say 2.5 seconds in, hit Ctrl-C to interrupt it.
3. Observe the effects on the table. How many rows were updated? How many were not updated? Is the table even readable, or was it corrupted when you hit Ctrl-C?
4. Try the same experiment with UPDATE against an InnoDB table, interrupting the query in progress.
5. Observe the InnoDB table. Zero rows were updated. InnoDB has assured you have atomic updates, and if the full update could not be committed, it rolls back the whole change. Also, the table is not corrupt. This works even if you use killall -9 mysqld to simulate a crash.

Performance is desirable of course, but not losing data should trump that.