Execution Plan Caching Issues

Translated
1

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 执行计划缓存问题

| username: GreenGuan

We have a business scenario for extracting full database data (similar to statements with join and where conditions). The efficiency of single execution is acceptable, but high concurrency leads to excessive CPU pressure. We want to optimize the CPU for this scenario. One solution is to avoid SQL parsing, compiling, and optimizing under high concurrency. According to the official documentation, it is possible to cache the execution plan of a fixed SQL in memory, thus avoiding the steps of parsing, compiling, and optimizing, and directly fetching data from KV based on the cached data. However, there is a drawback: for specific types of SQL, such as range queries with different conditions, it may use inefficient indexes. Another issue is that the plan cache uses an LRU list. If set too small, it may cause frequent evictions, which could lead to performance loss. Therefore, is there a way to use the plan cache for only one specific statement without affecting other SQL?

2
| username: cheng | Original post link

You can refer to this preprocessing, it should meet your requirements
Prepared Statements | PingCAP Docs

3
| username: GreenGuan | Original post link

May I ask if preprocessing also needs to be enabled with plan cache to take effect?

4
| username: cheng | Original post link

Prepared statements are a way to template multiple SQL statements that only differ in parameters, separating the SQL statements from the parameters. They can be used to improve the performance of SQL statements: since the statements are pre-parsed on the TiDB side, subsequent executions only need to pass parameters, saving the cost of full SQL parsing, SQL statement string concatenation, and network transmission.

5
| username: h5n1 | Original post link

Preprocessing addresses the parse/compile time, while execution plan caching addresses the time to generate the physical execution plan, allowing it to be reused directly. However, TiDB’s execution plan caching relies on the preprocessing function, and only preprocessed plans can be cached.

6
| username: GreenGuan | Original post link

From a theoretical standpoint, preprocessing can reduce CPU pressure in high-concurrency scenarios, which aligns well with our business context. I need to test it.

7
| username: system | Original post link

This topic will be automatically closed 60 days after the last reply. No new replies are allowed.