Scalability of Using MySQL as a Key/Value Database

Question

I am interested to know the performance impacts of using MySQL as a key-value database vs. say Redis/MongoDB/CouchDB. I have used both Redis and CouchDB in the past so I'm very familiar with their use cases, and know that it's better to store key/value pairs in say NoSQL vs. MySQL.

But here's the situation:

• the bulk of our applications already have lots of MySQL tables
• We host everything on Heroku (which only has MongoDB and MySQL, and is basically 1-db-type per app)
• we don't want to be using multiple different databases in this case.

So basically, I'm looking for some info on the scalability of having a key/value table in MySQL. Maybe at three different arbitrary tiers:

• 1000 writes per day
• 1000 writes per hour
• 1000 writes per second
• 1000 reads per hour
• 1000 reads per second

A practical example is in building something like MixPanel's Real-time Web Analytics Tracker, which would require writing very often depending on traffic.

Wordpress and other popular software use this all the time: Post has "Meta" model which is just key/value, so you can add arbitrary properties to an object which can be searched over.

Another option is to store a serializable hash in a blob but that seems worse.

What is your take?

Answer 1

There is no doubt that using a NOSQL solution is going to be faster, since it is simpler.
NOSQL and Relational do not compete with each other, they are different tools that can solve different problems.
That being said for 1000 writes/day or per hour, MySQL will have no problem.
For 1000 per second you will need some fancy hardware to get there. For the NOSQL solution you will probably still need some distributed file system.

It also depends on what you are storing.

Answer 2

I'd say that you'll have to run your own benchmark because it is only you that knows the following important aspects:

• the size of the data to be stored in this KV table
• the level of parallelism you want to achieve
• the number of existing queries reaching your MySQL instance

I'd also say that depending on the durability requirements for this data, you'll also want to test multiple engines: InnoDB, MyISAM.

While I do expect some NoSQL solutions to be faster, based on your constraints you may find out that MySQL will perform good enough for your requirements.

Answer 3

SQL databases are more and more used as a persistance layer, with computations and delivery cached in Key-Value repositories.

With this in mind, those guys have done quite a test here:

• InnoDB inserts 43,000 records per second AT ITS PEAK*;
• TokuDB inserts 34,000 records per second AT ITS PEAK*;
• This KV inserts 100 millions of records per second (2,000+ times more).

To answer your question, a Key-Value repository is more than likely to outdo MySQL by several orders of magnitude:

Processing 100,000,000 items:

kv_add()....time:....978.32 ms
kv_get().....time:....297.07 ms
kv_free()....time:........0.00 ms

OK, your test was 1,000 ops per second, but it can't hurt to be able to do 1,000 times more!

See this for further details (they also compare it with Tokyo Cabinet).

Answer 4

Check out the series of blog posts here where the author runs tests comparing MongoDB and MySQL performance, and fights through the MySQL performance tuning mess. MongoDB was doing ~100K row reads per second, MySQL in c/s mode was doing 43K max, but with the embedded library he managed to get it up to 172K row reads per second.

It sounds a little complicated to get that high on a single node, so ymmv.

The writes/second question is a little harder, but this still might give you some ideas on configs to try.