Evolution of MySQL Application Architecture in Large-Scale Websites

         As the most popular open-source database, MySQL is widely used in Web applications and other small-to-medium projects. However, it’s hard to ignore that in many large IT companies, after heavy optimization and customization, MySQL has gradually deviated from the original open-source version and become more like a fork, such as Facebook’s recently open-sourced WebScaleSQL. Recently, @大熊先生 published a blog post analyzing the changes in MySQL applications from the perspective of large-scale website architecture evolution. We’re sharing it with you here.

This article primarily describes the evolution of MySQL architecture under different levels of concurrent website traffic.

Scalability

Architecture scalability is often closely related to concurrency. Without concurrency growth, there is no need for a highly scalable architecture. Here’s a brief introduction to scalability. The commonly used scaling methods are as follows:

• Scale-up: Vertical scaling, achieving scaling and improving service capacity by replacing with better machines and resources.
• Scale-out: Horizontal scaling, achieving scaling and improving service capacity by adding nodes (machines).

For high-concurrency Internet applications, horizontal scaling is undoubtedly the way to go. Meanwhile, vertically purchasing higher-end machines has always been a taboo and not a long-term solution. So, under the theory of horizontal scaling, what is the ideal state of scalability?

The Ideal State of Scalability

A service, when facing higher concurrency, can increase the supported concurrency level by simply adding machines, and the process of adding machines has no impact on the online service (no down time). This is the ideal state of scalability!

Architecture Evolution

V1.0 Simple Website Architecture

The architecture behind a simple small website or application can be very straightforward. Data storage only needs one MySQL Instance to meet data read and write requirements (ignoring data backup instances here). Websites at this stage generally store all information in a single Database Instance.

Under this architecture, let’s look at what the data storage bottlenecks are:

• Total data size — one machine cannot hold it all.
• Data indexes (B+ Tree) — one machine’s memory cannot hold them all.
• Traffic (mixed reads and writes) — a single instance cannot handle the load.

Only when one or more of the above 3 conditions are met should we consider evolving to the next level. From this, we can see that for many small companies and applications, this architecture is already sufficient to meet their needs. Accurately estimating data volume in the early stages is a very important part of preventing over-engineering. After all, no one wants to waste their energy on things that may never happen.

Here’s a simple personal example: for tables like user information (3 indexes), with 16GB of memory, you can hold the indexes for roughly 20 million rows of data. A simple mixed read/write load of around 3000/s is manageable. Does your application scenario match this?

V2.0 Vertical Splitting

Generally, when V1.0 hits a bottleneck, the easiest splitting method is vertical splitting. What does “vertical” mean? From a business perspective, it means splitting data with weak associations into different instances to eliminate the bottleneck. Taking the diagram as an example, user information data and business data are split into three different instances. For scenarios with many repetitive reads, we can also add a Cache layer to reduce the pressure on the DB.

Under this architecture, let’s examine where the data storage bottleneck lies.

A single instance handling a single business still faces the bottleneck described in V1.0: When hitting performance limits, consider upgrading to a higher V version described later in this article. If read requests are causing the performance bottleneck, consider upgrading to V3.0. For other bottlenecks, consider upgrading to V4.0.

V3.0 Master-Slave Architecture

This architecture primarily addresses the read issues found in V2.0. By attaching a real-time data backup to the Instance, it migrates the read load. In the MySQL scenario, this is achieved through a master-slave structure: the master handles write pressure, while the slaves share the read load. For applications with a high read-to-write ratio, the V3.0 master-slave architecture is fully capable.

Under this architecture, let’s examine where the data storage bottleneck lies. It’s clear: the master database cannot withstand the write volume.

V4.0 Horizontal Sharding

When the V2.0 or V3.0 solutions encounter bottlenecks, they can be resolved through horizontal sharding. Horizontal sharding is quite different from vertical sharding. After vertical sharding, a single instance still holds the complete dataset for its specific business. However, after horizontal sharding, any given instance holds only 1/n of the total data. Taking the UserInfo sharding example below, UserInfo is split into 3 Clusters, each holding 1/3 of the total data. The sum of the data across the 3 Clusters equals one complete dataset.

Note: We no longer refer to a single instance here, but rather a Cluster, representing a small MySQL cluster containing both master and slave nodes.

So, how should data be routed in such an architecture?

1. Range Sharding 

The sharding key routes data based on continuous intervals. This is generally used in scenarios with strict auto-increment ID requirements, such as UserId. Take a simple UserId Range example, splitting by a range of 30 million UserIds: Cluster 1 handles UserIds 1-30,000,000, and Cluster 2 handles UserIds 30,000,001-60,000,000.

2. List Sharding

The List sharding concept is similar to Range sharding; both route to different Clusters based on different sharding keys, but the specific methods differ slightly. List sharding is mainly used when the sharding key values are not a continuous sequence but need to land on the same Cluster, as in the following scenario:

Assume there are 20 video stores distributed across 4 authorized distribution regions, as shown in the table below: 

The business requires organizing all data from a region for combined search—a scenario easily handled by List partitioning.

3. Hash Partitioning

Data is partitioned by hashing the sharding key. Common hashing methods include modulo and string hashing. For example, using UserId % n to determine which cluster handles read/write operations. We won’t delve into other hashing algorithms here.

4. Issues Introduced by Data Partitioning

The main issue introduced by horizontal data partitioning is that read/write operations can only be performed via the sharding key. For example, with partitioning based on UserId as the sharding key, to look up a user’s detailed information, you must know the UserId first to determine the target cluster. If you need to retrieve user info by UserName, an additional reverse index mechanism (similar to HBase secondary indexes) is required—for instance, storing a username->userid mapping in Redis. A query by UserName then becomes: first query username->userid, then query the corresponding information via userid.

While this approach is straightforward in practice, we must not overlook a hidden risk: data inconsistency. The username->userid mapping stored in Redis and the userid->username stored in MySQL must remain consistent, which can often be difficult to guarantee. For instance, when changing a username, you need to update both Redis and MySQL simultaneously. Achieving transactional guarantees across these two systems is very difficult (e.g., MySQL operation succeeds but Redis fails—distributed transactions come with high implementation costs). For internet applications, availability is the top priority, with consistency secondary, so tolerating a small amount of inconsistency is acceptable. After all, the proportion of such inconsistencies is negligible and can be safely ignored. (Write updates typically leverage message queues with retry logic to ensure eventual success.)

Under this architecture, let’s examine the bottleneck in data storage.

Theoretically, the architecture built on this partitioning concept has no bottleneck (provided the sharding key ensures relatively balanced traffic across clusters). However, there is one truly painful task: the cost of rebalancing data during cluster expansion. For example, if I originally have 3 clusters but my data grows rapidly and I need 6 clusters, each original cluster must be split into two. The typical process is:

1. Take one slave offline and stop replication.
2. Record incremental write logs (implementation-wise, the business side can perform an additional persistent write to a message queue, or a MySQL master trigger can record writes, etc.).
3. Begin splitting the static slave’s data into two.
4. Replay the incremental writes until caught up, maintaining approximate sync with the original cluster.
5. Switch write traffic from the original 3 clusters to the new 6 clusters.

Doesn’t this feel like mid-air refueling? It’s a dirty, labor-intensive, and error-prone task. To avoid this, we generally design a sufficient number of sharding clusters from the very beginning to preempt the potential need for cluster expansion.

V5.0 Cloud Computing Takes Off (Cloud Database)

Cloud computing is now a breakthrough point for cost-saving within major IT companies. For MySQL as a data storage solution, the key is turning it into a SaaS offering. In Microsoft’s official documentation, the three main challenges of building a sufficiently mature SaaS (MS briefly outlines the 4 maturity levels of SaaS applications) are described as the "three headed monster": configurability, scalability, and multi-tenant storage structure design. Configurability and multi-tenant storage structure design are not particularly difficult for MySQL SaaS, so we’ll focus on scalability here.

After the architecture evolved to V4.0, MySQL as a SaaS service no longer faces scalability issues thanks to well-designed sharding keys. However, scaling up or down still involves some dirty work. As a SaaS, scaling cannot be avoided. Therefore, if the dirty work from V4.0 can be transformed to meet two criteria: 1. Scaling is transparent to the front-end app (no business code changes required); 2. Scaling is fully automated and has zero impact on online services. Achieving these two points earns it the ticket to become a true SaaS.

The key implementation point requires that scaling be transparent to the business with no code changes, which means we must eat our own dog food and solve this inside the MySQL SaaS itself. The common approach is to introduce a Proxy that parses the SQL protocol, locates the cluster based on the sharding key, and determines whether an operation is a read or write to route it to the Master or Slave. All these internal details are shielded by the Proxy.

Here we use a diagram from Taobao to illustrate what tasks a Proxy needs to handle

The key architectural requirement is fully automated scaling (up/down) with zero impact on online services. Scaling operations correspond to data splitting and data merging at the data level. Achieving full automation can be approached in many different ways, but the general idea is related to the bottleneck discussion from V4.0. Currently, the most promising solution seems to be implementing a Sync Slave that masquerades as a real slave, parses the MySQL replication protocol, and then executes data-splitting logic to shard the full dataset. The specific architecture is shown in the diagram below:

To the Original Master, the Sync Slave is indistinguishable from a regular MySQL Slave and requires no special treatment. When scaling up or down is needed, you attach a Sync Slave, initiate a full sync plus incremental sync, and wait for it to catch up on data. Taking scale-up as an example, once the data between the new and old services is nearly synchronized, how do you make the cutover transparent to the business? The key again lies in the Proxy. This problem then transforms into how to allow the Proxy to perform a hot backend switch, which becomes a much more manageable issue.

Another noteworthy development: On May 28, 2014 — in response to modern Web and cloud application demands — Oracle announced MySQL Fabric. I’ve also included a lot of Fabric resources in the corresponding materials section. Those interested might want to take a look; it could become a future solution for cloud database scaling. 

V more ?

Waiting for a revolution……