MySQL Scaling Options

- Executive Summary

MySQL is the most popular relational database in the world of web applications – be it Web 2.0
sites, SaaS applications or any other web application. These types of applications usually have
lots of users – which translates to a high number of hits/second on their MySQL instance, and a
big database size. As a result, there are many online discussions concerning MySQL scalability.
Many blog threads start with what can be done on the MySQL level, move to the code level and
then speak of alternatives to MySQL, such as NOSQL solutions.
This white paper summarizes the various options for scaling MySQL itself – that is, without
replacing the database and with minimal code changes.
The paper presents the most popular alternatives, and discusses the pros and cons of each. As a
final solution to these challenges, the paper introduces the ScaleBase Database Load Balancer
solution, and demonstrates how it will assist teams struggling with database scalability.

- MySQL Scaling issues

While MySQL is a great database, at some point it runs into major performance issues. A
database that performs well with 1 million records will probably run into performance issues
with 50 million records. The exact number varies – as it depends on the server hardware used,
complexity of queries, the read/write ratio of the SQL commands, number of open connections,
and, of course, the number of hits/second the database handles.
The issues of scaling MySQL are well documented. Most decisions will affect both the storage
engine itself (MyISAM or InnoDB for that matter) and the MySQL engine (which is responsible
for parsing SQL commands, handling locks and atomicity amongst others).
So what to do when this barrier is reached? There are a lot of possible solutions. This paper
discusses solutions that do not require any code change – meaning they’re fast to implement
and are usually very cost efficient.

- Possible Alternatives

• Tuning ( Database Tuning, SQL Statement Tuning)
• Scaling Up (Stronger Servers, Solid State Drives)
  
• Partitioning (Note: not sharding)
  

- Scaling Out

Once the database is busy enough, or big enough, there is a need to scale-out – run multiple
databases that share the load. Usually, this solution is attempted only after all other alternatives
were implemented or tried – as it requires code modifications.

• Read/Write Splitting
  

Read/Write splitting is a technique for scaling out databases. When introducing Read/Write
Splitting the application sends all writes to a single, master, database. That master database
uses database replication to copy data to one or more database slaves. The application can
direct the read operations to any one of these slaves, thus distributing the read load between
multiple servers.

• Sharding
  

With Sharding, a database’s data is split into multiple databases, each storing a subset of the
data. Consider the following diagram:

A single database table (in the above example – Employees) is split into multiple databases, each
storing some of the original table rows.

The application needs to be aware of the shards, and keep track of which data subset is stored
in which database. Accessing the correct database to extract data therefore becomes the
application’s responsibility.
For those who are used to the concept of partitioning, Sharding is the next step, where
partitioning over different files in the same server, evolves into harnessing the power of several
different servers for our partitions.
Sharding has proved itself as the ultimate scaling solution for MySQL, and is used in most
popular MySQL-backed websites. As a matter of fact, it’s the only way to scale reads as well as
writes. However, the development effort for building shards supporting code is huge, which is
why most companies use sharding only as a “Last Resort”

Ref: http://www.scalebase.com/downloads/MySQL%20Scaling%20Strategies.pdf