大表加字段困难

super_pikachu · 2025年10月10日 11:26

【详述】
1.聚合表增加key 字段非常慢
2.而且会导致集群压力增大查询失败
3.并且需要的时间非常长，之前测试过需要大概8个小时才能完成增加key列
【背景】
【业务影响】集群压力增大查询容易失败加字段时间过长
【是否存算分离】否
【StarRocks版本】3.3.18
【集群规模】3fe + 16be
【联系方式】pikachu.liu@adtiming.com
【附件】
加字段语句

 ALTER TABLE xxx ADD COLUMN recharge_value tinyint AFTER ml_group_index;

建表语句

CREATE TABLE `xxx` (
  `__dt` datetime NOT NULL COMMENT "UTC时间,精确到小时,分区字段",
  `pub_app_id` int(11) NOT NULL DEFAULT "0" COMMENT "PubApp ID",
  `placement_id` int(11) NOT NULL DEFAULT "0" COMMENT "Placement ID",
  `ad_group_id` int(11) NOT NULL DEFAULT "0" COMMENT "AdGroup ID",
  `campaign_id` int(11) NOT NULL DEFAULT "0" COMMENT "Campaign ID",
  `pub_source_id` int(11) NOT NULL DEFAULT "0" COMMENT "PubSource ID",
  `publisher_id` int(11) NOT NULL DEFAULT "0" COMMENT "Publisher ID",
  `bundle` varchar(2000) NOT NULL DEFAULT "" COMMENT "Bundle ID",
  `ad_type` tinyint(4) NOT NULL DEFAULT "0" COMMENT "Ad Type",
  `template_id` int(11) NOT NULL DEFAULT "0" COMMENT "Template ID",
  `ptml_id` int(11) NOT NULL DEFAULT "0" COMMENT "Placement Template ID",
  `bm_id` int(11) NOT NULL DEFAULT "0" COMMENT "Pub BM ID",
  `am_id` int(11) NOT NULL DEFAULT "0" COMMENT "PubApp AM ID",
  `network_id` int(11) NOT NULL DEFAULT "0" COMMENT "Network ID",
  `n_am_id` int(11) NOT NULL DEFAULT "0" COMMENT "Network AM ID",
  `n_bm_id` int(11) NOT NULL DEFAULT "0" COMMENT "Network BM ID",
  `advertiser_id` int(11) NOT NULL DEFAULT "0" COMMENT "Advertiser ID",
  `app_id` varchar(300) NOT NULL DEFAULT "" COMMENT "App ID",
  `creative_id` int(11) NOT NULL DEFAULT "0" COMMENT "Creative ID",
  `material_id` int(11) NOT NULL DEFAULT "0" COMMENT "Material ID",
  `endcard_id` int(11) NOT NULL DEFAULT "0" COMMENT "Endcard ID",
  `country` varchar(3) NOT NULL DEFAULT "" COMMENT "Country",
  `lang` varchar(512) NOT NULL DEFAULT "" COMMENT "Language",
  `sdkv` varchar(512) NOT NULL DEFAULT "" COMMENT "SDK Version",
  `event` varchar(512) NOT NULL DEFAULT "" COMMENT "Event Name",
  `pmodel` int(11) NOT NULL DEFAULT "0" COMMENT "Placement Model",
  `pcrid` int(11) NOT NULL DEFAULT "0" COMMENT "Placement Creative ID",
  `pmid` int(11) NOT NULL DEFAULT "0" COMMENT "Placement Material ID",
  `old_user` tinyint(4) NOT NULL DEFAULT "0" COMMENT "User Type",
  `a_am_id` int(11) NOT NULL DEFAULT "0" COMMENT "Adv AM ID",
  `a_bm_id` int(11) NOT NULL DEFAULT "0" COMMENT "Adv BM ID",
  `ag_am_id` int(11) NOT NULL DEFAULT "0" COMMENT "AdGroup AM ID",
  `ad_sence` int(11) NOT NULL DEFAULT "0" COMMENT "Ad Scene",
  `ad_plat` tinyint(4) NOT NULL DEFAULT "0" COMMENT "Ad Platform",
  `template_type` tinyint(4) NOT NULL DEFAULT "0" COMMENT "Template Type",
  `aaid` int(11) NOT NULL DEFAULT "0" COMMENT "Advertiser App ID",
  `is_apk` tinyint(4) NOT NULL DEFAULT "0" COMMENT "App Type",
  `billing_type` tinyint(4) NOT NULL DEFAULT "0" COMMENT "Billing Type",
  `alg_id` int(11) NOT NULL DEFAULT "0" COMMENT "ML Name",
  `mmp` int(11) NOT NULL DEFAULT "0" COMMENT "MMP",
  `event_value` varchar(128) NOT NULL DEFAULT "" COMMENT "Event Value",
  `std_event` varchar(512) NOT NULL DEFAULT "" COMMENT "Standard Event",
  `is_invalid` tinyint(4) NOT NULL DEFAULT "0" COMMENT "Is Invalid",
  `is_wv` tinyint(4) NOT NULL DEFAULT "0" COMMENT "Is WebView",
  `n_origin` tinyint(4) NOT NULL DEFAULT "0" COMMENT "Network Origin",
  `t_am_id` int(11) NOT NULL DEFAULT "0" COMMENT "Template AM ID",
  `endcard_am_id` int(11) NOT NULL DEFAULT "0" COMMENT "Endcard AM ID",
  `ocpa_bid` int(11) NOT NULL DEFAULT "0" COMMENT "Optimization Mode",
  `rtb_auto_tml` tinyint(4) NOT NULL DEFAULT "0" COMMENT "RTB Template Model",
  `ad_scene` int(11) NOT NULL DEFAULT "0" COMMENT "Ad Scene",
  `adx_id` int(11) NULL COMMENT "Adx ID",
  `pub_am_id` int(11) NOT NULL DEFAULT "0" COMMENT "Publisher AM ID",
  `bundle_tag` int(11) NOT NULL DEFAULT "0" COMMENT "Bundle Tag",
  `app_tag` int(11) NOT NULL DEFAULT "0" COMMENT "App Tag",
  `pwa` tinyint(4) NULL COMMENT "Is PWA",
  `e_did` tinyint(4) NOT NULL DEFAULT "0" COMMENT "Empty Device ID",
  `price_model` tinyint(4) NULL COMMENT "Price Model",
  `bist` int(11) NULL DEFAULT "0" COMMENT "",
  `domain` varchar(65533) NULL DEFAULT "" COMMENT "",
  `pub_app_type` int(11) NULL COMMENT "",
  `ml_group_index` int(11) NULL COMMENT "",
  `publisher_source_type` tinyint(4) NULL DEFAULT "0" COMMENT "",
  `init_cnt` bigint(20) SUM NOT NULL DEFAULT "0" COMMENT "Init Count",
  `lad_cnt` bigint(20) SUM NOT NULL DEFAULT "0" COMMENT "Lad Count",
  `plc_impr_cnt` bigint(20) SUM NOT NULL DEFAULT "0" COMMENT "Placement Impression Count",
  `plc_click_cnt` bigint(20) SUM NOT NULL DEFAULT "0" COMMENT "Placement Click Count",
  `tml_view_cnt` bigint(20) SUM NOT NULL DEFAULT "0" COMMENT "Template View Count",
  `tml_play_cnt` bigint(20) SUM NOT NULL DEFAULT "0" COMMENT "Template Play Count",
  `impr_cnt` bigint(20) SUM NOT NULL DEFAULT "0" COMMENT "Impression Count",
  `click_cnt` bigint(20) SUM NOT NULL DEFAULT "0" COMMENT "Click Count",
  `install_cnt` bigint(20) SUM NOT NULL DEFAULT "0" COMMENT "Install Count",
  `event_cnt` bigint(20) SUM NOT NULL DEFAULT "0" COMMENT "Total Event Count",
  `revenue` decimal(38, 6) SUM NOT NULL DEFAULT "0" COMMENT "Revenue",
  `cost` decimal(38, 6) SUM NOT NULL DEFAULT "0" COMMENT "Cost",
  `no_billing_event_cnt` bigint(20) SUM NOT NULL DEFAULT "0" COMMENT "No Billing Event Count",
  `pub_event_cnt` bigint(20) SUM NOT NULL DEFAULT "0" COMMENT "Publisher Event Count",
  `ocpa_event_cnt` bigint(20) SUM NULL DEFAULT "0" COMMENT "",
  `effective_cpc_cnt` bigint(20) SUM NOT NULL DEFAULT "0" COMMENT "Effective CPC Count",
  `tml_cnt` bigint(20) SUM NOT NULL DEFAULT "0" COMMENT "Template Count",
  `cl_cnt` bigint(20) SUM NOT NULL DEFAULT "0" COMMENT "CL Count",
  `payment_event_cnt` bigint(20) SUM NOT NULL DEFAULT "0" COMMENT "Payment Events Count",
  `key_event_cnt` bigint(20) SUM NOT NULL DEFAULT "0" COMMENT "Key Event Count",
  `ec_view` bigint(20) SUM NOT NULL DEFAULT "0" COMMENT "EC View",
  `ec_click` bigint(20) SUM NOT NULL DEFAULT "0" COMMENT "EC Click",
  `cpc_revenue` decimal(38, 6) SUM NOT NULL DEFAULT "0" COMMENT "CPC Revenue",
  `af_revenue` decimal(38, 6) SUM NOT NULL DEFAULT "0" COMMENT "AF Revenue",
  `plc_view_cnt` bigint(20) SUM NOT NULL DEFAULT "0" COMMENT "Placement View Count",
  `filled_cnt` bigint(20) SUM NOT NULL DEFAULT "0" COMMENT "Filled Count",
  `bid_price` decimal(38, 6) SUM NOT NULL DEFAULT "0" COMMENT "Bid Price",
  `nf_price_cnt` bigint(20) SUM NOT NULL DEFAULT "0" COMMENT "No Filled Price Count",
  `no_bid_price` decimal(38, 6) SUM NOT NULL DEFAULT "0" COMMENT "No Bid Price",
  `render_success` bigint(20) SUM NULL COMMENT "",
  `offer_view` bigint(20) SUM NULL COMMENT "",
  `offer_show` bigint(20) SUM NULL COMMENT "",
  `offer_click` bigint(20) SUM NULL COMMENT "",
  `open_app` bigint(20) SUM NULL COMMENT "",
  `day_0_ocpa_event_cnt` bigint(20) SUM NULL COMMENT ""
) ENGINE=OLAP 
AGGREGATE KEY(`__dt`, `pub_app_id`, `placement_id`, `ad_group_id`, `campaign_id`, `pub_source_id`, `publisher_id`, `bundle`, `ad_type`, `template_id`, `ptml_id`, `bm_id`, `am_id`, `network_id`, `n_am_id`, `n_bm_id`, `advertiser_id`, `app_id`, `creative_id`, `material_id`, `endcard_id`, `country`, `lang`, `sdkv`, `event`, `pmodel`, `pcrid`, `pmid`, `old_user`, `a_am_id`, `a_bm_id`, `ag_am_id`, `ad_sence`, `ad_plat`, `template_type`, `aaid`, `is_apk`, `billing_type`, `alg_id`, `mmp`, `event_value`, `std_event`, `is_invalid`, `is_wv`, `n_origin`, `t_am_id`, `endcard_am_id`, `ocpa_bid`, `rtb_auto_tml`, `ad_scene`, `adx_id`, `pub_am_id`, `bundle_tag`, `app_tag`, `pwa`, `e_did`, `price_model`, `bist`, `domain`, `pub_app_type`, `ml_group_index`, `publisher_source_type`)
PARTITION BY date_trunc('hour', __dt)
DISTRIBUTED BY HASH(`pub_app_id`, `ad_group_id`, `app_id`)
PROPERTIES (
"compression" = "LZ4",
"fast_schema_evolution" = "true",
"partition_live_number" = "9600",
"replicated_storage" = "true",
"replication_num" = "3"
);

BE相关压力增加

schema change memory也增加

Aurafin · 2025年10月10日 12:04

这个地方有2个问题

即使使用了fast_schema_evolution 属性，但是在某些情况下也是无法使用 fast ddl 模式来添加字段
Schema变更需要重建索引元数据 : 对于聚合表,每次schema变更都需要更新所有索引的元数据。 key列越多,需要处理的元数据就越复杂。根据我们的实践经验来看，聚合表不建议创建有过多的key 列，这对导入会增加更多的资源消耗，您这个表有62个key 列，这会导致需要处理的情况更复杂。

建议尽可能的减少key 列，另外添加字段时可以批量添加，避免业务高峰。当然如果可以建议更换为duplicate key 模型

super_pikachu · 2025年10月10日 12:43

是的我们看相关代码也发现 fast_schema_evolution 在key列的schema change时候不生效，但是指标列是生效的也确实非常快，秒级就完成
2.
因为我们这个表是报表数据并且有flink 实时任务不断往里写，需要依赖聚合表的compaction合并，将相同维度指标列合并，要不然这个数据量全是明细数据数据可能会非常大，所以改用明细表可能还不太满足我们这个:-)

明天我们会测试一下改用明细表看看会膨胀多少和查询时间受不受影响