High-concurrency point queries

Background

Built on a column-oriented storage engine, SelectDB is well-suited for analytical queries on large datasets. However, high-concurrency online serving often requires retrieving an entire row by its primary key. This operation is known as a point query.

In such scenarios, traditional column-oriented storage engines face three major challenges:

• I/O amplification: In wide table scenarios, retrieving an entire row from columnar storage can cause high-volume random I/O, degrading query performance.

• Excessive execution overhead: For simple point queries, the traditional planning and execution path is too long and creates significant overhead.

• FE bottleneck: Under high concurrency, the FE, which serves as the access entry point, can consume substantial CPU resources when parsing and planning SQL statements, potentially becoming a bottleneck.

To address these issues, SelectDB provides a set of optimizations for point queries across cluster configuration, table schema design, and query execution. These optimizations help you build online query services that handle up to 100,000 concurrent requests.

Optimize cluster parameters

Cluster configuration

For high-concurrency, low-latency performance, optimize your cluster parameters. A pre-configured template is recommended, but you can also configure the parameters manually.

Method 1: Apply a configuration template (Recommended)

When creating an instance, select High Concurrency Scenarios in the Application Scenario section. For an existing instance, navigate to the Parameters page and apply the High Concurrency Scenarios template. This template automatically applies the configurations listed in the table below.

Method 2: Configure parameters manually

To configure BE parameters manually, refer to the following table:

Cluster global variables

In addition to cluster parameters, adjust cluster-level global variables. Connect to SelectDB with a MySQL client and run the following SQL commands:

Optimize the table structure

To enable short-circuit optimization for point queries, you must use the unique key model and enable row storage when creating the table.

The following example shows a CREATE TABLE statement for a typical high-concurrency point query scenario:

CREATE TABLE `tbl_point_query` (
    `k1` int(11) NULL,
    `v1` decimal(27, 9) NULL,
    `v2` varchar(30) NULL,
    `v3` varchar(30) NULL,
    `v4` date NULL,
    `v5` datetime NULL,
    `v6` float NULL,
    `v7` datev2 NULL
) ENGINE=OLAP
UNIQUE KEY(`k1`)
COMMENT 'OLAP'
DISTRIBUTED BY HASH(`k1`) BUCKETS 1
PROPERTIES (
    "enable_unique_key_merge_on_write" = "true",
    "store_row_column" = "true",
    "light_schema_change" = "true"
);

Key properties

• UNIQUE KEY(`k1`): Defines the table model as a unique key model and sets k1 as the unique key.

• "enable_unique_key_merge_on_write" = "true": Enables the Merge-on-Write mode for the unique key model, which is a prerequisite for enabling row storage.

• "store_row_column" = "true": Core optimization. When enabled, this property creates an additional copy of the data in row format. This allows a point query to read an entire row directly, avoiding the I/O amplification inherent in columnar storage and activating the short-circuit optimization.

• "light_schema_change" = "true": Recommended. The short-circuit optimization relies on the column unique ID provided by this feature to precisely locate columns.

Conditions for triggering short-circuit optimization

A query must meet all the following conditions to use the short-circuit path:

1. The query is a SELECT ... FROM ... WHERE ... statement that targets only a single table.

2. The WHERE clause must contain equality conditions for all columns of the UNIQUE KEY (for example, WHERE k1 = 123), and the conditions must be connected by AND. Range queries, OR conditions, or other complex conditions are not supported.

3. A query cannot contain JOIN, aggregation, or nested subqueries.

Cost consideration: Storage overhead and partial row storage

Enabling row storage ("store_row_column" = "true") consumes additional storage space. If your queries only need to return a subset of columns, you can use the "row_store_columns" property in the CREATE TABLE statement to specify which columns to store in row format. This helps save disk space. For example:

PROPERTIES (
    ...
    "row_store_columns" = "k1,v1,v2"
);

In this case, the short-circuit optimization is triggered only when the columns in the SELECT list (for example, SELECT k1, v1, v2 FROM ...) are a subset of the columns specified in row_store_columns.

Optimize the query

jdbc:mysql://127.0.0.1:9030/ycsb?useServerPrepStmts=true

// The PreparedStatement object should be reused, not created for each query.
PreparedStatement readStatement = conn.prepareStatement("SELECT * FROM tbl_point_query WHERE k1 = ?");

// Execute query 1
readStatement.setInt(1, 1234);
ResultSet resultSet1 = readStatement.executeQuery();

// Execute query 2
readStatement.setInt(1, 1235);
ResultSet resultSet2 = readStatement.executeQuery();

Verify and troubleshoot

Verify short-circuit optimization

Run the EXPLAIN command to check whether the short-circuit optimization is active in a query's execution plan.

mysql> EXPLAIN SELECT * FROM tbl_point_query WHERE k1 = 123;
+----------------------------------------------------------+
| Explain String                                           |
+----------------------------------------------------------+
| ...                                                      |
|   0:VOlapScanNode                                        |
|      TABLE: test.tbl_point_query(tbl_point_query)        |
|      PREDICATES: `k1` = 123                              |
|      ...                                                 |
|      SHORT-CIRCUIT                                       |
+----------------------------------------------------------+

If the execution plan contains the SHORT-CIRCUIT keyword, the short-circuit optimization has been successfully enabled.

Verify PreparedStatement

After you enable enable_prepared_stmt_audit_log, you can check the FE's audit log (fe.audit.log). If the log contains a Stmt=EXECUTE field as shown below, PreparedStatement is active.

... |State=EOF| ... |Time(ms)=2| ... |Stmt=EXECUTE ...

The Stmt=EXECUTE field in the log indicates that the query was executed through the prepared statement interface, successfully bypassing the SQL parsing process.

Performance troubleshooting

Under an ideal configuration (for example, a cluster with 96-core nodes), SelectDB can achieve 100,000 QPS with an average response latency within 5 ms. If your stress test results fall short, troubleshoot as follows:

• Verify that all optimizations are active: Check that all cluster parameters, table schema properties, and query optimizations mentioned in this document are correctly configured and have taken effect.

• Identify client-side bottlenecks: Check for resource bottlenecks on the stress testing machine, such as CPU, memory, or network reaching their limits. Try increasing the test concurrency and observe whether the QPS increases accordingly.

• Identify SelectDB cluster bottlenecks: Check whether the CPU utilization of the FE and BE nodes is excessively high or maxed out. Verify that the cache hit rate on the BEs is close to 100%.

FAQ