Database governance tools

Background

In the distributed system architecture, business traffic is end-to-end. Each request will go through many layers of processing, such as from the portal gateway to the Web server to the call between services, and then to the service access cache or DB and other storage.

Database is a very important part of our system. Therefore, whether in the governance of stability or in the development and efficiency improvement scenarios, the governance capabilities related to the database are the capabilities that our system needs. The following summarizes some common scenarios and capabilities in database governance when microservices access the database layer.

Overview of database governance in OpenSergo

This article will introduce MSE service governance's recently launched database governance tool: non-intrusive database access to achieve read and write separation.

What is read/write separation?

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 processing the transactional addition, deletion and modification operations, and the slave database is responsible for processing the database architecture of the query operations.

Why should we separate reading and writing?

stability

A request from a major customer came to query the database and returned tens of thousands of hundreds of megabytes of data. The CPU of the database was directly full. I don't know if you have encountered similar problems.

performance

In the process of business processing, if there are far more read operations than write operations on the database, and the real-time requirements of the data query results are not high in the business (for example, the second delay can be tolerated), then the read-write separation scheme can be considered when optimizing the system performance. The read-only database can bear the pressure of the main database and effectively improve the performance of the microservice application.

Scale growth

As the business grows, it will 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. Database expansion is the most difficult. At present, the common database expansion methods are as follows:

• Vertical upgrade

• Sub-base and sub-table

• Read and write separation

The vertical upgrade needs to interrupt the service and the high availability is not as good as other methods. The selection of the partition key of the database and table will be a difficulty. There will be many restrictions on the use of SQL. At the same time, the transformation of the business is also a very heavy workload. Relatively speaking, read/write separation is the lowest intrusion to business and the easiest solution to achieve expansion. According to experience, the read-write ratio of most applications is more than 5:1, and some scenarios are even more than 10:1. In the application scenario where there are a small number of write requests to the database, but there are 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.

To sum up, the database read/write separation scheme can meet the needs of stability governance, performance improvement and database expansion of most companies on Alibaba Cloud.

Common solutions for read/write separation

At present, the popular read-write separation scheme in the industry is usually based on the above master-slave database architecture. The implementation of read-write separation is mostly through the introduction of data access agent products such as odp and mycat, which help to achieve read-write separation through its read-write separation function. The advantage of introducing the data access agent is that the source program can achieve read/write separation without any changes. The disadvantage is that the performance will be reduced due to the addition of a layer of middleware as the transfer agent. The data access agent is also easy to become a performance bottleneck.

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

The read/write separation of ShardingSphere [2] mainly depends on the relevant functions of the kernel. It includes resolution engine and routing engine. The parsing engine converts the user's SQL into Statement information that can be recognized by ShardingSphere. The routing engine routes the SQL according to the read/write type of the SQL and the status of the transaction. 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 flow governance scheme. You can realize the read/write separation of the database without modifying any business code. The following describes the read/write separation capability of MSE based on MySQL data storage.

prerequisite

• Application access to MSE

• Deploy Demo application

Deploy three applications A, B and C in Alibaba Cloud container service and connect them to MSE service governance [3] to add agents with database governance capabilities.

• Create RDS read-only instance [4]

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

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 of weak read requests through the console or specify some interfaces as weak read requests

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

We can configure some business requests with large amount of data and less sensitive to delay as weak type

SQL Insight

As mentioned above, we only need two easy steps to realize the read and write separation capability of the database. Based on the database read-write separation ability and the SQL insight of MSE database governance, we can quickly locate query requests with excessive RT, and help us further analyze the impact of SQL on our database stability.

I can observe the real-time data of SQL requests in the application and resource API dimensions (detailed to the second level). At the same time, MSE also provides the topN list of SQL. We can see at a glance the SQL statements with high RT and large amount of data returned by query.

summary

This article describes in detail the introduction of the dynamic read/write separation capability in the database governance capability matrix to be launched by MSE. Through the SQL insight provided by MSE and our understanding of business, we can quickly locate and divide interface requests into weak requests. Redistributing read operations that have a great impact on the performance and stability of the main database to the RDS read-only database can effectively reduce the read and write pressure of the main database and further improve the stability of the microservice application.

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

Finally, the standard of service governance, OpenSergo, is mentioned:

Q: What is OpenSergo [5]

A: OpenSergo is a set of open, universal, distributed service architecture oriented service governance standards that cover the whole link isomerization ecosystem. Based on industry service governance scenarios and practices, it forms a common standard for service governance. The biggest feature of OpenSergo is to define the service governance rules with a unified set of configurations/DSL/protocols, and face the multilingual heterogeneous architecture to achieve the full link ecological coverage. Whether the language of 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 conduct unified governance and control for each layer through the same set of OpenSergo CRD standard configuration, without paying attention to the differences of various frameworks and languages, and reducing the complexity of heterogeneous and full-link service governance and control