Unknown Cause Leading to TiDB preparedStmt Related Memory Usage Not Being Reclaimed

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

Original topic: 不明原因导致TiDB preparedStmt相关内存占用无法回收

| username: Qiuchi

[TiDB Usage Environment] Test
[TiDB Version] 6.1.0
[Reproduction Path/Problem Phenomenon and Impact]
When executing a large number of prepared statement inserts using batch insert with size = 2000, jdbc param = “useServerPrepStmts=true&cachePrepStmts=true&prepStmtCacheSize=1000&prepStmtCacheSqlLimit=20480&useConfigs=maxPerformance&rewriteBatchedStatements=true”

During the insertion process, TiDB’s memory usage keeps increasing. When the insertion is intermittently paused and resumed, the memory usage is as shown below:

After canceling the query and disconnecting, memory is reclaimed after a period of time.
This situation cannot be stably reproduced, but when it occurs, restarting TiDB or TiKV alone cannot solve the problem (memory will be cleared but cannot be reclaimed after insertion). The problem disappears after restarting the entire cluster.
pprof.tidb-server.alloc_objects.alloc_space.inuse_objects.inuse_space.021.pb.gz (112.6 KB)

| username: ealam_小羽 | Original post link

Looking at this configuration, both prepStmtCacheSize and prepStmtCacheSqlLimit are much larger than the maximum configurations in the official best practices. Therefore, executing a large number of prepared statement inserts might consume a lot of memory. Maybe you should try adjusting these two parameters according to the actual scenario? I see you mentioned it’s a test environment.

| username: Qiuchi | Original post link

This test uses the same insert statement, so it doesn’t seem to be an issue with the length or number of prepared statements, right?

| username: ealam_小羽 | Original post link

Take a look at this post, it seems somewhat similar to this. Can you check it again?

| username: Qiuchi | Original post link

Indeed, this is the issue. After I turned off the execution plan cache, the memory no longer surged. Previously, I had some misunderstandings and confused the parsing of the syntax tree with the generation of the physical execution plan, which are two different stages. But besides that, I still have some questions. One is whether the length of the rewritten SQL is limited by prepStmtCacheSqlLimit when using rewriteBatchedStatements. After I turned off tidb_enable_prepared_plan_cache, I found that the insert SQL, which originally had a parsing time of 0, needs to re-parse the AST every time it is executed. The second is that according to the description in Execution Plan Cache | PingCAP Documentation Center, generally, repeated execution of insert statements should not trigger a cache miss, but in practice, TiDB parses a large number of execution plans and puts them into the cache, causing OOM. The third is why this is not an issue that can be stably reproduced, and why restarting the cluster affects it.

Thanks for pointing me in the right direction. I’ll go ask that moderator for further advice.

| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.