为了更快的定位您的问题，请提供以下信息，谢谢
【详述】sql 如下：
insert overwrite dwd.dwd_rcm_recommend_result_tmp_hf partition(ds=‘2024-07-15 01:00:00’, algo_id=‘ContentBased1’)
with
user_vec as (
select
user_id
, bitmap_agg(a.t_idx) as indeies_bm
, array_agg(t_idx order by t_idx) as indeies
, array_agg(score order by t_idx) as scores
, round(sqrt(sum(pow(score, 2))), 5) as norm
from dwd.dwd_rcm_users_tag_tuple_hf a
– left join dwd.dwd_rcm_tag_index b
– on a.t_idx = b.t_idx
– and b.ds = ‘$[yyyy-MM-dd HH]’
where a.ds = ‘2024-07-15 01’ and a.dim != ‘feed’
group by user_id
)
, feed_vec as (
select
b.feed_id
, bitmap_agg(idx.t_idx) as indeies_bm
, array_agg(idx.t_idx order by idx.t_idx) as indeies
, array_agg(b.score order by idx.t_idx) as scores
, round(sqrt(sum(pow(b.score, 2))), 5) as norm
from (
select feed_id, unnest.col1 as dim, unnest.col2 as tag, unnest.col3 as score
from (
select
id as feed_id
, array_concat(
[
ROW(‘sector’, sector, 1.0)
, ROW(‘author’, author, 1.0)
, ROW(‘category’, category, 1.0)
, ROW(‘lang’, original_language, 1.0)
],
array_map(
X -> ROW(‘coin’, replace(upper(x -> ‘$.name’), ‘"’, ‘’), cast(x -> ‘$.count’ as double))
, CAST(matched_currencies AS ARRAY)
)
) as tags
FROM ods.ods_s_content_information_rt
where ds >= ‘2024-07-15’
) a, unnest(tags) as unnest
) b
join dwd.dwd_rcm_tag_index idx
on b.dim = idx.dim
and b.tag = idx.tag
and idx.dim != ‘feed’
and idx.ds = ‘2024-07-15 01’
group by b.feed_id
)
, cosine_sim as (
select , row_number() over (partition by user_id order by sim desc) as r
from (
select
user_vec.user_id
, feed_vec.feed_id
– , user_vec.scores as u_vec
– , feed_vec.scores as f_vec
– , user_vec.indeies as u_ids
– , feed_vec.indeies as f_ids
, round(array_sum(array_map(
(x, y) -> round(xy, 5)
, array_filter((x, y) -> bitmap_contains(feed_vec.indeies_bm, y), user_vec.scores, user_vec.indeies)
, array_filter((x, y) -> bitmap_contains(user_vec.indeies_bm, y), feed_vec.scores, feed_vec.indeies)
)) / (user_vec.norm * feed_vec.norm), 5) as sim
from feed_vec
join user_vec
on 1=1
and BITMAP_HAS_ANY(user_vec.indeies_bm, feed_vec.indeies_bm)
) a
)
select
‘2024-07-15 01:00:00’ as ds
, ‘ContentBased1’ as algo_id
, user_id
, feed_id
, round(sim, 5) as score
, r
, null as params
from cosine_sim
where r <= 20
【背景】执行一段sql be节点就全挂了
【业务影响】
【是否存算分离】否
【StarRocks版本】3.1.3
【集群规模】例如：3fe+3be（fe与be混部）
【机器信息】CPU虚拟核/内存/网卡，例如：48C/64G/万兆
【联系方式】1067341434@qq.com
【附件】
- fe.log/beINFO/相应截图
- 慢查询：
- Profile信息，获取Profile，通过Profile分析查询瓶颈
- 并行度：show variables like ‘%parallel_fragment_exec_instance_num%’;
- pipeline是否开启：show variables like ‘%pipeline%’;
- be节点cpu和内存使用率截图
- 查询报错：
- query_dump，怎么获取query_dump文件
- be crash
3.1.3 RELEASE (build 384ba23)
query_id:54da7831-4282-11ef-906a-02257bc2b2c5, fragment_instance:54da7831-4282-11ef-906a-02257bc2b2c9
tracker:process consumption: 6045581624
tracker:query_pool consumption: 8930192
tracker:load consumption: 24944
tracker:metadata consumption: 430906817
tracker:tablet_metadata consumption: 175966382
tracker:rowset_metadata consumption: 119709081
tracker:segment_metadata consumption: 31759684
tracker:column_metadata consumption: 103471670
tracker:tablet_schema consumption: 3228462
tracker:segment_zonemap consumption: 23450306
tracker:short_key_index consumption: 2151907
tracker:column_zonemap_index consumption: 39192062
tracker:ordinal_index consumption: 31886664
tracker:bitmap_index consumption: 0
tracker:bloom_filter_index consumption: 0
tracker:compaction consumption: 0
tracker:schema_change consumption: 0
tracker:column_pool consumption: 412671043
tracker:page_cache consumption: 4187753536
tracker:update consumption: 253813060
tracker:chunk_allocator consumption: 558121752
tracker:clone consumption: 0
tracker:consistency consumption: 0
*** Aborted at 1721031300 (unix time) try “date -d @1721031300” if you are using GNU date ***
PC: @ 0x860fd7c __udivmodti4
*** SIGFPE (@0x860fd7c) received by PID 9339 (TID 0x7f1f2b3dc700) from PID 140574076; stack trace: ***
@ 0x62f3702 google::(anonymous namespace)::FailureSignalHandler()
@ 0x7f1fe5b208e0 (unknown)
@ 0x860fd7c __udivmodti4
@ 0x4042a93 _ZN9starrocks13DecimalV3Cast9to_stringInEENSt7__cxx1112basic_stringIcSt11char_traitsIcESaIcEEERKNSt9enable_ifIX29is_underlying_type_of_decimalIT_EES9_E4typeEii.isra.0
@ 0x41da500 starrocks::DecimalNonDecimalCast<>::decimal_to()
@ 0x41dac2d starrocks::DealNullableColumnUnaryFunction<>::evaluate<>()
@ 0x41dafa2 starrocks::VectorizedCastToStringExpr<>::evaluate_checked()
@ 0x3a0c283 starrocks::ExprContext::evaluate()
@ 0x3a0c5cf starrocks::ExprContext::evaluate()
@ 0x2d49524 starrocks::pipeline::ProjectOperator::push_chunk()
@ 0x2a6996b starrocks::pipeline::PipelineDriver::process()
@ 0x5607bbe starrocks::pipeline::GlobalDriverExecutor::_worker_thread()
@ 0x4f07072 starrocks::ThreadPool::dispatch_thread()
@ 0x4f01b6a starrocks:
:supervise_thread()
@ 0x7f1fe5b1644b start_thread
@ 0x7f1fe510552f __GI___clone
@ 0x0 (unknown)
start time: Mon Jul 15 08:16:50 UTC 2024 - 外表查询报错
- be.out和fe.warn.log