Team assignments outgrew its data-store

What are Team Assignments?

Team Assignments are used by organizations to assign social messages to their team members. If a message comes through one of your social streams in HootSuite, you can assign it to a particular user, or team, to respond to. Details on how we did it after the fold.

What was the problem?

We had this data sitting in Memcached, backed by MySQL. This solution was put together when we had a much smaller user base and wasn’t designed to scale at the rate it needs to now. Memcached tried as hard as possible to be a persistent data-store, with MySQL handling the heavy lifting of sorting the data in the Team Assignments list view and bringing Memcached back up to speed when Memcached fell over. Unfortunately, you’ve likely already guessed how that was going. MySQL was too often filling the disaster recovery role for Memcached, being that there was no quick failover solution in place. This was okay when there was only a small amount of teams using it, but it has been growing in popularity quickly!

Redis to the rescue!

Redis is basically Memcached, with a few extra data-types and persistent storage. In addition to Strings, you also get Hashes, Lists, Sets, and Sorted Sets. Redis also gives you the ability to intersect sets and store those results if need be, super-charging how you can store your data. Redis also has… wait for it… multiple fail-over solutions! Pow Pow Pow: all of our requirements in one neat little package.

Our SQL data for this example is structured like so:

create table assignments {
id int PRIMARY KEY auto_increment not null;
team int
organization int
social_network int
network_type enum
from_member int
to_member int
created_date datetime
status enum
resolved_date datetime

*Please excuse my pseudo SQL if you spot any problems
Disclaimer: Field names have been changed for security purposes

This data populates multiple views

Dashboard Stream View

If you’ve ever used our Team Assignments feature, you’ll recognize the yellow and blue wrappers around messages in your dashboard. If you haven’t, it looks like this:

Screen Shot 2014-01-25 at 4.48.07 PM

Team Assignments List View

This is the view that you get when you click “Assignments” in our menu bar on the left.

Screen Shot 2014-01-25 at 4.53.04 PM

Notice the List View gives you many ways to sort your data. This is a challenge in Redis, because you can’t query it like an RDBMS. You don’t get any powerful sorting or querying language — after all, these are just keys that contain values with very little actual structure.

How our data is structured in Redis

We create a Hash of each assignment

A Hash allows us to store all of the data for each assignment. We store it like so:

$data = array(
'team' = $team,
'organization' = $organization,
'socialNetwork' = $socialNetwork,

$redis->hMSet('assignment:256359', $data);

We take each of the columns in MySQL and create a set from them

Each of our fields become Sets of their own. Each of these Sets have assignment’s that belong to that key.

  • team
  • organization
  • social_network
  • from_member
  • to_member
  • created_by

With these sets, we use SMEMBERS (Set Members) to get all members (values) in the set. We can do the following to find all assignment id’s for organization 256359:

$ redis-cli> SMEMBERS organization:256359
1) "2049130"
2) "2199636"

If somebody adds a new assignment for organization 256359, we get the id for that assignment and add it to the set with SADD:

$ redis-cli> SADD organization:256359 $assignment

If we want to grab all RESOLVED tickets for organization 256359, we do an intersect on the two sets with SINTER, like so:

$ redis-cli> SINTER organization:256359 status:RESOLVED
1) "2049130"
2) "2199636"

We have all of our keys, now, to solve this problem of sorting on multiple data-points

In order to solve our sorting problem, we create an md5 hash of all of our search parameters to store as a key with our intersected sets as the value. Let’s say we want all tickets for organization 256359 that are open, and assigned to member 2176483.

$searchTerms = array(
'organization' => 256359,
'toMember' => 5339920,
'status' => 'open');
$searchHash = md5($searchTerms);

$searchKey = 'search:' . $searchHash . ':sorted';
foreach($searchTerms as $key => $value) {
$intersectVal .= "orgnization:"
. $searchTerms['organization'] . " ";
$intersectVal .= "to_member:"
. $searchTerms['toMember'] . " ";
$intersectVal .= "status:"
. $searchTerms['status'];

$redis->SInterStore($searchKey, $intersectVal);

Still to come…

It’s filling RAM faster than Firefox 4. My next blog will likely cover a solution to that problem.

Until next time, cheers!


Interested in Redis, memcached, and building amazing software? Join us at HootSuite.