Practical Distributed Processing Using MySQL Built-In Functionality

A couple of years ago at our company, the idea came up to rank the most influential people in the social media space, per topic. Since the calculations for this are substantial and chew on a lot of data, I figured it should run in the database. “Oh yeah,” I said, “I’ll code that. How long could it take?” Famous last words.
It worked for a while running in the main database, but since then we have a lot more data and the “influencer” calculation has grown much more complex. Several months ago, due to the load this calculation placed on the main database, we moved it off to a dedicated set of servers. The work is done there and the results are sent back, and it’s been working quite well for months.

This system works because our application of it satisfies several requirements:

• the calculation doesn’t require our “bulkiest” tables — the involved tables are long but not wide
• in some cases, only a small subset of columns on wide tables is required
• the calculation is relatively isolated from the main system
• the resulting data is contained in individual MyISAM tables

This presentation will begin by reviewing several key concepts that are the foundation of this system, including:

• replication
• replication into blackhole tables with triggers
• federated tables
• scp (ssh file copy) using public-key authentication

After the concept review, there’s a high-level overview of our distributed system:

• general architecture
• queueing
• worker coordination
• result delivery

Finally, we’ll look at the code involved. A generalized (but functional) version of the work-distribution code will be available for download following the presentation. Even if this system isn’t an exact match to your intense-computation problem, I’m confident that seeing what we did will lead you to your own solution.