ApsaraDB RDS:HTAP-based automatic query routing

Introduction

The HTAP-based query routing solution for RDS for MySQL leverages the distinct advantages of different storage engines. A database proxy automatically directs requests to the most suitable instance type based on the estimated execution cost of SQL queries, achieving workload isolation and performance optimization.

• Row-oriented instances: The primary RDS for MySQL instance and regular read-only instances use the InnoDB engine, which is ideal for handling high-concurrency OLTP read and write requests.

• Columnstore instances: DuckDB-based analytical read-only instances use the DuckDB engine, which is designed for complex OLAP analytical queries.

Two routing methods are supported:

• HTAP-based automatic query routing: Automatically routes OLAP and OLTP queries to columnstore or row-oriented instances based on the estimated SQL execution cost.

• Manual routing by using hints: If automatic routing does not meet your expectations or your MySQL version does not support cost estimation, you can use hints to manually route SQL queries.

Usage notes

Before using HTAP-based automatic query routing, make sure that:

• Your RDS instance runs MySQL 8.0 with a minor engine version of 20250731 or later, and has a DuckDB-based analytical read-only instance is created.

• The database proxy feature is enabled, and the proxy version is 2.25.8 or later.

Precautions

• The latency threshold and consistency level of the database proxy also apply to the DuckDB-based read-only instances.

• When you enable HTAP-based automatic query routing for a primary instance, theRead/Write Splitting Attribute of the instance's proxy endpoint must be set to Read/Write.

• The read weights of DuckDB-based analytical read-only instances affect only the load balancing among these instances and do not influence the routing logic between row-oriented and columnstore instances.

• If all DuckDB-based analytical read-only instances are at full capacity, subsequent analytical requests are queued for processing.

• If you select Custom for Read Weight Allocation, the read weight of new DuckDB-based analytical read-only instance is set to 0 by default and must be configured manually.

Enable HTAP-based automatic query routing

1. Log on to the RDS console. In the left-side navigation pane, click Instances. In the top navigation bar, select the region where your primary RDS instance resides.

2. On the Instances page, click the ID of the primary RDS instance (with a icon).

3. In the left-side navigation pane, click Database Proxy.

4. In the Connection Information section, find the proxy endpoint and click Modify Configuration in the Actions column.

5. In the panel that appears, enable Auto HTAP-based Routing and click OK.

Allocate read weights to DuckDB-based analytical read-only instances

If your RDS for MySQL instance is configured with multiple DuckDB-based analytical read-only instances, you can set a read weight for each of these instances as needed. By default, the read weight for a proxy endpoint is set to System-assigned. A higher read weight indicates that the instance processes more read requests.

1. Log on to the RDS console. In the left-side navigation pane, click Instances. In the top navigation bar, select the region where your primary RDS instance resides.

2. On the Instances page, click the ID of the primary RDS instance (with a icon).

3. In the left-side navigation pane, click Database Proxy.

4. In the Connection Information section, find the proxy endpoint and click Modify Configuration in the Actions column.

5. In the dialog box that appears, select Automatic Distribution for Read Weight Allocation.

   If you select Custom, the read weight of new DuckDB-based analytical read-only instance is set to 0 by default and must be configured manually.

6. In the Read Weight for DuckDB-based Read-only Instance section, set the read weight for each DuckDB-based analytical read-only instance.

   For example, if a primary RDS instance has three DuckDB-based analytical read-only instances with read weights of 0, 100, and 200, respectively, only write requests are routed to the primary RDS instance. The three read-only instances handle read requests based on a 1:2 ratio.

   Note

   • The read weight can be an integer from 0 to 10,000.

   • If a DuckDB-based analytical read-only instance is deleted, its weight is automatically removed, and the weights of the other instances remain unchanged.

   • Modifications to this parameter take effect immediately without causing service interruptions. Both new and existing connections are routed based on the new weights.

Manually route queries by using hints

If automatic routing is not effective or your MySQL version does not support cost estimation, you can add a hint to your SQL statement to force a specific query route for the query.

• Route a query to the DuckDB-based analytical read-only node.

  /*force_ap_nodes*/ SELECT COUNT(*) FROM <Table_name>;

• Route a query to the primary RDS instance or regular read-only instance.

  SELECT /*+set_var(rds_cost_threshold_for_duckdb = 0)*/ COUNT(*) FROM <Table_name>;

Reference

• Create a DuckDB-based analytical instance

• What are database proxies?

• Related APIs:

  • DescribeDBProxy: queries the details of a database proxy.

  • DescribeDBProxyEndpoint: queries the information of a proxy endpoint.

  • ModifyDBProxyEndpoint: configures the access policy of a proxy endpoint.

Appendix: How HTAP-based automatic query routing works

1. The database proxy receives a query and forwards it to a row-oriented instance, such as the primary RDS instance or a read-only instance.

2. The optimizer of the row-oriented instance analyzes the query and estimates whether its execution cost exceeds a predefined threshold.

   • If the estimated cost is below the threshold, the query is executed on a row-oriented instance or between multiple regular read-only instances based on load balancing.

   • If the estimated cost is above the threshold, the query is routed to a DuckDB-based analytical read-only instance or between multiple DuckDB-based analytical read-only instances based on load balancing.