The last time Hackerfall tried to access this page, it returned a not found error. A cached version of the page is below, or clickhereto continue anyway

Benchmarking MySQL Against Redis | DAQRI Engineering Blog

Recently, we looked at using MySQL vs Redis for a small key/value dataset with mostly hot values. Mostly we looked at session stores for Rails, some cached copies of user permissions, plus a few other things with pretty much the same requirements.

The question we wanted to answer was, is MySQL fast enough that it wont be a significant drag on our Rails app to store the sessions there? Or is Redis mandatory in this case? When you scale out infrastructure, it can be nice to leverage some of the mature, powerful MySQL tools over some of the less mature Redis clustering/reliability stuff.

But only if its fast enough, obviously.

I thought the results were interesting, so Im sharing with you.

The Benchmark

Designing a good general-purpose benchmark and taking all the possible factors into account is hard.

So I didnt do that.

Instead, were using a very simple key/value lookup scheme with a very small set of cached data, just like the production system this is testing. I did not turn on the MySQL query cache, which is often the right answer even in production. It can get you in trouble. Theres a reason it defaults to being off.

Similarly, I used pretty much entirely default Redis settings. This is a tiny amount of data, both for the benchmark and in production. It needs to be served fast, but were talking about permissions data and session stores for (one segment of) a site that serves few simultaneous users. Not only does session data easily fit in RAM, it doesnt come close to straining RAM. The benchmark is really tiny, but our production data for this specific service is also quite small and will be entirely in cache.

So: dont treat this as a fully general Redis/MySQL benchmark. It really, really isnt.

I also didnt use any special capabilities of either one. The session store and permissions are trivial key/value lookup, so thats what I benchmarked. Ill repeat this one more time, so drive-by commenters look silly: this is a benchmark of our specific use case and no other.

Im also showing the results from localhost, but also giving you the code necessary to run it with your use case. In our case, network overhead to MySQL and Redis will be nearly identical so its a non-issue and just makes the speeds even closer.

The Results

For 10,000 repetitions (after 100 warm-up-the-cache requests), Redis takes about 6.3 seconds versus MySQL about 5.3 seconds. Thats about 53 usec versus 63 usec to process each request. While repeated runs keep showing about this much difference, thats not enough to matter for our specific use case - for us, the speed is effectively identical.

This is a full round trip including time for Ruby to make a call through a block, not just the raw datastore speed. Im using the MySQL2 gem and the Redis gem, which is also what we use. I suspected this would be true, though I didnt actually expect MySQL to win. I just thought it would be very competitive. But were calling both from Ruby, and this benchmark verifies that neither Redis nor MySQL is slow enough to be the bottleneck in that case.

BIG DISCLAIMER: this does NOT use ActiveRecord. With ActiveRecord the same requests take 21.0 seconds, or 210 usec/query, or more than 4x as long. You can see that in the benchmark code too. This turns out not to be a problem in our use case simple key/value queries, right? But I am not saying ActiveRecord is as fast as X for any value of X. ActiveRecord is far slower than the Ruby Redis gem, or the Ruby mysql2 gem, or many other things. Were just not using it so we dont care.

MethodTime/ReqTime Total Redis63 usec6.3 sec MySQL (raw)53 usec5.3 sec ActiveRecord210 usec21.0 sec

##Please Dont Apply This Generally

Note that for stuff that wont be in cache, none of this is accurate. For complex queries or data sets, none of this is accurate. When your Redis server has very different round-trip latency, none of this is accurate. If you repost this on Reddit and say see? MySQL is always faster than Redis! I will be very, very disappointed in you. Mostly because you use Reddit, but also because youre wrong about the benchmark.

Heres the code for the benchmark.

Continue reading on engineering.daqri.com