Analysis of MySQL high availability architecture at the business layer _ MySQL-mysql tutorial

Source: Internet
Author: User
Compared with the relative service time of 9x9x6 or 9x12x5 in traditional industries, the service requirements are 7x24 hours because of the real-time performance of Internet e-commerce and Internet games, both applications and databases require disaster tolerance and mutual backup. in the mysql system, Preface:

Compared with the relative service time of the traditional industry, 9x9x6 or 9x12x5, because of the real-time performance of Internet e-commerce and Internet games, the service requirement is. The Business architecture is both application and database, both require disaster tolerance and mutual backup. in the mysql system, it is best to implement the disaster tolerance system through the database architecture phase in the initial stage. So here we will explain mysql from a business perspectiveAll aspects of the architecture.

I. MySQL architecture design-Business Analysis (1) Read-Write-less

The dotted line indicates the deployment across data centers. for example, in an e-commerce system, a Master node has both read and write data, which is important for read data consistency. read data should be placed on the Master node.

M (R) is only a slave database. only when M (WR) fails will it be switched to M (R). at this time, M (R) will become a read/write database. For example, in a game system, many Salve instances mount the next M (R.

(2) read-write-less MMS-e-commerce

If it is e-commerce type, this type of read-write is less, generally one master is dragged to four to six slave, all slave attached to a master is enough.

When switching, switch the read/write business of M1 to M2, and then mount all the slave on M1 to M2, as shown below:

(3) read, write, and reduce MMSS-game

If it is in the gaming industry, reading a lot is quite obvious, there will be a general case where one Master will be mounted with more than 10 Slave, so at this time, you can mount some Slave instances to the new M (R. At least some pressure will be reduced, so that at least when the server fails, it will not affect all slave, and some slave on M (R) will continue, it will not affect all slave operations. See,

(4) read less and write more

This means that reading does not affect writing efficiency. Therefore, reading and writing can be stored in one M1 (WR), while the other does not provide reading or writing, and only provides standby redundancy for remote disaster tolerance.

This remote disaster tolerance is very important. Otherwise, if it is a single-host, unilateral business, in case of a fault in the idc room, it will generally affect online business, this will make the business unavailable for 2 hours. for online e-commerce transactions, the impact is quite large. Therefore, in order to maximize the hour guarantee, remote disaster tolerance must be implemented, MM must be used across IDCs. Although there are some requirements for resources, this mechanism is indispensable for HA.

During the switchover, you can directly switch all the reads and writes from M1 to M2.

(5) read and write data in the same autumn

Reading is similar to writing, but reading cannot affect writing ability. put reading and writing on M1 (WR), and then put some reading on M2 (R, of course, M1 and M2 are also deployed across data centers.

When switching, you can switch part of the read and write operations from M1 to M2.

II. MySQL architecture design-common architecture (1) strong consistency

The trade-off between read consistency and read consistency is that if the real-time read/write requirements are very high, the read and write will be placed on M1, and M2 is only used as standby, that is, take and the above one (4) the same architecture mode.

For example, the order processing process requires strong consistency for reading and real-time write and real-time read. this architecture mode is used for transactions or dynamic real-time report statistics.

(2) weak consistency

For weak consistency, you can share some read pressure and traffic on M2, such as reading some reports and reading modules of static configuration data. For example, monthly statistical reports, such as homepage recommendation products, do not have high real-time requirements. this weak consistency design architecture model can be used.

(3) intermediate consistency

If there is neither strong consistency nor weak consistency, we will adopt an intermediate strategy, that is, deploying an S1 (R) in the same data center as a slave database to provide Read services, this reduces the pressure on M1 (WR), while M2 in another idc is only used for standby disaster recovery.

Of course, three database servers will be used here, which may increase the procurement pressure. However, we can provide better external data service capabilities and approaches. In practice, we try our best to take both of them into account.

(4) Statistical Service

For example, PV, UV operations, page number statistics, traffic statistics, and data summary can all be categorized into statistical services.

Statistics on large queries on databases consume a lot of resources. Statistics are classified into real-time statistics and non-real-time statistics. because mysql master/slave adopts the logical SQL mode, it cannot reach 100% in real time, for online statistics that require strict and real-time statistics, such as railway tickets and financial settlement in different regions, mysql is not its strength. it is only possible to query the M1 master database.

A, but for non-strict real-time statistics, mysql has A good mechanism of binlog. we can use binlog to parse Parser, parse it and write it to the statistical table for statistics or send a message to the application for statistics. This is a quasi-real-time statistical operation with a certain short and acceptable statistical delay. if 100% real-time statistics are required, only the M1 master database is queried.

Statistics are achieved through binlog. in the Internet industry, especially e-commerce and games, it can solve more than 90% of statistics services. Sometimes, if users or customers ask for real-time read-time, you can talk about why real-time is required and understand specific business scenarios. some may really do not need real-time statistics and need to be weighed, it is necessary to communicate effectively with users and customers multiple times to make a suitable statistical architecture model for the business.

B. There is also an offline statistical service, such as monthly report and annual report statistics. This allows you to store data in a data warehouse or a third-party Nosql for statistics.

(5) historical data migration

For historical data migration, do not affect the current online business as much as possible, and do not affect the current online query and write operations. why do we need to migrate historical data? Because the data of some businesses is time-sensitive, such as the historical orders already completed in e-commerce, there will be no more update operations, only simple query operations, and queries will not be frequent, it may not even be queried once a day.

If the historical data is still in the online database or in the online table, the online performance will be affected. Therefore, you can migrate the data to the new historical database, this historical database can be mysql, nosql, data warehouse, or even hbase big data.

The solution is to query all the data in the slave database, filter and screen the data based on business rules such as time and a certain latitude, and store the data in the historical database (History Databases. After the migration is complete, return to the master database M1 and delete the historical data. In this way, at the business level, the query should take into account the current real-time data and historical data, and the online query and history query can be connected based on the migration rules on the filter. For example, if you query the online database in the history database one month ago, you can place this rule on the database migration filter layer and application query business module layer. If you can, you can also configure more details. the application query service module layer affects the database migration filter layer. for example, the previous query was divided into one month as the benchmark, and now the query service has changed, based on 15 days, the changes in the application query business module layer will automatically change the filter layer of the database to achieve semi-automation and intelligence.

(6) MySQL Sharding

The rac-based shared storage method like oracle does not need to be sharding, but only needs to be expanded from the rac storage. However, this cost is relatively higher, and shared storage is generally more expensive. with the explosive growth of business expansion data, you will accumulate your costs and even reach an astronomical number.

At present, this share disk method is not perfect for other solutions except for the perfect business logic layer of oracle.

Mysql sharding also has its limitations. after sharding, data query access and statistics will be very problematic. mysql sharding is a distributed method for solving share nothing storage, it can be divided into vertical split and horizontal split.

(6.1) vertical split

It can be split horizontally, vertically, horizontally, or by business.

6.1.1 horizontal split

Horizontal split in the Mysql database refers to the fact that each database instance has many database databases, and each database has table A and Table B. For example, database db1 has table A and Table B, db2 database also has table A and Table B, so we split Table B of db1 and db2 database into two databases, it is split into db1, db2, db3, and db4. database db1 and database db2 put table A data, and database db3 and database db4 put Table B data, db1 and db2 databases only have table A data, while db3 and db4 databases only have Table B data.

For example, for e-commerce, each database contains A log table and an Order table. assume that table A is A log table, and Table B is an Order table, generally, writing logs is not highly correlated with writing orders. we can tell you how to split A table log table and B table order table. In this case, A horizontal split is performed to split the log table of Table A and the order table of Table B into different databases, of course, the database names of Table A and Table B can be the same (PS: in different instances), as shown in:

PS: This split has little impact on different business tables, and the business associations between tables are weak or basically not. The benefit of splitting is that unrelated data tables are split into different instances, which is quite advantageous for the balance of database capacity expansion and performance improvement.

6.1.2 Vertical Split

Split different database databases on the same instance and put them into separate instances. The applicable scenarios and requirements for this split are that db1 and db2 have little business connection, similar to table A and Table B in 6.1.2. If you use db1 and db2 for cross-database services at the same time, I suggest you re-consider the business and try to put the tables of a module in a single database, do not kill database operations.

In this vertical split, the separate database db1, table A and Table B are strongly correlated. As shown in:

PS: many people who use mysql always put many non-business-related tables in the same database, or always put many db databases in the same instance, just like using oracle, it's just the concept of an instance. One principle of using Mysql is to use mysql as simple as possible. databases should be strictly separated; tables should be strictly split into databases if there is no relationship. In this way, it is very convenient to expand our business. you only need to split the database where the business module is located and put it on the new database server.

6.1.3 Horizontal Vertical Split

In some of the initial stages, all the tables in the database were put on an instance in order to quickly get out of the product. after business development, data splitting was faced, here we will combine the vertical split to implement it together, as shown in:


6.1.4 Business splitting

It is a bit similar to horizontal split, but it is different. For example, a single supplier may have 10 vendors on the entire website. each vendor on a single website has a certain amount, and the data volume between suppliers is similar, in this case, the supplier's latitude can be used for splitting.

For example, in the usern database, tables a, B, and c are strongly correlated and have complete business logic. here, only the user (supplier) latitude is not correlated, in this case, the data can be split by the user's latitude.

Both user 1 and user 2 have a complete set of business logic and are not associated with each other. Therefore, you can split User 1 and user 2 data into different database instances. Currently, many Internet companies or game companies split their businesses at the user level, such as qunaer, sohu game, and sina.

(6.2) horizontal split

Horizontal splitting is relatively simple, but difficult. it can lead to distributed, cross-data, and cross-transaction situations. The first is historical data and real-time data splitting, 2 is single-database, multi-table splitting, and 3 is multi-database, multi-table splitting.

6.2.1 splitting of historical real-time data

The same logic as historical data migration is to migrate data from the online database to the listory Database. for real-time read/write, data is stored in the online database, for far-fetched data, it is stored in the historical History DB record library, where the historical database can be mysql or another nosql database.


6.2.2 single-database, multi-table splitting

Instead of solving the capacity problem, it expands to solve the performance problem. the current instance has only one DB and a large table. a large table fills up the entire instance, at this time, we can't split the database, because there is only one single table, we can only split the table at this time, the way to split the table is mainly to solve the performance problem, because the larger a single table, for mysql, the tree structure of the traversal table will consume more resources, and sometimes a simple query may cause many leaf nodes in the entire database to change. The insert, update, and delete operations on a table will cause changes to almost all nodes. at this time, the operation volume will be very large and the read/write performance will be very low during the operation, in this case, we can consider splitting a large table into multiple small tables. in our work experience, we split it into 16 small tables based on hash modulo, the primary key/50 Modulo is also hashed to 50 small tables, and the instances are hashed into two small tables.

6.2.3 multi-database, multi-table splitting

On the basis of a single database and multiple tables, if the single database space resources are insufficient to provide business support, you can consider the multi-database and multi-table approach to solve the space and performance problems, however, there will be a problem with cross-database query operations, and there will be another policy for querying. for example, you can add a logic db layer to implement cross-database cross-instance automatic query, as shown in a simple example:


6.2.4 horizontal split summary

Horizontal split principle:

-- A. try to split the dimension evenly.

-- B. avoid cross-database transactions as much as possible.

-- C. avoid cross-database queries as much as possible.

Design:

-- A performs mod to split data tables based on the sharding dimension. most data tables are split by modulo, such as the hash 16-mode principle.

-- B split the database based on the data capacity

Data operations

-- A cross-transaction operations: Distributed transactions are indirectly implemented through log writing.

-- B Cross-database query: data aggregation or message service
6.2.5 case description

U case:

-Split the database into 64 sub-databases and 1024 sub-tables according to the user dimension.

? User_id % 1024 split into 1024 sub-tables

? Store 1024/64 sub-tables in each sub-Database

? You can use the last 4 digits or 3 digits of the id to perform the modulo operation.

U operation 1: Use Configure DB

-Perform a Configure DB for the split query operation. This DB stores the ing relationships between database tables of all instances. when my APP sends a request to query user1 data, which database table is the data of user1 stored in thousands of instances? This association information is in Configure DB. The APP first obtains the contact information of user1 in Configure DB (for example, in the t_0016 table stored on the d_01 database ), then, the APP queries the t_0016 table of the corresponding d_01 instance based on the association information to obtain data.

U operation 2: Use Proxy

-For the split query operation, create a Proxy for the APP to access the Proxy. the Proxy can be directly routed to a specific db instance according to the access rules, and generate a new SQL statement to operate the corresponding db instance, then, the operation result is returned to the APP through the Proxy protocol.

-The advantage is that the Proxy and db instance are in a network segment, so that the operation time between the Proxy and db instance is very short.

U operation 3: Using Data Engine

-For the split query operation, there is a Data Engine Service. This DES is configured with the ing relationships between all database instances. an Agent needs to be installed on the APP application, which is the synchronization logic, in the JDBC layer, DES can implement read/write splitting. for the principle, see TDDL implementation.



6.3 Cluster management

Vertical scaling: a single instance is split into multiple instances. vertical splitting is relatively simple and there are few changes. you must notify Configure DB or DES when splitting, to avoid data not found after splitting or data cannot be entered into the new database, as shown in:

Horizontal scale-up: relatively complex. based on the vertical scale-up into two databases, the table of the database is resized again, therefore, you need to promptly notify Configure DB and DES of the route connection information of finer databases and tables.


Bytes ----------------------------------------------------------------------------------------------------------------

<版权所有,文章允许转载,但必须以链接方式注明源地址,否则追究法律责任!>
PS: from learning MySQL Benefits of the MySQL database O & M course.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.