The Value of MySQL Storage Engines

Or, The World May Be Coming to an End

Something very disturbing happened yesterday. I started liking the idea of MySQL storage engines.

It happened after reading the second comment of this post. In retrospect this article had a lot of influence on my thinking as well, but it was the idea of pluggable storage engines as a way to advance new and better storage mechanisms - not just new and different database systems - that really changed my thinking.

So, in the coming weeks, I'll be doing what I can to learn more about the MySQL storage engines I've heard mentioned recently (namely ScaleDB, Tokutek, KickFire, InfoBright and a few others) and posting what I learn here. If there are others you think I should pay attention to please let me know.

RAM Storage is not the Answer... At Least not for MySQL

There's an interesting article over at MySQL Performance Blog that talks about some MySQL benchmarks using RAM-based storage.

Here's the most interesting part (to me, anyway):
However even with MyISAM we got CPU bound before we could reach the system capacity - these 70K queries/sec generated just over 50K IOs/sec while capacity was over 100K IOs/sec

I think Peter hits the nail on the head when he theorizes about the cause of the performance problems he saw:

My guess is Innodb just was not really designed and tested in such condition - normal case is to allocate cache memory to buffer pool so IOs will mostly come from drives directly

We all know that I'm not a big fan of MySQL, so I'm prone to believe that other systems might handle this better. But the point still holds - RAM storage is not the answer. Having super-fast I/O won't solve your performance problems unless the DBMS you're using is designed to take advantage of it. And given that every DBMS I can think of is designed around the assumption that I/O is expensive and contortions to avoid it are cheap, odds are that the database you're using isn't designed to take advantage of it. So don't bet that super-fast storage - RAM-based or otherwise - will solve your performance problems.

In short: think smaller, not faster.