"Go" MySQL high-availability architecture analysis and research at the business level

Original Address http://database.51cto.com/art/201507/483463_all.htm

Objective:

Relative to the traditional industry of service time 9x9x6 or 9x12x5, because the Internet e-commerce and the real-time Internet games, so the service requirements of the hour, business architecture, whether the application or the database, all need disaster preparedness, in the MySQL system, It is best to implement disaster-tolerant systems at the initial stage of the database architecture phase. So here's a look at all aspects of MySQL architecture from a business perspective.

One, MySQL architecture design-Business Analytics

(1) Read more and write less

The dotted line indicates the cross-room deployment, such as e-commerce system, a master both read and write, the consistency of the reading data need to be more important, read to put on master.

M (R) is only a standby library, and only M (WR) is hung and then switched to M (R), when M (r) becomes a read-write library. For example, the game system, a lot of salve will be mounted on the back of a M (R) above.

(2) Read and write less mms-e-commerce

If it is the type of e-commerce, this kind of read write less, generally 1 master dragged on 4 to 6 slave, all slave mounted in a master is enough.

Switch, the M1 read and write business to switch to M2 above, and then all M1 on the slave hanging on the M2 above, as follows:

(3) Read more write less mmss-game

If it is the game industry, read very much pretty obvious, will appear generally 1 master will hang more than 10 slave, so this time, you can put a part of slave mount New M (R) above. At least some of the pressure will be reduced so that at least when the server hangs up, it will not affect all slave, and some of the slave on M (R) continue without affecting all slave, as shown in Figure 3,

(4) Read less and write more

This means that reading does not affect the efficiency of writing, so reading and writing can be placed on one M1 (WR), while the other does not provide read or write, providing only standby redundancy for remote disaster recovery.

This remote disaster is very important, otherwise if it is a single-sided, unilateral business, in case the IDC engine room failure, will generally affect the online business, which caused the business 2 hours can not be applied, for online e-commerce transactions, the impact is quite large, so in order to maximize the guarantee of the hour, Must be done offsite disaster tolerance, mm to cross IDC room. Although there are some requirements for the resources, but for HA is indispensable, must have this mm mechanism.

When doing the switch, all the read and write from M1 directly to the M2 on it.

(5) Reading and writing

Reading and writing are similar, but reading can not affect the ability to write, read and write on M1 (WR), and then put part of the Reading on M2 (R), of course M1 and M2 are also deployed across the computer room.

When switching, it is possible to switch part of the read and write all from M1 to M2.

II: MySQL Architecture design-common architecture

(1) Strong consistency

The tradeoff of read consistency, if it is very high demand for read and write real-time, will be read and write on M1 above, M2 just as standby, is to take and the above one (4) read less write much of the same architecture pattern.

For example, the order processing process, then the need to read strong consistency, real-time realistic reading, similar to the transaction or dynamic real-time report statistics are used in this architecture mode

(2) Weak consistency

If it is weak consistency, you can share some reading pressure and flow on the M2, such as some report read and static configuration data reading module can be placed on the M2 above. such as the monthly statistical report, such as the home page recommended commodity business real-time requirements are not very high, can adopt this weak consistency design architecture model.

(3) Intermediate consistency

If neither strong consistency nor weak consistency, then we take the middle of the strategy is to deploy a S1 (R) in the same room, as a repository, to provide read services, reduce the pressure of M1 (WR), and another IDC room M2 only do standby disaster-tolerant way of use.

Of course, there will be 3 database servers, which may increase the procurement pressure, but we can provide better external data service capabilities and channels, in practice, as much as possible.

(4) Statistical services

such as PV, UV operation, statistics of the number of pages, traffic statistics, data aggregation, etc., can be classified as a statistical type of business.

The statistics of large queries on the database are very resource consuming. Statistics are divided into real-time statistics and non-real-time statistics, because MySQL master and slave is a logical SQL mode, so can not reach 100% real-time, if the online to strict real-time statistics such as train tickets and financial settlement, such as the statistics, MySQL this piece is not its strengths, Only the query M1 main library is implemented.

A, but for not strict real-time statistics, MySQL has a very good mechanism is binlog, we can parse the parser by Binlog, parse out to write statistics or send messages to the application terminal program to do statistics. This is quasi-real-time statistical operation, there is a certain short-term acceptable statistical delay phenomenon, if you want to 100% real-time statistics only query M1 the main library.

By Binlog the way to achieve statistics, in the Internet industry, especially e-commerce and game this piece, almost can solve more than 90% of the statistical business. Sometimes if the user or customer put forward to real-time read-time, we can communicate why need real-time, understand the specific business scenarios, some may really do not need real-time statistics, need to weigh, need to communicate with users and customers many times, to make more suitable for the business of statistical architecture model.

B, there is a kind of offline statistics business, such as monthly Report Annual report statistics, which can put the data in the Data warehouse or third-party nosql inside statistics.

(5) Historical data migration

Historical data migration, need to try not to affect the current online business, try not to affect the current line of query writing operations, why do historical data migration? Because some of the business data is time-sensitive, such as the e-commerce has completed the history of orders, there will be no update operation, only very simple query operation, And the query is not very frequent, and may not even be queried one day.

If this time the historical data in the online library or in the online table, it will affect the performance of online, so for this, you can move the data to a new historical database, the historical database can be either MySQL or NoSQL, It can also be data warehouses and even hbase big data.

The way to do this is through the slave library to query out all the data, and then according to business rules such as time, a latitude and other filters to filter out data, into the historical database (history Databases) inside. After the migration, go back to the main library M1 and delete the historical data. So at the business level, the query will take into account the current real-time data and historical data, can be on the filter according to the migration rules of the online query and the history query docking together. For example, within one months of the online library query one months ago in the History Library query, you can put this rule in the DB Migration filter layer and Application Query Business module layer. If possible, you can also configure more granular, through the application of the query business module layer to affect the DB Migration filter layer, such as the previous query is divided into one months as the benchmark, and now the query business changes to 15 days as the benchmark, then the application of the Query business module layer changes will automatically let the DB filter layer also change, Achieve a half automation, a little more intelligent.

(6) MySQL sharding

Like Oracle, a RAC-based shared storage-based approach does not require sharding to be extended from RAC storage alone. But this price is relatively higher, shared storage is generally more expensive, with the expansion of business data explosion, you will continue to accumulate your costs, even to an astronomical figure.

The current way of share disk is that other solutions are not perfect except that Oracle's business logic layer is perfect.

MySQL Sharding also has its limitations, sharding after the data query access and statistics will have a lot of problems, MySQL sharding is to solve the storage of share nothing a distributed method, broadly divided into vertical split and horizontal split.

(6.1) Vertical split

Can be split horizontally, can be split vertically, can be split horizontally and vertically, but also according to business split.

6.1.1 Horizontal Split

The horizontal split in the MySQL library means, every DB instance has many DB libraries, each of which has a table B table, such as the DB1 library has a table B, DB2 Library also has a and B table, then we put DB1, DB2 Library of a table B split out, a library into 2, Split into DB1, DB2, DB3, DB4, where DB1 Library and DB2 library put a table data, DB3 Library and DB4 library put B table data, DB1, DB2 library only a table data, DB3, DB4 library only b table data.

For example, as an e-commerce, each library has a log table and order table, if a is a Log table logs table, B is the Order Form Orders table, generally writing and writing orders are not strongly related, we can say a table log table and B table order form split out. So at this time, we did a horizontal split, split the A-table and B-table order tables into different libraries, and the database names of a and B tables can also be consistent (PS: in different instances), as shown in:

PS: This split is mainly for different business on the table of little impact, the business association between the table is weak or basically no business association. The benefit of splitting is that irrelevant data tables are split into different instances, and it's pretty good for the capacity expansion of the database and the equalization of performance improvements.

6.1.2 Longitudinal split

Split the different DB libraries on the same instance into separate, different instances. This split adaptation scenario and requirement is that DB1 and DB2 are not much of a business connection, similar to the one in 6.1.2 and the B table. If you use the cross-library business at the same time using DB1 and DB2, personal advice to reconsider the business, re-comb the table as far as possible to put a module in a library, do not collapse library operations.

This library is split vertically, with a separate library db1, and table A and table B are strongly correlated. As shown in the following:

PS: See a lot of people who use MySQL, always put a lot of non-business related tables in a library, or always put a lot of DB Library in the same instance, just like using Oracle as a instance concept. MySQL use a big principle is simple, as far as possible single, simple to use MySQL, the library to strictly separate, the table is not related, to strictly split into the library. In this way, the expansion of our business is very convenient and simple, just need to split the db of the business module, put into the new database server.

6.1.3 Transverse longitudinal split

Some just start, in order to quickly out of the product, so the library all the tables are placed on an instance, and so on after the business development, the face of data splitting, here will be the horizontal vertical split together to achieve, as shown:

6.1.4 Business Split

It's a bit similar to horizontal splitting, but there are different places. such as a supplier, the entire site may have 10 suppliers, a site on each of the suppliers have a certain amount, and the size of the data between the suppliers of similar size, then you can use the latitude of the supplier to do the split.

For example, in the Usern library, A, B, and C tables are strongly related, there is a complete business logic exists, where only the user (supplier) latitude is not related, then this time can be the user's latitude to split the data.

That is, user 1 and User 2 each have a complete set of business logic, and are not related to each other, so you can divide user 1 and user 2 data into different database instances. At present, many internet companies or game companies have a lot of business is to user latitude to split, such as Qunaer, Sohu game, Sina and so on.

(6.2) Horizontal split

Horizontal split is relatively simple, but the difficulty is large, will lead to distributed situation, cross-data situation, cross-transaction situation can be divided into about three categories, 1 is historical data and real-time data splitting, 2 is the Library multi-table splitting, 3 is multi-Library multi-table splitting.

6.2.1 split of real-time data historical data

and historical data migration is the same logic, is to migrate the online library data into the Listory database, for real-time reading and writing, the data is placed in the online DB Library, for the longer-time data, is placed in the history of the DB Record library, The history library here can be either MySQL or other NoSQL libraries.

6.2.2 Single-Library multi-table splitting

Mainly not to solve the capacity problem, but to solve the performance problem and expand, join the current instance only a db, there is a large table, a large table on the entire instance is full, this time can not split the db, because there is only a single table, this time we can only split the table, the way to split the table is mainly to solve performance problems, Because a single table is larger, it can consume more resources for MySQL to traverse the table's tree structure, and sometimes a simple query can cause many leaf nodes throughout the db to change. Table inserts, UPDATE, delete operations will cause almost all the node changes, at this time the operation will be very large, the operation of the reading and writing performance will be very low, this time we can consider to split the large table into a number of small tables, the work experience is broken into a model according to the hash into 16 small tables, There are also broken into 50 small tables according to the ID primary key/50 modulus, and the instance is broken up into 2 small tables.

6.2.3 Multi-Library multi-table split

On the basis of multiple tables, if the single-Library space resources are not enough to provide business support, can consider multi-Library multi-table approach to do, solve the problem of space and performance, but there is a problem is cross-Library query operation, query will have another strategy, such as adding a logic DB layer to enable cross-Library cross-instance automatic query, as shown in the following:

Summary of 6.2.4 Horizontal splitting

Horizontal Split principle:

–a. Split dimensions as evenly as possible.

–b. Avoid cross-library transactions as much as possible.

–c. Avoid cross-Library queries as much as possible.

Design:

–a according to the split dimension, do the MoD to split the data table, most of the module is the split mechanism, such as the hash of the 16-mode principle.

–b Divide the database according to the data capacity

Data manipulation

–a Cross-transaction operations: Distributed transactions are implemented indirectly by pre-writing the log.

–b Cross-Library query: Data rollup or messaging service

6.2.5 Case Description

U case:

– Split into 64 sub-Libraries according to user dimensions, 1024 sub-tables

• user_id%1024 split into 1024 table
• 1024/64 sub-tables in each library
• Modulo, you can use the ID of the last 4 bits of data or 3 digits to modulo it.

U operation 1: With Configure DB

– After splitting the query operation, do a configure DB, this DB is the mapping of all instances of the library table, when my app sent a request query User1 data, then this User1 data is stored in thousands of instances of which library table? This information is in configure DB, the app goes to configure DB to get User1 of the contact information (such as the t_0016 table stored in the D_01 library), and then the app based on the associated information directly to query d_01 instance T_ 0016 data are obtained from the table.

U operation 2: Using proxy

-After splitting the query operation, do a proxy,app access Proxy,proxy according to the access rules can be directly routed to a specific DB instance, generate new SQL to operate the corresponding DB instance, and then through the proxy protocol operation to return the results of the operation to the app.

– The advantage is that the proxy and DB instances are in a network segment, so that the time to operate the proxy with the DB instance is very short.

U operation 3: Using Data Engine

– After splitting the query operation, there is a data Engine Service, this des is configured all the database instance mapping, need to install an agent on the application side, is the synchronization logic, in the JDBC layer implementation, DES can achieve read and write separation, The principle can refer to the implementation of TDDL.

6.3 Cluster Management

Vertical expansion: an instance split into multiple instances, the vertical split is relatively simple, the modification of less things, when the split to notify the Configure DB or DES, so as not to query after splitting the data or data input less than the new db above, as shown:

Horizontal expansion: More complex, on the basis of vertical expansion into 2 libraries, once again to expand the library table, so need to notify configure DB and Des finer library and table routing connection information.

"Go" MySQL high-availability architecture analysis and research at the business level