How to Optimize SQL with Long LockKeys_time Duration

Translated
1

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

Original topic: LockKeys_time占用时长大的sql该怎么优化

| username: Miracle

[Overview] Scenario + Problem Overview

The SQL statement is a very simple update, where id is the primary key, and the table has approximately 90,000 rows. This update has taken anywhere from 3 to 9 seconds.

update t1 set col_1='a', col_2='b', col_3='c' where id=123;

I checked the slow_query table, and the SQL execution time is mostly spent on LockKeys_time.

[Problem] Current Issues Encountered

  1. What does lockkeys_time mean? I couldn’t find it in the official documentation; it feels like lock waiting?
  2. How can I optimize this kind of SQL?

[TiDB Version]
V-5.4.0

[Execution Plan]

id              task    estRows operator info                               actRows execution info                                                                                                          memory  disk

Update_9        root    0       N/A                                         0       time:4.61s, loops:2, , lock_keys: {time:844.1µs, region:2, keys:2, lock_rpc:784.261µs, rpc_count:2, retry_count:1}  0 Bytes N/A

└─Point_Get_1   root    1       table:xxx, index:PRIMARY(id), lock  1       time:4.61s, loops:3, Get:{num_rpc:2, total_time:644.2µs}                                                            N/A     N/A
2
| username: Min_Chen | Original post link

Hello, from the provided execution plan, it appears that point_get is relatively time-consuming. Please check the monitoring to determine if there are any issues with TiKV or the network.

3
| username: xfworld | Original post link

  1. The term “lockkeys_time” can be understood literally, meaning that after retrieving data from the database, the data is locked.

  2. How long does it take to execute the query “select col_1, col_2, col_3 from t1 where id = 123;” and get the result?

4
| username: alfred | Original post link

The long time spent on LockKeys_time is likely caused by lock waiting. When LockKeys_time is relatively long, you can check the lock waiting situation. Lock waiting is caused by data operation conflicts due to business logic, program scheduling, etc. At the database level, you can look for related conflicting transactions for optimization.

5
| username: Miracle | Original post link

Individually trying select or update is quite fast, around 0.5 seconds, so it should be an occasional phenomenon. The slow SQL is executed in the business code.

6
| username: system | Original post link

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