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.