How to Optimize High CPU Usage in TiDB

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

Original topic: 如何优化tidb占用CPU高的问题

| username: TiDBer_FyDaj6Qg

【TiDB Usage Environment】Production Environment
【TiDB Version】
【Reproduction Path】Large data writes
【Encountered Issues: Problem Phenomenon and Impact】
【Resource Configuration】
【Attachments: Screenshots/Logs/Monitoring】

| username: wluckdog | Original post link

According to the slow SQL on the dashboard, optimize it first.

| username: wangccsy | Original post link

SQL optimization.

| username: zhanggame1 | Original post link

For large data inserts, you can consider using batch inserts, which means using “insert into XXX values” with multiple values combined into one.

| username: Jayjlchen | Original post link

4.0 lacks a lot of diagnostic convenience and is nearing the end of its lifecycle. It is recommended to upgrade to 6.5.x. The dashboard has a top SQL panel (top CPU), which can quickly address these issues.

TiDB Version Lifecycle Support Policy | PingCAP

| username: TIDB-Learner | Original post link

  1. Use haproxy
  2. Optimize SQL
| username: 春风十里 | Original post link

First, we need to determine what is causing the high CPU usage, right? In TiDB, it is generally caused by SQL, which could be due to a large number of SQL parsing, slow SQL, or a large number of small SQLs. Check the TiDB monitoring and the top SQL in the dashboard.

| username: YuchongXU | Original post link

Take a look at topsql first.

| username: dba远航 | Original post link

Optimize SQL, small transactions, low concurrency

| username: Kongdom | Original post link

Version 4.0.0 is a bit outdated, making troubleshooting difficult. It is recommended to start with slow queries and the tidb.log logs, searching for expensive_query in the logs.

| username: TiDBer_小阿飞 | Original post link

Load balancing, HA + KEEPLIVE.

| username: FutureDB | Original post link

  1. Optimize the slow SQLs that use a lot of CPU based on the Top SQL in TiDB Dashboard.
  2. Ensure load balancing of TiDB nodes by checking the Connection Count distribution under the TiDB panel in Grafana to evaluate whether the connection count is relatively balanced across each TiDB node.
  3. For large data volume writes, consider using read-write separation (TiFlash for reading + streaming batch writes) or batch splitting for writes. Aim for smaller transactions to avoid large transactions.
| username: 路在何chu | Original post link

Take a look at the SQL statistics, or write a script to check the currently executing SQL. You can analyze the slow logs afterward.

| username: 哈喽沃德 | Original post link

The main cause of high CPU usage is still due to query SQL. Start by optimizing the SQL. If that doesn’t solve the problem, you can only increase the configuration.