【详述】开启CBO使用内存对比
【背景】无?
【业务影响】 2.3.7 内存使用exec_mem_limit ,BE 直接挂
【StarRocks版本】例如:2.3.7
【集群规模】例如:3fe(1 follower+2observer)+5be(fe与be混部)
【SQL】
概要
SELECT COUNT(DISTINCT ((dm_ec_cashloan_risk_diff_sence_monitor
.p_date
>= DATE(‘2022-12-01’)) AND (dm_ec_cashloan_risk_diff_sence_monitor
.p_date
<= DATE(‘2023-03-08’)))) AS ctd_Calculation_250583147382595594_ok
,
COUNT(DISTINCT (CASE WHEN (dm_ec_cashloan_risk_diff_sence_monitor
.af_user_type
= ‘47’) THEN dm_ec_cashloan_risk_diff_sence_monitor
.af_user_type
WHEN (dm_ec_cashloan_risk_diff_sence_monitor
.last_state_name
= ‘SCORE_AFPIEMPTY_CLIK_RECALL’) THEN dm_ec_cashloan_risk_diff_sence_monitor
.af_user_type
ELSE ‘正常’ END)) AS ctd_Calculation_542402341543010329_ok
,
COUNT(DISTINCT dm_ec_cashloan_risk_diff_sence_monitor
.af_user_type
) AS ctd_af_user_type_ok
,
COUNT(DISTINCT dm_ec_cashloan_risk_diff_sence_monitor
.afpi
) AS ctd_afpi_ok
,
COUNT(DISTINCT dm_ec_cashloan_risk_diff_sence_monitor
.all_pass_num
) AS ctd_all_pass_num_ok
,
COUNT(DISTINCT dm_ec_cashloan_risk_diff_sence_monitor
.bf_user_type
) AS ctd_bf_user_type_ok
,
COUNT(DISTINCT dm_ec_cashloan_risk_diff_sence_monitor
.canrepply_cnt
) AS ctd_canrepply_cnt_ok
,
COUNT(DISTINCT dm_ec_cashloan_risk_diff_sence_monitor
.done_num
) AS ctd_done_num_ok
,
COUNT(DISTINCT dm_ec_cashloan_risk_diff_sence_monitor
.is_risk_platform
) AS ctd_is_risk_platform_ok
,
COUNT(DISTINCT dm_ec_cashloan_risk_diff_sence_monitor
.last_state_name
) AS ctd_last_state_name (复制)_323133334705078295_ok
,
COUNT(DISTINCT dm_ec_cashloan_risk_diff_sence_monitor
.last_state_name
) AS ctd_last_state_name_ok
,
COUNT(DISTINCT dm_ec_cashloan_risk_diff_sence_monitor
.main_rank_level
) AS ctd_main_rank_level_ok
,
COUNT(DISTINCT dm_ec_cashloan_risk_diff_sence_monitor
.main_rank_name
) AS ctd_main_rank_name_ok
,
COUNT(DISTINCT dm_ec_cashloan_risk_diff_sence_monitor
.media_source
) AS ctd_media_source_ok
,
COUNT(DISTINCT dm_ec_cashloan_risk_diff_sence_monitor
.operation_level
) AS ctd_operation_level_ok
,
COUNT(DISTINCT dm_ec_cashloan_risk_diff_sence_monitor
.order_seq
) AS ctd_order_seq_ok
,
COUNT(DISTINCT dm_ec_cashloan_risk_diff_sence_monitor
.pass_num
) AS ctd_pass_num_ok
,
COUNT(DISTINCT dm_ec_cashloan_risk_diff_sence_monitor
.risk_flow_id
) AS ctd_risk_flow_id_ok
,
COUNT(DISTINCT dm_ec_cashloan_risk_diff_sence_monitor
.risk_platform_type_name
) AS ctd_risk_platform_type_name_ok
,
COUNT(DISTINCT dm_ec_cashloan_risk_diff_sence_monitor
.second_canrepply_cnt
) AS ctd_second_canrepply_cnt_ok
,
COUNT(DISTINCT dm_ec_cashloan_risk_diff_sence_monitor
.second_done_num
) AS ctd_second_done_num_ok
,
COUNT(DISTINCT dm_ec_cashloan_risk_diff_sence_monitor
.second_pass_num
) AS ctd_second_pass_num_ok
,
COUNT(DISTINCT dm_ec_cashloan_risk_diff_sence_monitor
.term_time_period
) AS ctd_term_time_period_ok
,
COUNT(DISTINCT dm_ec_cashloan_risk_diff_sence_monitor
.terms
) AS ctd_terms_ok
,
COUNT(DISTINCT dm_ec_cashloan_risk_diff_sence_monitor
.tixian_1_sorce_num
) AS ctd_tixian_1_sorce_num_ok
,
COUNT(DISTINCT dm_ec_cashloan_risk_diff_sence_monitor
.tixian_7_sorce_num
) AS ctd_tixian_7_sorce_num_ok
,
COUNT(DISTINCT dm_ec_cashloan_risk_diff_sence_monitor
.user_num
) AS ctd_user_num_ok
FROM ec_report.dm_ec_cashloan_risk_diff_sence_monitor
HAVING (COUNT(user_num) > 0);
详情:
1、SET GLOBAL cbo_enable_low_cardinality_optimize = true ;
QueryCpuCost: 16s517ms
- QueryMemCost: 15.841GB
profile
Query:
Summary:
- Query ID: 0521dd6c-bf1f-11ed-98f5-00163e30e77c
- Start Time: 2023-03-10 16:39:06
- End Time: 2023-03-10 16:39:22
- Total: 15s902ms
- Query Type: Query
- Query State: EOF
- StarRocks Version: 2.3.7
- User: root
- Default Db: default_cluster:ec_report
- Sql Statement: SELECT COUNT(DISTINCT ((dm_ec_cashloan_risk_diff_sence_monitor
.p_date
>= DATE(‘2022-12-01’)) AND (dm_ec_cashloan_risk_diff_sence_monitor
.p_date
<= DATE(‘2023-03-08’)))) AS ctd_Calculation_250583147382595594_ok
, COUNT(DISTINCT (CASE WHEN (dm_ec_cashloan_risk_diff_sence_monitor
.af_user_type
= ‘47’) THEN dm_ec_cashloan_risk_diff_sence_monitor
.af_user_type
WHEN (dm_ec_cashloan_risk_diff_sence_monitor
.last_state_name
= ‘SCORE_AFPIEMPTY_CLIK_RECALL’) THEN dm_ec_cashloan_risk_diff_sence_monitor
.af_user_type
ELSE ‘正常’ END)) AS ctd_Calculation_542402341543010329_ok
, COUNT(DISTINCT dm_ec_cashloan_risk_diff_sence_monitor
.af_user_type
) AS ctd_af_user_type_ok
, COUNT(DISTINCT dm_ec_cashloan_risk_diff_sence_monitor
.afpi
) AS ctd_afpi_ok
, COUNT(DISTINCT dm_ec_cashloan_risk_diff_sence_monitor
.all_pass_num
) AS ctd_all_pass_num_ok
, COUNT(DISTINCT dm_ec_cashloan_risk_diff_sence_monitor
.bf_user_type
) AS ctd_bf_user_type_ok
, COUNT(DISTINCT dm_ec_cashloan_risk_diff_sence_monitor
.canrepply_cnt
) AS ctd_canrepply_cnt_ok
, COUNT(DISTINCT dm_ec_cashloan_risk_diff_sence_monitor
.done_num
) AS ctd_done_num_ok
, COUNT(DISTINCT dm_ec_cashloan_risk_diff_sence_monitor
.is_risk_platform
) AS ctd_is_risk_platform_ok
, COUNT(DISTINCT dm_ec_cashloan_risk_diff_sence_monitor
.last_state_name
) AS ctd_last_state_name (复制)_323133334705078295_ok
, COUNT(DISTINCT dm_ec_cashloan_risk_diff_sence_monitor
.last_state_name
) AS ctd_last_state_name_ok
, COUNT(DISTINCT dm_ec_cashloan_risk_diff_sence_monitor
.main_rank_level
) AS ctd_main_rank_level_ok
, COUNT(DISTINCT dm_ec_cashloan_risk_diff_sence_monitor
.main_rank_name
) AS ctd_main_rank_name_ok
, COUNT(DISTINCT dm_ec_cashloan_risk_diff_sence_monitor
.media_source
) AS ctd_media_source_ok
, COUNT(DISTINCT dm_ec_cashloan_risk_diff_sence_monitor
.operation_level
) AS ctd_operation_level_ok
, COUNT(DISTINCT dm_ec_cashloan_risk_diff_sence_monitor
.order_seq
) AS ctd_order_seq_ok
, COUNT(DISTINCT dm_ec_cashloan_risk_diff_sence_monitor
.pass_num
) AS ctd_pass_num_ok
, COUNT(DISTINCT dm_ec_cashloan_risk_diff_sence_monitor
.risk_flow_id
) AS ctd_risk_flow_id_ok
, COUNT(DISTINCT dm_ec_cashloan_risk_diff_sence_monitor
.risk_platform_type_name
) AS ctd_risk_platform_type_name_ok
, COUNT(DISTINCT dm_ec_cashloan_risk_diff_sence_monitor
.second_canrepply_cnt
) AS ctd_second_canrepply_cnt_ok
, COUNT(DISTINCT dm_ec_cashloan_risk_diff_sence_monitor
.second_done_num
) AS ctd_second_done_num_ok
, COUNT(DISTINCT dm_ec_cashloan_risk_diff_sence_monitor
.second_pass_num
) AS ctd_second_pass_num_ok
, COUNT(DISTINCT dm_ec_cashloan_risk_diff_sence_monitor
.term_time_period
) AS ctd_term_time_period_ok
, COUNT(DISTINCT dm_ec_cashloan_risk_diff_sence_monitor
.terms
) AS ctd_terms_ok
, COUNT(DISTINCT dm_ec_cashloan_risk_diff_sence_monitor
.tixian_1_sorce_num
) AS ctd_tixian_1_sorce_num_ok
, COUNT(DISTINCT dm_ec_cashloan_risk_diff_sence_monitor
.tixian_7_sorce_num
) AS ctd_tixian_7_sorce_num_ok
, COUNT(DISTINCT dm_ec_cashloan_risk_diff_sence_monitor
.user_num
) AS ctd_user_num_ok
FROM ec_report.dm_ec_cashloan_risk_diff_sence_monitor
HAVING (COUNT(1) > 0)
- QueryCpuCost: 16s517ms
- QueryMemCost: 15.841GB
- Variables: parallel_fragment_exec_instance_num=2,pipeline_dop=0
- Collect Profile Time: 9ms
Planner:
- CoordDeliverExec: 30ms / 1
- CoordPrepareExec: 0ms / 1
- Total: 11ms / 1
Execution Profile 0521dd6c-bf1f-11ed-98f5-00163e30e77c:
- ExecutionTotalTime: 16s129ms
Fragment 0:
- BackendAddresses: 172.18.36.98:9060
- BackendNum: 1
- FragmentInstancePrepareTime: 726.191us
- InstanceNum: 1
- MemoryLimit: 24.00 GB
- PeakMemoryUsage: 15.83 GB
Pipeline (id=2):
- ActiveTime: 323.384us
- BlockByInputEmpty: 0
- BlockByOutputFull: 0
- BlockByPrecondition: 0
- DegreeOfParallelism: 1
- DriverPrepareTime: 84.218us
- DriverTotalTime: 15s874ms
- OverheadTime: 0ns
- PendingTime: 15s868ms
- InputEmptyTime: 15s868ms
- FirstInputEmptyTime: 15s868ms
- FollowupInputEmptyTime: 0ns
- OutputFullTime: 0ns
- PendingFinishTime: 0ns
- PreconditionBlockTime: 0ns
- ScheduleCount: 1
- ScheduleTime: 5.771ms
- TotalDegreeOfParallelism: 1
- YieldByPreempt: 0
- YieldByTimeLimit: 0
RESULT_SINK:
CommonMetrics:
- CloseTime: 39.728us
- OperatorTotalTime: 164.661us
- PeakMemoryUsage: 0.00
- PrepareTime: 18.99us
- PullChunkNum: 0
- PullRowNum: 0
- PullTotalTime: 0ns
- PushChunkNum: 1
- PushRowNum: 1
- PushTotalTime: 124.747us
- SetFinishedTime: 44ns
- SetFinishingTime: 142ns
UniqueMetrics:
PROJECT (plan_node_id=6):
CommonMetrics:
- CloseTime: 12.331us
- OperatorTotalTime: 25.517us
- PeakMemoryUsage: 0.00
- PrepareTime: 7.436us
- PullChunkNum: 1
- PullRowNum: 1
- PullTotalTime: 397ns
- PushChunkNum: 1
- PushRowNum: 1
- PushTotalTime: 12.571us
- RuntimeBloomFilterNum: 0
- RuntimeInFilterNum: 0
- SetFinishedTime: 65ns
- SetFinishingTime: 153ns
UniqueMetrics:
- CommonSubExprComputeTime: 415ns
- ExprComputeTime: 6.855us
CHUNK_ACCUMULATE (plan_node_id=5):
CommonMetrics:
- CloseTime: 337ns
- OperatorTotalTime: 2.3us
- PeakMemoryUsage: 0.00
- PrepareTime: 11.996us
- PullChunkNum: 1
- PullRowNum: 1
- PullTotalTime: 185ns
- PushChunkNum: 1
- PushRowNum: 1
- PushTotalTime: 1.35us
- SetFinishedTime: 152ns
- SetFinishingTime: 294ns
UniqueMetrics:
AGGREGATE_BLOCKING_SOURCE (plan_node_id=5):
CommonMetrics:
- CloseTime: 5.649ms
- ConjunctsEvaluate: 0
- ConjunctsInputRows: 1
- ConjunctsOutputRows: 1
- ConjunctsTime: 25.152us
- JoinRuntimeFilterEvaluate: 0
- JoinRuntimeFilterInputRows: 0
- JoinRuntimeFilterOutputRows: 0
- JoinRuntimeFilterTime: 0ns
- OperatorTotalTime: 5.764ms
- PeakMemoryUsage: 0.00
- PrepareTime: 14.464us
- PullChunkNum: 1
- PullRowNum: 1
- PullTotalTime: 114.674us
- PushChunkNum: 0
- PushRowNum: 0
- PushTotalTime: 0ns
- RuntimeBloomFilterNum: 0
- RuntimeInFilterNum: 0
- SetFinishedTime: 113ns
- SetFinishingTime: 207ns
UniqueMetrics:
Pipeline (id=1):
- ActiveTime: 15s698ms
- BlockByInputEmpty: 0
- BlockByOutputFull: 0
- BlockByPrecondition: 0
- DegreeOfParallelism: 1
- DriverPrepareTime: 1.150ms
- DriverTotalTime: 15s868ms
- OverheadTime: 274.959us
- PendingTime: 169.752ms
- InputEmptyTime: 169.752ms
- FirstInputEmptyTime: 169.752ms
- FollowupInputEmptyTime: 0ns
- OutputFullTime: 0ns
- PendingFinishTime: 0ns
- PreconditionBlockTime: 0ns
- ScheduleCount: 16
- ScheduleTime: 433.130us
- TotalDegreeOfParallelism: 1
- YieldByPreempt: 0
- YieldByTimeLimit: 15
AGGREGATE_BLOCKING_SINK (plan_node_id=5):
CommonMetrics:
- CloseTime: 12.828us
- OperatorTotalTime: 15s697ms
- PeakMemoryUsage: 0.00
- PrepareTime: 1.128ms
- PullChunkNum: 0
- PullRowNum: 0
- PullTotalTime: 0ns
- PushChunkNum: 16
- PushRowNum: 16
- PushTotalTime: 15s697ms
- RuntimeBloomFilterNum: 0
- RuntimeInFilterNum: 0
- SetFinishedTime: 384ns
- SetFinishingTime: 1.289us
UniqueMetrics:
- AggregateFunctions: multi_distinct_count(43: count), multi_distinct_count(44: count), multi_distinct_count(45: count), multi_distinct_count(46: count), multi_distinct_count(47: count), multi_distinct_count(48: count), multi_distinct_count(49: count), multi_distinct_count(50: count), multi_distinct_count(51: count), multi_distinct_count(52: count), multi_distinct_count(53: count), multi_distinct_count(54: count), multi_distinct_count(55: count), multi_distinct_count(56: count), multi_distinct_count(57: count), multi_distinct_count(58: count), multi_distinct_count(59: count), multi_distinct_count(60: count), multi_distinct_count(61: count), multi_distinct_count(62: count), multi_distinct_count(63: count), multi_distinct_count(64: count), multi_distinct_count(65: count), multi_distinct_count(66: count), multi_distinct_count(67: count), multi_distinct_count(68: count), count(69: count)
- AggComputeTime: 15s697ms
- ExprComputeTime: 203.117us
- ExprReleaseTime: 608.550us
- GetResultsTime: 66.262us
- HashTableSize: 0
- InputRowCount: 16
- PassThroughRowCount: 0
- ResultAggAppendTime: 0ns
- ResultGroupByAppendTime: 0ns
- ResultIteratorTime: 0ns
- RowsReturned: 0
- StreamingTime: 0ns
LOCAL_EXCHANGE_SOURCE (pseudo_plan_node_id=-100):
CommonMetrics:
- CloseTime: 1.196us
- OperatorTotalTime: 59.383us
- PeakMemoryUsage: 0.00
- PrepareTime: 5.716us
- PullChunkNum: 16
- PullRowNum: 16
- PullTotalTime: 42.349us
- PushChunkNum: 0
- PushRowNum: 0
- PushTotalTime: 0ns
- SetFinishedTime: 15.290us
- SetFinishingTime: 548ns
UniqueMetrics:
Pipeline (id=0):
- ActiveTime: 126.107us
- __MAX_OF_ActiveTime: 165.3us
- __MIN_OF_ActiveTime: 48.160us
- BlockByInputEmpty: 90
- __MAX_OF_BlockByInputEmpty: 15
- __MIN_OF_BlockByInputEmpty: 15
- BlockByOutputFull: 0
- BlockByPrecondition: 0
- DegreeOfParallelism: 6
- DriverPrepareTime: 44.841us
- __MAX_OF_DriverPrepareTime: 89.348us
- __MIN_OF_DriverPrepareTime: 30.342us
- DriverTotalTime: 238.814ms
- __MAX_OF_DriverTotalTime: 238.923ms
- __MIN_OF_DriverTotalTime: 238.710ms
- OverheadTime: 56.706us
- __MAX_OF_OverheadTime: 73.455us
- __MIN_OF_OverheadTime: 39.919us
2、1、SET GLOBAL cbo_enable_low_cardinality_optimize =false ;:
- QueryCpuCost: 1s596ms
- QueryMemCost: 329.165MB
profile
Query:
Summary:
- Query ID: a15e4823-bf21-11ed-98f5-00163e30e77c
- Start Time: 2023-03-10 16:57:48
- End Time: 2023-03-10 16:57:48
- Total: 374ms
- Query Type: Query
- Query State: EOF
- StarRocks Version: 2.3.7
- User: root
- Default Db: default_cluster:ec_report
- Sql Statement: SELECT COUNT(DISTINCT ((dm_ec_cashloan_risk_diff_sence_monitor
.p_date
>= DATE(‘2022-12-01’)) AND (dm_ec_cashloan_risk_diff_sence_monitor
.p_date
<= DATE(‘2023-03-08’)))) AS ctd_Calculation_250583147382595594_ok
, COUNT(DISTINCT (CASE WHEN (dm_ec_cashloan_risk_diff_sence_monitor
.af_user_type
= ‘47’) THEN dm_ec_cashloan_risk_diff_sence_monitor
.af_user_type
WHEN (dm_ec_cashloan_risk_diff_sence_monitor
.last_state_name
= ‘SCORE_AFPIEMPTY_CLIK_RECALL’) THEN dm_ec_cashloan_risk_diff_sence_monitor
.af_user_type
ELSE ‘正常’ END)) AS ctd_Calculation_542402341543010329_ok
, COUNT(DISTINCT dm_ec_cashloan_risk_diff_sence_monitor
.af_user_type
) AS ctd_af_user_type_ok
, COUNT(DISTINCT dm_ec_cashloan_risk_diff_sence_monitor
.afpi
) AS ctd_afpi_ok
, COUNT(DISTINCT dm_ec_cashloan_risk_diff_sence_monitor
.all_pass_num
) AS ctd_all_pass_num_ok
, COUNT(DISTINCT dm_ec_cashloan_risk_diff_sence_monitor
.bf_user_type
) AS ctd_bf_user_type_ok
, COUNT(DISTINCT dm_ec_cashloan_risk_diff_sence_monitor
.canrepply_cnt
) AS ctd_canrepply_cnt_ok
, COUNT(DISTINCT dm_ec_cashloan_risk_diff_sence_monitor
.done_num
) AS ctd_done_num_ok
, COUNT(DISTINCT dm_ec_cashloan_risk_diff_sence_monitor
.is_risk_platform
) AS ctd_is_risk_platform_ok
, COUNT(DISTINCT dm_ec_cashloan_risk_diff_sence_monitor
.last_state_name
) AS ctd_last_state_name (复制)_323133334705078295_ok
, COUNT(DISTINCT dm_ec_cashloan_risk_diff_sence_monitor
.last_state_name
) AS ctd_last_state_name_ok
, COUNT(DISTINCT dm_ec_cashloan_risk_diff_sence_monitor
.main_rank_level
) AS ctd_main_rank_level_ok
, COUNT(DISTINCT dm_ec_cashloan_risk_diff_sence_monitor
.main_rank_name
) AS ctd_main_rank_name_ok
, COUNT(DISTINCT dm_ec_cashloan_risk_diff_sence_monitor
.media_source
) AS ctd_media_source_ok
, COUNT(DISTINCT dm_ec_cashloan_risk_diff_sence_monitor
.operation_level
) AS ctd_operation_level_ok
, COUNT(DISTINCT dm_ec_cashloan_risk_diff_sence_monitor
.order_seq
) AS ctd_order_seq_ok
, COUNT(DISTINCT dm_ec_cashloan_risk_diff_sence_monitor
.pass_num
) AS ctd_pass_num_ok
, COUNT(DISTINCT dm_ec_cashloan_risk_diff_sence_monitor
.risk_flow_id
) AS ctd_risk_flow_id_ok
, COUNT(DISTINCT dm_ec_cashloan_risk_diff_sence_monitor
.risk_platform_type_name
) AS ctd_risk_platform_type_name_ok
, COUNT(DISTINCT dm_ec_cashloan_risk_diff_sence_monitor
.second_canrepply_cnt
) AS ctd_second_canrepply_cnt_ok
, COUNT(DISTINCT dm_ec_cashloan_risk_diff_sence_monitor
.second_done_num
) AS ctd_second_done_num_ok
, COUNT(DISTINCT dm_ec_cashloan_risk_diff_sence_monitor
.second_pass_num
) AS ctd_second_pass_num_ok
, COUNT(DISTINCT dm_ec_cashloan_risk_diff_sence_monitor
.term_time_period
) AS ctd_term_time_period_ok
, COUNT(DISTINCT dm_ec_cashloan_risk_diff_sence_monitor
.terms
) AS ctd_terms_ok
, COUNT(DISTINCT dm_ec_cashloan_risk_diff_sence_monitor
.tixian_1_sorce_num
) AS ctd_tixian_1_sorce_num_ok
, COUNT(DISTINCT dm_ec_cashloan_risk_diff_sence_monitor
.tixian_7_sorce_num
) AS ctd_tixian_7_sorce_num_ok
, COUNT(DISTINCT dm_ec_cashloan_risk_diff_sence_monitor
.user_num
) AS ctd_user_num_ok
FROM ec_report.dm_ec_cashloan_risk_diff_sence_monitor
HAVING (COUNT(1) > 0)
- QueryCpuCost: 1s596ms
- QueryMemCost: 329.165MB
- Variables: parallel_fragment_exec_instance_num=2,pipeline_dop=0
- Collect Profile Time: 7ms
Planner:
- CoordDeliverExec: 19ms / 1
- CoordPrepareExec: 0ms / 1
- Total: 9ms / 1
Execution Profile a15e4823-bf21-11ed-98f5-00163e30e77c: