Dynamic read/write separation

From the perspective of application, we have summarized some practical experience in stability governance, performance optimization, efficiency improvement and other aspects of our scenarios when accessing and using databases. For each back-end application, the database is undoubtedly the top priority. We hope that our database governance capabilities can help you better use database services.

MSE Database Governance Complete Solution

This article will introduce in detail the hot functions of MSE database governance, and the design and implementation of dynamic read/write separation.

Overview of Read Write Separation

Common scenarios of database dynamic read/write separation:

• A request from a large customer returns tens of thousands of hundreds of megabytes of data from the query database, and the CPU of the database is directly full.

• Some businesses of the microservice application are not so important, but there is a lot of logic to query the database, which affects the stability of the database instance, leading to the decline of the overall service quality.

• In the process of business processing, if the number of read operations to the database is far more than write operations, the solution of separating read and write can be considered when optimizing the system performance. On the one hand, the read-only database can bear the pressure of the main database, on the other hand, it can effectively avoid lock waiting caused by data updates, and improve the performance of microservice applications.

• With the growth of business, we need to expand the database instance at a certain time. According to experience, the read/write ratio of most applications is more than 5:1, and some scenarios are even larger than 10:1. In an application scenario where there are a few write requests to the database but a large number of read requests, a single instance may not be able to withstand the read pressure, or even have an impact on the business.

It can be learned that the database read/write separation scheme can meet the stability governance, performance improvement and database expansion needs of most Alibaba Cloud companies.

Students who interpret and implement the separation of writing will pay attention to the following issues:

• How does MSE solve the invasion of read/write separation to business? How to achieve the separation of reading and writing without changing a line of code.

• How does MSE achieve refined and dynamic read/write separation control? Even though we don't know what the real SQL of this business interface is, we can control the read SQL access to read-only instances of this interface.

• How does MSE solve the consistency problem caused by read/write separation? For consistency sensitive businesses, how to ensure consistency and meet the requirements for consistency levels in different scenarios.

Uncover MSE Read Write Separation Technology

The read/write separation means that the database is divided into the master database and the slave database. That is, the master database is responsible for handling transactional add/delete operations, and the slave database is responsible for handling query operations. Just looking at the concept of read/write separation, the first impression is that the business must be invasive. How does MSE achieve non invasion?

Non intrusive: no need to modify one line of code

MSE database governance capability dynamically increases users' data sources through JavaAgent technology, injects dynamic read/write separation capability, and supports dynamic routing of weak read requests to read-only instances at runtime.

MSE implements abstraction at the data source level, where DynamicConnection and DynamicStatement will implement the Master/Slaver switch according to specific rules, so as to route SQL according to the SQL read/write type, transaction status and user's business rules, and forward qualified read SQL requests to the RDS read-only instance.

Refined routing: according to the request conditions, interfaces, and SQL multi-level conditions

Many times we access the database by writing DAO. In some complex application scenarios, we may only know the DAO interface, and in some complex scenarios, we may only know the interface of the microservice. We may not even know which DAO interface and SQL statement are called internally, or even if the O&M role participates in the design, we may not know which microservice interface causes the read request to cause the database jitter, We only know a certain uid of the portal application. How can we route read requests in the business interface to read-only instances?

MSE database governance provides complete callStack information at the application level, which allows us to clearly see which interfaces execute which SQL from the application perspective.

MSE supports the marking of weak read requests at the entrance microservice, microservice interface, DAO level, supports the marking of weak read marks at multiple levels such as SQL calls in the current thread, SQL calls in the current microservice, and all SQL calls at the request link level that meet the traffic conditions through the link transmission technology, and finally passes them to the routing engine of the read write separation component to judge the routing basis of SQL.

Strong consistency mode: specify interface and transaction

When the database load is very high, such as when DDL (such as adding fields) operations are performed on large tables or data is inserted in large batches, the delay will be very serious, resulting in the inability to read the latest data from the read-only instance. MSE provides some policies to solve the above problems. Some interfaces or certain businesses have high consistency. We can tell MSE through rule configuration that certain read interfaces are marked as strong read requests in specific scenarios. MSE uses some mechanisms to ensure the strong consistency of read/write separation.

Whitescreen capability: real-time perception of read-write separation through AccessLog

With read-write separation capability, how do we know the implementation of read-write separation, which applications and requests are separated to read-only instances? The white screen capability of MSE provides a complete set of AccessLog.

• The read request is routed to the read-only instance

• The read request is routed to the primary instance

summary

From the perspective of application, combined with the general technology of microservice governance, MSE launched a complete database governance solution, from SQL insight, SQL flow control degradation and fault tolerance, connection pool governance to database grayscale, dynamic read/write separation. We hope that the database governance capability can help users' microservices better use the database, reduce the cost of using the database, and improve the stability of database access.

MSE's database governance capabilities also require more and more in-depth customer scenarios and implementation practices. If you are interested in MSE's database governance capabilities, please contact us. Only products polished by customers will become more durable.

While building database governance capabilities, we are also building database governance standards with the community through OpenSergo.