tidb节点内存异常

心在飞翔 · 2025 年1 月 6 日 03:36

【 TiDB 使用环境】生产环境
【 TiDB 版本】7.5.2
【复现路径】做过哪些操作出现的问题
【遇到的问题：问题现象及影响】
有2个tidb节点，运行一段时间之后，2个节点的内存使用量差异很大，一个内存使用在14G，一个5.7G

【资源配置】进入到 TiDB Dashboard -集群信息 (Cluster Info) -主机(Hosts) 截图此页面
【附件：截图/日志/监控】
用dashboard做了一下内存分析
异常节点的分析图：

正常节点的分析图

有猫万事足 · 2025 年1 月 6 日 03:44

2个方向：
1，负载均衡怎么做的？连接数是否均衡？是不是有重负载的应用直连了其中一个tidb？
2，看topsql界面，看看2个tidb的运行负载差异大不大。
https://docs.pingcap.com/zh/tidb/stable/top-sql#tidb-dashboard-top-sql-页面

2个profiling图太糊了，很多细节看不清，总体感觉负载差异很大。

心在飞翔 · 2025 年1 月 6 日 04:05

负载均衡用的SLB，流量都是均衡分配的，图片貌似页面展示有点模糊，上传了一个附件

有猫万事足 · 2025 年1 月 7 日 06:18

1736230596278(1)

我查了下，有问题的节点的这个分支下全是和统计信息保存相关的内容。这部分在正常节点上是没有的。

所以内存占用高的主要原因是这个节点在做统计信息得收集。

这块到8.1版本才有比较大的提升，7.5版本的建议要么控制收集统计信息的时间，要么考虑把子版本升级一下，看看是否会有所改善。

sgjr · 2025 年1 月 7 日 06:35

如果是收集统计信息导致的内存过高，可以看一下tidb日志，找出收集的库表。看看收集的表是否有大字段，可以通过设置参数tidb_analyze_skip_column_types跳过大字段的收集

TiDBer_xTvoCh2f · 2025 年1 月 7 日 07:02

tidb的内存分析真不错

residentevil · 2025 年1 月 7 日 11:43

我也在尝试使用ppro工具分析tidbserver内存，看官方文档展示确实很详细

心在飞翔 · 2025 年1 月 10 日 06:28

非常感谢，如果是这样，我看看能不能定期重启一下tidb节点，解决这个问题

心在飞翔 · 2025 年1 月 10 日 06:30

查询了一下， ```
ANALYZE TABLE

心在飞翔 · 2025 年1 月 10 日 07:29

分析了一下，是分区表的问题，每个月1号的时候，使用了新分区，貌似系统都会重新收集一下分区的信息，内存异常增加时间和这个统计信息收集能对的上。

有猫万事足 · 2025 年1 月 10 日 11:31

可能和新分区无关，就是在跨月的时候存在问题。

github.com/pingcap/tidb

row count estimation on DATETIME type is over-estimated when time span across years/months

opened 08:53AM - 04 Jan 24 UTC

pcqz

type/enhancement sig/planner found/gs

## Bug Report Please answer these questions before submitting your issue. Tha…nks! ### 1. Minimal reproduce step (Required) [test.zip](https://github.com/pingcap/tidb/files/13827966/test.zip) ``` use test; source test.sql; load stats 'test.json'; explain select * from test where updated_date > '2023-12-31 23:40:00' and updated_date<'2023-12-31 23:50:00'; explain select * from test where updated_date > '2023-12-31 23:50:00' and updated_date<'2024-01-01 00:00:00'; ``` ### 2. What did you expect to see? (Required) ``` mysql> explain select * from test where updated_date > '2023-12-31 23:40:00' and updated_date<'2023-12-31 23:50:00'; +-------------------------------+---------+-----------+---------------------------------------------------+-------------------------------------------------------------------+ | id | estRows | task | access object | operator info | +-------------------------------+---------+-----------+---------------------------------------------------+-------------------------------------------------------------------+ | IndexLookUp_7 | 1382.13 | root | | | | ├─IndexRangeScan_5(Build) | 1382.13 | cop[tikv] | table:test, index:IX_CUST_RELA_DATE(updated_date) | range:(2023-12-31 23:40:00,2023-12-31 23:50:00), keep order:false | | └─TableRowIDScan_6(Probe) | 1382.13 | cop[tikv] | table:test | keep order:false | +-------------------------------+---------+-----------+---------------------------------------------------+-------------------------------------------------------------------+ 3 rows in set (0.00 sec) ``` ### 3. What did you see instead (Required) ``` mysql> explain select * from test where updated_date > '2023-12-31 23:50:00' and updated_date<'2024-01-01 00:00:00'; +-------------------------------+------------+-----------+---------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | id | estRows | task | access object | operator info | +-------------------------------+------------+-----------+---------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | IndexLookUp_10 | 9355819.67 | root | | | | ├─IndexRangeScan_8(Build) | 9355819.67 | cop[tikv] | table:test, index:IX_CUST_RELA_DATE(updated_date) | range:(2023-12-31 23:50:00,2024-01-01 00:00:00), keep order:false, stats:partial[ix_tpcnr_bcn:allEvicted, ix_tpcnr_epn:allEvicted, ix_tpcnr_pan:allEvicted...(more: 1 allEvicted)] | | └─TableRowIDScan_9(Probe) | 9355819.67 | cop[tikv] | table:test | keep order:false, stats:partial[ix_tpcnr_bcn:allEvicted, ix_tpcnr_epn:allEvicted, ix_tpcnr_pan:allEvicted...(more: 1 allEvicted)] | +-------------------------------+------------+-----------+---------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 3 rows in set (0.01 sec) ``` ### 4. What is your TiDB version? (Required) v6.5.3

修复的进度看这个issue

github.com/pingcap/tidb

planner: fix wrong count when is out of range with datetime

pingcap:master ← hawkingrei:50080

opened 09:00AM - 02 Jan 25 UTC

hawkingrei

+29719 -27

### What problem does this PR solve?  Issue Number: close #50080 Problem Summary: ### What changed and how does it work? First, we found that this issue shows a significant difference between datetime with an index and without an index. Therefore, one of the goals of this PR is to reduce the error between them. ``` > explain select * from test where updated_date > '2023-12-31 23:50:00' and updated_date<'2024-01-01 00:00:00'; +---------------------------+------------+-----------+---------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | id | estRows | task | access object | operator info | +---------------------------+------------+-----------+---------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | IndexLookUp_10 | 9768858.44 | root | | | | ├─IndexRangeScan_8(Build) | 9768858.44 | cop[tikv] | table:test, index:IX_CUST_RELA_DATE(updated_date) | range:(2023-12-31 23:50:00,2024-01-01 00:00:00), keep order:false, stats:partial[ix_tpcnr_bcn:unInitialized, ix_tpcnr_epn:unInitialized, ix_tpcnr_pan:unInitialized...(more: 1 unInitialized)] | | └─TableRowIDScan_9(Probe) | 9768858.44 | cop[tikv] | table:test | keep order:false, stats:partial[ix_tpcnr_bcn:unInitialized, ix_tpcnr_epn:unInitialized, ix_tpcnr_pan:unInitialized...(more: 1 unInitialized)] | +---------------------------+------------+-----------+---------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ > drop index IX_CUST_RELA_DATE on test; > explain select * from test where updated_date > '2023-12-31 23:50:00' and updated_date<'2024-01-01 00:00:00'; +---------------------+--------------+-----------+---------------+----------------------------------------------------------------------------------------------------------------+ | id | estRows | task | access object | operator info | +---------------------+--------------+-----------+---------------+----------------------------------------------------------------------------------------------------------------+ | TableReader_7 | 2333.21 | root | | data:Selection_6 | | └─Selection_6 | 2333.21 | cop[tikv] | | gt(test.test.updated_date, 2023-12-31 23:50:00.000000), lt(test.test.updated_date, 2024-01-01 00:00:00.000000) | | └─TableFullScan_5 | 859718933.00 | cop[tikv] | table:test | keep order:false | +---------------------+--------------+-----------+---------------+----------------------------------------------------------------------------------------------------------------+ ``` The issue actually occurs at this point: we found that datetime is first specially encoded into a uint64, and this uint64 is different from a timestamp — it cannot be directly subtracted. <img width="1116" alt="image" src="https://github.com/user-attachments/assets/9ce477c6-c263-40f4-a315-dfb4b1b0e414" /> by ```codec.EncodeKey``` -> ```encode``` -> ```EncodeMySQLTime``` -> ```ToPackedUint```, ```datetime``` will become a ```uint64```. ``` // ToPackedUint encodes Time to a packed uint64 value. // // 1 bit 0 // 17 bits year*13+month (year 0-9999, month 0-12) // 5 bits day (0-31) // 5 bits hour (0-23) // 6 bits minute (0-59) // 6 bits second (0-59) // 24 bits microseconds (0-999999) // // Total: 64 bits = 8 bytes // // 0YYYYYYY.YYYYYYYY.YYdddddh.hhhhmmmm.mmssssss.ffffffff.ffffffff.ffffffff ``` If we only use this uint64 to compare values, it can still be manageable. However, directly using it to compare the difference in magnitude will cause issues. but PR is only for single-column index. ### Check List Tests - [x] Unit test - [ ] Integration test - [ ] Manual test (add detailed scripts or steps below) - [ ] No need to test > - [ ] I checked and no code files have been changed. > Side effects - [ ] Performance regression: Consumes more CPU - [ ] Performance regression: Consumes more Memory - [ ] Breaking backward compatibility Documentation - [ ] Affects user behaviors - [ ] Contains syntax changes - [ ] Contains variable changes - [ ] Contains experimental features - [ ] Changes MySQL compatibility ### Release note Please refer to [Release Notes Language Style Guide](https://pingcap.github.io/tidb-dev-guide/contribute-to-tidb/release-notes-style-guide.html) to write a quality release note. ```release-note None ```

心在飞翔 · 2025 年1 月 14 日 07:25

确实是跨月的时候有发生这个

心在飞翔 · 2025 年1 月 14 日 07:26

这个bug哪个版本能解决呢

有猫万事足 · 2025 年1 月 14 日 11:00

追踪第二个链接，看什么时候close。现在还是open的，合并代码没成功。