High CPU Usage on TiDB Server During Batch Insert Operations

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

Original topic: 批量insert数据时,tidb server cpu使用高

| username: Hacker_QfCMflZ4

[TiDB Usage Environment] Production Environment
[TiDB Version] 6.5.0
[Reproduction Path] Two TiDB servers, one running data separately, batch insert
[Encountered Problem: Phenomenon and Impact] High CPU usage
[Resource Configuration] 16 cores, 32GB
[Attachments: Screenshots/Logs/Monitoring]

| username: 魔礼养羊 | Original post link

The high CPU utilization of TiDB is due to parsing and optimizing SQL statements. The common approach is to first analyze the logs to see if there are a lot of repeated connections in the interactions with PD and TiKV. Next, check if the SQL statements can be optimized, perhaps by reducing indexes or other methods. Finally, consider using two TiDB servers for load balancing, as distributed databases are designed for this purpose.

| username: tidb菜鸟一只 | Original post link

How do you perform batch inserts? Are you using prepared statements and batch?

| username: Hacker_QfCMflZ4 | Original post link

Yes

| username: Hacker_QfCMflZ4 | Original post link

Is this log from PD?

| username: Jellybean | Original post link

High CPU usage on the TiDB server is usually caused by frequent SQL parsing and optimization. It seems that the precompilation feature you used did not take effect correctly. You can double-check whether the precompilation and batch insertion features are properly enabled on both the client and server sides.

| username: Hacker_QfCMflZ4 | Original post link

The execution process of inserting 500 pieces of data does seem to have issues.

| username: 特雷西-迈克-格雷迪 | Original post link

Is batch insertion 500 rows? It shouldn’t cause high CPU usage, right?

| username: tidb菜鸟一只 | Original post link

Did you enable the parameter useServerPrepStmts=true in your JDBC connection? It seems like it’s not enabled. If it were enabled, it shouldn’t regenerate the execution plan, right?