PolarDB for MySQL clusters provide the automatic in-memory column index (IMCI)-based query acceleration feature to help automatically accelerate slow SQL queries.
Benefits
Less manual tuning: The automatic IMCI-based query acceleration feature automatically creates an appropriate IMCI based on slow SQL queries to accelerate the execution of slow SQL queries, which eliminates the need to analyze each slow query.
Continuous optimization: The automatic IMCI-based query acceleration feature continuously monitors and adjusts the IMCI policies based on the application loads to ensure optimal database performance.
Version requirements
Database edition: Enterprise Edition.
Edition: Cluster Edition.
Database engine: 8.0.1 whose revision version is 8.0.1.1.45.2 or later.
Database edition: Standard Edition.
Database engine: 8.0.1 whose revision version is 8.0.1.1.45.2 or later.
For information about how to obtain the database engine version of a cluster, see Query the engine version.
Usage notes
Multi-master Cluster (Database/Table) Edition clusters do not support the automatic IMCI-based query acceleration feature.
Serverless clusters do not support the automatic IMCI-based query acceleration feature.
After you enable the automatic IMCI-based query acceleration feature for a cluster, the added read-only column store nodes are charged as common nodes. For more information about the billing rules, see Billing rules of compute nodes.
After you enable the automatic IMCI-based query acceleration feature, the system automatically creates IMCIs based on slow SQL queries. The IMCIs occupy a specific amount of storage space.
After you enable the automatic IMCI-based query acceleration feature, the system uses the SQL Trace feature to record the execution history of slow SQL queries. In most cases, this operation consumes no more than 3% of the resources of database nodes. For more information, see SQL Trace.
The automatic IMCI-based query acceleration feature uses nonblocking DDL to add IMCIs. The nonblocking DDL feature allows new transactions to access the destination table when the MDL-X lock is not acquired. This ensures the stability of the entire business system. For more information, see Nonblocking DDL.
Enable the automatic IMCI-based query acceleration feature
Log on to the PolarDB console.
In the left-side navigation pane, click Clusters.
In the upper-left corner, select the region in which the cluster is deployed.
On the Clusters page, find the cluster that you want to manage and then click the cluster ID.
On the Basic Information page, click Enable for the Automatic IMCI-based Query Acceleration option.
Proceed to the next step based on whether the cluster has read-only column store nodes.
If the cluster has read-only column store nodes, click OK in the Enable Automatic IMCI-based Query Acceleration dialog box.
If the cluster does not have read-only column store nodes, click OK in the Enable Automatic IMCI-based Query Acceleration dialog box. You are redirected to the page for adding read-only column stores.
NoteYou can immediately add a read-only column store node on the page for adding read-only column store nodes. You can also manually add read-only column store nodes later. For more information, see Add a read-only column store node.
The automatic IMCI-based query acceleration feature takes effect only if the cluster has a read-only column store node.
If you enable the automatic IMCI-based query acceleration feature on a cluster that does not have a read-only column store node, the system uses the SQL trace feature to record the execution history of slow SQL queries but does not create an IMCI to provide the acceleration service. For more information, see SQL Trace.
Disable the automatic IMCI-based query acceleration feature
Log on to the PolarDB console.
In the left-side navigation pane, click Clusters.
In the upper-left corner, select the region in which the cluster is deployed.
On the Clusters page, find the cluster that you want to manage and then click the cluster ID.
On the Basic Information page, click Disable for the Automatic IMCI-based Query Acceleration option.
In the Disable Automatic IMCI-based Query Acceleration dialog box, click OK.
After you disable the automatic IMCI-based query acceleration feature, only the parameters related to automatic IMCI-based query acceleration are disabled. The read-only column store nodes, IMCIs, and related data are retained. If you no longer need to retain the read-only column store nodes or the IMCIs on the nodes, you can delete the read-only column store nodes in the console or use SQL statements to delete the IMCIs. For more information, see Add or remove read-only nodes and Execute DDL statements to dynamically create and delete an IMCI.