MyISAM versus InnoDB

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?

Every application has it's own performance profile for using a database, and chances are it will change over time.

The best thing you can do is to test your options. Switching between MyISAM and InnoDB is trivial, so load some test data and fire jmeter against your site and see what happens.

If you use MyISAM, you won't be doing **any** transactions per hour, unless you consider each DML statement to be a transaction (which in any case, won't be durable or atomic in the event of a crash).

Therefore I think you have to use InnoDB.

300 transactions per second sounds like quite a lot. If you absolutely need these transactions to be durable across power failure make sure your I/O subsystem can handle this many writes per second easily. You will need at least a RAID controller with battery backed cache.

If you can take a small durability hit, you could use InnoDB with innodb_flush_log_at_trx_commit set to 0 or 2 (see docs for details), you can improve performance.

There are a number of patches which can increase concurrency from Google and others - these may be of interest if you still can't get enough performance without them.

In my experience, MyISAM was a better choice as long as you don't do DELETEs, UPDATEs, a whole lot of single INSERT, transactions, and full-text indexing. BTW, CHECK TABLE is horrible. As the table gets older in terms of the number of rows, you don't know when it will end.

myisam is a NOGO for that type of workload (high concurrency writes), i dont have that much experience with innodb (tested it 3 times and found in each case that the performance sucked, but it's been a while since the last test)
if you're not forced to run mysql, consider giving postgres a try as it handles concurrent writes MUCH better

For that ratio of read/writes I would guess InnoDB will perform better.
Since you are fine with dirty reads, you might (if you afford) replicate to a slave and let all your reads go to the slave. Also, consider inserting in bulk, rather than one record at a time.

I think this is an excellent article on explaining the differences and when you should use one over the other:

[To see links please register here]

I've figure out that even though Myisam has locking contention, it's still faster than InnoDb in most scenarios because of the rapid lock acquisition scheme it uses. I've tried several times Innodb and always fall back to MyIsam for one reason or the other. Also InnoDB can be very CPU intensive in huge write loads.

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

[MyISAM tables use table-level locking][1]. 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.


[1]:

[To see links please register here]

I tried to run insertion of random data into MyISAM and InnoDB tables. The result was quite shocking. MyISAM needed a few seconds less for inserting 1 million rows than InnoDB for just 10 thousand!