starrocks 存算分离 数据到一定量级， limit 不带条件查询不出来数据
·加上一些条件就查询出来了。
第一个这个 弄个profile看看
mysql> explain select * from db_2000003485.tb_2000003485_starrocks_test_mix_20__0f648f46_hGbti2 limit 10 ;
±----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Explain String |
±----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| PLAN FRAGMENT 0 |
| OUTPUT EXPRS:1: timestamp | 2: id | 3: c_X5JNR4 | 4: c_bx1Y34 | 5: c_KfjqeH | 6: c_lMJp7C | 7: c_YMZODv | 8: c_682MBR | 9: c_Sl5QIZ | 10: c_l58pZ3 | 11: c_uUH3un | 12: c_ZCZeEP | 13: c_HArj7G | 14: c_Q5czRT | 15: c_7s7WzF | 16: c_oVjvmw | 17: c_z3Gag3 | 18: c_kR3t5Y | 19: c_9ey2ki | 20: c_mTlUV6 | 21: c_fvP1xM | 22: timestamp | 23: source | 24: path | 25: extra | 26: errors | 27: c_saZk79 | 28: c_E95hVz | 29: c_evAS3D | 30: c_HZ3wYO | 31: c_BuHaX2 | 32: c_aRBoP9 | 33: c_fUzEih | 34: c_LLrvOz | 35: c_RFk8U8 | 36: c_TSc2GP | 37: c_Cqz6sB | 38: c_HXmJHP | 39: c_SdJS3D | 40: c_URY5t4 | 41: c_kwCesS | 42: c_t1Pu4B | 43: c_2qSgnk | 44: c_Z75PvM | 45: c_DUybRL | 46: c_Kml82l | 47: c_3GIXeD | 48: c_VJYCFE | 49: c_lpVcxp | 50: c_CQiWC4 | 51: c_pmePmR | 52: c_Hs4Jgb | 53: c_Rr6mph | 54: c_HkMODC |
| PARTITION: UNPARTITIONED |
| |
| RESULT SINK |
| |
| 1:EXCHANGE |
| limit: 10 |
| |
| PLAN FRAGMENT 1 |
| OUTPUT EXPRS: |
| PARTITION: RANDOM |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 01 |
| UNPARTITIONED |
| |
| 0:OlapScanNode |
| TABLE: tb_2000003485_starrocks_test_mix_20__0f648f46_hGbti2 |
| PREAGGREGATION: ON |
| partitions=1/9 |
| rollup: tb_2000003485_starrocks_test_mix_20__0f648f46_hGbti2 |
| tabletRatio=1/0 |
| tabletList=21169 |
| cardinality=10 |
| avgRowSize=3298.4973 |
| limit: 10 |
±----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
27 rows in set (0.00 sec)
辛苦帮忙看下
profile:
mysql> analyze profile from ‘36cda89a-0b88-11ef-a835-fa163e208230’ ;
±---------------------------------------------------------------------------------------------------------------------------------------------------------+
| Explain String |
±---------------------------------------------------------------------------------------------------------------------------------------------------------+
| Summary |
| QueryId: 36cda89a-0b88-11ef-a835-fa163e208230 |
| Version: 785d145-785d145 |
| State: Finished |
| TotalTime: 10ms |
| ExecutionTime: 2.354ms [Scan: 0ns (0.00%), Network: 307.913us (13.08%), ResultDeliverTime: 0ns (0.00%), ScheduleTime: 103.395us (4.39%)] |
| CollectProfileTime: 3ms |
| FrontendProfileMergeTime: 1.773ms |
| QueryPeakMemoryUsage: 17.094 KB, QueryAllocatedMemoryUsage: 17.602 KB |
| Top Most Time-consuming Nodes: |
| 1. EXCHANGE (id=1) [GATHER]: 400.006us (85.46%) |
| 2. RESULT_SINK: 37.401us (7.99%) |
| 3. OLAP_SCAN (id=0) : 30.629us (6.54%) |
| Top Most Memory-consuming Nodes: |
| 1. EXCHANGE (id=1) [GATHER]: 4.313 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.688 KB, InstanceAllocatedMemoryUsage: 16.077 KB |
| │ PrepareTime: 256.772us |
| └──RESULT_SINK |
| │ TotalTime: 37.401us (7.99%) [CPUTime: 37.401us] |
| │ OutputRows: 0 |
| │ SinkType: MYSQL_PROTOCAL |
| └──EXCHANGE (id=1) [GATHER] |
| Estimates: [row: 10, cpu: 0.00, memory: 0.00, network: 0.00, cost: 82462.43] |
| TotalTime: 400.006us (85.46%) [CPUTime: 92.093us, NetworkTime: 307.913us] |
| OutputRows: 0 |
| PeakMemory: 4.313 KB, AllocatedMemory: 7.327 KB |
| Detail Timers: |
| OverallTime: 313.129us |
| RpcAvgTime: 307.913us |
| WaitTime: 358.830us |
| |
| Fragment 1 |
| │ BackendNum: 1 |
| │ InstancePeakMemoryUsage: 1.336 KB, InstanceAllocatedMemoryUsage: 1.522 KB |
| │ PrepareTime: 146.349us |
| └──DATA_STREAM_SINK (id=1) |
| │ PartitionType: UNPARTITIONED |
| └──OLAP_SCAN (id=0) |
| Estimates: [row: 10, cpu: 32984.97, memory: 0.00, network: 0.00, cost: 16492.49] |
| TotalTime: 30.629us (6.54%) [CPUTime: 30.629us] |
| OutputRows: 0 |
| Table: : tb_2000003485_starrocks_test_mix_20__0f648f46_hGbti2 |
| |
±---------------------------------------------------------------------------------------------------------------------------------------------------------+
52 rows in set (0.00 sec)
再提供一个信息，建表时有分区，但是Profile中PartitionType:UNPARTITIONED，不确定是否符合预期
PARTITION BY RANGE(timestamp)
(PARTITION p_202404280800 VALUES [(“2024-04-27 06:00:00”), (“2024-04-28 08:00:00”)),
PARTITION p_202404291000 VALUES [(“2024-04-28 08:00:00”), (“2024-04-29 10:00:00”)),
PARTITION p_202404301200 VALUES [(“2024-04-29 10:00:00”), (“2024-04-30 12:00:00”)),
PARTITION p_202405011400 VALUES [(“2024-04-30 12:00:00”), (“2024-05-01 14:00:00”)),
PARTITION p_202405021600 VALUES [(“2024-05-01 14:00:00”), (“2024-05-02 16:00:00”)),
PARTITION p_202405031800 VALUES [(“2024-05-02 16:00:00”), (“2024-05-03 18:00:00”)),
PARTITION p_202405040600 VALUES [(“2024-05-03 18:00:00”), (“2024-05-04 06:00:00”)),
PARTITION p_202405050600 VALUES [(“2024-05-04 06:00:00”), (“2024-05-05 06:00:00”)),
PARTITION p_202405081200 VALUES [(“2024-05-05 06:00:00”), (“2024-05-08 12:00:00”)))
DISTRIBUTED BY RANDOM