物化视图后单点查询慢问题,Deploy时间长

💬 StarRocks 用户问答 日常运维
#1

【业务影响】查询感觉较慢
【是否存算分离】否
【StarRocks版本】3.1.6
【集群规模】1FE+2BE
【机器信息】96C/128G/千兆网
【详述】同样是1亿3千万数据，在MySQL中点查只需要0.06s，在SR中将表物化为分区视图，并且创建了索引，点查时间不太稳定快的时候0.3s，慢的时候大概要1s，下面是对应的profile
0.3秒profile.txt (47.9 KB)
0.8秒profile.txt (47.3 KB)

#2

image
求助：Deploy耗时较长，无法定位发生原因

#3

sp_traffic_mv_target是物化视图吗？

#4

是的。这是它的建表语句
CREATE MATERIALIZED VIEW sp_traffic_mv_target (created_at, marketplace_id, advertiser_id, time_window_start, campaign_id, ad_group_id, ad_id, keyword_id, placement, currency, cost, impressions, clicks,
INDEX index1 (marketplace_id) USING BITMAP,
INDEX index2 (campaign_id) USING BITMAP,
INDEX index3 (time_window_start) USING BITMAP,
INDEX index4 (keyword_id) USING BITMAP)
PARTITION BY (created_at)
DISTRIBUTED BY HASH(created_at, marketplace_id, advertiser_id, time_window_start, campaign_id, ad_group_id, ad_id, keyword_id, placement, currency)
REFRESH ASYNC START(“2024-01-08 16:30:00”) EVERY(INTERVAL 30 MINUTE)
PROPERTIES (
“replicated_storage” = “true”,
“replication_num” = “2”,
“storage_medium” = “HDD”
)
AS SELECT sp_traffic.created_at, sp_traffic.marketplace_id, sp_traffic.advertiser_id, sp_traffic.time_window_start, sp_traffic.campaign_id, sp_traffic.ad_group_id, sp_traffic.ad_id, sp_traffic.keyword_id, sp_traffic.placement, sp_traffic.currency, sum(sp_traffic.cost) AS cost, sum(sp_traffic.impressions) AS impressions, sum(sp_traffic.clicks) AS clicks
FROM amazon_marketing_stream.sp_traffic
WHERE (sp_traffic.time_window_start >= (now() - INTERVAL 8 DAY)) AND (sp_traffic.match_type IN (‘TARGETING_EXPRESSION’, ‘TARGETING_EXPRESSION_PREDEFINED’))
GROUP BY sp_traffic.marketplace_id, sp_traffic.advertiser_id, sp_traffic.time_window_start, sp_traffic.campaign_id, sp_traffic.ad_group_id, sp_traffic.ad_id, sp_traffic.keyword_id, sp_traffic.placement, sp_traffic.currency, sp_traffic.created_at;

#5

麻烦试下建mv的时候考虑以下几点再测试下：

  1. 将keyword_id置于第一列
  2. DISTRIBUTED BY HASH里不用太多，1-3列能保证数据均匀的列即可
    比如：
    CREATE MATERIALIZED VIEW sp_traffic_mv_target ( keyword_id , created_at , marketplace_id , advertiser_id , time_window_start , campaign_id , ad_group_id , ad_id , placement , currency , cost , impressions , clicks ,
    INDEX index1 ( marketplace_id ) USING BITMAP,
    INDEX index2 ( campaign_id ) USING BITMAP,
    INDEX index3 ( time_window_start ) USING BITMAP,
    INDEX index4 ( keyword_id ) USING BITMAP)
    PARTITION BY ( created_at )
    DISTRIBUTED BY HASH( keyword_id )
#6

收到，正在尝试，感谢

#7

有作用，维持在150ms左右，之前可以达到30~70ms这样

#8

多个物化视图join也不是很稳定
多表joinprofile.txt (78.7 KB)

#9

之前30-60ms，现在变慢了吗

京ICP备2022026421号-1