This topic describes how to use the row-column routing mechanism to separate online transaction processing (OLTP) and online analytical processing (OLAP) traffic.
Overview
PolarDB-X provides the following three types of instances:
Primary instance: Processes transactional processing (TP) workloads. By default, this instance accesses row store data and provides a cluster endpoint. This endpoint supports read/write splitting for TP traffic and forwards analytical processing (AP) traffic to column store and regular read-only instances.
Read-only instance: Processes read traffic for TP workloads. By default, it accesses row store data and is physically isolated from the primary instance. It can provide 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: Processes read traffic for AP workloads. It only allows access to column store data and is physically isolated from the primary instance. It can provide a separate endpoint. This instance is often used for data analytics.
Data synchronization from the row store to the column store has a latency of several seconds. To prevent data inconsistency issues that could arise from this latency, the primary and read-only instances can access only row store data by default.
To route an SQL statement to a column store read-only instance for execution, you can add the following prefix to the SQL statement:
/*+TDDL: workload_type=ap enable_columnar_optimizer=true*/
Routing mechanism for a primary instance with a column store read-only instance
Hybrid workload scenario
If your OLAP and OLTP SQL queries must use the same database connection, you can use the cluster endpoint of the primary instance. The system automatically routes traffic based on the scan cost of SQL queries. OLAP traffic is routed to the column store read-only instance. OLTP traffic continues to be processed on the primary instance.
Automatic routing rules:
Read traffic with an estimated scan cost that exceeds 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 below 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) for scheduling and 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 open 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 Available Instances list and 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 your application requires strong data consistency between the primary instance and the column store read-only instance, do not use the automatic routing mechanism.
Separated workload scenario
If your OLAP and OLTP services use different applications to access the database, you can configure different endpoints for the applications. Connect the OLTP application to the cluster endpoint and the OLAP application to the endpoint of the column store read-only instance. This method separates traffic between the primary instance and the column store read-only instance.
Routing mechanism for a primary instance with a read-only instance
Transparent read/write splitting
PolarDB-X provides transparent read/write splitting with strong consistency. The main advantage of this solution is that you do not need to change your application code. You can 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 open 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 Available Instances list and move it to the Selected Instances list.
In the Row Store Read-only Settings section, configure the items as needed.
Configuration items:
Configuration item
Options
Description
Strongly Consistent 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 requests to the primary instance, you must set Fail Back Read-only Traffic To Primary Instance to Enable.
Weak Consistency
Requests routed to a read-only DN can only read the latest data available on that DN.
Enable MPP Query Acceleration
Enable/Disable
Based on statistics, estimates the cost and routes complex AP queries to the read-only instance (CN+DN) for MPP acceleration.
Read/Write Splitting
Percentage Of Traffic Routed To Read-only Instances
Routes a percentage of pushdown queries, which were originally sent to the primary instance DNs, to the read-only DNs.
Read-only Instance Latency Threshold (s)
If the latency of a read-only DN exceeds this threshold, the system automatically routes requests to another read-only DN with normal latency (if available) or to the primary instance. To route requests to the primary instance, you must set Fail Back Read-only Traffic To Primary Instance to Enable.
Fail Back Read-only Traffic To Primary Instance
When enabled, if the latency of a read-only DN exceeds the threshold, read traffic is failed back to the primary instance.
Click OK.
Manual read/write splitting
To implement read/write splitting at the application level, use different endpoints by configuring the primary instance endpoint for write operations and the read-only instance endpoint for read operations. The main feature of this solution is that read and write traffic are isolated from each other.
Routing mechanism for a single primary instance
If you have only a primary instance, your application can access only row store data by default.
FAQ
How do I use a column store to accelerate queries after I purchase a primary instance and a column store read-only instance?
A: You can use a column store read-only instance in one of two ways:
Connect to the endpoint of the column store read-only instance to directly access column store data. This method is suitable if you know which queries should run on the column store. For more information, see the Separated workload scenario section.
Connect to the cluster endpoint of the primary instance. The database then automatically routes complex queries to the column store read-only instance based on query cost. This method is better for hybrid workload scenarios because you do not need to manually classify query types. For more information, see the Hybrid workload scenario section.
Can the cost calculation based on statistics be inaccurate, causing queries not to be routed to the column store read-only instance as expected?
A: Yes, it can. Because statistics are collected periodically, 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 these SQL statements are always routed to the column store read-only instance for execution.
How do 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.
The query performance on a column store read-only instance is not significantly better than on the primary instance. How can I solve this?
A: Column store read-only instances typically run complex queries, which have high requirements for instance specifications. For better performance, consider purchasing a column store read-only instance with at least 32 CPU cores. These instances are highly cost-effective.