This is the first out of a 2 part post about how we improved query performance on our analytics dashboard by over 7000x. All just by moving some of our data from MySQL to Redis. Part 1 will be a technical explanation of the setup, while part 2 will show the benchmarks we saw when comparing fetching data from both systems.

GROW is SOOMLA’s new user intelligence services presented by a brand new Analytics Dashboard. We wanted to provide mobile gaming studios with various ways to investigat e their games using informative and important data metrics. The problem was, queries were slow and the user experience was bad. Most of the slowness stemmed from the fact we used MySql to calculate unique users in multiple different filters which was a bad choice for real-time uniqueness calculations. We tried to figure out ways to improve that until we stumbled upon a new method for calculating unique users with Redis.

We were already using Redis at this point, but only for internal purposes, and not for serving data to our web app, so we decided on setting up different servers that would only serve data for the dashboard. We looked at different options (clusters coming to Redis 3, which has officially been released since this blog post was written, Redis Sentinel using at least 3 different servers) but decided that for our usage, setting up a simple master-slave duo would be enough. Failover will be taken care of semi-manually instead of the overhead of sentinels, once a crash is identified we run a script that notifies the slave to be master, and switches the IP on all servers that connect to Redis (we based some of our approach on some great advice by the awesome @jondot).

When looking to set up a few Redis servers, we saw 2 major options:

Setting up our own machines and running Redis off them

Using a cloud Redis service such as Amazon Elasticache/Azure Cache/redislabs

After considering pricing and our specific needs we decided to manage our own machine, which came to a third of the price of other self managed cloud services.

Here is our process of setting up 2 Redis machines as Master/Slave

We start with a fresh instance on Amazon EC2, using Ubuntu 14.04

EC2 was just our choice for testing purposes… you can absolutely select your preferred cloud service provider.

Setup

SSH in and add all necessary keys to ~/.ssh/authorized_keys

Install the latest version of Redis via Chris Lea’s PPA:

sudo add-apt-repository ppa:chris-lea/redis-server sudo apt-get update sudo apt-get install redis-server

redis-server should be running now

Run redis-benchmark -q -n 100000 -c 50 -P 12 to make sure everything is running ok

Config

Open /etc/redis/redis.conf and change the following settings:

tcp-keepalive 60

comment bind

This makes the machine accessible to anyone on the web

requirepass choose extremely secure password

The extreme speediness of Redis is a double edged sword when it comes to password protection.

An attacker can be able to try as much as 150,000 passwords/second so make that password secure.

maxmemory-policy noeviction

For our needs, no key can ever be deleted

appendonly yes

We will be using both AOF and RDB backups

appendfilename redis-[prod/stg]-s[1/2]-ao.aof

If configuring slave

This part is just for our slave

slaveof ip port

masterauth master password

Save and exit.

Restart redis with sudo service redis-server restart .

You should now be able to connect to redis via

redis-cli -h 127.0.0.1 -p [your port] AUTH ֿ

Machine Config

Install Git:

sudo apt-get update sudo apt-get install git

Install Node.JS + NPM:

We will migrate our data to Redis with some node scripts

sudo apt-get install nodejs sudo apt-get install npm sudo ln -s /usr/bin/nodejs /usr/sbin/node

Setting up semi-auto failover

We will set up the failover in a way where the redis server IP is an environment variable. On failure we will receive notification and set up a script that will:

switch the environment variable to be the IP of the slave machine

send the slave a SLAVEOF NO ONE command

command update the master’s configuration to be slave of the original slave machine

After that we can take our time and figure out why the master server crashed.

To setup the local variable

create a new file in /etc/profile.d with a .sh extension, the file content should be

export REDIS_SERVER=

To make changes have effect immediately run

source .sh

And make sure everything is set by running

printenv REDIS_SERVER

Now, in your server environment configuration, set the Redis server url to be

server: process.env.REDIS_SERVER

Now your server should successfully connect to Redis via the environment variable.

If you’re running your server as a service

In this case the daemon service will not recognise your environment variables, therefor you should inject the .sh in your daemon script in /etc/init.d/yourservice

the injection should look like this

source /etc/profile.d/.sh

inserted before the start/stop/restart functions.

That’s it for setting up our server, stay tuned for the 2nd part, how using Redis HyperLogLog made our queries 7000x faster.