Shulou(Shulou.com)05/31 Report--

It is believed that many inexperienced people do not know what to do about how to build the common architecture of the Internet in the MySQL database. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

I. principles of database architecture

1. High availability

2. High performance

3. Consistency

4. Scalability

Second, the common architecture scheme one: the main and standby architecture, only the main library provides read and write services, and the standby library is redundant for failover.

Jdbc:mysql://vip:3306/xxdb

High availability analysis: high availability, the main library is dead, keepalive (just a tool) will automatically switch to the standby library. This process is transparent to the business layer and no code or configuration changes are required.

High-performance analysis: both read and write operate on the main library, which is easy to cause bottlenecks. Most Internet applications read more and write less, so reading will first become a bottleneck, and then affect the performance of writing. In addition, the preparation of the database is only a simple backup, and the resource utilization rate is 50%, which can be solved by solution 2.

Consistency analysis: both read and write operate the main database, and there is no data consistency problem.

Scalability analysis: it is impossible to expand the read performance by adding slave libraries, so as to improve the overall performance.

Landing analysis: two points affect the use of landing. First, performance is mediocre, which can be improved by building efficient indexes and introducing caches to increase read performance. This is also a general solution. Second, the scalability is poor, which can be extended by sub-database and sub-table.

Solution 2: dual main architecture, two main libraries provide services at the same time, load balancing

Jdbc:mysql://vip:3306/xxdb

High availability analysis: high availability, one main library failed, does not affect the other main library to provide services. This process is transparent to the business layer and no code or configuration changes are required.

High performance analysis: the performance of reading and writing is twice as high as that of scheme 1.

Consistency analysis: there is a data consistency problem. Look at the consistency solution.

Scalability analysis: of course, it can be extended to three main loops, but the author does not recommend it (there will be one more layer of data synchronization, so the synchronization time will be longer). If it has to be extended at the database schema level, extend to scenario 4.

Landing analysis: two points affect the use of landing. First, the problem of data consistency, the consistency solution can solve the problem. Second, the problem of primary key conflict, ID uniformly generates services from distributed ID to generate solvable problems.

Plan 3: master-slave architecture, one master and multiple slaves, separation of reading and writing

Jdbc:mysql://master-ip:3306/xxdb

Jdbc:mysql://slave1-ip:3306/xxdb

Jdbc:mysql://slave2-ip:3306/xxdb

High availability analysis: single point of master library, high availability of slave library. Once the main library is down, the write service cannot be provided.

High-performance analysis: most Internet applications read more and write less, reading will first become a bottleneck, and then affect the overall performance. The performance of the read is improved, and the overall performance is also improved. In addition, the master database does not need an index, and different indexes can be built between the online slave library and the offline slave library (if there are more than one online slave library, the loss outweighs the gain; offline slave library is usually a database for developers to check for online problems, and more indexes can be built).

Consistency analysis: there is a data consistency problem. Look at the consistency solution.

Scalability analysis: read performance can be extended by adding slave libraries to improve overall performance. (the problem is that the more slave libraries, the more binlog logs that need to be pulled from the main database, which in turn affects the performance of the main database, and the data synchronization will take longer to complete.)

Landing analysis: two points affect the use of landing. First, the problem of data consistency, the consistency solution can solve the problem. Second, the main library single point problem, the author did not think of a good solution.

Note: think about a question: what happens when a slave hangs up? How can the load balancing strategy of read-write separation be fault-tolerant?

Plan 4: double master + master-slave architecture, seemingly perfect scheme

Jdbc:mysql://vip:3306/xxdb

Jdbc:mysql://slave1-ip:3306/xxdb

Jdbc:mysql://slave2-ip:3306/xxdb

High availability analysis: high availability.

High performance analysis: high performance.

Consistency analysis: there is a data consistency problem. Look at the consistency solution.

Scalability analysis: read performance can be extended by adding slave libraries to improve overall performance. (the problems brought about are the same as those in option 2)

Landing analysis: the same as the second plan, but there is another layer of data synchronization, and the data delay is more serious.

Consistency solution category 1: master library and slave library consistency solution

Note: what is circled in the figure is the place of data synchronization. Data synchronization (pulling binlog logs from the master database from the library and executing it again) takes time. During this synchronization time, there will be inconsistencies between the data of the master database and the slave database. If there is a read request during synchronization, what is read is the old data from the library. As shown in the following picture.

Now that you know the cause of data inconsistency, there are several solutions for reference:

1, ignore directly, if the business allows delay to exist, then ignore it.

2. The master is forced to read, and the master / standby architecture is adopted, and both reading and writing are in the main library. Use caching to extend database read performance. One thing to know: if the cache hangs, an avalanche may occur, but generally distributed caches are highly available.

3. Select the read master, generate a key according to the database + table + business characteristics during the write operation and put it in the Cache and set the timeout (greater than or equal to the master-slave data synchronization time). When reading a request, key is generated in the same way to check the Cache and then determine whether it is hit or not. If hit, read the master library, otherwise read the slave library. The price is that there is one more cache read and write, which can be ignored.

4. Semi-synchronous replication, and the write request will not be returned until master-slave synchronization is completed. It is often called "semi-synchronous replication" semi-sync. This can take advantage of the native functions of the database, and it is relatively simple to implement. The cost is the increase of write request delay and the decrease of throughput.

5. Database middleware, introducing open source (mycat, etc.) or self-developed database middle layer. Personal understanding, the train of thought is the same as choosing the reader. The cost of database middleware is relatively high, and one more layer is introduced.

The second category: DB and cache consistency solutions

Let's first take a look at the common ways to use caching:

Step 1: eliminate caching

Step 2: write to the database

Step 3: read the cache? Return: read database

Step 4: read the database and write to the cache.

Note: if you follow this approach, figure 1, there will be no DB and cache inconsistencies; figure 2, there will be DB and cache inconsistencies, that is, 4.read executes before 3.sync. If left unprocessed, the data in the cache may always be dirty. The solution is as follows:

Note: when setting up the cache, be sure to add the expiration time to prevent the failure of delay elimination of the cache!

Fourth, some personal opinions 1. Structural evolution

1. Architecture evolution 1: scheme 1-> option 1 + sub-library sub-table-> option 2 + sub-library sub-table-> scheme 4 + sub-library sub-table

2. Architecture evolution 2: scheme 1-> scheme 1 + sub-library sub-table-> scheme 3 + sub-library sub-table-> scheme 4 + sub-library sub-table

3. Architecture evolution 3: option 1-> option 2-> option 4-> option 4 + sub-library table

4. Architecture evolution 4: option 1-> option 3-> option 4 + sub-library table

2. Personal opinions

1. Caching and indexing are common ways to improve database performance

2. The benefits of sub-database and sub-table are huge, but it also brings some problems.

3. Whether it is master / slave + sub-library sub-table or master-slave + read-write separation + sub-library sub-table, the specific business scenarios should be considered. After four years of development, most of the database architectures still adopt scheme 1 and scheme 1 + sub-database sub-table, and only a few of them use scheme 3 + read-write separation + sub-database sub-table. In addition, the database cloud services provided by Aliyun are also master and backup solutions, and secondary architecture is needed for master-slave + read-write separation.

4. Remember one sentence: architectures that do not take into account business scenarios are rogue.

After reading the above, have you mastered how to build the common architecture of the Internet in the MySQL database? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.