This topic describes how to use the row-column routing mechanism to split traffic between your transactional processing (TP) and analytical processing (AP) workloads.
Background information
PolarDB-X provides the following three types of instances:
Primary instance: Handles transactional processing (TP) traffic. It accesses row store data by default and provides a cluster endpoint. This endpoint supports read/write splitting for TP traffic and forwards analytical processing (AP) traffic to column-store read-only instances and standard read-only instances.
Read-only instance: Handles read traffic for TP workloads. It accesses row store data by default and is physically isolated from the primary instance, providing a separate endpoint. This instance is often used for application-level read/write splitting or extract, transform, and load (ETL) scenarios.
Column-store read-only instance: Handles read traffic for AP workloads. It allows access only to column store data and is physically isolated from the primary instance, providing a separate endpoint. This instance is often used in data analytics scenarios.
Data synchronization from the row store to the column store has a latency of several seconds. To prevent data inconsistency, primary and read-only instances can access only row store data by default.
You can add the following prefix to an SQL statement to route it to a column-store read-only instance for execution:
/*+TDDL: workload_type=ap enable_columnar_optimizer=true*/
Routing with a primary instance and a column-store read-only instance
Mixed workload scenario
If your AP and TP SQL statements must use the same connection to access the database, use the cluster endpoint of the primary instance. The system automatically routes traffic based on the scan cost of each SQL statement. AP traffic is routed to the column-store read-only instance, while TP traffic continues to run on the primary instance.
Automatic routing rules:
Read traffic with an estimated scan cost higher than the threshold is identified as AP traffic. The SQL statement is automatically routed to the column-store read-only instance for execution.
Read traffic with an estimated scan cost lower than the threshold, read traffic within transactions, and write traffic are identified as TP traffic. The SQL statement is automatically routed to the primary instance for execution.
If a primary instance has multiple column-store read-only instances, the system automatically uses Massively Parallel Processing (MPP) scheduling for execution.
To configure automatic routing:
Log on to the PolarDB-X console.
In the top navigation bar, select the region where the instance is deployed.
On the Instances page, click the PolarDB-X 2.0 tab.
Click the ID of the target primary instance to go to the Basic Information page.
In the Connection Information section, click Configuration Management.
In the Configuration Management dialog box, perform the following operations:
In the Resource Configuration section, select the target column-store read-only instance from the Optional instance list to move it to the Selected Instances list.
In the Read-only settings for column-oriented data section, click Enable, and then click OK.
Data synchronization between the column-store read-only instance and the primary instance has a latency of several seconds. If you require data consistency between these instances, do not use the automatic routing mechanism.
Workload separation scenario
If your AP and TP workloads access the database from different applications, you can configure different endpoints for each application. Connect the TP application to the cluster endpoint and the AP application to the endpoint of the column-store read-only instance. This separates traffic between the primary instance and the column-store read-only instance.
Routing with a primary instance and a read-only instance
Transparent read/write splitting
PolarDB-X provides a transparent read/write splitting feature with strong consistency. The main advantage of this solution is that you do not need to modify your application code. Simply connect to the cluster endpoint of the primary instance. The system automatically routes a configurable percentage of read traffic to the read-only instance.
To configure transparent read/write splitting:
Log on to the PolarDB-X console.
In the top navigation bar, select the region where the instance is deployed.
On the Instances page, click the PolarDB-X 2.0 tab.
Click the ID of the target primary instance to go to the Basic Information page.
In the Connection Information section, click Configuration Management.
In the Configuration Management dialog box, perform the following operations:
In the Resource Configuration section, select the target row-store read-only instance from the Optional instance list to move it to the Selected Instances list.
In the Read-only settings for row-oriented data section, configure the settings as needed.
Configuration item descriptions:
Configuration item
Option
Description
Strong consistency read
Strong Consistency
Requests routed to a read-only DN can read the latest data. If the latency of a read-only DN exceeds the threshold, the system automatically routes requests to another read-only DN with normal latency, if available, or to the primary instance. To route to the primary instance, you must set the Read-only Traffic Switched Back to Primary Instance option to Enable.
Weak Consistency
Requests routed to a read-only DN can only read the latest data available on that DN.
Enable MPP
Enable/Disable
Uses statistics to estimate costs and routes complex AP queries to the read-only instance (CN+DN) for MPP acceleration.
Read/Write Splitting
Percentage of queries routed to the read-only instance
Routes a percentage of pushdown queries, which would otherwise go to the primary instance DN, to the read-only DNs.
Latency Threshold for Read-Only Instances (s)
If the latency of a read-only DN exceeds the threshold, the system automatically routes requests to another read-only DN with normal latency or to the primary instance. To route to the primary instance, you must set the Read-only Traffic Switched Back to Primary Instance option to Enable.
Read-only Traffic Switched Back to Primary Instance
When enabled, read traffic fails over to the primary instance if the latency of the read-only DN exceeds the threshold.
Click OK.
Manual read/write splitting
If you want to implement read/write splitting at the application level, use different endpoints. Configure write operations to use the primary instance endpoint and read operations to use the read-only instance endpoint. The main feature of this solution is that read and write traffic are isolated and do not affect each other.
Routing with a single primary instance
If you have only a primary instance, applications can access only row store data by default.
FAQ
After purchasing a primary instance and a column-store read-only instance, how do I use the column store to accelerate queries?
A: There are two ways to use a column-store read-only instance:
Connect to the endpoint of the column-store read-only instance to directly access column store data. This method is suitable when you know which queries should run on the column store. For more information, see Workload separation scenario.
Connect to the endpoint of the primary instance. In this case, the database automatically routes complex queries to the column-store read-only instance based on query cost. This method is better for mixed workload scenarios because you do not need to manually distinguish query types. For more information, see Mixed workload scenario.
Can cost-based calculations that use statistics lead to errors, causing a query to not be routed to the column-store read-only instance as expected?
A: Yes, they can. Because statistics are collected at regular intervals, they can become outdated. This can lead to inaccurate scan cost calculations and unexpected query routing. For critical complex queries, you can bind a column-store HINT to the SQL statement. This ensures that the statement is always routed to the column-store read-only instance.
How can I bind a column-store HINT to a specific SQL statement without changing the application code?
A: PolarDB-X provides the execution plan management feature. You can use this feature to bind a routing rule to a specific SQL statement. The database will then automatically route this type of SQL statement to the column-store read-only instance.
A query runs on a column-store read-only instance, but its performance is not significantly better than when it runs on the primary instance. How can I improve its performance?
A: Column-store read-only instances are designed to run complex queries, which have high requirements for instance specifications. For better performance, we recommend purchasing a column-store read-only instance with at least 32 cores. These instances provide a good price-to-performance ratio.