All Products
Search
Document Center

PolarDB:Read/write splitting

Last Updated:Feb 21, 2024

A PolarDB for MySQL cluster supports the read/write splitting feature. In read/write splitting mode, you need only to use a cluster endpoint in Read/Write (Automatic Read/Write Splitting) mode to send requests from applications to a PolarDB cluster. In this mode, write requests are automatically forwarded to the primary node. Read requests are automatically forwarded to the primary node or read-only nodes based on the load of each node. The load of a node is based on the number of pending requests.

Advantages

  • Read consistency

    When a client connects to a backend server by using a cluster endpoint, the built-in database proxy for read/write splitting automatically establishes connections between the primary node and read-only nodes. In a session, the built-in database proxy first selects an appropriate node based on the data synchronization progress of each database node. Then, the proxy forwards read and write requests to the nodes whose data is up-to-date and valid. This helps balance read and write requests among the nodes.

    A PolarDB for MySQL cluster

  • Native read/write splitting for enhanced performance

    You can create your own proxy in the cloud to implement read/write splitting. However, high latency may occur because data is parsed and forwarded by multiple components before the data is written to a database. PolarDB for MySQL uses a built-in proxy that is deployed in existing secure links to implement read/write splitting and ensure that data does not pass through multiple components. This reduces the latency and speeds up data processing.

  • Easy maintenance

    For traditional database services, read/write splitting is time-consuming. You must specify the endpoints of the primary node and each read-only node in applications. You also need to configure forwarding rules to send write requests to the primary node and read requests to read-only nodes.

    PolarDB for MySQL provides a cluster endpoint that can be used by an application to connect to the cluster. After the application is connected to the cluster, you can send read and write requests from the application to the cluster. Write requests are automatically forwarded to the primary node, and read requests are automatically forwarded to the primary node or read-only nodes. The process of read/write splitting is transparent to users. This reduces maintenance costs.

    You need only to add read-only nodes to scale the processing capabilities of your cluster. You do not need to modify your application.

  • Node health checks for enhanced database availability

    The read/write splitting module of PolarDB automatically performs health checks on all nodes in a cluster. If a node fails or the latency exceeds a specified threshold, PolarDB for MySQL stops sending read requests to this node. Write and read requests are sent to other healthy nodes. This ensures that applications can access the cluster even if a read-only node fails. After the node recovers, PolarDB for MySQL automatically adds the node to the list of nodes that are available to receive requests.

  • Free feature that reduces resource and maintenance costs

    The read/write splitting feature is available free of charge.

Rules for forwarding requests

Forwarding rules in read/write splitting mode:

  • The following requests are forwarded only to the primary node:

    • Requests for DML statements such as INSERT, UPDATE, DELETE, and SELECT FOR UPDATE

    • All data definition language (DDL) statements used to perform operations such as creating databases or tables, deleting databases or tables, and changing schemas or permissions.

    • All requests that are encapsulated in transactions.

    • Requests for user-defined functions

    • Requests for stored procedures

    • Requests for EXECUTE statements

    • Requests for multi-statements

    • Requests that involve temporary tables.

    • Requests for SELECT last_insert_id() statements

    • All requests to query or modify user environment variables

  • The following requests are forwarded to the primary node or read-only nodes:

    Note

    The requests are forwarded to the primary node only if Primary Node Accepts Read Requests is set to Yes.

    • Read requests that are not encapsulated in transactions

    • Requests for COM_STMT_EXECUTE statements

  • The following requests are forwarded to all nodes:

    • All requests to modify system environment variables

    • Requests for USE statements

    • Requests for COM_STMT_PREPARE statements

    • Requests that are sent to execute COM_CHANGE_USER, COM_QUIT, and COM_SET_OPTION statements.

    • Requests for SHOW PROCESSLIST statements

      Note

      After a SHOW PROCESSLIST statement is executed, PolarDB for MySQL returns all processes that are running on any nodes in your database system.

    • All requests for KILL statements in SQL (not KILL commands in Linux)

Forwarding rules in read-only mode:

  • DDL and DML operations are not supported.

  • Requests are forwarded to read-only nodes in load balancing mode.

  • Read requests are not forwarded to the primary node. Even if the primary node is added to the list of service nodes, read requests are still not forwarded to it.

Features

PolarDB for MySQL provides the following features for read/write splitting: