High Memory Usage in Queries for Version 5.4.0

Translated
1

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

Original topic: 5.4.0查询使用内存高

| username: magongyong

【TiDB Usage Environment】Production Environment or Test Environment or POC
Production Environment

【TiDB Version】
tidb 4.0.13 and tidb 5.4.0

【Encountered Problem】
The same SQL query uses 40MiB of memory on the tidb server in version 4.0.13, but uses 267MiB in version 5.4.0.
Query in 4.0.13 as shown in the screenshot below:

Image
Image825×533 53.9 KB

Image
Image803×144 14.1 KB

#####################################
Query in 5.4.0 as shown in the screenshot below:

Image
Image833×547 60.1 KB

Image
Image798×189 26.8 KB

【Reproduction Path】What operations were performed to encounter the problem
【Problem Phenomenon and Impact】
Batch processing tasks in the 4.0.13 cluster directly report OOM in the new cluster.

【Attachments】

Please provide the version information of each component, such as cdc/tikv, which can be obtained by executing cdc version/tikv-server --version.

2
| username: magongyong | Original post link

It seems unrelated to the version. Another cluster with the same version uses less memory, with the same database, same tables, same data volume, and the same SQL.

3
| username: 小王同学Plus | Original post link

Hello, has the issue been resolved?

Are these two separate environments, or are you upgrading from v4.0.13 to v5.4.0?
If they are separate environments, you need to confirm whether the machine configurations and data volumes of the two environments are the same. From the above, it seems that the number of scanned keys is not quite the same.

You can try manually collecting the statistics first.

4
| username: cs58_dba | Original post link

It seems like the new version also has some pitfalls.

5
| username: xuexiaogang | Original post link

Got it, if you can avoid it, it’s not a pitfall.

6
| username: magongyong | Original post link

Later, we modified the parameter prepared-plan-cache.capacity from 50000 to 10000, and the issue did not reoccur. The default value used before the upgrade was 100, and it was changed to 50000 in the new version after the upgrade. We didn’t expect it to have such a significant impact.

7
| username: 近墨者zyl | Original post link

Thanks for sharing, avoiding pitfalls in advance.

8
| username: system | Original post link

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