Database management tool: dynamic read-write separation

作者：十眠

背景

在分布式系统架构中,业务的流量都是端到端的.每个请求都会经过很多层处理,比如从入口网关再到 Web Server 再到服务之间的调用,再到服务访问缓存或 DB 等存储.​

对于我们的系统来说,数据库是非常重要的一块.因此无论是在稳定性的治理上,还是在开发提效等场景下,数据库相关的治理能力都是我们系统所需具备的能力.下面总结了微服务访问数据库层时,在数据库治理中的常见的一些场景与能力.​

OpenSergo 领域中关于database management概览

本文将介绍 MSE Service Governance has recently launched a database governance tool：The ability to separate read and write for database access without intrusion.

什么是读写分离？

Read-write separation is to split the database into the master library and the slave library,That is, the main library is responsible for handling transactional additions, deletions and modifications,The database schema in which the slave library is responsible for processing query operations.

为什么要读写分离？

稳定性

A big client's request came over,Querying the database returns hundreds of thousands of records M 的数据,数据库的 CPU 直接打满.I don't know if anyone has encountered a similar problem.

性能

在业务处理过程中,If there are far more reads to the database than writes,At the same time, the real-time requirements of data query results are not high in business（For example, delays in the order of seconds can be tolerated）,Then, when optimizing system performance, you can consider introducing a read-write separation scheme,Read-only libraries can take the pressure of the main library,Effectively improve the performance of microservice applications.

规模增长

随着业务增长,After reaching a certain scale, it can be expanded,But many are stuck in the expansion step,Greatly limited the speed of responding to market changes,Among them, the expansion of the database is the most difficult,At present, the common database expansion methods are as follows：

• 垂直升级
• 分库分表
• 读写分离 

Vertical upgrades require outages and are less highly available than several other approaches,The selection of partition keys for sub-database and sub-table will be difficult,SQL There are many restrictions on use,At the same time, 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.Based on experience, the read-write ratio for most applications is in 5:1 以上,Some scenes are even massively higher 10:1,在对数据库有少量写请求,但有大量读请求的应用场景下,单个实例可能无法承受读取压力,甚至对业务产生影响.

In summary, the database read-write separation solution can meet the stability governance of most companies on Alibaba Cloud、Performance improvement and the need for database expansion.

读写分离常见方案

At present, the popular read-write separation scheme in the industry,It is usually a database architecture based on the above master-slave model.The implementation of read-write separation is mostly through introduction odp、mycat and other data access proxy products,It helps achieve read-write separation through its read-write separation function.The advantage of introducing a data access proxy is that the source program can achieve read-write separation without any changes,The disadvantage is that there is an additional layer of middleware as a transit agent,There will be a drop in performance,Data access proxies can also easily become a performance bottleneck.

ShardingSphere 读写分离方案^[1]（摘自 shardingsphere 官网）

ShardingSphere^[2]The separation of read and write mainly depends on the related functions of the kernel.Including parsing engine and routing engine.The parsing engine converts the user's SQL 转化为 ShardingSphere 可以识别的 Statement 信息,Routing Engine according to SQL the type of read and write and the state of the transaction SQL 的路由.如下图所示,ShardingSphere Read and write operations are recognized,Each will be routed to a different database instance.

MSE Database read-write separation capability

MSE Provides a dynamic data traffic management solution,您可以在不需要修改任何业务代码的情况下,Implement the read-write separation capability of the database.下面介绍 MSE 基于 Mysql The read-write separation capability of data storage.

前提条件

• 应用接入 MSE
• 部署 Demo 应用 

在阿里云容器服务中部署 A、B、C 三个应用,and will apply both接入 MSE 服务治理^[3],Used to increase the ability of database management Agent.

• 创建 RDS 只读实例^[4] 

我们需要创建 RDS 只读实例,利用只读实例满足大量的数据库读取需求,增加应用的吞吐量.

配置读写分离规则

• 我们需要配置以下环境变量来额外开启/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
      
      
      
      

       
       
1.
       
       
2.
       
       
3.
       
       
4.
       
       
5.
       
       
6.
       
       
7.
       
       
8.
       
       
9.
       
       
10.
       
       
11.
       
       
12.
       
       
13.
       
       
14.
      
      

上述 OpenSergo Standard rule representation /getLocation The request of the interface is a weak read request.

We target some large data volume queries、Business requests that are less sensitive to latency can be configured as weak 类型

SQL 洞察

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 and write separation,配合 MSE database management SQL Insights we can quickly locate RT Excessive query request,Help us analyze further SQL Impact on the stability of our database.

I can observe applications and resources API 维度的 SQL Request real-time data（细化至秒级）,同时 MSE 还提供了 SQL 的 topN 列表,我们可以一眼看出 RT 高,The query returns a large amount of data SQL 语句.

总结

本文详细描述了 MSE An introduction to dynamic read-write separation capabilities in the upcoming Database Governance Capability Matrix.通过 MSE 提供的 SQL 洞察能力,Combined with our understanding of business,We can quickly locate and divide interface requests into weak requests.Read operations that will greatly affect the performance and stability of the main library,divert to RDS 只读库,It can effectively reduce the read and write pressure of the main library,Further improve the stability of microservice applications.

We start from an application point of view,It abstracts some common scenarios and corresponding governance capabilities when we access and use databases,Cleaned up our governance in stability、性能优化、提效等方面的实战经验.对于每一个后端应用来说,数据库无疑是重中之重,我们希望通过我们的数据库治理能力,可以帮助到大家更好地使用数据库服务.

Finally, the standard of service governance is mentioned OpenSergo：

Q：OpenSergo^[5] 是什么

A：OpenSergo 是一套开放、通用的、面向分布式服务架构、覆盖全链路异构化生态的服务治理标准,基于业界服务治理场景与实践形成服务治理通用标准.OpenSergo The biggest feature is a unified set of configurations/DSL/协议定义服务治理规则,面向多语言异构化架构,做到全链路生态覆盖.无论微服务的语言是 Java, Go, Node.js 或其它语言,无论是标准微服务或 Mesh 接入,从网关到微服务,从数据库到缓存,从服务注册发现到配置,开发者都可以通过同一套 OpenSergo CRD 标准配置针对每一层进行统一的治理管控,而无需关注各框架、语言的差异点,降低异构化、全链路服务治理管控的复杂度

OpenSergo 也会在 9 月推出数据库治理相关的标准,会进一步抽象与标准化数据库治理相关的能力.目前 OpenSergo 社区正在联合各个社区进行进一步的合作,通过社区来一起讨论与定义统一的服务治理标准.当前社区也在联合 bilibili、字节跳动等企业一起共建标准,也欢迎感兴趣的开发者、社区与企业一起加入到 OpenSergo 服务治理标准共建中.欢迎大家加入 OpenSergo 社区交流群（钉钉群）进行讨论：34826335

参考链接：

[1] ShardingSphere 读写分离方案：

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

[2] ShardingSphere：

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

[3] 接入 MSE 服务治理：

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

[4] 创建 RDS 只读实例：

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

[5] OpenSergo：

​ ​https://opensergo.io/zh-cn/​​