Automatically improves the query performance of slow SQL statements - PolarDB

PolarDB for MySQL columnstore index (IMCI) provides automatic IMCI-based query acceleration, which improves the query performance of slow SQL statements without manual intervention.

Overview

PolarDB for MySQL columnstore index (IMCI) technology is designed for complex queries over large data volumes in OLAP scenarios. With columnstore indexes, PolarDB for MySQLintegrates real-time transaction processing and real-time data analytics as a one-stop hybrid transactional and analytical processing (HTAP) database solution.

Traditionally, to accelerate the tables involved in slow queries, you must manually use CREATE TABLE or ALTER TABLE to add COLUMNAR=1 to the COMMENT field of the table to create a columnstore index. This manual approach is inefficient when many SQL templates have complex logic.

Automatic IMCI-based query acceleration (AutoIndex) automatically adds columnstore indexes to the target tables based on slow queries, which accelerates the queries without manual intervention.

Technical background

AutoIndex is a technique that tunes SQL performance by automatically adding indexes. Row store indexes have been extensively researched and commercially implemented, for example, in Oracle and SQL Server. These systems analyze the secondary indexes that may need to be added based on predicates, ORDER BY clauses, or joins. They recalculate the query cost assuming that such an index is created, and then create the indexes that significantly reduce the query cost.

In the columnstore index field, AutoIndex has been adopted by systems such as Redshift, Snowflake, Databricks, and Heatwave. Typical automation methods include:

Automatic Load/Unload: Automatically creates columnstore indexes and accelerates slow queries based on query execution. Automatically removes columnstore indexes that are infrequently used or consume excessive resources to reduce storage consumption.
Automatic Encode: Automatically selects the optimal encoding algorithm based on the data distribution of each column to further compress storage.
Automatic selection of Distribute Key: Balances data distribution in MPP scenarios, reduces data skew, and optimizes data shuffling for distributed GROUP BY and JOIN operations.
Automatic selection of Sort Key: Uses frequently used predicates, join columns, or sort columns to accelerate query filtering or sorting.

In IMCI, automatically creating a columnstore index costs less and is more fault-tolerant than creating a row store index, for the following reasons:

Lower impact on writes: Too many row store indexes significantly affect write performance on the primary node, whereas IMCI does not materialize columnstore data on the primary node. After a columnstore index is added, write operations need only be synchronized to the read-only columnstore index node through Redo, with almost no impact on write performance.
Higher compression ratio: Columnstore indexes generally achieve a compression ratio of 3x to 5x, so the storage overhead of new indexes is relatively low.
Limited impact on business operations: Lock-free, efficient data structures collect SQL Trace information, and Nonblock DDL (nonblocking DDL) reduces disruption to business operations.

How it works

Slow query tracking and DDL statement generation

Each cluster node uses SQL Trace to collect slow queries and automatically generates DDL statements to create columnstore indexes for qualifying SQL statements. The process is as follows:

SQL Trace: Uses SQL templates as keys and the official MySQL lock-free hash table to track and record SQL execution information. This design suits high-concurrency scenarios with large numbers of SQL templates. In Sysbench test scenarios, SQL Trace affects database performance by no more than 3%.
Trigger condition: When the number of rows scanned by a slow query reaches the configured threshold (100,000 rows by default), AutoIndex generates a DDL statement to add a columnstore index to the corresponding table.
Low-overhead design: To avoid repeatedly parsing SQL statements, opening tables, and obtaining MDL locks, AutoIndex directly uses the cached table list of the THD (Thread Handler), which adds almost no extra system overhead.

You can connect directly to each node and query the information_schema.imci_autoindex system table to view information about the tracked slow queries for which DDL statements have been generated. Example:

SELECT * FROM information_schema.imci_autoindex

Combine this table with information_schema.sql_sharing to obtain more detailed SQL execution information:

SELECT *
FROM information_schema.imci_autoindex autoindex, information_schema.sql_sharing share
WHERE autoindex.sql_id = share.sql_id
  AND share.type = 'SQL'

Slow query collection, aggregation, and DDL execution

The read-only columnstore index node in the cluster collects and aggregates slow queries with columnstore index recommendations from all nodes. If multiple read-only columnstore index nodes exist, one of them acts as the leader. This node sorts the recommendations by execution time in descending order and executes the recommended DDL statements in sequence until the per-round DDL execution limit is reached.

Instant DDL
Adding a columnstore index on the primary node is an Instant DDL operation. It modifies only the data dictionary and makes no actual data changes in the primary database. The Redo is replicated to the read-only columnstore index node, which builds the columnstore index in the background.
Nonblock DDL (nonblocking DDL)
Standard DDL blocks new transactions while waiting to obtain an MDL-X lock. If Nonblock DDL fails to obtain the lock, it first allows new transactions to proceed and then continuously retries lock acquisition. In AutoIndex scenarios, DDL is not an urgent operation. If an operation fails in the current round, it can be retried in the next automatic scheduling round.
You can query the information_schema.imci_autoindex_executed system table from the cluster endpoint to view recently executed columnstore index recommendation statements and the associated slow queries (up to 128 records are retained).
Scheduling and execution limits
- By default, the interval between rounds is 1 minute, and a maximum of 5 DDL statements can be executed in each round.
- Columnstore indexes that are not covered in the current round are retried only after subsequent slow queries hit them again.
- Resource governance applies when the read-only columnstore index node builds columnstore indexes. Resource metrics increase, but node resources are not saturated.
- The next round is scheduled only after the columnstore indexes of the current round have been built on the read-only columnstore index node.

Columnstore index activation and usage

To maximize the effectiveness of AutoIndex, attach the read-only columnstore index node to the cluster endpoint and enable automatic request distribution between row store and column store nodes. During real service traffic playback, slow queries trigger AutoIndex to create columnstore indexes. After the read-only columnstore index node finishes building the indexes, the automatic request distribution mechanism routes the relevant queries to that node based on query cost, which accelerates the queries.

If service traffic connects directly to a regular read-only node, you can manually switch the traffic to the read-only columnstore index node after a columnstore index is created. For information about columnstore index status queries and request distribution policies, see IMCI FAQ.

Test results

The following example is based on the TPC-H 100 GB test set.

Note

The TPC-H implementation in this topic is based on TPC-H benchmarking. These test results cannot be compared with published TPC-H benchmark results because the tests do not fully meet all TPC-H requirements.

Environment setup: Run all 22 TPC-H test queries once every minute. Enable HTAP Optimization (Transactional/Analytical Processing Splitting) for the cluster endpoint, and set the row store degree of parallelism to 8, mainly to shorten the test time.
Test results: The following table shows that query performance improves substantially in later rounds. During the latter part of the first round, starting at approximately the 12th query, AutoIndex had created columnstore indexes for most tables, and the optimizer automatically routed these queries to the read-only columnstore index node.

Execution round	Total time (s)	Row store plans/columnstore plans	Performance gain
1	8293	10/12	-
2	118	1/21	Approx. 70x
3	110	0/22	Approx. 75x

Benefits

Columnstore indexes offer a range of benefits, with significant improvements in compatibility, performance, and cost:

100% MySQL compatibility: Supports all MySQL data types and is fully compatible with the MySQL protocol.
Excellent HTAP performance: Generally improves performance in analytical scenarios by one to two orders of magnitude.
Hybrid row and columnstore storage, lower cost: Ensures transactional consistency with hybrid row and columnstore storage, while columnstore indexes deliver better performance at a lower cost in some query scenarios.
Simple automatic acceleration: Enables automatic performance acceleration with one click, without complex configuration or tuning.
Reduced operations workload: Automatically creates columnstore indexes for slow queries, which reduces manual slow query optimization work.
Reduced IT costs: Optimizes only slow queries instead of creating columnstore indexes for all databases and tables, which saves memory and storage resources.

Supported versions

Enterprise Edition clusters must meet the following conditions:
- Series: Cluster Edition.
- Database engine version:
  - MySQL 8.0.1, and the minor engine version must be 8.0.1.1.45.2 or later.
  - MySQL 8.0.2, and the minor engine version must be 8.0.2.2.27 or later.
Standard Edition clusters must meet the following conditions:
- CPU architecture: x86.
- Database engine version: MySQL 8.0.1, and the minor engine version must be 8.0.1.1.45.2 or later.

Note

To query the cluster version, see Query the engine version.

Usage notes

Multi-master Cluster (Limitless) EditionSeries and Serverless clusters do not support automatic IMCI-based query acceleration.
After you enable automatic IMCI-based query acceleration:
- The fees for the added read-only columnstore index nodes follow the billing rules of regular compute nodes.
- The system automatically creates columnstore indexes based on your slow SQL statements (columnstore index data occupies storage space).
- The system uses the SQL Trace feature to record the execution history of slow SQL statements. In most cases, this consumes no more than 3% of database node performance.
Automatic IMCI-based query acceleration internally uses Nonblock DDL to add columnstore indexes. Nonblock DDL allows new transactions to access the target table even when an MDL-X lock cannot be obtained, which keeps the entire business system stable. For more information, see Nonblocking DDL statements.

Enable automatic IMCI-based query acceleration

Log in to the PolarDB console，In the navigation pane on the left, click Clusters. Select the Region where the cluster is deployed, and then click the cluster ID to go to the cluster details page。
On the Basic Information page, find the Automatic IMCI-based Query Acceleration field and click Enable.
The next steps differ depending on whether the current cluster has a read-only columnstore index node:
- If the current cluster already has a read-only columnstore index node, in the Enable Automatic IMCI-based Query Acceleration dialog box, click OK to enable automatic IMCI-based query acceleration.
- If the current cluster does not have a read-only columnstore index node, in the Enable Automatic IMCI-based Query Acceleration dialog box, click OK. You are redirected to the page for adding a read-only columnstore index node.
  Note
  - After you click OK, you can immediately Add a read-only IMCI node or manually Add a read-only IMCI node later.
  - After you enable automatic IMCI-based query acceleration, the current cluster must contain at least one read-only columnstore index node. Otherwise, even if Automatic IMCI-based Query Acceleration is set to Enable, the acceleration service is not provided.
  - After you enable automatic IMCI-based query acceleration, if you have not added a read-only columnstore index node, the system uses the SQL Trace feature to record the execution history of slow SQL statements but does not create columnstore indexes. Therefore, no acceleration service is provided.

Disable automatic IMCI-based query acceleration

Log in to the PolarDB console，In the navigation pane on the left, click Clusters. Select the Region where the cluster is deployed, and then click the cluster ID to go to the cluster details page。
On the Basic Information page, find the Automatic IMCI-based Query Acceleration field and click Close.
In the Disable Automatic IMCI-based Query Acceleration dialog box, click OK to disable automatic IMCI-based query acceleration.

Note

Disabling automatic IMCI-based query acceleration only disables the parameters related to the feature. The read-only columnstore index nodes and their data are retained. If you no longer need to keep the read-only columnstore index nodes or the columnstore indexes on them, you can delete the read-only columnstore index nodes in the console, or delete columnstore indexes by using SQL statements.