Feature Introduction
The SQL execution process for MySQL data primarily involves four stages: parse, prepare, optimize, and execute. The execution plan caching capability comes into play in Prepare statement mode. This mode skips the parse and prepare stages during execution, and the execution plan further omits the optimization phase, thereby enhancing performance.
The MySQL 8.0 20210830 version only applies to (UK&PK) point queries. We will expand the scope of functionality in subsequent versions.
Supported Versions
Kernel version MySQL 8.0 20210830 and above.
Applicable Scenarios
For online workloads with frequent short point queries, using the prepare statement mode can improve application performance. The extent of performance enhancement depends on the specific online workload.
Impact on Performance
For point queries (UK&PK) SQL, latency performance improved by 20% - 30%, and throughput performance improved by 20% - 30% (in the point_select.lua test of sysbench).
For memory overhead, when the plan cache switch is turned on, memory usage will increase compared to when it is not turned on.
Usage Instructions
Add a cdb_plan_cache switch to control whether to enable the plan cache, and add a cdb_plan_cache_stats switch to monitor the cache hit status. These parameters are at the tencentroot level.
|
| | | OFF | ON/OFF | Feature switch: whether to enable the plan cache |
Note:
The cdb_plan_cache_stats parameter switch must be enabled before you can view related data using the show cdb_plan_cache_stat command. To enable it, submit a ticket. The show cdb_plan_cache command is used to view the plan cache hit status. The meanings of the fields are as follows:
|
| SQL statement, here is a SQL statement with ?, indicating that the execution plan of this SQL statement has been cached. |
| The mode of SQL caching currently only supports the prepare mode. |
| The number of hits in the current session |
Note:
When the cdb_plan_cache_stats switch is turned on, it is equivalent to information logging, which will have an impact on performance.
Related Status Description
When the status of each stage of SQL execution is viewed via show profile, if the executed SQL hits the plan cache, the optimizing, statistics, and preparing states will be omitted.