Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.Original topic: TIDBserver 内存占用过高如何进行分析
|
username: residentevil
[TiDB Usage Environment] Production Environment
[TiDB Version] V 7.1.0
[Encountered Problem: Problem Phenomenon and Impact] How to analyze high memory usage of TIDBserver
To check the current memory usage of an instance or cluster, you can query the system table INFORMATION_SCHEMA.(CLUSTER_)MEMORY_USAGE.
To view memory-related operations and execution basis in the instance or cluster, you can query the system table INFORMATION_SCHEMA.(CLUSTER_)MEMORY_USAGE_OPS_HISTORY. For each instance, this table retains the most recent 50 records.
The MEMORY_USAGE_OPS_HISTORY view shows 0 entries. What could be the reason for this, and are there other ways to find out what memory the tidbserver process is using?
The sudden surge in TiDB server is usually caused by slow SQL. Let’s analyze it.
There are only write requests online, no read requests.
Well… I really haven’t encountered this situation before.
Try it, including all the high-memory SQL that is not in the slow queries that have been executed or not completed.
You can query the system table INFORMATION_SCHEMA.(CLUSTER_)MEMORY_USAGE.
Take a look at the top SQLs in the dashboard, it’s usually them causing the trouble.
Generally, starting with topsql, once the topsql issue of high resource consumption is resolved, subsequent problems should also be resolved.
Here is a troubleshooting approach you can refer to:
tidb-server memory is too high → indicates that a large amount of data is consuming memory → operations involving too much data are usually slow SQL → troubleshoot slow SQL → tidb dashboard → check cluster top SQL and slow query SQL → select and confirm the time period and tidb-server instance node with high memory → identify one or several types of SQL → analyze SQL usage → optimize SQL or adjust business → expand memory or scale out tidb-server nodes.
Only INSERT+DELETE operations, no SELECT read requests.
When checking resource utilization on the dashboard, it is usually caused by slow SQL queries. If there are no slow SQL queries, check if the statements are consuming too much memory.
Check the top SQL in the dashboard during that period, it’s usually caused by slow SQL.
The online read and write SQL have all stopped, but the memory occupied by the TIDBSERVER process has not decreased. Could it be related to the large number of tables I have created? Currently, there are more than 60,000 tables in the database.
How can I see which threads in the TIDBSERVER process are consuming the most memory? Generally, in native MySQL 8.0, there is a MEM view in the SYS schema that clearly shows how much memory each thread is consuming.
Threads can be seen using this command: top -Hp $(pidof tidb-server), but the results don’t reveal much.
Are there any other solutions? This issue is not closely related to SQL.
curl -G tidb_ip:port/debug/pprof/heap > heap.profile
Then use the command go tool pprof db.heap.prof followed by top to view.
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: