PolarDB:Load balancing

Load balancing policies

Primary Node Accepts Read Requests

After you set Primary Node Accepts Read Requests to No, common read requests are no longer forwarded to the primary node. In a transaction, read requests that require high consistency are still forwarded to the primary node to meet business requirements. If all read-only nodes fail, read requests are forwarded to the primary node. If your workloads do not require high consistency, you can set the consistency level to eventual consistency to reduce the number of read requests that are forwarded to the primary node. You can also use the transaction splitting feature to reduce the number of read requests that are forwarded to the primary node before a transaction is started. However, broadcast requests such as SET and PREPARE requests are forwarded to the primary node.

Transaction splitting

If the cluster endpoint that is used to connect to the PolarDB cluster is in read/write mode, PolarProxy forwards read and write requests to the primary node and read-only nodes. To ensure data consistency among transactions within a session, PolarProxy sends all requests in transactions of the session to the primary node. For example, database client drivers such as the Java Database Connectivity (JDBC) encapsulate requests in a transaction. In this case, all requests from applications are sent to the primary node. This results in heavy loads on the primary node. However, no requests are sent to read-only nodes. The following figure shows the process.

To fix this issue, PolarDB provides the transaction splitting feature in the sessions that are at the Read Committed isolation level. This feature ensures data consistency in a session and allows PolarDB to send read requests to read-only nodes to reduce the loads on the primary node. You can reduce the read loads on the primary node without the need to modify the code or configuration of your application. This way, the stability of the primary node is improved. For more information about how to enable transaction splitting, see Configure PolarProxy.

PolarDB for MySQL allows you to split transactions at two levels: read request splitting before first write request (which is selected by default and is 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 in a transaction before the first write request to read-only nodes. This reduces loads on the primary node.

  

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

  When read request splitting before first write request is used, read requests in a transaction after the first write request are still routed to the primary node. This still causes unbalanced loads. To resolve unbalanced loads, PolarDB for MySQL provides the full transaction splitting feature. This allows all read requests in a transaction to be routed to read-only nodes and returns correct results. This feature further relieves the pressure on the primary node.

  

  All previous write operations must be synchronized to a read-only node before split read requests after first write request can be routed to a read-only node. If session consistency is selected, the system checks whether all previous write operations are synchronized to the read-only nodes in the current session before routing split read requests after first write request. If so, split read requests after first write request can be routed to the read-only nodes in the current session. Otherwise, split read requests after first write request are routed to the primary node. Similarly, if global consistency is selected, the system checks whether all previous write operations are synchronized to the read-only nodes in all sessions before routing split read requests after first write request. If so, split read requests after first write request can be routed to the read-only nodes. Otherwise, split read requests after first write request are routed to the primary node. Full transaction splitting cannot be enabled if eventual consistency is selected.

  Supported versions and limits

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

  • A PolarDB for MySQL 5.6 cluster whose revision version is 5.6.1.0.29 or later.

  • A PolarDB for MySQL 5.7 cluster whose revision version is 5.7.1.0.9 or later.

  • A PolarDB for MySQL 8.0.1 cluster whose revision version is 8.0.1.1.18 or later.

  • A PolarDB for MySQL 8.0.2 cluster.

  • The engine parameter loose_query_cache_type must be set to OFF. The default value of this parameter is OFF for PolarDB for MySQL 5.6, 5.7, and 8.0.1 clusters, and is ON for PolarDB for MySQL 8.0.2 clusters. If you modify this parameter, you must restart PolarDB clusters.

• Disable transaction splitting

  After you disable transaction splitting, all requests in a transaction are routed to the primary node.

Weight-based load balancing

By default, PolarDB for MySQL PolarProxy selects the node that has minimum concurrent requests to route requests. This policy can basically route traffic to different backend nodes in a balanced manner. The load balancing results can be ensured even if backend nodes use different specifications. However, customers have varied business loads and different requirements for traffic distribution.

PolarDB for MySQL introduces the weight-based load balancing feature. You can configure different weights for nodes. Then, the weight and the number of concurrent requests are referenced for the final routing decision. You can only configure weights in the following two dimensions:

• Global dimension

  Your weight settings are valid for all endpoints.

• Endpoint dimension

  Your weight settings are valid only for the current endpoint and overwrite the weight settings in the global dimension. If you first configure the weights in the global dimension and then configure the weights for a specified endpoint, the weights for the endpoint are actual valid.

Usage notes

• To use this feature, PolarProxy must be 2.8.3 or later.

• Because both the current node load and custom weight are considered, the actual overall ratio may differ a bit from the specified ratio. However, the former will gradually move closer to the latter.

• A serverless cluster does not support weight configuration in the endpoint dimension.

How it works

The final weight of each node is dynamically adjusted based on the weight you specify and the number of concurrent requests on each node. A simplified formula can be used:

Dynamic weight = Custom weight/Number of concurrent requests

A higher value of the dynamic weight means that the node is more likely to be used to route requests. The weight-based load balancing policy provides a flexible routing method. In reality, business traffic gradually changes based on the weight you specify. More time is required compared with the pure weight-based polling method.

Procedure

Configure weights in the global dimension

1. Log on to the PolarDB console.

2. In the upper-left corner, select the region in which the cluster is deployed.

3. Find the cluster and click its ID.

4. In the Standard Enterprise Edition or Dedicated Enterprise Edition section of the Basic Information page, click Database Proxy Settings.

5. In the Database Proxy Settings dialog box, configure a weight for each node based on your business requirements.

   

6. Click OK.

Configure weights in the endpoint-dimension

1. Log on to the PolarDB console.

2. In the upper-left corner, select the region in which the cluster is deployed.

3. Find the cluster and click its ID.

4. In the Standard Enterprise Edition or Dedicated Enterprise Edition section of the Basic Information page, click Configure in the upper-right corner of the cluster endpoint or custom endpoint.

5. In the Node Settings section of the Modify Endpoint Settings dialog box, select Configure Node Weight and set a weight for the node.

   

6. Click OK.

Test data

The following figure shows the actual test data after weights are configured for the nodes.

If the weight ratio for the three node is 1:2:3 (1 for the primary node), expected test results can be obtained. Note that the sysbench oltp_read_only test set is used.

On-demand connections

Background information

For endpoints used in active request-based load balancing, the full connection method is used by default. After a client initiates a session to PolarProxy, PolarProxy sets up a session (connection) to every node in the endpoint. A 1:N correspondence is formed. Read requests from this client session are routed to other nodes depending on active loads of the current node. Broadcast requests such as SET statements are routed to all nodes. If a large number of nodes are created, establishing connection and broadcasting decreases the overall efficiency.

How it works

When the on-demand connection method is used, PolarProxy establishes connections to backend nodes on demand. As long as data consistency is ensured and the primary node can process loads, fewer connections to nodes are established to reduce overheads related to establishing connections and broadcasting. In most cases, a session is connected to one primary node and one read-only node (eventual consistency is provided only). In scenarios with a large number of short-lived connections or broadcast statements, this method can significantly improve performance.

In the preceding figure, if the PolarDB cluster contains only one primary node and three read-only nodes and data consistency is not considered, the following efficiency results concerning request routing and data reading in the three scenarios are obtained:

• Non-on-demand connections

  A session causes PolarProxy to establish connections to the four nodes, and broadcast statements are routed to the four nodes.

• On-demand connections for read-only sessions

  A session causes PolarProxy to establish a connection to one read-only node. Read-only requests (including broadcast requests) are routed only to this read-only node. This significantly improves data reading efficiency.

• On-demand connections for read/write sessions

  A session causes PolarProxy to establish a connection to one read-only node and a connection to one primary node. The broadcast request is routed to only the two nodes. This also significantly improves data reading efficiency.

Scenarios

• A large number of read-only nodes

• Short-lived connections

• A large number of broadcast statements (for example, in the PHP short-lived connection scenario, the first statement of a session is generally the SET NAMES utf8mb4 statement)

• Most queries using short PREPARE statements

Limits

• PolarProxy must be 2.8.34 or later. For more information about how to view PolarProxy version of a cluster, see Query the PolarProxy version.

• When you execute the SHOW PROCESSLISTS statement to view the number of connected nodes, not all connections may be displayed.

• When you execute KILL statement to terminate specified connections, not the specified connections to all nodes may be terminated.

Performance test

Test environment

• Nodes: one primary node and seven read-only nodes

• SQL statements used: SET NAMES utf8mb4 and SELECT 1

• Test tool: Sysbench. The same number of concurrent requests are sent each time.

• Test scenarios: the connection pool feature is disabled, the connection pool feature is enabled at the session level, and the connection pool feature is enabled at the transaction level. Each scenario is divided into two parts: The on-demand connection featured is disabled in the first part, and the on-demand connection feature is enabled in the second part.

Test results

• Performance test results when the connection pool feature is disabled:

  • The following figure shows changes in the CPU utilization of the nodes. After the on-demand connection feature is enabled, the CPU utilization of the nodes decreases by more than 60%.

    

  • The following figure shows changes in the total connections to the nodes. After the on-demand connection feature is enabled, the total connections to the nodes decrease by more than 80%.

    

  • The following figure shows changes in the overall QPS. After the on-demand connection feature is enabled, the overall QPS increases by 35%.

    

• Performance test results in the scenario where the connection pool is enabled at the session level:

  • The following figure shows changes in the CPU utilization of the nodes. After the on-demand connection feature is enabled, the CPU utilization of the nodes decreases by 50% to 60%.

    

  • The following figure shows changes in the total connections to the nodes. After the on-demand connection feature is enabled, the total connections to the nodes decrease by 60%.

    

  • The following figure shows changes in the overall QPS. After the on-demand connection feature is enabled, the overall QPS increases by 30%.

    

• Performance test results when the connection pool feature is enabled at the transaction level:

  • The following figure shows changes in the CPU utilization of the nodes. After the on-demand connection feature is enabled, the CPU utilization of the nodes decreases by 60%.

    

  • The following figure shows changes in the total connections to the nodes. After the on-demand connection feature is enabled, the total connections of the nodes decrease by 50%.

    

  • The following figure shows changes in the overall QPS. After the on-demand connection feature is enabled, the overall QPS increases by 260%.