请问tidb 应用反应慢，应如何分析， tidb可以查有没有锁吗？

炼气期DBA · 2025 年1 月 23 日 07:52

[root@host4 ~]# free -m
total used free shared buff/cache available
Mem: 31356 27389 2812 40 1154 915
Swap: 20479 2296 18183

tidb.log
[2025/01/23 16:06:48.612 +08:00] [WARN] [2pc.go:1803] [“schemaLeaseChecker is not set for this transaction”] [conn=3002299850] [session_alias=] [sessionID=3002299850] [startTS=455506554677886977] [checkTS=455506554677886979]
[2025/01/23 16:06:48.665 +08:00] [WARN] [2pc.go:1803] [“schemaLeaseChecker is not set for this transaction”] [conn=3002299850] [session_alias=] [sessionID=3002299850] [startTS=455506554690732035] [checkTS=455506554690732037]
[2025/01/23 16:06:48.722 +08:00] [WARN] [2pc.go:1803] [“schemaLeaseChecker is not set for this transaction”] [conn=3002299850] [session_alias=] [sessionID=3002299850] [startTS=455506554703839235] [checkTS=455506554703839236]
[2025/01/23 16:06:48.758 +08:00] [WARN] [2pc.go:1803] [“schemaLeaseChecker is not set for this transaction”] [conn=3002299850] [session_alias=] [sessionID=3002299850] [startTS=455506554703839237] [checkTS=455506554716946433]
[2025/01/23 16:06:48.792 +08:00] [WARN] [2pc.go:1803] [“schemaLeaseChecker is not set for this transaction”] [conn=3002299850] [session_alias=] [sessionID=3002299850] [startTS=455506554716946435] [checkTS=455506554716946436]
[2025/01/23 16:06:48.831 +08:00] [WARN] [2pc.go:1803] [“schemaLeaseChecker is not set for this transaction”] [conn=3002299850] [session_alias=] [sessionID=3002299850] [startTS=455506554730053633] [checkTS=455506554730053634]
[2025/01/23 16:06:48.852 +08:00] [INFO] [coprocessor.go:1343] [“[TIME_COP_PROCESS] resp_time:351.554696ms txnStartTS:455506554638565382 region_id:154133 store_addr:192.168.6.34:20160 kv_process_ms:349 kv_wait_ms:0 kv_read_ms:340 processed_versions:176184 total_versions:180482 rocksdb_delete_skipped_count:175 rocksdb_key_skipped_count:180698 rocksdb_cache_hit_count:1104 rocksdb_read_count:0 rocksdb_read_byte:0”] [conn=3002300236] [session_alias=]
[2025/01/23 16:06:48.862 +08:00] [WARN] [2pc.go:1803] [“schemaLeaseChecker is not set for this transaction”] [conn=3002299850] [session_alias=] [sessionID=3002299850] [startTS=455506554743160836] [checkTS=455506554743160840]
[2025/01/23 16:06:48.905 +08:00] [WARN] [2pc.go:1803] [“schemaLeaseChecker is not set for this transaction”] [conn=3002299850] [session_alias=] [sessionID=3002299850] [startTS=455506554743160850] [checkTS=455506554756268033]
[2025/01/23 16:06:48.937 +08:00] [WARN] [2pc.go:1803] [“schemaLeaseChecker is not set for this transaction”] [conn=3002299850] [session_alias=] [sessionID=3002299850] [startTS=455506554756268034] [checkTS=455506554756268036]
[2025/01/23 16:06:48.976 +08:00] [WARN] [2pc.go:1803] [“schemaLeaseChecker is not set for this transaction”] [conn=3002299850] [session_alias=] [sessionID=3002299850] [startTS=455506554769637383] [checkTS=455506554769637384]
[2025/01/23 16:06:49.007 +08:00] [WARN] [2pc.go:1803] [“schemaLeaseChecker is not set for this transaction”] [conn=3002299850] [session_alias=] [sessionID=3002299850] [startTS=455506554769637388] [checkTS=455506554782482436]
[2025/01/23 16:06:49.050 +08:00] [WARN] [2pc.go:1803] [“schemaLeaseChecker is not set for this transaction”] [conn=3002299850] [session_alias=] [sessionID=3002299850] [startTS=455506554782482438] [checkTS=455506554782482439]
[2025/01/23 16:06:49.080 +08:00] [WARN] [2pc.go:1803] [“schemaLeaseChecker is not set for this transaction”] [conn=3002299850] [session_alias=] [sessionID=3002299850] [startTS=455506554795589633] [checkTS=455506554795589634]
[2025/01/23 16:06:49.759 +08:00] [INFO] [coprocessor.go:1343] [“[TIME_COP_PROCESS] resp_time:428.630647ms txnStartTS:455506554861125633 region_id:155133 store_addr:192.168.6.31:20160 kv_process_ms:426 kv_wait_ms:0 kv_read_ms:380 processed_versions:224745 total_versions:224849 rocksdb_delete_skipped_count:207 rocksdb_key_skipped_count:248956 rocksdb_cache_hit_count:1143 rocksdb_read_count:0 rocksdb_read_byte:0”] [conn=3002299658] [session_alias=]
[2025/01/23 16:06:50.012 +08:00] [INFO] [coprocessor.go:1343] [“[TIME_COP_PROCESS] resp_time:350.82719ms txnStartTS:455506554953138179 region_id:154133 store_addr:192.168.6.34:20160 kv_process_ms:349 kv_wait_ms:0 kv_read_ms:340 processed_versions:176184 total_versions:180482 rocksdb_delete_skipped_count:175 rocksdb_key_skipped_count:180698 rocksdb_cache_hit_count:1104 rocksdb_read_count:0 rocksdb_read_byte:0”] [conn=3002300256] [session_alias=]
[2025/01/23 16:06:50.391 +08:00] [INFO] [coprocessor.go:1343] [“[TIME_COP_PROCESS] resp_time:375.231813ms txnStartTS:455506555044888577 region_id:154133 store_addr:192.168.6.34:20160 kv_process_ms:370 kv_wait_ms:0 kv_read_ms:370 processed_versions:176184 total_versions:180482 rocksdb_delete_skipped_count:175 rocksdb_key_skipped_count:180698 rocksdb_cache_hit_count:1104 rocksdb_read_count:0 rocksdb_read_byte:0”] [conn=3002300256] [session_alias=]
[2025/01/23 16:06:54.908 +08:00] [INFO] [coprocessor.go:1343] [“[TIME_COP_PROCESS] resp_time:402.688521ms txnStartTS:455506556224536578 region_id:108033 store_addr:192.168.6.34:20160 kv_process_ms:400 kv_wait_ms:0 kv_read_ms:390 processed_versions:91816 total_versions:92203 rocksdb_delete_skipped_count:467 rocksdb_key_skipped_count:184698 rocksdb_cache_hit_count:1765 rocksdb_read_count:0 rocksdb_read_byte:0”] [conn=3002300174] [session_alias=]
[2025/01/23 16:06:55.203 +08:00] [WARN] [2pc.go:1803] [“schemaLeaseChecker is not set for this transaction”] [conn=3002299244] [session_alias=] [sessionID=3002299244] [startTS=455506556394668040] [checkTS=455506556394668041]
[2025/01/23 16:06:55.215 +08:00] [WARN] [2pc.go:1803] [“schemaLeaseChecker is not set for this transaction”] [conn=3002299244] [session_alias=] [sessionID=3002299244] [startTS=455506556407775233] [checkTS=455506556407775234]
[2025/01/23 16:06:56.649 +08:00] [INFO] [coprocessor.go:1343] [“[TIME_COP_PROCESS] resp_time:433.727411ms txnStartTS:455506556670181381 region_id:155133 store_addr:192.168.6.31:20160 kv_process_ms:431 kv_wait_ms:0 kv_read_ms:390 processed_versions:224745 total_versions:224849 rocksdb_delete_skipped_count:207 rocksdb_key_skipped_count:248956 rocksdb_cache_hit_count:1143 rocksdb_read_count:0 rocksdb_read_byte:0”] [conn=3002300200] [session_alias=]
[2025/01/23 16:06:56.727 +08:00] [INFO] [coprocessor.go:1343] [“[TIME_COP_PROCESS] resp_time:362.655373ms txnStartTS:455506556709240840 region_id:154133 store_addr:192.168.6.34:20160 kv_process_ms:360 kv_wait_ms:0 kv_read_ms:370 processed_versions:176184 total_versions:180486 rocksdb_delete_skipped_count:178 rocksdb_key_skipped_count:180707 rocksdb_cache_hit_count:1104 rocksdb_read_count:0 rocksdb_read_byte:0”] [conn=3002300236] [session_alias=]
^C

zhanggame1 · 2025 年1 月 23 日 08:04

服务器内存32G，按官方要求，要求关闭swap，你的swap在用说明内存不够。

混合部署需要限制tikv和tidb内存使用量，需要设置下:
tikv内存限制：
tiup cluster edit-config tidb-test
在tikv那里加上
tikv:
storage.block-cache.capacity: 8GB

root用户登录，执行
set global tidb_server_memory_limit=‘30%’;

炼气期DBA · 2025 年1 月 23 日 08:07

发现只有3条慢的sql，开发人员说和慢的模块与关。。。

zhanggame1 · 2025 年1 月 23 日 08:08

我重新回复了下，你看看，需要设置内存参数，然后关闭交换分区，开那个会卡

Leonard · 2025 年1 月 23 日 09:55

select * from INFORMATION_SCHEMA.DATA_LOCK_WAITS;
select * from INFORMATION_SCHEMA.DEADLOCKS;

查看锁