MySQL high scale architecture building millions of online system practices

The large amount of data is a very obvious challenge at the present stage. In many cases we have contacted recently, the amount of data easily reached more than 8 T, and the backup of data has become very troublesome. We are in the age of massive data.

The Internet industry may not have high requirements for consistency in the past, but the traditional financial industry such as banks has more than 280 transfer procedures alone, and now the rapid completion of transfer operations, strong consistency plays an important role.

Like wechat, Alipay scan code functions are linked to the database, if there is a problem with these functions, everyone will be very angry, which involves the availability of the database.

Finally, there is a wide range of issues, such as how to handle the situation of multiple logins with one registration at a certification authority.

The high concurrency and flexibility of MySQL are unmatched by other databases. Multi-idc architecture enables MySQL to be distributed to multiple computer rooms, and architecture processing is very simple. In addition, MySQL is Sharp nothing, each node has a copy of data, the damage rate is greatly reduced.

– There is no execution plan cache and the CPU usage is high

– Query A single-core operation, which is not suitable for running large and complex SQL

– Sensitive to connection data before MySQL5.7 (recommended limit to 300)

– Storage engine based solution (Innodb, TokuDB, MyRocks, Spider)

– Transaction nesting and Hash Join are not supported

Even with all these challenges, the list of domestic successes is long. For example, wechat Tenpay, logistics, P2P credit, game industry, Internet industry.

The capacity planning area needs to pay special attention to resource allocation alignment. Many companies’ databases vary in size, ranging from a dozen terabytes to a few tens of megabytes.

In this way, the management of the entire resource will be very chaotic. If you want to carry out large-scale management, you need to take DB as a storage resource and formulate allocation standards. For example, if a single instance runs on PCIE, the instance size is about 1 TB, and the single library size is about 200 GB. If the size exceeds 200 GB, it will be split.

In addition, we advocate single-machine multi-instance. The advantages of this is controllable, convenient migration, internal DB resource management platform easy to start. On the other hand, if the storage capacity of a single instance exceeds 4 TB, backup management is very uncomfortable.

As the project grows, everyone faces the problem of how to split the data. My advice is to split up the data that stands out, such as the user friend relationship data in the project if it is very large, then split it up, and some non-standard data such as log data can also be split up. By doing this step by step, you can plan the data that consumes a lot of resources earlier.

The splitting principle we advocate is to split it according to functions first, such as authentication type, user core type, user basic information and so on. After splitting by function, horizontal splitting can be considered when the single library is larger than 200G. Generally, two algorithms are used: Range and Hash. When a single instance reaches about 1 TB, it is divided into sets. For example, 1-20 million is Set1, and 20-40 million is Set2. The problem of data distribution in multiple IDCs can also be conveniently solved through Set governance.

Distributed transaction is a common complex type of transaction. A common scenario is that more than ten interfaces are called on the same DB, so how to split the transaction becomes a problem. In distributed transactions, you can imagine a scenario in which the number of concurrent transactions is limited to the number supported over a high-speed channel, and each user can only manipulate data in his environment. This approach uses message queue decoupling. In addition, in order to prevent users from starting a new transaction without completing the current transaction, the concept of state machine needs to be introduced.

The most dumb problem faced by DB calls of complex projects is that a DB is called by N more services, and finally it is impossible to tell which IP corresponds to which service, and when DB needs to be migrated, it does not know who needs to be notified.

In order to solve the problem, it is necessary to apply the virtual DB function, a single DB only to its own services open permissions, refuse other services to directly access other functions OF DB, and services only through service invocation without contact with DB.

In addition, in the case of not knowing its concurrency limit, we should use streaming call to control the concurrency within a certain range and introduce overload protection.

Long service chain calls sometimes encounter a developer connecting to a database Timeout, most likely because the developer fetched the connection from the connection pool and then put it back into the pool after processing. The correct thing to do is to take the connection and get the result, put the connection into the connection pool, and then process the result. To avoid this, you should communicate with the development about long service chain calls.

The first thing to talk about in the usability area is high availability. MHA was first used in this area, and now almost every company maintains a copy of its OWN MHA code rather than using the official one. The other way is to implement it autonomously, in a language like Python, based on the idea of MHA.

Then the service architecture support, to consider the DB in the failover program will not be abnormal, DB failover after failure, there is no alternative.

According to our experience, several measures should be considered for availability, including automatic security threshold control, DB unavailability processing in the process of high availability switchover, whether the data consistency in the multi-write mechanism is convenient for checking, and the data compensation scheme in the later period.

Multi-room deployment is an area that companies are exploring and has many features. For example, when a node writes data, other IDCs read data nearby. Multipoint write, bus summary. In addition, multiple room code distribution requires access to the database of each room. In this case, SmartDNS will be introduced.

In fact, there are a lot of Cache choices, generally at the beginning of the project can not consider the Cache only Cache calls the most. We list some Cache categories below.

Non-volatile Cache

– Memcache

– Redis

Non-volatile Cache

– Redis

– MongoDB

– MySQL NDB Cluster

Redis-cluster and MongoDB Cluster are recommended. In terms of vulnerability, you can choose Redis, but you must consider that the database can withstand the failure of Redis. The general solution is to automatically degrade the connection layer when the database response is slow.