Database management tool: dynamic read-write separation

Author: ten sleep

background

In a distributed system architecture, business traffic is end-to-end. Each request will go through many layers of processing, such as from the ingress gateway to the Web Server to the invocation between services, and then to the service accessing cache or storage such as DB.

The database is a very important piece of our system. Therefore, whether in terms of stable governance or in scenarios such as development and efficiency improvement, database-related governance capabilities are the capabilities our system needs to have. The following summarizes some common scenarios and capabilities in database governance when microservices access the database layer.

An overview of database governance in the OpenSergo world

This article will introduce the recent launch of MSE Service Governance, a powerful tool for database governance: the ability to separate read and write for database access without intrusion.

What is read-write separation?

Read-write separation is to split the database into the master database and the slave database, that is, the master database is responsible for transactional addition, deletion and modification operations, and the slave database is responsible for the database architecture of query operations.

Why read and write separation?

stability

A large customer's request comes, and the query database returns tens of thousands of pieces of data of hundreds of megabytes, and the CPU of the database is directly full. I don't know if any of you have encountered similar problems.

performance

In the process of business processing, if the read operations to the database are far more than the write operations, and the real-time requirements of the data query results are not high (for example, seconds-level delay can be tolerated), then the system performance optimization can be performed. Considering the introduction of the read-write separation scheme, the read-only library can bear the pressure of the main library and effectively improve the performance of microservice applications.

Scale growth

With the growth of the business, the capacity can be expanded after reaching a certain scale, but many of them are stuck in the expansion step, which greatly limits the speed of responding to market changes. Among them, the expansion of the database is the most difficult. At present, the common methods of database expansion are as follows: way:

• vertical upgrade
• Sub-library and sub-table
• read-write separation

Vertical upgrade requires service interruption and high availability is not as good as other methods. The selection of partition keys for sub-database and sub-tables will be difficult. There will be many restrictions on the use of SQL, and the transformation of the business is also a very large workload. Relatively speaking, read-write separation is the least intrusive and easiest to implement expansion plan. According to experience, the read-write ratio of most applications is above 5:1, and in some scenarios, a large number is even higher than 10:1. In an application scenario where there are a small number of write requests to the database but a large number of read requests, a single instance may not be able to withstand it. Read pressure and even have a business impact.

In summary, the database read-write separation solution can meet the needs of most companies on Alibaba Cloud for stability management, performance improvement, and database expansion.

Common solutions for read-write separation

At present, the popular read-write separation solutions in the industry are usually based on the above-mentioned master-slave mode database architecture. Most of the implementation schemes of read-write separation are to introduce data access agent products such as odp and mycat, and use their read-write separation function to help realize read-write separation. The advantage of introducing a data access agent is that the source program can be separated from reading and writing without any changes. The disadvantage is that the performance will be degraded due to the addition of a layer of middleware as a transit agent, and the data access agent can easily become a performance bottleneck.

ShardingSphere read-write separation scheme [ 1] (taken from shardingsphere official website)

The read-write separation of ShardingSphere [ 2] mainly depends on the related functions of the kernel. Including parsing engine and routing engine. The parsing engine converts the user's SQL into Statement information that can be recognized by ShardingSphere, and the routing engine performs SQL routing according to the SQL read/write type and transaction status. As shown in the figure below, ShardingSphere recognizes read and write operations and routes them to different database instances.

MSE database read-write separation capability

MSE provides a dynamic data traffic management solution, you can realize the read-write separation capability of the database without modifying any business code. The following describes the read-write separation capability of MSE based on Mysql data storage.

Preconditions

• Application access to MSE
• Deploy the demo application

Three applications A, B, and C are deployed in Alibaba Cloud Container Service, and all applications are connected to the MSE service governance [ 3] to increase the agents with database governance capabilities.

• Create RDS read-only instance [ 4 ]

We need to create an RDS read-only instance, and use the read-only instance to meet a large number of database read requirements and increase the throughput of the application.

Configure read-write separation rules

• We need to configure the following environment variables to additionally enable/configure the read-write separation capability of the database

• We can configure the rules for weak read requests through the console or specify certain interfaces as weak read requests

 apiVersion: database.opensergo.io/v1alpha1
kind: AccessControlRule
metadata:
  name: read-only-control-rule
  labels:
    app: foo
spec:
  selector:
    app: foo
  target: 
    - resource:
        path: '/getLocation'
  controlStrategies:
    weak: true

The above rules of the OpenSergo standard indicate that the request of the /getLocation interface is a weak read request.

We can configure weak type for some large data volume queries and business requests that are less sensitive to delays

SQL Insights

As above, we can achieve the read-write separation ability of the database in just two easy steps. Based on the ability of database read-write separation and with the SQL insight of MSE database management, we can quickly locate query requests with excessive RT, helping us to further analyze the impact of SQL on the stability of our database.

I can observe the real-time data of SQL requests (refined to the second level) in the API dimension of applications and resources. At the same time, MSE also provides the topN list of SQL. We can see at a glance that the RT is high and the query returns a large amount of data.

Summarize

This article details the introduction of dynamic read-write separation capabilities in MSE's upcoming Database Governance Capability Matrix. Through the SQL insight provided by MSE, combined with our understanding of the business, we can quickly locate and divide interface requests into weak requests. The read operations that will have a great impact on the performance and stability of the main library can be offloaded to the RDS read-only library, which can effectively reduce the read and write pressure on the main library and further improve the stability of microservice applications.

From the perspective of application, we abstract some common scenarios and corresponding governance capabilities when we access and use databases, and organize our practical experience in stability governance, performance optimization, and efficiency improvement. For every back-end application, the database is undoubtedly the top priority. We hope that through our database governance capabilities, we can help everyone use database services better.

Finally, the standard OpenSergo for service governance is mentioned:

Q: What is OpenSergo [ 5]

A: OpenSergo is a set of open, general-purpose service governance standards oriented to distributed service architecture and covering the whole-link heterogeneous ecology. It forms a general service governance standard based on industry service governance scenarios and practices. The biggest feature of OpenSergo is that it defines service governance rules with a unified set of configuration/DSL/protocol, oriented to multi-language heterogeneous architecture, and achieves full-link ecological coverage. Whether the language of the microservice is Java, Go, Node.js or other languages, whether it is standard microservice or mesh access, from gateway to microservice, from database to cache, from service registration discovery to configuration, developers can The same set of OpenSergo CRD standard configuration performs unified governance and control for each layer, without paying attention to the differences between frameworks and languages, reducing the complexity of heterogeneous and full-link service governance and control

OpenSergo will also launch standards related to database governance in September, which will further abstract capabilities related to standardized database governance. At present, the OpenSergo community is working with various communities for further cooperation, and through the community to discuss and define a unified service governance standard. The current community is also working with bilibili, ByteDance and other companies to jointly build standards. Interested developers, communities and companies are also welcome to join in the joint construction of OpenSergo service governance standards. Welcome to join the OpenSergo community exchange group (Dingding group) for discussion: 34826335

Reference link:

[1] ShardingSphere read-write separation scheme:

https://shardingsphere.apache.org/document/current/cn/features/readwrite-splitting/

[2] ShardingSphere:

https://shardingsphere.apache.org/document/current/en/overview/

[3] Access to MSE service governance:

https://help.aliyun.com/document_detail/425896.html

[4] Create an RDS read-only instance:

https://help.aliyun.com/document_detail/26136.html

[5] OpenSergo:

https://opensergo.io/en-us/