PolarDB:Automatic IMCI-based query acceleration

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

• Enterprise Edition:

  • Edition: Cluster Edition.

  • Database engine:

    • MySQL 8.0.1 whose revision version is 8.0.1.1.45.2 or later.

    • MySQL 8.0.2 whose revision version is 8.0.2.2.27 or later.

• Standard Edition:

  • CPU Architecture: X86.

  • Database engine: MySQL 8.0.1 whose revision version is 8.0.1.1.45.2 or later.

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 IMCI nodes are charged as common nodes. For more information about the billing rules, see Billing rules of compute nodes.

  • The system automatically creates IMCIs based on slow SQL queries. The IMCIs occupy a specific amount of storage space.

  • 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.

• 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

1. Log on to the PolarDB console. In the left-side navigation pane, click Clusters. In the upper-left corner, select the region of the cluster. In the cluster list, find the cluster and click its ID to go to its Basic Information page.

2. On the Basic Information page, click Enable for the Automatic IMCI-based Query Acceleration option.

   

3. Proceed to the next step based on whether the cluster contains read-only IMCI nodes.

   • If the cluster contains read-only IMCI nodes, click OK in the Enable Automatic IMCI-based Query Acceleration dialog box.

     

   • If the cluster does not contain read-only IMCI nodes, click OK in the Enable Automatic IMCI-based Query Acceleration dialog box. You are redirected to the page for adding read-only IMCI nodes.

     

     Note

     • You can immediately add a read-only IMCI node after clicking OK, or add the node later. For more information, see Add a read-only IMCI node.

     • The automatic IMCI-based query acceleration feature takes effect only if the cluster contains at least one read-only IMCI node.

     • If you enable the automatic IMCI-based query acceleration feature on a cluster that does not contain a read-only IMCI 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.

Disable the automatic IMCI-based query acceleration feature

1. Log on to the PolarDB console. In the left-side navigation pane, click Clusters. In the upper-left corner, select the region of the cluster. In the cluster list, find the cluster and click its ID to go to its Basic Information page.

2. On the Basic Information page, click Disable for the Automatic IMCI-based Query Acceleration option.

   

3. In the Disable Automatic IMCI-based Query Acceleration dialog box, click OK.