JD Zhilian cloud MySQL read write separation best practice, easy to do high performance database expansion

Time:2021-3-4

In the digital age, database is the core resource for any enterprise. MySQL as the most popular relational database, although it is open source software, but it is easy to understand, easy to deploy and manage, and has many advantagesAcid features, powerful SQL queryIt is used as the key core storage by various business systems.

JD Zhilian cloud MySQL read write separation best practice, easy to do high performance database expansion

With the development of business volume and the growth of data volume, it is difficult for stand-alone database to meet the business needs. The most common performance expansion method of MySQL is “read-write separation”. Its essence is to disperse the access pressure to multiple MySQL nodes, but the storage pressure is not dispersed. “Sub database and sub table” can disperse the access and storage pressure, but the implementation difficulty and maintenance cost will be greatly reduced Increase.

JD Zhilian cloud MySQL read write separation best practice, easy to do high performance database expansion

Structure diagram of read write separation

  • It needs a master-slave cluster architecture, one master and one slave or one master and multiple slaves;
  • The master library responds to the write request and the slave library responds to the read request;
  • The master database synchronizes the data to the slave database through replication, and each node saves a copy of business data.

JD Zhilian cloud MySQL read write separation best practice, easy to do high performance database expansion

There are two common ways to achieve the “read write separation” of MySQL in the industry_Client implementation and proxy implementation,_ The two methods have their own advantages and disadvantages, which need to be combined with their own business characteristics, system architecture and operation and maintenance capabilities. Because the introduction of agent will make the system architecture more complex, and the agent itself should consider performance, high availability, stability and other factors, small and medium-sized companies generally use the client method to achieve read-write separation. Large companies have the resources to invest in the development and maintenance of database agents. The more services they access, the more value they bring.

First of all, let’s talk about the client implementation method. For example, Java language can be simply encapsulated based on mybatis and hibernate to realize the separation of reading and writing. The architecture is as follows:

JD Zhilian cloud MySQL read write separation best practice, easy to do high performance database expansion

The agent implementation method requires the introduction of database agent to realize the functions of read-write separation and database management. The agent is compatible with SQL protocol. For the client, the agent is the database. The architecture is as follows:

JD Zhilian cloud MySQL read write separation best practice, easy to do high performance database expansion

The advantages and disadvantages of the two architectures are compared

JD Zhilian cloud MySQL read write separation best practice, easy to do high performance database expansion

No matter which architecture, the implementation logic is not complex, but there is one detail that needs special attention_MySQL master-slave replication delay._

MySQL replication is asynchronous. Although there is an optimization scheme for different delay reasons, the delay is objective and cannot be avoided. When there is a large delay in read-only, there will be data inconsistency. The data read is before the main database is submitted, which is called “overdue read”. At this time, there may be problems in the business. Common solutions are as follows:

  • Core business read and write all point to the main library, non core business use read and write separation. For example, in financial business, in order to ensure the consistency of data, scalability is sacrificed. For example, the business of forum will be inquired later after posting, and its impact will be much smaller, which can be tolerated;
  • Second read, check again after read failure, it has nothing to do with business logic, just need to re encapsulate the database access layer, the implementation cost is small. However, in some scenarios, it expands the number of read requests and increases the pressure on the database. For example, DDoS attacks can easily crush the database;
  • Judge delay to read again, judge whether there is delay before reading from the database, query from the database without delay, and query from the main database if there is delay. MySQL itself provides a monitoring method to judge the delay. If gtid is turned on, the retrieved from the slave library is directly compared_ Gtid_ Set and executed_ Gtid_ If they are the same, it means that there is no delay from the slave library; if gtid is not turned on, two sets of values need to be compared, master_ Log_ File and relay_ Master_ Log_ File、Read_ Master_ Log_ POS and Exec_ Master_ Log_ If the two sets of values of POS are exactly the same, it means that there is no delay in the slave library.

JD Zhilian cloud MySQL read write separation best practice, easy to do high performance database expansion

JD Zhilian cloud rds-mysql supportRead write separation agentFunction: after creating a read-only instance, open the read-write separation agent to configure the address of the read-write agent in the program. The read request is automatically forwarded to the read-only instance and the write request is automatically forwarded to the main instance by the read-write agent. At present, MySQL 5.6-8.0, percona and MariaDB all support opening read-only agents. For details, see:https://docs.jdcloud.com/cn/r…

The basic principle and best practice are introduced from the data plane and control plane of this function.

JD Zhilian cloud MySQL read write separation best practice, easy to do high performance database expansion

When users create a read-write separation agent, they will configure parameters such as read-only library delay, health check, load balancing policy, etc. according to the user’s business characteristics, the read-write separation function is realized.

The creation interface of JD Zhilian cloud “read write separation agent” is as follows:

JD Zhilian cloud MySQL read write separation best practice, easy to do high performance database expansion

  1. _Read only instance delay threshold:_ When the delay between the read-only instance and the primary instance exceeds the threshold, the read-only instance will be removed from the back end of the read-write agent.
  2. Load balancing strategy:The load balancing strategy for the read-write agent to distribute read-write requests supports the minimum number of active connections, the minimum number of connections, the minimum synchronization delay and the average response delay.
  3. Connection timeout:Read only instance health check method. After the timeout, the read-only instance is removed from the read-write agent backend.
  4. _Repeat times:_ Read only instance health check method. After the number of retries exceeds the specified number, the read-only instance health check fails and will be removed from the read-write agent backend.
  5. Inspection interval:Read only instance health check method, according to the time interval of read-write agent back-end read-only instance health check.

Best practice: read only instance delay threshold is configured according to business characteristics, and the default time is 100s, which is strongly recommended_greater than_ Check the time interval.

JD Zhilian cloud MySQL read write separation best practice, easy to do high performance database expansion

Through RDS console, you can manage the whole life cycle of “read write separation agent”, including creating, modifying configuration, deleting and opening public network access. In addition, the “read-write separation agent” can also sense the high availability switch of forwarding MySQL instances, modify the configuration process, and create and delete read-only instances.

  1. When the forwarding MySQL is switched from master to slave, the “read / write separation agent” will automatically adjust the write request and forward it to the new master database;
  2. Modify the configuration. When the forwarding MySQL master-slave instance or read-only instance triggers the business process of expansion or reduction, the “read-write separation agent” will automatically adjust to forward the read-write request to the new instance;
  3. After the user adds a new read-only instance, the read-write separation agent will automatically forward the request to the new read-only instance according to the load balancing algorithm; when the user deletes the read-only instance, the read-write separation agent will automatically stop forwarding the request to the deleted read-only instance.

Although the “read-write separation agent” will introduce proxy forwarding requests between the client and the database, which will cause performance loss, the back-end has multiple read-only nodes to respond to requests. The maximum amount of read-only instance data of JD Zhilian cloud is 8. Therefore, in the case of large user business read requests, using the “read-write separation agent” is the best way to improve performance.

Through the above content, you must have a preliminary understanding of how MySQL uses the method of read-write separation to ensure high-performance extension. If you want to further experience MySQL service, please click_[read the original]_ Link experience trial.