【详述】联表查询大概率出现超时
【是否存算分离】否
【StarRocks版本】3.2.13
【集群规模】3fe(1 leader+2follower)+3be(fe与be混部)
联表查询时极大概率超时,报StarRocks planner use long time 10000 ms in logical phase
starrocks查询语句:
SELECT a
.integer_01
AS a.integer_01
FROM (SELECT query_data_olap
.__time
, query_data_olap
.count
, query_data_olap
.string_01
, query_data_olap
.string_02
, query_data_olap
.string_03
, query_data_olap
.string_04
, query_data_olap
.string_05
, query_data_olap
.string_06
, query_data_olap
.string_07
, query_data_olap
.string_08
, query_data_olap
.string_09
, query_data_olap
.string_10
, query_data_olap
.integer_01
, query_data_olap
.integer_02
, query_data_olap
.integer_03
, query_data_olap
.integer_04
, query_data_olap
.integer_05
, query_data_olap
.integer_06
, query_data_olap
.integer_07
, query_data_olap
.integer_08
, query_data_olap
.integer_09
, query_data_olap
.integer_10
, query_data_olap
.long_01
, query_data_olap
.long_02
, query_data_olap
.long_03
, query_data_olap
.long_04
, query_data_olap
.long_05
, query_data_olap
.long_06
, query_data_olap
.long_07
, query_data_olap
.long_08
, query_data_olap
.long_09
, query_data_olap
.long_10
, query_data_olap
.float_01
, query_data_olap
.float_02
, query_data_olap
.float_03
, query_data_olap
.float_04
, query_data_olap
.float_05
, query_data_olap
.float_06
, query_data_olap
.float_07
, query_data_olap
.float_08
, query_data_olap
.float_09
, query_data_olap
.float_10
, query_data_olap
.double_01
, query_data_olap
.double_02
, query_data_olap
.double_03
, query_data_olap
.double_04
, query_data_olap
.double_05
, query_data_olap
.double_06
, query_data_olap
.double_07
, query_data_olap
.double_08
, query_data_olap
.double_09
, query_data_olap
.double_10
FROM dte_druid_catalog
.default_db
.ODAEDATASET__DEFAULT_query_data_olap__DEFAULT
AS query_data_olap
) AS a
INNER JOIN (SELECT query_data_jdbc
.STRING_01
AS STRING_01
, query_data_jdbc
.STRING_02
AS STRING_02
, query_data_jdbc
.STRING_03
AS STRING_03
, query_data_jdbc
.STRING_04
AS STRING_04
, query_data_jdbc
.STRING_05
AS STRING_05
, query_data_jdbc
.INTEGER_01
AS INTEGER_01
, query_data_jdbc
.INTEGER_02
AS INTEGER_02
, query_data_jdbc
.INTEGER_03
AS INTEGER_03
, query_data_jdbc
.INTEGER_04
AS INTEGER_04
, query_data_jdbc
.INTEGER_05
AS INTEGER_05
FROM dte_jdbc_catalog_query_data_jdbc_connection
._default
.QUERY_DATA_JDBC
AS query_data_jdbc
) AS b
ON a
.integer_01
= b
.INTEGER_01
WHERE a
.__time
>= 1745982000000 AND a
.__time
<= 1745982299999 GROUP BY a
.integer_01
执行报错:
ERROR 1064 (HY000): StarRocks planner use long time 10000 ms in logical phase, This probably because 1. FE Full GC, 2. Hive external table fetch metadata took a long time, 3. The SQL is very com
olex. You could 1. adjust FE JVM config, 2. try query again, 3. enlarge new_planner_optimize_timeout session variable
目前通过query profile定位到是starrocks在计划阶段应该有一个步骤是会尝试用已有的物化视图匹配目标物理数据集,从而提升查询效率。
在把查询超时限制放开之后,查询总耗时44秒里面,有41秒是在计划阶段,并且在匹配物化视图的时候消耗了大量时间
IsProfileAsync: true
Planner:
Total[1] 41s742ms
– Analyzer[1] 4s972ms
— Lock[1] 0
— AnalyzeTable[2] 52ms
— AnalyzeTable[2] 4s917ms
– Transformer[1] 2ms
– Optimizer[1] 33s517ms
— MVPreprocess[1] 33s509ms
— MVChooseCandidates[1] 33s506ms
— MVGenerateMVPlan[1] 0
— MVValidateMV[1] 0
— MVProcessWithView[1] 0
– RuleBaseOptimize[1] 3ms
– CostBaseOptimize[1] 2ms
– PhysicalRewrite[1] 0
– PlanValidate[1]
— InputDependenciesChecker[1] 0
— TypeChecker[1] 0
— CTEChecker[1] 0
— ColumnReuseChecker[1] 0
– ExecPlanBuild[1] 4ms
— Pending[1] 0
– Prepare[1] 0
– Deploy[1] 15ms
— DeployInternalTime[1] 15ms
— DeployLockSerializeConcurrentTime[3] 1ms
— DeployStageByStageTime[9] 1ms
— DeployAsyncSendTime[4] 0
— DeployAsyncTime[9] 11ms
DeployDataSize: 12784
Reason:
MV rewrite fail for dte_DEFAULT_test_scan_all_add_test_2: MV contains extra tables besides FK-PK
MV rewrite fail for dte_DEFAULT_test_scan_all_IMF_test_2: MV contains extra tables besides FK-PK
MV rewrite fail for dte_DEFAULT_test_scan_all_1h_test_2: MV contains extra tables besides FK-PK
MV rewrite fail for dte_DEFAULT_test_scan_all_1y_test_2: MV contains extra tables besides FK-PK
MV rewrite fail for dte_DEFAULT_MaxTimeOut_11ge_test_2: MV contains extra tables besides FK-PK
MV rewrite fail for dte_DEFAULT_TimeOutIllege_test_2: MV contains extra tables besides FK-PK
MV rewrite fail for dte_DEFAULT_test_scan_all_time_test_2: MV contains extra tables besides FK-PK
MV rewrite fail for dte_DEFAULT_test_scan_all_time_test_2: MV contains extra tables besides FK-PK
MV rewrite fail for dte_DEFAULT_group_concat_default_sep_file_bbbKey: MV contains extra tables besides FK-PK
MV rewrite fail for dte_DEFAULT_test_scan_all_1d_test_2: MV contains extra tables besides FK-PK
MV rewrite fail for dte_DEFAULT_JSON_QUERYOlap_test: MV contains extra tables besides FK-PK