How to Implement Non‑Intrusive Read‑Write Splitting for Databases with MSE

Background

In distributed systems each request passes through multiple layers such as gateways, web servers, service calls, caches, and databases. Because the database is critical for both stability and development efficiency, governance capabilities are essential.

What is Read‑Write Splitting?

Read‑write splitting separates the primary (write) database from one or more replicas (read) so that write operations go to the primary and read‑only queries are routed to replicas.

Why Use Read‑Write Splitting?

Stability : heavy read traffic can saturate the primary’s CPU, causing outages.

Performance : offloading reads to replicas improves response time for read‑heavy workloads.

Scalability : easier horizontal scaling compared with vertical upgrades or sharding, and it introduces minimal intrusion.

Common Expansion Methods

Vertical upgrade

Sharding (partitioning)

Read‑write splitting

Read‑write splitting has the lowest intrusion and is the easiest to implement among these options.

Industry Solutions

Products such as Mycat, ODP, and ShardingSphere provide proxy‑based read‑write splitting. ShardingSphere relies on a parsing engine and a routing engine: the parser converts SQL to an internal statement, and the router directs read or write statements to the appropriate instance.

MSE Dynamic Read‑Write Splitting

MSE offers a non‑intrusive way to enable read‑write splitting for MySQL without modifying application code.

Prerequisites

Application is already connected to MSE.

Deploy a demo application.

Deploy three services (A, B, C) in Alibaba Cloud Container Service and attach them to MSE.

Create an RDS read‑only instance.

Configure Read‑Write Rules

Set environment variables or use an OpenSergo CRD to mark certain APIs as weak‑read. Example rule:

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

Requests to /getLocation are treated as weak‑read and will be routed to a read‑only replica.

SQL Insight

After enabling read‑write splitting, MSE provides real‑time SQL metrics, a top‑N list of slow queries, and per‑API latency, helping locate high‑RT queries that stress the primary.

Conclusion

The article describes MSE’s upcoming database governance features, especially dynamic read‑write splitting, and shows how combining it with SQL insight can reduce primary load, improve stability, and scale read‑heavy microservice applications.