Snappy Dashboards with Redis
It seems that almost every application I’ve worked on has some sort of dashboard component. It’s not usually the first feature but it often becomes the most useful. Dashboards are great because they tell you a little bit about everything.
But dashboards are hard to build.
They break resource-oriented design patterns and often rely on complicated one-off SQL queries. Dashboards also usually feature graphs and activity streams, both of which are not trivial to implement well.
At Sqoot, a local deal API, we have a simple dashboard for our customers. Given a time frame, it tells developers how many deals they’ve served (impressions), how many clicks they’ve driven, and how much affiliate revenue they’ve earned (earnings). It might also be interesting to know how many API calls they’ve made.
All of these stats follow a pretty standard query pattern:
SELECT COUNT(*) FROM collection WHERE USER = ? AND TIME > ? AND TIME < ? |
This works great for small collections or well-indexed relational tables. For example, earnings are reported exactly like this. But enormous collections, especially when stored in document-centric databases, become slow to query. This is especially true if you have to join disparate data sources.
So we developed another way to store the counts we need. It’s a thin Ruby wrapper around Redis and we call it The Count (ah ah ah ah ah!). Here’s how it works.
For every stat we’re interested in, we set a few keys:
{stat}/year:{YYYY}
{stat}/year:{YYYY}/month:{MM}
{stat}/year:{YYYY}/month:{MM}/day:{DD}
{stat}/user:{id}/year:{YYYY}
{stat}/user:{id}/year:{YYYY}/month:{MM}
{stat}/user:{id}/year:{YYYY}/month:{MM}/day:{DD} |
The first three keys tell us totals for all users by year, month, and day. The last three scope the same totals to one user. For example, if you want to know how many clicks user 5 drove in September:
REDIS.get("clicks/user:5/year:2012/month:09").to_i |
You can also query across date ranges with the help of some Ruby:
REDIS.mget([ "clicks/user:5/year:2012/month:06", "clicks/user:5/year:2012/month:07", "clicks/user:5/year:2012/month:08" ]).inject { |sum, i| sum + i.to_i } |
Every time an API call, impression, or click happens, we update all the relevant keys at once:
REDIS.multi { keys.each { |key| REDIS.incr key } } |
If Redis isn’t available, for whatever reason, we could rebuild the gaps from the canonical data in Mongo. We’ve never had to do this.
Now, when our customers load their dashboard, we don’t need to go slogging through millions of records. This makes our dashboards snappy. It’s also worth noting that this approach takes up very little space. If we segment by day, each year for each stat only uses 375 keys per user and another 375 for the aggregate data. The values are almost inconsequential since their just numbers. All our dashboard data is stored in this way with our friends at Redis To Go in a 20 MB instance.
A guest post by Avand Amiri co-founder of Sqoot.
Avand Amiri is the co-founder of Sqoot, a local deal API that helps developers monetize. He’s lesser known for half a dozen fledgling startups, including PiggyBack.it. Avand’s a product designer, Rubyist, DreamIt Ventures & 500 Startups alumnus, and recovering corporate.
October 16th, 2012 at 12:38 am (#)
Thanks for the blog post! Definitely a great use of Redis.
Stylistically, I’d recommend going with:
REDIS.mget([
"clicks/user:5/year:2012/month:06",
"clicks/user:5/year:2012/month:07",
"clicks/user:5/year:2012/month:08"
]).map(&:to_i).reduce(:+)
October 16th, 2012 at 12:50 am (#)
You could save some additional space by just using single letters. Seems small, but it could add up to a lot at scale. e.g. {stat}/u:{id}/y:{YYYY}/m:{MM}/d:{DD}. On that one key using single letters save 12 bytes (probably not exact depending on compression, but you get the idea).
October 16th, 2012 at 2:59 am (#)
You can reduce the key space further by using 1 character for the year (should keep you going for another 60+ years at least), and another for the month, and day. So the string
/y:YYYY/m:MM/d:DD
can be reduced to
/XYZ
where “X” corresponds to the year-2000 in Base-64 ; Y is a char from A-L, Z is a char in range A-Z0-4
Go from 16 chars to 3 …
October 16th, 2012 at 4:39 am (#)
Another often overlooked command to keep in mind when tracking with counters is hincrby (http://redis.io/commands/hincrby). If you can logically aggregate certain counters into a redis hash, you can group them up that way and still get atomic counter operations. Another advantage to this, iirc, is redis tends to be more space efficient with hashes than lots of individual string values. But this is always one of the reasons I like redis, you can map things a few different ways, and flex them to patterns your brain grasps better!
Thanks for sharing your work.
October 16th, 2012 at 6:20 am (#)
I know nothing of Ruby, but The Count is a winning name!
October 16th, 2012 at 7:12 am (#)
You might also consider using a standard date format like ISO-8601. It supports formats to the year, month, day, hour, minute or second granularity. Then the keys could looks something like
user:{id}/{ISO Date}
{ISO Date}
At that key another trick is to use of hash for the values with keys like ‘clicks’, ‘impressions’, etc.
October 16th, 2012 at 9:52 am (#)
nice and clean design
October 16th, 2012 at 6:12 pm (#)
Excuse me for a bit cynical comment, but… congratulations on reinventing RRD, except that you do less, from scratch and by hand.
The most serious problem is complex data accesses. As Redis database is a dictionary (hashmap), data is stored non-linearly and querying is O(n), so while simplest counters (n=1) are perfectly fine (and something as complex as RRD is surely an overkill for such case), something a bit more complex, like a relatively detailed graph may eventually become quite a resource hog.
November 6th, 2012 at 2:30 pm (#)
[...] Snappy Dashboards with Redis http://blog.togo.io/how-to/snappy-dashboards-with-redis/ [...]
November 6th, 2012 at 2:30 pm (#)
[...] Snappy Dashboards with Redis http://blog.togo.io/how-to/snappy-dashboards-with-redis/ [...]