MySQL Application architecture evolution in large Web sites

As the most popular open source database, MySQL is widely used in Web applications and other small and medium-sized projects. However, in many large IT companies, MySQL has gradually deviated from the original open source version after being highly optimized and customized, more like a branch, such as the Webscalesql of Facebook, which was not long ago open source. Recently, Mr. Xiong has published a blog post, from the perspective of large-scale web site architecture development of the changes in MySQL application, here for you to share.

This paper mainly describes the evolution of MySQL architecture under different concurrent access levels of the website.

Scalability

The scalability of the architecture is often closely related to concurrency, there is no concurrent growth, there is no need to do a highly scalable architecture, here a simple introduction to scalability, commonly used to expand the following two kinds of means:

Scale-up: Scaling vertically, scaling by replacing better machines and resources, improving service capabilities

Scale-out: Scaling horizontally, scaling by adding nodes (machines), improving service capability

For the Internet's high concurrent applications, it is no doubt that the horizontal expansion is the way out, colleagues through the vertical purchase of higher-end machines has been our taboo problem, nor long-term. So what is the ideal state of extensibility under the theory of horizontal expansion?

The ideal state of extensibility

A service, when faced with higher concurrency, can simply increase the machine to enhance the concurrency of service support, and increase the machine process on the online service has no impact (no down time), this is the ideal state of scalability!

Evolution of the architecture

V1.0 Simple Web site architecture

A simple small web site or the structure behind the application can be very simple, data storage only need a MySQL instance to meet the data read and write requirements (here ignores the example of data backup), in this time period of the site, usually put all the information to a database Instance inside.

In this architecture, let's look at what is the bottleneck of data storage?

Total size of the amount of data a machine won't fit.

Index of data (b + tree) The memory of one machine is not fit

Traffic (mixed read-write) An instance cannot withstand

It is only when one or more of the above 3 things are met that we need to consider evolution down the first level. From then on we can see that, in fact, for many small companies small applications, this architecture is enough to meet their needs, the initial data volume accurate assessment is to eliminate the excessive design is very important, after all, no one is willing to waste their energy for the impossible things.

Here a simple example of my case, for user information such tables (3 index), 16G memory can be put down, about 20 million rows of data index, simple read and write mixed traffic 3000/s around no problem, your application scenario?

V2.0 Vertical Split

Generally when V1.0 encounter bottlenecks, the first and easiest way to split is vertical split, what is vertical? is from the business point of view, the correlation is not strong data splitting to different instance, so as to eliminate bottlenecks. In the illustration, the user information data and the business data are split into three different instances. For more types of repetitive reading scenarios, we can also add a layer of cache to reduce the pressure on db.

In this architecture, let's look at what is the bottleneck of data storage?

Single instance business still exists V1.0 bottleneck: When encountering bottlenecks, you can consider to this article higher v version upgrade, if read request lead to achieve performance bottleneck can consider to V3.0 upgrade, other bottlenecks consider to V4.0 upgrade.

V3.0 Master and slave architecture

This kind of architecture mainly solves the reading problem under the V2.0 architecture, by giving the instance data real-time backup idea to migrate the reading pressure, in the MySQL scene is through the master-slave structure, the main library to write pressure, through the library to share the reading pressure, for writing less read more applications, V3.0 master-slave architecture can be competent.

In this architecture, let's look at what is the bottleneck of data storage? It is clear that the write Volume main library cannot withstand.

V4.0 Horizontal Split

For V2.0, V3.0 scenarios encounter bottlenecks, can be solved by horizontal split, horizontal split and vertical split has a big difference, the result of vertical splitting split, in one instance is the full amount of data, and after the horizontal split, any instance has only a full amount of 1/n data, The following figure UserInfo splits into examples, divides the userinfo into 3 cluster, each cluster holds the total 1/3 data, the sum of 3 cluster data equals a complete data.

Note: This is no longer called a single instance. It's called a cluster. Represents a small MySQL cluster containing the principal and subordinate.

So how should the data in the schema be routed?

1. Range Split

Sharding key by continuous interval routing, generally used in a strict ID requirements of the scene, such as UserId, UserId range of a small example, to UserId 30 million for the range to split: number 1th cluster UserId is 130 million, The number 2nd cluster UserID is 30.01 million-60 million.

2. List Split

List splitting is the same as range splitting mentality by routing different sharding keys to different cluster, but the specific methods are somewhat different. List is mainly used to do sharding key is not a continuous interval sequence falling to a cluster situation, such as the following scenario:

Suppose there are 20 video stores, distributed in 4 distribution areas, as shown in the following table:

Area

Store ID Number

North

3, 5, 6, 9, 17

Eastern

1, 2, 10, 11, 19, 20

Western

4, 12, 13, 14, 18

Central Area

7, 8, 15, 16.

The business wants to be able to organize all the data in a region to search, and this scenario list split can be easily done

3. Hash split

Sharding key by hashing the way to split, commonly used hashing methods, such as the remainder, string hashing, and so on, in addition to the value of the userid%n to determine the data to read and write which cluster, other hashing algorithm here is not detailed.

4. Problems introduced after data splitting

The problem of data horizontal split is mainly through sharding key to read and write operations, such as UserID for sharding key Segmentation example, read userid details, you must first know UserID, In order to calculate in which cluster to query, assuming that I need to username to retrieve user information, need to introduce additional reverse indexing mechanism (similar to the HBase Level two index), such as on the Redis store Username->userid mapping, The example of username query becomes the first through the query Username->userid, and then through the UserID query the appropriate information.

In practice this is simple, but let's not overlook an additional risk that is inconsistent with data. The username->userid stored in Redis and the userid->username stored in MySQL must be consistent, which is often a difficult thing to do, for example, to modify the user name of the scene, You need to modify both Redis and MySQL at the same time. These two things are very difficult to do transaction guarantee, such as MySQL operation succeeds, but Redis operation failed (the cost of distributed transaction introduction is higher). Usability is the most important for Internet applications, and consistency is second, so a small amount of inconsistency can be tolerated. After all, the proportion of such inconsistencies can be negligible to a negligible extent. (The general write update also uses MQ to ensure that the retry operation is not stopped until it succeeds)

In this architecture, let's look at what is the bottleneck of data storage?

In this split concept of the structure of the framework, there is no theoretical bottleneck (sharding key to ensure that the flow of each cluster relatively balanced under the premise). But there is a disgusting thing, that is, cluster expansion of the time to redo the cost of data, such as I originally have 3 cluster, but now my data growth faster, I need 6 cluster, then we need to split each cluster into two, the general practice is:

Pick a Slave, stop sync

Incremental log to write (implementation can be done on the business side write more than once persistent MQ or MySQL master create trigger record write, etc.)

Start slave data to static two

Playback increment writes until all increments are followed up, basically keeping up with the original cluster

Write switch, switching from original 3 Cluster to 6 Cluster

There is no similar plane air refueling feeling, this is a dirty live, dirty, easy to trouble the work, in order to avoid this, we generally at the beginning, design enough sharding cluster to prevent the possibility of cluster expansion of this matter.

V5.0 Cloud computing Take-off (Cloud database)

Cloud computing is now a key to cost savings within major IT companies, and how to make it a SaaS is critical for MySQL, the data store. In MS's official document, the 3 major challenges of building a sufficiently mature SaaS (MS Simple List of level 4 maturity for SaaS applications) are: scalability, scalability, and the design of a multiuser storage architecture called three headed monster. Configurable and multiuser storage architecture design is not particularly difficult in the MySQL SaaS issue, so it's important to say scalability.

MySQL as a SaaS service, after the architecture evolved to V4.0, rely on a good sharding key design, there is no longer an extensibility problem, but he faced with the expansion of the shrink, there are some dirty work to do, and as a SaaS, can not avoid the problem of capacity reduction, So as long as you can turn the dirty work of V4.0 into: 1th, the expansion capacity of the front-end app transparent (business code does not require any changes); 2nd, the expansion capacity of the volume of the total automation and no impact on the online services. If you realize these two points, then he's got a ticket for SaaS.

For the key to the implementation of the architecture, the need to meet the business transparent, capacity expansion shrink to the business does not need any changes, then we must eat our own dog food, in your MySQL SaaS internal solution to this problem, the general practice is that we need to introduce a proxy, Proxy to parse the SQL protocol, press sharding key to find cluster, judge whether it is read operation or write operation to request Master or slave, all the internal details are blocked by proxy.

Here's a look at Taobao to enumerate what the proxy needs to do.

For the key point of the architecture implementation, the expansion capacity is fully automated and has no effect on the online service; Capacity expansion the data operations that correspond to data splitting and data merging, there are very many different ways to achieve complete automation. General ideas and V4.0 introduced the bottleneck part of the current view of the problem is a better solution is to achieve a disguised Slave sync Slave, analysis of the MySQL synchronization protocol, and then realize the data splitting logic, the total amount of data to be split. The detailed structure is shown in the following figure:

The sync slave is no different from a common MySQL slave for original master and does not require any extra discrimination. Need to enlarge/shrink capacity, hang up a sync slave, start full sync + incremental sync, wait for a period of time to recover data. To enlarge the case, if the expansion of the service and capacity before the data has been basically synchronized, then how to do the switch does not affect the business? In fact, the key point is still in the introduction of the proxy, the problem is converted to how to let proxy do hot swap back-end problems. This has become a very good problem to deal with.

Also noteworthy is: May 28, 2014--in order to meet the current demand for web and cloud applications, Oracle announced the introduction of MySQL fabric, in the corresponding data section I also put a lot of Fabric information, interested to see, Might be a future solution to the expansion of cloud database capacity of the means.

V more?

Wait for the revolution ...