查询越来越慢, 内存page out和page free非常大

[liujiangithub]

Performance Issue
客户端读取循环读取数据，读取的越来越慢。

Problem Description
1 构建一个超级表，大概有1000个子表，80多万条数据
2 两个客户端程序使用同样的sql语句做分页查询，按时间正序，单次查询500条，每次查询成功后偏移量加500
3 虚存2G多的客户端性能正常(单次查询最大300毫秒左右，与taos命令执行结果一致)，虚存3.5G的客户端程序越查询越慢(大概5分钟后单次查询就慢到了4秒)，到后面累积单次查询都达到7秒后停止了测试。分别重复验证了多次，均保持同样的现象
4 通过系统命令查看出问题的客户端执行时，page out很大

To Reproduce
Steps to reproduce the behavior:

1. Database parameters used:

BUFFER 256 CACHESIZE 1 CACHEMODEL 'last_row' COMP 2 DURATION 10080m WAL_FSYNC_PERIOD 3000 MAXROWS 4096 MINROWS 100 STT_TRIGGER 1 KEEP 263520m,263520m,263520m PAGES 256 PAGESIZE 4 PRECISION 'ms' REPLICA 1 WAL_LEVEL 1 VGROUPS 2 SINGLE_STABLE 0 TABLE_PREFIX 0 TABLE_SUFFIX 0 TSDB_PAGESIZE 4 WAL_RETENTION_PERIOD 3600 WAL_RETENTION_SIZE 0 KEEP_TIME_OFFSET 0 ENCRYPT_ALGORITHM 'none' S3_CHUNKSIZE 262144 S3_KEEPLOCAL 5256000m S3_COMPACT 0

2. Verbs used: Insert/Import/Select?
   select data_0 as data_0,data_1 as data_1,data_2 as data_2,data_3 as data_3,data_4 as data_4,data_5 as data_5,data_6 as data_6,data_7 as data_7,data_8 as data_8,data_9 as data_9,data_10 as data_10,data_11 as data_11,data_12 as data_12,data_13 as data_13,data_14 as data_14,data_15 as data_15,data_16 as data_16,data_17 as data_17,data_18 as data_18,data_19 as data_19,data_20 as data_20,data_21 as data_21,data_22 as data_22,data_23 as data_23,data_24 as data_24,data_25 as data_25,data_26 as data_26,data_27 as data_27,data_28 as data_28,data_29 as data_29,data_30 as data_30,data_31 as data_31,data_32 as data_32,data_33 as data_33,data_34 as data_34,data_35 as data_35,data_36 as data_36,data_37 as data_37,data_38 as data_38,data_39 as data_39,data_40 as data_40,data_41 as data_41,data_42 as data_42,data_43 as data_43,data_44 as data_44,data_45 as data_45,data_46 as data_46,data_47 as data_47,data_48 as data_48,data_49 as data_49 from iotmodel_1821292163079602176 where timestamp >= '2024-07-16 06:00:52.201' limit $offset,500(offset从0开始一一直到80万后重置为0)

3. Describe the total amount of data 大概有1000个子表，80多万条数据

4. Observed performance vs. expected performance 希望查询性能能稳定，不是越来越慢。
   Screenshots
   If applicable, add screenshots to help explain your problem.
   有问题客户端运行时的内存page
   

Environment (please complete the following information):

• OS: [e.g. CentOS 7.0] ubuntu18.04
• Memory 16G, CPU 8核心16线程, current Disk Space 468G，使用102G
• TDengine Version [e.g. 1.6.1.7] tdengine3.3.1.0

Additional Context
1 测试过程中短连接和长连接两种方式都试过，现象完全一样
2 测试过程中没有内存泄露，没有多余的其它操作，整个系统只执行该读取动作。
3 当出现问题的客户端越来越慢时，正常的客户端和taos命令查询速度仍然是正常的。
4 两种客户端查询的代码和sql语句完全相同。
5 整个系统的cpu占用不到一半。