PolarDB:Configure read/write splitting for PolarDB-X

Limitations

Read/write splitting is supported only on Enterprise Edition instances of PolarDB-X.

Background information

If you purchase only a primary instance, you receive a single connection endpoint called a cluster endpoint. This endpoint is primarily used for general-purpose online business scenarios. If you add read-only instances to your primary instance, a read-only routing endpoint is provided for each read-only instance. This endpoint is designed for resource-isolated scenarios, such as offline data exports and batch processing, to ensure that read-only resources are used exclusively. In HTAP scenarios where you have both a primary instance and read-only instances, you can use both the cluster endpoint and read-only routing endpoints. You can enable the read/write splitting feature by configuring the settings of the cluster endpoint.

Configure cluster endpoint and read/write splitting

1. Log on to the PolarDB-X console.

2. In the top navigation bar, select the region where the instance is deployed.

3. On the Instances page, click the PolarDB-X 2.0 tab.

4. Find the instance that you want to manage and click its ID.

5. In the Connection Information section, click Configuration Management to view the configuration settings for the cluster endpoint.

   The following table describes the parameters.

   Parameter	Description
   Resource configuration	You can add read-only instances to the resource pool of the primary instance to offload read traffic. Instances not added to the resource pool do not handle read traffic from the primary instance.
   Auto-add read-only instances	Controls whether new read-only instances are automatically added to the primary instance's resource pool. This feature is enabled by default.
   Intelligent read/write splitting	This feature is enabled by default. It automatically routes AP traffic to multiple read-only instances and uses Massively Parallel Processing (MPP) for acceleration. For more information, see read/write splitting.
   Read-only traffic percentage	Routes a specified percentage of read queries from the primary instance to its read-only instances.
   Data read consistency	Strong consistency: Ensures that read requests on a read-only instance access data committed on the primary instance before the request was initiated. Weak consistency: Requests routed to a read-only instance access the latest data available on that instance. This may result in reading stale data due to replication latency between the primary and read-only instances. Note If replication latency exists, a strong consistency read request waits for the updated data to become available. The default wait timeout is 3 seconds.
   Read-only instance latency threshold	If the replication latency of a read-only instance exceeds this threshold, its read traffic is routed to other healthy read-only instances or, if none are available, to the primary instance.
   Failover of read-only traffic to primary instance	If the replication latency of all read-only instances exceeds the threshold or due to an HA event, all read-only instances are considered unavailable. This setting determines whether to fail over their read traffic to the primary instance. We recommend using two or more read-only instances to improve availability.

   Note

   • After you purchase read-only instances, they are automatically added to the resource pool of the primary instance, and intelligent read/write splitting is enabled by default. However, TP read traffic is not routed to the read-only instances until you explicitly set a value for the read-only traffic percentage.

   • The default wait timeout for strong consistency reads is 3 seconds. You can adjust this timeout by modifying the loose_replica_read_timeout parameter on the Parameter Settings page.

   • The read/write splitting feature requires PolarDB-X version 5.4.13-16415631 or later.

Configure read-only routing endpoint

1. Log on to the PolarDB-X console.

2. In the top navigation bar, select the region where the instance is deployed.

3. On the Instances page, click the PolarDB-X 2.0 tab.

4. Find the instance that you want to manage and click its ID.

5. In the Connection Information section, click Configuration Management to view the configuration settings for the read-only routing endpoint.

   Note

   Settings:

   • Strong consistency: Ensures that queries sent through the read-only routing endpoint access the most recent data committed on the primary instance. If replication latency is high, queries will wait.

   • Weak consistency: Allows queries sent through the read-only routing endpoint to access the latest data available on that read-only instance. With replication latency, queries return the available data immediately without waiting.

FAQ

• Q: I purchased a read-only instance. Why is there no traffic on its data nodes (DNs)?

  A: Your read traffic might be TP read traffic. After purchasing a read-only instance, you must configure the read-only traffic percentage in the cluster endpoint settings.

• Q: I purchased a read-only instance and set the read-only traffic percentage to 0. Why is the instance still receiving traffic?

  A: The traffic is likely AP traffic. After you purchase a read-only instance, the intelligent read/write splitting feature is enabled by default, which automatically routes AP traffic to read-only instances. You can disable intelligent read/write splitting to stop this routing.

• Q: I purchased a read-only instance and configured the read-only traffic percentage. Why is there no traffic on its data nodes (DNs)?

  A: The primary instance routes traffic only to read-only instances in its resource pool. Your read-only instance may have been removed from the resource pool.

• Q: Can I implement read/write splitting in my application by using different connection strings instead of using the transparent read/write splitting feature of PolarDB-X?

  A: Yes. If you do not want to use the read/write splitting feature of PolarDB-X, you can remove the read-only instance from the primary instance's resource pool.

• Q: Is it mandatory to enable strong consistency for reads?

  A: If your application does not require a strong consistency read, disable this feature for better performance. Otherwise, you must enable the strong consistency read feature. If this feature is enabled, high replication latency, such as during a DDL operation, can cause query timeouts.

• Q: How do I disable strong consistency for reads?

  A: If your application connects to the primary instance, set Data Read Consistency to Weak Consistency in the cluster endpoint settings. If your application connects to a read-only instance, select Weak Consistency in the read-only routing endpoint settings.

• Q: When using the read/write splitting feature of PolarDB-X, I frequently encounter query timeouts. What should I do?

  A: If strong consistency is enabled, query timeouts are likely caused by replication latency. Common causes for this latency include:

  • DDL operations or large-scale data imports on the primary instance.

  • The read-only instance's specifications are significantly lower than the primary instance's.