A Summary of Scaling Options for MySQL

Unfortunately, there isn’t one silver bullet scaling solution that best suits all projects. Each project has unique requirements that should be considered when choosing a scaling strategy. This is a topic that comes up often, so after many trips through the analysis, I thought I’d write up a high-level summary of the options.

For the attention deficit, here are the shortcuts to the good stuff:

1. Benefits of Scaling

2. Costs of Scaling

3. Project Considerations

4. Scaling Options

5. Scaling Vertically

6. Scaling Horizontally

7. Active-Passive

8. Master-Slave

9. Cluster

10. Sharding

Benefits of Scaling

Scaling a database can become necessary for a variety of reasons:

1. Support a higher volume of users

2. Provide better performance for existing users

3. Store a larger volume of data

4. Improve system availability

5. Geographic dispersion (generally related to performance and availability)

Costs of Scaling

Of course all these benefits of scaling a database come at a cost. Some of the costs to consider include the following:

1. Cost of hardware – It goes without saying that upgrading existing servers or adding new servers to a system will incur hardware and/or hosting costs

2. Deployment effort – whether it’s time spent by in-house developers or the cost of external contractors, you should consider the effort to install and configure hardware, software, and networking. In some cases, application code changes might even be required.

3. Maintenance – As the system becomes more complex, the maintenance costs grow in a variety of areas: documentation, training, backups, deploying application enhancements, etc.

Project Considerations

Every project has its own unique requirements and characteristics that will shape the selection of the appropriate scaling option. Here are some things to think about before selecting a strategy for scaling your application database:

1. What is the anticipated database load?

2. What are the requirements for availability?

3. What are the requirements for performance?

4. What are the requirements for failover and recoverability?

5. What is the ratio of write operations vs. read operations?

6. Where are your users physically located?

7. Does the database create higher than normal processing load (like for heavy usage of stored procedures)?

8. If this is an upgrade for an existing system, what is the utilization of the existing hardware (CPU, RAM, network I/O, disk I/O)? Where is the bottleneck?

Scaling Options

Options for scaling a database can be grouped into two major categories: Vertical and Horizontal. Scaling vertically generally describes adding capacity to the existing database instance(s) without increasing the number of servers or database instances. Horizontal Scaling, on the other hand, refers the alternative approach of adding servers and database instances.

Scaling Vertically

The easiest way to increase the capacity of a MySQL database is to upgrade the server hardware. Most often, this means adding CPU and/or RAM, but can also include disk I/O capacity. Scaling vertically can be done by upgrading existing server hardware or by moving to a new server.

1. Easiest, simplest option for scaling your MySQL database

2. Doesn’t improve availability (db is still running on a single box)

3. Good for boosting CPU and RAM, but limited potential for improving network I/O or disk I/O

4. Beefy hardware is expensive

5. There is a finite limit to the capacity achievable with vertical scaling

Scaling Horizontally

For projects with requirements for high availability or failover, adding additional servers can be the right solution. Horizontal scaling can be done with a variety of configurations: Active-Passive, Master-Slave, Cluster, or Sharding.

Active-Passive

With two database instances, one is the active instance and the other is the passive. All queries (reads and writes) are directed to the active instance. Write operations are replicated to the passive instance to keep it in sync. In the event of failure of the active instance, query and update traffic is redirected to the passive instance. For more information on MySQL replication, look here.

1. Improves availability and failover

2. Doesn’t improve capacity or performance (over a single instance of the same size)

3. Redirecting traffic from the active instance to the passive can be done automatically (recommended) with a load-balancer or switch, or can be done manually using a VIP or internal DNS configuration change

Master-Slave

Using two or more database instances, one instance is the master and the others are slaves. All writes are routed to the master instance, then replicated to the slaves. Read operations are routed to the slave instances. In the event of failure of the master, one of the slave instances must be designated as the new master and update operations rerouted.

1. An unlimited number of slave instances can be added, but only one instance can be the master

2. Great for applications with mostly READ operations

3. Good for addressing I/O (network or disk) bottlenecks

4. Good for geographic dispersion

5. Requires application logic to separate read and write operations

6. Replication is asynchronous, so instances are not guaranteed to be in sync

7. Master instance can be scaled vertically

Cluster

MySQL 5.0 first introduced native clustering capability which has since been improved in subsequent releases. The cluster distributes query processing and data storage across multiple nodes to eliminate single points of failure and allow almost unlimited scaling. The cluster is made up of three types of nodes: storage nodes, SQL nodes (query processing), and management nodes. For more detail on the function and configuration of the cluster nodes, please refer to the MySQL documentation. To realize the full high-availability potential, a cluster should contain at least two of each type of node. The recommended minimum configuration includes six servers, although 3-box configuration is possible.

1. Good solution for high-availability

2. In-memory storage provides high-performance

3. Good for scaling applications with heavy write loads

4. Can scale specific portions of the cluster (query processing or storage) to target bottlenecks (CPU, RAM, I/O, storage volume)

5. Synchronous replication between nodes makes cluster inappropriate for geographic dispersion

6. Not a good fit for hosting environments with SAN

7. Requires lots of RAM for storage nodes (roughly double the db size)

8. High complexity

Sharding

Sharding comes in many forms. In the most basic sense, it describes breaking up a large database into many smaller databases. Sharding can include strategies like carving off tables, or cutting up tables vertically (by columns). I'm mostly referring to the strategy of “horizontal table partitioning” - dividing large tables by row. This is a relatively exotic configuration and generally used only by the most demanding applications.

1. Good strategy for handling extreme database loads

2. Easiest/most appropriate when most load is accessing a few tables and JOIN operations across shards are not required

3. Success depends largely of sharding strategy and shard sizing

4. May require painful periodic shard resizing

5. Very high complexity

6. Improves availability - The impact of a shard failure is small (affects only a portion of data), but sharding must be coupled with an additional strategy (like active-passive) to provide complete high-availability

I recommend considering sharding only as a scaling strategy of last resort. Before going down the path, I would consider any and all of the scaling strategies above.  If sharding really looks like the best option, the good news is that several sharding solutions have emerged, so you shouldn't have to roll your own.

More in-depth discussions can be found here:

1. An Unorthodox Approach To Database Design : The Coming Of The Shard

2. http://www.codefutures.com /database-sharding/

3. http://blog.maxindelicato.com /2008/12/scalability-strategies- primer-database- sharding.html

4. http://www.jurriaanpersyn.com /archives/2009/02/12/ database-sharding- at-netlog-with- mysql-and- php/

5. http://www.addsimplicity.com /adding_simplicity_an_engi /2008/08/ shard-lessons .html

6. And many, many more…