Basic architecture evolution of MySQL

"MySQL's basic architecture Evolution "

Without concurrent growth, there is no need for a highly scalable architecture.

　　scale-up: scale-up, scaling by replacing it with better machines and resources to improve service capabilities
scale-out: scale-out, scaling by adding nodes (machines) for improved service capability
For the high concurrent application of the Internet, it is no doubt that scale out is the way out, through the vertical purchase of more high-end machine has been our taboo problem, is not a long-term solution.

A service that, when confronted with higher concurrency, can increase the concurrency of service support by simply adding machines, and adding no impact to the online service (no down time) in the machine process, which is the ideal state of extensibility!

"V1.0 Simple site Architecture "

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

　　

　　what is the bottleneck of data storage in this architecture ?
1. Total size of data when a machine doesn't fit
2. Index of data (b + Tree) when the memory of a machine does not fit
3. Traffic (Read-write mix) An instance cannot withstand

We need to consider the next level of evolution only if one or more of these 3 things are satisfied. 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 of accurate assessment is an important part of the elimination of excessive design, after all, no one is willing to waste their experience for impossible things.

Here is a simple example, for the user information such a table (3 index), 16G memory can drop the index of about 2000W rows of data, simple read and write mixed traffic 3000/s around no problem.

"V2.0 Vertical Split "

Generally when the V1.0 encounter bottlenecks, the first easiest way to split is vertical split, what is vertical? It is from the business point of view, the association of data is not strong split into different instance, so as to achieve the goal of eliminating bottlenecks. Take the example in the diagram to split the user information data, and the business data, into a different three instances. We can also add a layer of cache to reduce the pressure on the db for scenarios where the repeat read type is much more.

　　

　　

In this architecture, let's look at the bottleneck of data storage.

1. Single Instance single service still exists V1.0 the bottleneck

When you encounter bottlenecks, consider upgrading to the higher V version of this article, if the read request leads to a performance bottleneck, consider upgrading to V3.0, and other bottlenecks to V4.0 upgrade

"V3.0 master-slave architecture "

This kind of architecture mainly solves the reading problem under the V2.0 architecture, through to the instance hangs the data real-time backup idea to migrate the reading pressure, in the MySQL scene is through the master-slave structure, the main library writes down the pressure, through the library to share the reading pressure, for writes less reads many applications, the V3.0 master-slave architecture fully can be competent

　　

　　In this architecture, let's look at the bottleneck of data storage.
1. The Write Volume Master library cannot withstand

"V4.0 Horizontal split "

For the V2.0 V3.0 solution encountered bottlenecks, can be resolved by horizontal split, horizontal split and vertical split has a large difference, the result of vertical split, in one instance is the full amount of data, and the horizontal split, any instance has only a full amount of 1/n data, to userinfo split into examples , splitting the userinfo into 3 cluster, each cluster holding 1/3 of the total data, 3 cluster The sum of the data equal to a full data (note: Here is no longer called a single instance, but a cluster represents a small MySQL cluster containing master and slave)

　　　　　　

Reference: http://www.cnblogs.com/Creator/p/3776110.html

Basic architecture evolution of MySQL