TiDB 4.0.13 Small-Scale Deployment in Production Environment: TiDB Queries Extremely Slow After 2 Months, Restarting TiDB and Nodes Ineffective

Translated
1

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

Original topic: TiDB4.0.13在生产环境小规模部署，2个月后TiDB查询非常慢，重启TiDB和重启节点均无效

| username: TiDBer_G64jJ9u8

[TiDB Usage Environment] Kylin V10 SP1 arm, three-node deployment: 3 PD, 3 TiKV, 3 TiDB
[Overview] TiDB 4.0.13 deployed in K8S environment, small-scale queries and writes, with some table truncations in between
[Background] Appeared after 2 months of operation in production environment, restarting nodes and TiDB had no effect
[Phenomenon] All queries through microservices or MySQL client have significant delays, a table with one row takes 1.5s to query.
Analyzing PD, TiDB processes, and TiKV processes revealed that TiKV is extremely abnormal, with CPU at 300% and memory consumption at 8G, while TiKV memory is configured to 8G. One node even consumes 13G, but the total data in the data directory is around 5G, and the data dumped via mysqldump is around 500M. The status of the 3 TiKV nodes is similar.
[Problem] The database inexplicably slows down without obvious abnormal information
[Business Impact] System is unusable
[TiDB Version] 4.0.13
[Application Software and Version]
[K8S] 1.20.7
[Attachments]

TiKV thread status:
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
28113 root 20 0 80.9g 9.5g 26624 R 87.5 15.2 1770:28 raftstore-4-0
28114 root 20 0 80.9g 9.5g 26624 R 87.5 15.2 1769:45 raftstore-4-1
25597 root 20 0 80.9g 9.5g 26624 R 43.8 15.2 1129:11 grpc-server-1
28112 root 20 0 80.9g 9.5g 26624 S 43.8 15.2 762:43.62 steady-timer
25599 root 20 0 80.9g 9.5g 26624 S 31.2 15.2 671:00.90 grpc-server-3

Through Explain analysis, the time is all spent on TiKV
mysql> explain analyze select * from user;
±-----------------------±--------±--------±----------±-----------------±---------------------------------------------------------------------------------------------------------------------±-----------------------------------------------±-----------±-----+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
±-----------------------±--------±--------±----------±-----------------±---------------------------------------------------------------------------------------------------------------------±-----------------------------------------------±-----------±-----+
| TableReader_5 | 4.00 | 4 | root | | time:1.13s, loops:2, cop_task: {num: 1, max: 1.13s, proc_keys: 4, rpc_num: 1, rpc_time: 1.13s, copr_cache: disabled} | data:TableRangeScan_4 | 1006 Bytes | N/A |
| └─TableRangeScan_4 | 4.00 | 4 | cop[tikv] | table:user | tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 4, total_keys: 9} | range:[0,+inf], keep order:false, stats:pseudo | N/A | N/A |
±-----------------------±--------±--------±----------±-----------------±---------------------------------------------------------------------------------------------------------------------±-----------------------------------------------±-----------±-----+
2 rows in set (1.13 sec)

Memory blocks as follows:

17939: /tikv-server --pd=http://basic-pd:2379 --advertise-addr=basic-tikv-0.basic-tikv-peer.myspace.svc:20160 --addr=0.0.0.0:20160 --status-addr=0.0.0.0:20180 --data-dir=/var/lib/tikv --capacity=0 --config=/etc/tikv/tikv.toml
0000aaabc6950000 33536K r-x-- tikv-server
0000aaabc8a20000 2432K r---- tikv-server
0000aaabc8c80000 64K rw— tikv-server
0000aaabc8c90000 768K rw— [ anon ]
0000ffeb30000000 2359296K rw— [ anon ]
0000ffebc0000000 14155776K rw— [ anon ]
0000ffef30000000 3932160K rw— [ anon ]
0000fff028000000 38666240K rw— [ anon ]
0000fff960000000 524288K rw— [ anon ]
0000fff980000000 2097152K rw— [ anon ]
0000fffa1efe0000 524288K rw— [ anon ]
0000fffa3efe0000 64K ----- [ anon ]
0000fffa3eff0000 8192K rw— [ anon ]
0000fffa4c5b0000 64K ----- [ anon ]
0000fffa4c5c0000 8192K rw— [ anon ]
0000fffa4cdc0000 64K ----- [ anon ]
0000fffa4cdd0000 8320K rw— [ anon ]
0000fffa4d5f0000 64K ----- [ anon ]
0000fffa4d600000 2112K rw— [ anon ]
0000fffa4d810000 64K ----- [ anon ]
0000fffa4d820000 2112K rw— [ anon ]
0000fffa4da30000 64K ----- [ anon ]
0000fffa4da40000 2176K rw— [ anon ]
0000fffa4dc60000 64K ----- [ anon ]
0000fffa4dc70000 2048K rw— [ anon ]
0000fffa4de70000 64K ----- [ anon ]
0000fffa4de80000 2112K rw— [ anon ]
0000fffa4e090000 64K ----- [ anon ]
0000fffa4e0a0000 2048K rw— [ anon ]
0000fffa4e2a0000 64K ----- [ anon ]
0000fffa4e2b0000 2176K rw— [ anon ]
0000fffa4e4d0000 64K ----- [ anon ]
0000fffa4e4e0000 2112K rw— [ anon ]
0000fffa4e6f0000 64K ----- [ anon ]
0000fffa4e700000 2112K rw— [ anon ]
0000fffa4e910000 64K ----- [ anon ]
0000fffa4e920000 2048K rw— [ anon ]
0000fffa4eb20000 64K ----- [ anon ]
0000fffa4eb30000 2240K rw— [ anon ]
0000fffa4ed60000 64K ----- [ anon ]
0000fffa4ed70000 2048K rw— [ anon ]
0000fffa4ef70000 64K ----- [ anon ]
0000fffa4ef80000 2112K rw— [ anon ]
0000fffa4f190000 64K ----- [ anon ]
0000fffa4f1a0000 2048K rw— [ anon ]
0000fffa4f3a0000 64K ----- [ anon ]
0000fffa4f3b0000 8192K rw— [ anon ]
0000fffa4fbb0000 64K ----- [ anon ]
0000fffa4fbc0000 8192K rw— [ anon ]
0000fffa503c0000 64K ----- [ anon ]
0000fffa503d0000 8192K rw— [ anon ]
0000fffa50bd0000 128K r-x-- libresolv-2.28.so
0000fffa50bf0000 64K r---- libresolv-2.28.so
0000fffa50c00000 64K rw— libresolv-2.28.so
0000fffa50c10000 64K r-x-- libnss_dns-2.28.so
0000fffa50c20000 64K r---- libnss_dns-2.28.so
0000fffa50c30000 64K rw— libnss_dns-2.28.so
0000fffa50c40000 64K r-x-- libnss_files-2.28.so
0000fffa50c50000 64K r---- libnss_files-2.28.so
0000fffa50c60000 64K rw— libnss_files-2.28.so
0000fffa50c70000 128K rw— [ anon ]
0000fffa50c90000 64K ----- [ anon ]
0000fffa50ca0000 2112K rw— [ anon ]
0000fffa50eb0000 64K ----- [ anon ]
0000fffa50ec0000 2112K rw— [ anon ]
0000fffa510d0000 64K ----- [ anon ]
0000fffa510e0000 2112K rw— [ anon ]
0000fffa512f0000 64K ----- [ anon ]
0000fffa51300000 2240K rw— [ anon ]
0000fffa51530000 64K ----- [ anon ]
0000fffa51540000 2112K rw— [ anon ]
0000fffa51750000 64K ----- [ anon ]
0000fffa51760000 2048K rw— [ anon ]
0000fffa51960000 64K ----- [ anon ]
0000fffa51970000 2112K rw— [ anon ]
0000fffa51b80000 64K ----- [ anon ]
0000fffa51b90000 2048K rw— [ anon ]
0000fffa51d90000 64K ----- [ anon ]
0000fffa51da0000 2176K rw— [ anon ]
0000fffa51fc0000 64K ----- [ anon ]
0000fffa51fd0000 2048K rw— [ anon ]
0000fffa521d0000 64K ----- [ anon ]
0000fffa521e0000 2112K rw— [ anon ]
0000fffa523f0000 64K ----- [ anon ]
0000fffa52400000 2112K rw— [ anon ]
0000fffa52610000 64K ----- [ anon ]
0000fffa52620000 2176K rw— [ anon ]
0000fffa52840000 64K ----- [ anon ]
0000fffa52850000 2048K rw— [ anon ]
0000fffa52a50000 64K ----- [ anon ]
0000fffa52a60000 2112K rw— [ anon ]
0000fffa52c70000 64K ----- [ anon ]
0000fffa52c80000 2176K rw— [ anon ]
0000fffa52ea0000 64K ----- [ anon ]
0000fffa52eb0000 2112K rw— [ anon ]
0000fffa530c0000 64K ----- [ anon ]
0000fffa530d0000 2176K rw— [ anon ]
0000fffa532f0000 64K ----- [ anon ]
0000fffa53300000 2048K rw— [ anon ]
0000fffa53500000 64K ----- [ anon ]
0000fffa53510000 2112K rw— [ anon ]
0000fffa53720000 64K ----- [ anon ]
0000fffa53730000 2176K rw— [ anon ]
0000fffa53950000 64K ----- [ anon ]
0000fffa53960000 2112K rw— [ anon ]
0000fffa53b70000 64K ----- [ anon ]
0000fffa53b80000 2048K rw— [ anon ]
0000fffa53d80000 64K ----- [ anon ]
0000fffa53d90000 2048K rw— [ anon ]
0000fffa53f90000 64K ----- [ anon ]
0000fffa53fa0000 2176K rw— [ anon ]
0000fffa541c0000 64K ----- [ anon ]
0000fffa541d0000 2112K rw— [ anon ]
0000fffa543e0000 64K ----- [ anon ]
0000fffa543f0000 2112K rw— [ anon ]
0000fffa54600000 64K ----- [ anon ]
0000fffa54610000 2112K rw— [ anon ]
0000fffa54820000 64K ----- [ anon ]
0000fffa54830000 2048K rw— [ anon ]
0000fffa54a30000 64K ----- [ anon ]
0000fffa54a40000 2112K rw— [ anon ]
0000fffa54c50000 64K ----- [ anon ]
0000fffa54c60000 2112K rw— [ anon ]
0000fffa54e70000 64K ----- [ anon ]
0000fffa54e80000 2176K rw— [ anon ]
0000fffa550a0000 64K ----- [ anon ]
0000fffa550b0000 2112K rw— [ anon ]
0000fffa552c0000 64K ----- [ anon ]
0000fffa552d0000 2048K rw— [ anon ]
0000fffa554d0000 64K ----- [ anon ]
0000fffa554e0000 2112K rw— [ anon ]
0000fffa556f0000 64K ----- [ anon ]
0000fffa55700000 2112K rw— [ anon ]
0000fffa55910000 64K ----- [ anon ]
0000fffa55920000 2176K rw— [ anon ]
0000fffa55b40000 64K ----- [ anon ]
0000fffa55b50000 2048K rw— [ anon ]
0000fffa55d50000 64K ----- [ anon ]
0000fffa55d60000 2112K rw— [ anon ]
0000fffa55f70000 64K ----- [ anon ]
0000fffa55f80000 2112K rw— [ anon ]
0000fffa56190000 64K ----- [ anon ]
0000fffa561a0000 2112K rw— [ anon ]
0000fffa563b0000 64K ----- [ anon ]
0000fffa563c0000 2112K rw— [ anon ]
0000fffa565d0000 64K ----- [ anon ]
0000fffa565e0000 2176K rw— [ anon ]
0000fffa56800000 64K ----- [ anon ]
0000fffa56810000 8192K rw— [ anon ]
0000fffa57010000 64K ----- [ anon ]
0000fffa57020000 8256K rw— [ anon ]
0000fffa57830000 64K ----- [ anon ]
0000fffa57840000 2176K rw— [ anon ]
0000fffa57a60000 64K ----- [ anon ]
0000fffa57a70000 2112K rw— [ anon ]
0000fffa57c80000 64K ----- [ anon ]
0000fffa57c90000 2048K rw— [ anon ]
0000fffa57e90000 64K ----- [ anon ]
0000fffa57ea0000 2048K rw— [ anon ]
0000fffa580a0000 64K ----- [ anon ]
0000fffa580b0000 2048K rw— [ anon ]
0000fffa582b0000 64K ----- [ anon ]
0000fffa582c0000 2048K rw— [ anon ]
0000fffa584c0000 64K ----- [ anon ]
0000fffa584d0000 2048K rw— [ anon ]
0000fffa586d0000 64K ----- [ anon ]
0000fffa586e0000 2048K rw— [ anon ]
0000fffa588e0000 64K ----- [ anon ]
0000fffa588f0000 2048K rw— [ anon ]
0000fffa58af0000 64K ----- [ anon ]
0000fffa58b00000 2048K rw— [ anon ]
0000fffa58d00000 64K ----- [

2
| username: zhanggame1 | Original post link

The version is too old, and the data volume is not large. Consider upgrading to the latest version.

3
| username: zhanggame1 | Original post link

The data volume is not large, so exporting and rebuilding the database is not troublesome.

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

Create a new table and insert a row of data to see if it’s slow.

5
| username: dba-kit | Original post link

Could you check the GC parameter settings? Theoretically, with such a small amount of data, it shouldn’t suddenly slow down. However, I suggest you re-import and upgrade the version. The performance of version 6.5 has improved significantly compared to version 4.0.

6
| username: redgame | Original post link

Check the query plan and indexes.

7
| username: h5n1 | Original post link

Manually compact the 3 TiKV nodes:

tikv-ctl --host tikv_IP:20160 compact -c write -d kv --bottommost force
tikv-ctl --host tikv_IP:20160 compact -c default -d kv --bottommost force
8
| username: TiDBer_G64jJ9u8 | Original post link

Have there been any issues with this version before? The boss requires a clear analysis of the problem and what caused it.
It’s too difficult!

9
| username: zhanggame1 | Original post link

ARM + K8s is too rare, no one has experience with it, right?

10
| username: TiDBer_G64jJ9u8 | Original post link

There is only one row of data, this has nothing to do with the index. In the query plan, the description “Through Explain analysis, the time is all spent on TiKV” indicates this.

11
| username: TiDBer_G64jJ9u8 | Original post link

The current trend of domestic production is gradually shifting towards ARM + K8s. Is this issue related to that?

12
| username: zhanggame1 | Original post link

The most feared issue is hardware problems, as I have no idea how to troubleshoot them.

13
| username: TiDBer_G64jJ9u8 | Original post link

There’s no way around this. After checking system resources with iostat, CPU and disk I/O are all normal. The only suspicious point is that the operating system’s cache is very large, leaving only 4GB of memory available on the entire machine.