This document describes how to enable the AI optimizer and explains the fields on the AI optimizer page.
Enabling the AI Optimizer
Note:
Note: This feature is currently in the allowlist-based grayscale release phase. If you do not see the feature entry in the console, please wait patiently. For any questions, you can submit a ticket for feedback.
When you purchase a new cluster, enabling the AI optimizer takes effect in the cluster dimension. At the time of purchase, this feature is automatically enabled for all instances within the cluster.
For existing clusters, enabling the AI optimizer takes effect in the instance dimension. You can select any instance (multiple selection is supported) under the cluster to enable this feature.
Scenario 1: Enabling the AI Optimizer When Purchasing a New Cluster
When you purchase a new cluster on the TDSQL-C for MySQL purchase page, you can enable this feature under Advanced Configuration by setting the Enable AI Optimizer option to Yes.
If you want to purchase a cluster with provisioned resources, you can refer to Create Cluster for detailed steps.
If you want to purchase a Serverless cluster, you can refer to Create a Serverless Cluster for detailed steps.
Scenario 2: Enabling the AI Optimizer for an Existing Cluster
2. In the cluster list, click Cluster ID to go to the cluster management page.
3. Select the AI Optimizer page and click Enable Now.
4. In the pop-up window, select the instances under the cluster for which you want to enable the AI optimizer (both read-write instances and read-only instances can be selected/multiple selection is supported), and then click OK.
Scenario 3: Enabling the AI Optimizer for Instances in a Cluster
If your cluster contains multiple instances and the AI optimizer is not enabled for all of them, you can follow the steps below to enable this feature for specific instances.
2. In the cluster list, click Cluster ID to go to the cluster management page.
3. Select the AI Optimizer page, and click Feature Settings on the right.
4. In the pop-up window, enable the feature button for the target instance.
AWR Description
AWR, or Automatic Workload Repository, represents a collection of capabilities for collecting data for training and optimization, enabling various controls and adjustments through parameters. After you enable the AI optimizer, AWR is automatically enabled. The parameters currently provided by AWR are as follows:
Parameter Name
Type
Value Range
Description
txsql_awr_enabled_level
uint
0 | 2
This parameter indicates the level selector for AWR.
A value of 0: indicates that it is inactive, which also means the AI optimizer is disabled.
A value of 2: enables the AAS precise time timer. This also means the AI optimizer is enabled. When you purchase a new cluster or enable the AI optimizer for an existing cluster through the console, this parameter value is set to 2 by default.
Note:
When you disable the AI optimizer through the console, this parameter is not automatically disabled (its value remains 0). To disable it, see Set Instance Parameters to make the adjustment manually.
2. In the cluster list, click Cluster ID to go to the cluster management page.
3. Select the AI Optimizer page.
View Overview
On the AI optimizer page, you can switch between instances to view them.
After you select the instance to view, the number of SQL templates optimized by the AI optimizer for the current instance and their runtime are displayed below. Until you disable the feature, the AI optimizer continuously performs multiple rounds of AI training, explores quantized execution plans, and reduces SQL statement execution time.
Viewing Metric Descriptions
The AI optimizer currently involves the following performance metrics. By comparing the metric data before and after optimization, you can clearly perceive the optimization effect after enabling the AI optimizer, thereby determining whether the feature currently meets your requirement for reducing SQL statement execution time.
Total SQL Execution Time: displays a comparison of SQL template execution times before and after optimization. It is calculated by accumulating the execution times of SQL templates executed before and after training.
Average CPU Utilization: displays a comparison of the average CPU utilization of instances before and after optimization. This metric is calculated based on the monitored average CPU utilization of instances before and after training. Due to usage patterns, the utilization after optimization may be higher than that before optimization.
Average Slow Log Count: displays a comparison of the average slow log count of instances before and after optimization. This metric is calculated based on the monitored average slow log count of instances before and after training. Due to usage patterns, the count after optimization may be higher than that before optimization.
Viewing Optimization Details
You can view the optimization detail list for the current instance across all time, the last 24 hours, the last 7 days, the last 30 days, or a custom time range. You can also enter an SQL template in the search box to quickly search for and obtain the optimization details of the corresponding optimization task. To refresh the list, click
. The optimization list displays the optimization tasks within the selected time range, presented by SQL template. The fields in the optimization detail list are as follows:
Field Name
Description
SQL Template
Displays the complete SQL template details.
Task Status
Displays the current status of the optimization task, supports filtering, and includes the following values:
In Progress: indicates that the current task is being continuously optimized.
Invalid: indicates that the current task has stopped being optimized. The task status becomes invalid if it is overwritten by a new task or if you manually stop the optimization.
Optimization Count
Displays the number of optimizations for the current optimization task.
Total Optimized SQL Statements
Displays the cumulative number of optimized SQL statements for the current optimization task.
Operation
Displays the operations that can be performed on the current optimization task.
Stop Optimization: Click Stop Optimization to manually stop an optimization task in progress.
Resume Training: Click Resume Training to manually resume an optimization task that is in a stopped state.
Viewing SQL Template Details
Locate the target optimization task in the Optimization Details list. Click
to expand the details of the current optimization task, where you can obtain more information.
Field Name
Description
Optimization Phase
Displays the historical and current status of optimization tasks, in the following format:
2025-04-28 03:04:36
In effect
Training Epoch
Displays the training epoch of the optimization task. Example: 1.
Average Execution Time (ms)
Displays the average execution time before and after optimization for the corresponding optimization stage. Unit: ms.
Average Scanned Rows
Displays the average number of rows scanned before and after optimization for the corresponding optimization stage.
Optimized Number of SQLs
Displays the number of optimized SQL statements for the corresponding optimization stage.
Operation
View: Click View to view the visualized execution plan details for the corresponding optimization stage in a pop-up window.
Viewing Execution Plan Details
Locate the target optimization task in the Optimization Details list. Click
to expand the details of the current optimization task. Then, click View in the Actions column to query the execution plan details in the sidebar.
SQL Statement Example
You can view the SQL command details of the current execution plan under SQL Statement Example.
Plan Visualization
You can view the execution plan details before and after optimization under Plan Visualization, including comparisons of execution time and scanned rows. Click View Full Screen to query the specific execution plan more clearly.
Plan Table
You can view the execution plan table details before and after optimization under Plan Table, including comparisons of execution time and scanned rows. Click View Full Screen to query the plan table details more clearly.
Field Name
Description
id
Sequence number of the SELECT query. It indicates the execution order of subqueries in SQL. Subqueries with larger id values are executed first. Subqueries with the same id are executed from top to bottom.
Example: 1.
selectType
Query type. It indicates the complexity of the query at this step.
Example: PRIMARY.
table
Name of the table being accessed. It shows which table is being read at this step.
Example: nation.
partitions
Matched partitions. If the table is a partition table, the name of the matched partition is displayed; for non-partition tables, -- is displayed.
Example: --.
type
Access type (a key performance indicator). It indicates how TDSQL-C for MySQL searches for data.
Example: ALL.
possibleKeys
Possible indexes. A list of indexes that the AI optimizer considers theoretically usable.
Example: PRIMARY.
key
Index actually used. The index that the AI optimizer ultimately decides to use.
Example: PRIMARY.
keyLen
Length of the index key. The total number of bytes of the index fields used, which can be used to determine whether the index is fully utilized.
Example: 4.
ref
Index reference relationship. It displays the columns or constants that are compared with the index in the key column.
Example: tpch1g.nation.n_nationkey.
rows
Estimated Number of Rows to Scan.
Example: 382.
filtered
Filter percentage. It indicates the percentage of rows remaining after conditional filtering relative to the estimated number of rows (0 - 100). A smaller value indicates poorer filtering effectiveness.
