The Methods of Sharding MySQL

When a component of your organization goes from medium to large in its size or volume, there are a series of performance problems that must be addressed. Sharding the cluster of MySQL databases is almost always the proper solution, but there are many different ways of sharding. This tutorial will go over some of them with actual working examples from high-volume shops in the past.

When a part of your infrastructure gets to its maximum utilization, you need to break it into multiple pieces. Here are the major ways of sharding:

  1. By Application or Functionality - taking sets of related tables that are busy and putting them onto their own servers.
  2. By ID - taking portions of a particular table and moving them out onto their own servers, typically by hashing a row ID.

Some of the tools that will be useful in sharding:

  1. Vitess/Vtocc - from YouTube, this does query rewriting and multiplexing.
  2. HAProxy - a service for handling MySQL connections to many servers.
  3. Gizzard - a sharding software framework for rolling your own distributed database on existing infrastructure components.
  4. JetPants - an automation toolkit for handling large/complex MySQL replication topologies.

In this tutorial, we'll set up one each of the sharding tools and use them to show how to distribute your load across MySQL servers.

Track: 
Tutorials
Experience level: 
Advanced

Schedule info

Room: 
Nolita

Schedule Info

1 October 14:00 - 17:00 @
Nolita

Speakers

Tim Ellis's picture
CTO, PalominoDB

Tim Ellis presided over some of the larger MySQL installations during the 2000-2011 timeframe at such places as Digg, Mozilla, Riot Games (League of Legends), and StumbleUpon. Running large scaled database installations using Opsdev methodologies was his specialty.

In the last 5 years, he has begun specialising in building hybrid database clusters, using MySQL for relational data and various distributed databases (often called "NoSQL") to store key/value or sparse hash map data.

He has always been a strong proponent of using the right tool for the job, which can sometimes be a surprising and radical notion.

Slides


Terminal Output


Screenshots



Sponsored By