Suset Acosta
A Summary of Scaling Options for MySQL
Nov 09, 2011
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:
Benefits of Scaling
Scaling a database can become necessary for a variety of reasons:
Support a higher volume of users
Provide better performance for existing users
Store a larger volume of data
Improve system availability
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:
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
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.
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:
What is the anticipated database load?
What are the requirements for availability?
What are the requirements for performance?
What are the requirements for failover and recoverability?
What is the ratio of write operations vs. read operations?
Where are your users physically located?
Does the database create higher than normal processing load (like for heavy usage of stored procedures)?
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.
Easiest, simplest option for scaling your MySQL database
Doesn’t improve availability (db is still running on a single box)
Good for boosting CPU and RAM, but limited potential for improving network I/O or disk I/O
Beefy hardware is expensive
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.
Improves availability and failover
Doesn’t improve capacity or performance (over a single instance of the same size)
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.
An unlimited number of slave instances can be added, but only one instance can be the master
Great for applications with mostly READ operations
Good for addressing I/O (network or disk) bottlenecks
Good for geographic dispersion
Requires application logic to separate read and write operations
Replication is asynchronous, so instances are not guaranteed to be in sync
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.
Good solution for high-availability
In-memory storage provides high-performance
Good for scaling applications with heavy write loads
Can scale specific portions of the cluster (query processing or storage) to target bottlenecks (CPU, RAM, I/O, storage volume)
Synchronous replication between nodes makes cluster inappropriate for geographic dispersion
Not a good fit for hosting environments with SAN
Requires lots of RAM for storage nodes (roughly double the db size)
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.
Good strategy for handling extreme database loads
Easiest/most appropriate when most load is accessing a few tables and JOIN operations across shards are not required
Success depends largely of sharding strategy and shard sizing
May require painful periodic shard resizing
Very high complexity
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:
An Unorthodox Approach To Database Design : The Coming Of The Shard
http://www.codefutures.com /database-sharding/
http://blog.maxindelicato.com /2008/12/scalability-strategies- primer-database- sharding.html
http://www.jurriaanpersyn.com /archives/2009/02/12/ database-sharding- at-netlog-with- mysql-and- php/
http://www.addsimplicity.com /adding_simplicity_an_engi /2008/08/ shard-lessons .html
This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.