Load balancing

Load balancing policies

• Connections-based Load Balancing: This policy is ideal for high-performance scenarios that do not require advanced features such as consistency levels or transaction splitting.

• Active Request-based Load Balancing: Read requests are automatically routed among multiple read-only nodes within a cluster endpoint based on the number of active requests. This policy supports advanced features such as consistency levels, transaction splitting, and connection persistence, and it balances load based on the real-time status of each node.

The following table compares the two policies.

Primary node accepts read requests

If you set Primary Node Accepts Read Requests to No, PolarProxy no longer sends regular read requests to the primary node. However, read requests within a transaction that require consistency are still sent to the primary node to meet business requirements. Additionally, if all read-only nodes fail, read requests are also sent to the primary node. If your business has low consistency requirements, you can set the consistency level to eventual consistency to reduce read requests to the primary node. You can also use the transaction splitting feature to reduce read requests that are sent to the primary node before the actual transaction starts. Broadcast requests, such as SET or PREPARE, are still sent to the primary node.

Transaction splitting

When you use a PolarDB cluster endpoint in Read/Write (Automatic Read/Write Splitting) mode, PolarProxy distributes read and write requests to the primary node and read-only nodes. To ensure transactional consistency within a session, PolarProxy sends all in-transaction requests to the primary node. For example, some database client drivers, such as JDBC, wrap requests in transactions by default. As a result, all application requests are sent to the primary node, which can lead to high load on the primary node while read-only nodes are underutilized, as shown in the following figure:

To address this issue, PolarDB provides the transaction splitting feature at the Read Committed isolation level. This feature sends read requests within a transaction to read-only nodes to reduce the load on the primary node, while ensuring read/write consistency for your services. You can offload read pressure from the primary node to read-only nodes and improve the stability of the primary node without changing your application code or configurations. For detailed instructions on how to enable transaction splitting, see Configure PolarProxy.

PolarDB for MySQL provides two levels of transaction splitting: Read request splitting before first write request (default, the original transaction splitting feature) and Full transaction splitting (read request splitting before and after first write request).

• Read request splitting before first write request

  PolarProxy sends read requests that precede the first write request in a transaction to read-only nodes, which reduces the load on the primary node.

• Full transaction splitting (read request splitting before and after first write request)

  With read request splitting before the first write request, read requests that occur after a write are still routed to the primary node, which can result in an imbalanced load. To fully address the load balancing issues caused by transactions, PolarDB for MySQL introduces full transaction splitting. This feature allows all read operations within a transaction to be routed to read-only nodes while ensuring correct results, further reducing the pressure on the primary node.

  A read-after-write request can be routed to a read-only node only if the data from the preceding write operation in the transaction has been synchronized to that node. If you have configured session consistency, PolarProxy first checks if the read-only node for the current session has synchronized the previous writes before routing the read-after-write request. If it has, the request is routed to the read-only node; otherwise, it is routed to the primary node. Similarly, if you have configured global consistency, PolarProxy checks if the transactions from all current sessions have been synchronized to the read-only node. If they have, the request is routed there; otherwise, it is routed to the primary node. Full transaction splitting does not support eventual consistency.

  Versions and limitations

  To use the full transaction splitting feature, your PolarDB for MySQL cluster must meet the following requirements:

  • Engine version:

    • PolarDB for MySQL 5.6, revision 5.6.1.0.29 or later.

    • PolarDB for MySQL 5.7, revision 5.7.1.0.9 or later.

    • PolarDB for MySQL 8.0.1, revision 8.0.1.1.18 or later.

    • PolarDB for MySQL 8.0.2, any revision.

  • Engine parameter:

    The loose_query_cache_type parameter must be set to OFF. PolarDB for MySQL 5.6, 5.7, and 8.0.1 use OFF by default. Version 8.0.2 uses ON by default. Changing this parameter requires a restart of the PolarDB cluster.

  Note

  • Transaction splitting is supported only for sessions at the Read Committed isolation level and is enabled by default.

  • Due to read/write consistency constraints, read requests are not routed to a read-only node if its consistency level does not meet the requirements.

  • If your PolarProxy version is earlier than 2.4.14, only read request splitting before the first write request is supported. Full transaction splitting is not supported.

  • If your PolarProxy version is 2.4.14 or later and transaction splitting is configured for full transaction splitting, you must re-establish persistent connections for the change to take effect. For short-lived connections, the change takes effect immediately.

• Disable transaction splitting

  When transaction splitting is disabled, all requests within a transaction are routed to the primary node.

Weight-based load balancing

By default, the PolarDB for MySQL PolarProxy routes requests to the node with the fewest active (concurrent) requests. This policy generally balances traffic across backend nodes based on their load. It also performs well when backend nodes have different specifications. However, production workloads and traffic distribution requirements vary.

To better meet these needs, PolarDB for MySQL introduces weight-based load balancing. You can configure different weights for each node. During the routing process, both the weight and the number of concurrent requests are used as criteria to dynamically adjust the final routing decision. Currently, weights can be configured at the following two levels:

• Global dimension

  This configuration applies to all endpoints.

• Endpoint dimension

  The weight at the endpoint dimension applies only to the load balancing of that endpoint and overrides the global setting. For example, if you first configure a weight at the global dimension and then configure a separate weight for a specific endpoint, the load balancing for that endpoint will be based on the endpoint-level configuration.

On-demand connections