Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.Original topic: SQL 偶发主键查询速度特别慢
|
username: keithyzk
【TiDB Usage Environment】Production environment with 3 TiDB, 3 PD, and 7 TiKV
【TiDB Version】v5.0.4
【Reproduction Path】Primary key query is occasionally very slow, taking several minutes to return results
【Problem Encountered: Phenomenon and Impact】
select from where primary key = ; Occasionally very slow, taking several minutes
【Attachments: Screenshots/Logs/Monitoring】
SQL statement, folder_id is the primary key
SELECT
dmsfolderd0_.folder_id AS folder1_58_0_,
dmsfolderd0_.instance_id AS instance2_58_0_,
dmsfolderd0_.area_id AS area3_58_0_,
dmsfolderd0_.folder_parentFolderId AS folder4_58_0_,
dmsfolderd0_.folder_guid AS folder5_58_0_,
dmsfolderd0_.folder_code AS folder6_58_0_,
dmsfolderd0_.folder_sortOrder AS folder7_58_0_,
dmsfolderd0_.folder_name AS folder8_58_0_,
dmsfolderd0_.folder_path AS folder9_58_0_,
dmsfolderd0_.folder_size AS folder10_58_0_,
dmsfolderd0_.folder_maxFolderSize AS folder11_58_0_,
dmsfolderd0_.folder_alertSize AS folder12_58_0_,
dmsfolderd0_.folder_maxFileSize AS folder13_58_0_,
dmsfolderd0_.folder_forbiddenFileExtensions AS folder14_58_0_,
dmsfolderd0_.folder_childFoldersCount AS folder15_58_0_,
dmsfolderd0_.folder_childFilesCount AS folder16_58_0_,
dmsfolderd0_.securityLevel_id AS securitylevel17_58_0_,
dmsfolderd0_.folder_lockCount AS folder18_58_0_,
dmsfolderd0_.folder_permStrategy AS folder19_58_0_,
dmsfolderd0_.folder_verStrategy AS folder20_58_0_,
dmsfolderd0_.folder_defaultViewId AS folder21_58_0_,
dmsfolderd0_.folder_state AS folder22_58_0_,
dmsfolderd0_.folder_ownerId AS folder23_58_0_,
dmsfolderd0_.folder_createType AS folder24_58_0_,
dmsfolderd0_.folder_createTime AS folder25_58_0_,
dmsfolderd0_.folder_createOperator AS folder26_58_0_,
dmsfolderd0_.folder_modifyTime AS folder27_58_0_,
dmsfolderd0_.folder_modifyOperator AS folder28_58_0_,
dmsfolderd0_.folder_type AS folder29_58_0_,
dmsfolderd0_.folder_offlinePermission AS folder30_58_0_,
dmsfolderd0_.folder_ExtensionVerifyType AS folder31_58_0_,
dmsfolderd0_.folder_remark AS folder32_58_0_,
dmsfolderd0_.folder_modifyOperatorName AS folder33_58_0_,
dmsfolderd0_.folder_createOperatorName AS folder34_58_0_,
dmsfolderd0_.folder_deleteTime AS folder35_58_0_,
dmsfolderd0_.folder_deleteOperator AS folder36_58_0_,
dmsfolderd0_.folder_purgeTime AS folder37_58_0_,
dmsfolderd0_.folder_purgeOperator AS folder38_58_0_,
dmsfolderd0_.folder_isDeleted AS folder39_58_0_,
dmsfolderd0_.folder_isCascadeDelete AS folder40_58_0_,
dmsfolderd0_.folder_deleteName AS folder41_58_0_,
dmsfolderd0_.folder_deleteOperatorName AS folder42_58_0_,
dmsfolderd0_.folder_purgeOperatorName AS folder43_58_0_,
dmsfolderd0_.folder_rootFolderId AS folder44_58_0_
FROM
dms_folder dmsfolderd0_
WHERE
dmsfolderd0_.folder_id = 28145829;
Analysis during slow occurrence
id task estRows operator info actRows execution info memory disk
Point_Get_1 root 1 table:dms_folder, handle:28145829 0 time:11m21.7s, loops:1, Get:{num_rpc:32, total_time:10m40s},tikvRPC_backoff:{num:31, total_time:41.6s},regionMiss_backoff:{num:1, total_time:2ms} N/A N/A
Analysis during normal operation
|
username: 
|
username: 
|
username: 
|
username: 
.
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: