starrocks 存算分离集群，数据上量后使用不带条件的 limit 查询不到数据

ffff · 2024年05月7日 08:06

【详述】
select * from tb_2000003485_starrocks_test_hedwig__25a340a0_kwgV5A_20240506_debug limit 2 ;
执行这条 sql 不出结果

但是带了条件就可以查询出结果

【背景】
建表sql

USE db_2000003485;
CREATE TABLE tb_2000003485_starrocks_test_hedwig__25a340a0_kwgV5A_20240506_debug (
timestamp datetime NULL COMMENT “”,
__id__ bigint(20) NULL COMMENT “”,
c_p4Pgok varchar(1048576) NULL COMMENT “”,
c_Qv4rXi varchar(1048576) NULL COMMENT “”,
c_CW418U varchar(1048576) NULL COMMENT “”,
c_pvm4Wz bigint(20) NULL COMMENT “”,
c_fm34at varchar(1048576) NULL COMMENT “”,
c_GBZpyD varchar(1048576) NULL COMMENT “”,
_timestamp_ bigint(20) NULL COMMENT “”,
__source__ varchar(1024) NULL COMMENT “”,
__path__ varchar(1024) NULL COMMENT “”,
__extra__ json NULL COMMENT “”,
__errors__ json NULL COMMENT “”,
c_6Ntzu2 varchar(1048576) NULL COMMENT “”,
c_vTWSHi varchar(1048576) NULL COMMENT “”,
c_qzqPfa varchar(1048576) NULL COMMENT “”,
c_udle72 varchar(1048576) NULL COMMENT “”,
c_3dwxXy varchar(1048576) NULL COMMENT “”,
c_wVEKzk varchar(1048576) NULL COMMENT “”,
c_HRczUh varchar(1048576) NULL COMMENT “”,
c_y9ApYv varchar(1048576) NULL COMMENT “”,
c_VEeLG4 varchar(1048576) NULL COMMENT “”,
c_QhjAna varchar(1048576) NULL COMMENT “”,
c_owFDBY boolean NULL COMMENT “”,
c_vyvdCn boolean NULL COMMENT “”,
c_GieBTJ boolean NULL COMMENT “”,
c_FMpB9l boolean NULL COMMENT “”,
c_6mPLPS boolean NULL COMMENT “”,
c_Q9x3Xr bigint(20) NULL COMMENT “”,
c_iVpCoY bigint(20) NULL COMMENT “”,
c_vdPT2W bigint(20) NULL COMMENT “”,
c_KOpeSX bigint(20) NULL COMMENT “”,
c_gPv1DZ bigint(20) NULL COMMENT “”,
c_wyeTer double NULL COMMENT “”,
c_DZL1HI double NULL COMMENT “”,
c_ub3uxR double NULL COMMENT “”,
c_h2teLL double NULL COMMENT “”,
c_YWmhtq double NULL COMMENT “”,
c_U8bijl datetime NULL COMMENT “”,
c_aByaKX datetime NULL COMMENT “”,
c_2NijxK datetime NULL COMMENT “”
) ENGINE=OLAP
DUPLICATE KEY(timestamp, __id__)
COMMENT “OLAP”
PARTITION BY RANGE(timestamp)
(PARTITION p_202405071800 VALUES [(“2024-05-04 12:00:00”), (“2024-05-07 18:00:00”)))
DISTRIBUTED BY RANDOM
PROPERTIES (
“replication_num” = “1”,
“bucket_size” = “31457280”,
“datacache.enable” = “true”,
“storage_volume” = “builtin_storage_volume”,
“enable_async_write_back” = “false”,
“enable_persistent_index” = “false”,
“compression” = “LZ4”
);

分区信息

mysql> show partitions from db_2000003485.tb_2000003485_starrocks_test_hedwig__25a340a0_kwgV5A_20240506_debug ;
±------------±---------------±---------------±---------------±------------±-------±-------------±-----------------------------------------------------------------------------------------------------±----------------±--------±---------±---------±----------------±-----------±------±------±------+
| PartitionId | PartitionName | CompactVersion | VisibleVersion | NextVersion | State | PartitionKey | Range | DistributionKey | Buckets | DataSize | RowCount | EnableDataCache | AsyncWrite | AvgCS | P50CS | MaxCS |
±------------±---------------±---------------±---------------±------------±-------±-------------±-----------------------------------------------------------------------------------------------------±----------------±--------±---------±---------±----------------±-----------±------±------±------+
| 22174 | p_202405071800 | 7 | 10 | 11 | NORMAL | timestamp | [types: [DATETIME]; keys: [2024-05-04 12:00:00]; …types: [DATETIME]; keys: [2024-05-07 18:00:00]; ) | ALL KEY | 6 | 52.1MB | 12148172 | true | false | 3.50 | 7.00 | 7.00 |
| 22181 | p_202405071800 | 8 | 10 | 11 | NORMAL | timestamp | [types: [DATETIME]; keys: [2024-05-04 12:00:00]; …types: [DATETIME]; keys: [2024-05-07 18:00:00]; ) | ALL KEY | 6 | 48.2MB | 11647630 | true | false | 2.50 | 5.00 | 5.00 |
| 22155 | p_202405071800 | 0 | 4 | 5 | NORMAL | timestamp | [types: [DATETIME]; keys: [2024-05-04 12:00:00]; …types: [DATETIME]; keys: [2024-05-07 18:00:00]; ) | ALL KEY | 6 | 230MB | 3230504 | true | false | 1.00 | 1.00 | 1.00 |
±------------±---------------±---------------±---------------±------------±-------±-------------±-----------------------------------------------------------------------------------------------------±----------------±--------±---------±---------±----------------±-----------±------±------±------+
3 rows in set (0.01 sec)

Tablet

mysql> show tablet from db_2000003485.tb_2000003485_starrocks_test_hedwig__25a340a0_kwgV5A_20240506_debug ;
±---------±----------±---------±---------+
| TabletId | BackendId | DataSize | RowCount |
±---------±----------±---------±---------+
| 22158 | [] | 38MB | 538449 |
| 22159 | [] | 38.5MB | 538397 |
| 22160 | [] | 38.6MB | 538376 |
| 22161 | [] | 38.3MB | 538435 |
| 22162 | [] | 38MB | 538426 |
| 22163 | [] | 38.3MB | 538421 |
| 22175 | [] | 483.9KB | 83433 |
| 22176 | [] | 18MB | 3966094 |
| 22177 | [] | 469.5KB | 83423 |
| 22178 | [] | 16.2MB | 3965813 |
| 22179 | [] | 577.9KB | 83419 |
| 22180 | [] | 16.4MB | 3965990 |
| 22182 | [] | 15.5MB | 3882602 |
| 22183 | [] | 0B | 0 |
| 22184 | [] | 14.7MB | 3882503 |
| 22185 | [] | 0B | 0 |
| 22186 | [] | 17.9MB | 3882525 |
| 22187 | [] | 0B | 0 |
±---------±----------±---------±---------+
18 rows in set (0.06 sec)

【业务影响】 严重影响使用

【是否存算分离】 是

【StarRocks版本】 3.2.2

【集群规模】 3fe + 6cn（f存算分离）

【机器信息】 官方3.2.2版本 docker 镜像

【联系方式】 存算分离群-Seema 存算分离群-南忆

【附件】

查询分析

mysql> explain select * from tb_2000003485_starrocks_test_hedwig__25a340a0_kwgV5A_20240506_debug limit 2 ;
±-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Explain String |
±-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| PLAN FRAGMENT 0 |
| OUTPUT EXPRS:1: timestamp | 2: id | 3: c_p4Pgok | 4: c_Qv4rXi | 5: c_CW418U | 6: c_pvm4Wz | 7: c_fm34at | 8: c_GBZpyD | 9: timestamp | 10: source | 11: path | 12: extra | 13: errors | 14: c_6Ntzu2 | 15: c_vTWSHi | 16: c_qzqPfa | 17: c_udle72 | 18: c_3dwxXy | 19: c_wVEKzk | 20: c_HRczUh | 21: c_y9ApYv | 22: c_VEeLG4 | 23: c_QhjAna | 24: c_owFDBY | 25: c_vyvdCn | 26: c_GieBTJ | 27: c_FMpB9l | 28: c_6mPLPS | 29: c_Q9x3Xr | 30: c_iVpCoY | 31: c_vdPT2W | 32: c_KOpeSX | 33: c_gPv1DZ | 34: c_wyeTer | 35: c_DZL1HI | 36: c_ub3uxR | 37: c_h2teLL | 38: c_YWmhtq | 39: c_U8bijl | 40: c_aByaKX | 41: c_2NijxK |
| PARTITION: UNPARTITIONED
| RESULT SINK
| 1:EXCHANGE |
| limit: 2
| PLAN FRAGMENT 1 |
| OUTPUT EXPRS: |
| PARTITION: RANDOM
| STREAM DATA SINK |
| EXCHANGE ID: 01 |
| UNPARTITIONED
| 0:OlapScanNode |
| TABLE: tb_2000003485_starrocks_test_hedwig__25a340a0_kwgV5A_20240506_debug |
| PREAGGREGATION: ON
| partitions=1/1
| rollup: tb_2000003485_starrocks_test_hedwig__25a340a0_kwgV5A_20240506_debug |
| tabletRatio=1/0 |
| tabletList=22175 |
| cardinality=2 |
| avgRowSize=41.0 |
| limit: 2 |
27 rows in set (0.01 sec)

Profile

mysql> analyze profile from ‘737fb6bd-0c3e-11ef-8172-fa163e526b0c’ ;
| Summary |
| QueryId: 737fb6bd-0c3e-11ef-8172-fa163e526b0c |
| Version: UNKNOWN-UNKNOWN |
| State: Finished |
| TotalTime: 32ms |
| ExecutionTime: 2.631ms [Scan: 0ns (0.00%), Network: 382.902us (14.55%), ResultDeliverTime: 0ns (0.00%), ScheduleTime: 122.647us (4.66%)] |
| CollectProfileTime: 4ms |
| FrontendProfileMergeTime: 3.580ms |
| QueryPeakMemoryUsage: 22.344 KB, QueryAllocatedMemoryUsage: 22.366 KB |
| Top Most Time-consuming Nodes: |
| 1. EXCHANGE (id=1) [GATHER]: 623.755us (85.01%) |
| 2. RESULT_SINK: 60.631us (8.26%) |
| 3. OLAP_SCAN (id=0) : 49.322us (6.72%) |
| Top Most Memory-consuming Nodes: |
| 1. EXCHANGE (id=1) [GATHER]: 201.539 KB |
| NonDefaultVariables: |
| consistent_hash_virtual_number: 128 -> 32 |
| enable_adaptive_sink_dop: false -> true |
| enable_connector_adaptive_io_tasks: true -> false |
| enable_profile: false -> true |
| io_tasks_per_scan_operator: 4 -> 32 |
| parallel_fragment_exec_instance_num: 1 -> 8 |
| Fragment 0 |
| │ BackendNum: 1 |
| │ InstancePeakMemoryUsage: 1.938 KB, InstanceAllocatedMemoryUsage: 16.280 KB |
| │ PrepareTime: 233.041us |
| └──RESULT_SINK |
| │ TotalTime: 60.631us (8.26%) [CPUTime: 60.631us] |
| │ OutputRows: 0 |
| │ SinkType: MYSQL_PROTOCAL |
| └──EXCHANGE (id=1) [GATHER] |
| Estimates: [row: 2, cpu: ?, memory: ?, network: ?, cost: 12305.0] |
| TotalTime: 623.755us (85.01%) [CPUTime: 240.853us, NetworkTime: 382.902us] |
| OutputRows: 0 |
| PeakMemory: 201.539 KB, AllocatedMemory: 207.616 KB |
| Detail Timers: |
| OverallTime: 379.256us |
| RpcAvgTime: 382.902us |
| WaitTime: 416.006us |
| |
| Fragment 1 |
| │ BackendNum: 1 |
| │ InstancePeakMemoryUsage: 5.897 KB, InstanceAllocatedMemoryUsage: 6.086 KB |
| │ PrepareTime: 172.654us |
| └──DATA_STREAM_SINK (id=1) |
| │ PartitionType: UNPARTITIONED |
| └──OLAP_SCAN (id=0) |
| Estimates: [row: 2, cpu: ?, memory: ?, network: ?, cost: 2461.0] |
| TotalTime: 49.322us (6.72%) [CPUTime: 49.322us] |
| OutputRows: 0 |
| Table: : tb_2000003485_starrocks_test_hedwig__25a340a0_kwgV5A_20240506_debug

xfworld · 2024年05月16日 04:19

mysql> explain select * from tb_2000003485_starrocks_test_hedwig__25a340a0_kwgV5A_20240506_debug limit 2 ;

这个语句和你图中的语句对应的表不一样呢？

通过 explain 看看这两个SQL 语句的差异，对比下

ffff · 2024年05月17日 02:25

截图的sql用了老图，这两个sql本质是一样的，都查询不出来数据，不要看图，就看

select * from tb_2000003485_starrocks_test_hedwig__25a340a0_kwgV5A_20240506_debug limit 2 ;
这个·sql吧，下面的查询计划 profile 都是这个sql的