cooldwind opened a new issue #7634: URL: https://github.com/apache/incubator-doris/issues/7634
### Discussed in https://github.com/apache/incubator-doris/discussions/7071 <div type='discussions-op-text'> <sup>Originally posted by **luzhijing** November 10, 2021</sup> * 系统版本：0.15.1-rc09 * 报错信息： 通过调度任务跑 sql 脚本 ，数据量不大，但是be频发挂掉。 通过insert into select 更新表，select 没有问题，一起用be会挂掉。  * * 执行了什么操作导致的报错：比如执行的SQL、导入任务的数据量及频率等 * 具体表现： fe节点正常，但是be节点会无故崩溃挂掉，经常是挂掉1个，2个be。 * 操作系统：centOS 7 * 机器配置：CPU核数、内存、磁盘 生产环境：3台fe节点，16核CPU，32G内存，300G HDD，3台be节点，16核CPU，32G内存，500G HDD。共5台，其中一台 FE，BE 混部。exec_mem_limit = 8G, mem_limit = 90%。 * 平台：x86 * 是否能复现以及复现步骤 调度器执行sql: insert into ads_trade_stat ( biz_date ,app_name ,spu_id ,spu_title ,spu_kind ,cate_id ,cate_name1 ,cate_name2 ,order_user_cnt ,order_cnt ,order_amt ,pay_user_cnt ,pay_cnt ,pay_amt ,refund_user_cnt ,refund_cnt ,refund_amt ,create_time ,udpate_time ) SELECT DATE(a.biz_create_time) AS biz_date, c.app_name, a.spu_id, b.spu_title, b.spu_kind, b.cate_id, f.cate_name1, f.cate_name2, COUNT(DISTINCT a.user_id) AS order_user_cnt, COUNT(DISTINCT a.oid) AS order_cnt, SUM(a.total_amount) AS order_amt, COUNT(DISTINCT d.user_id) AS pay_user_cnt, COUNT(DISTINCT d.oid) AS pay_cnt, IFNULL(SUM(d.pay_amount),0) AS pay_amt, COUNT(DISTINCT e.user_id) AS refund_user_cnt, COUNT(DISTINCT e.oid) AS refund_cnt, IFNULL(SUM(e.amount),0)AS refund_amt, current_timestamp() as create_time, current_timestamp() as update_time FROM dwd_trade_order_detail a LEFT JOIN dwd_trade_pay d ON a.oid=d.oid and d.pay_status='paid' LEFT JOIN dwd_trade_refund_detail e ON a.oid=e.oid and a.iid=e.iid and e.refund_status in ('return_completed','refund_completed') LEFT JOIN dim_sku b ON a.spu_id = b.spu_id AND a.spu_spec_id = b.spu_spec_id and b.deleted='N' LEFT JOIN dim_item_category f ON b.cate_id=f.id and f.deleted='N' LEFT JOIN dim_support_app c ON a.app_id = c.id WHERE a.order_deleted='N' AND a.order_item_deleted='N' GROUP BY DATE(a.biz_create_time), c.app_name, a.spu_id, b.spu_title, b.spu_kind, b.cate_id, f.cate_name1, f.cate_name2; 数据量： select count(1) from dwd_trade_order_detail 73071 select count(1) from dwd_trade_pay 237612 select count(1) from dwd_trade_refund_detail 5384 select count(1) from dim_sku 4474 select count(1) from dim_item_category 9 select count(1) from dim_support_app 11 插入的数据也就是 1000 条左右; 建立表语句： CREATE TABLE `ads_trade_stat` ( `biz_date` date NULL COMMENT "", `app_name` text NULL COMMENT "", `spu_id` bigint(20) NULL COMMENT "", `spu_title` text NULL COMMENT "", `spu_kind` text NULL COMMENT "", `cate_id` bigint(20) NULL COMMENT "", `cate_name1` text NULL COMMENT "", `cate_name2` text NULL COMMENT "", `order_user_cnt` bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "", `order_cnt` bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "", `order_amt` bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "", `pay_user_cnt` bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "", `pay_cnt` bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "", `pay_amt` bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "", `refund_user_cnt` bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "", `refund_cnt` bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "", `refund_amt` bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "", `create_time` datetime REPLACE_IF_NOT_NULL NULL COMMENT "", `udpate_time` datetime REPLACE_IF_NOT_NULL NULL COMMENT "" ) ENGINE=OLAP AGGREGATE KEY(`biz_date`, `app_name`, `spu_id`, `spu_title`, `spu_kind`, `cate_id`, `cate_name1`, `cate_name2`) COMMENT "订单全链路统计" DISTRIBUTED BY HASH(`biz_date`) BUCKETS 10 PROPERTIES ( "replication_allocation" = "tag.location.default: 3", "in_memory" = "false", "storage_format" = "V2" ); CREATE TABLE `dwd_trade_order_detail` ( `id` bigint(20) NOT NULL COMMENT "", `oid` bigint(20) NULL COMMENT "", `iid` bigint(20) NULL COMMENT "", `biz_create_time` datetime NULL COMMENT "", `app_id` bigint(20) NULL COMMENT "", `store_id` bigint(20) NULL COMMENT "", `user_id` bigint(20) NULL COMMENT "", `spu_id` bigint(20) NULL COMMENT "", `spu_spec_id` bigint(20) NULL COMMENT "", `coupon_id` bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "", `marketing_coupon_id` bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "", `oid_status` text REPLACE_IF_NOT_NULL NULL COMMENT "", `iid_status` text REPLACE_IF_NOT_NULL NULL COMMENT "", `create_time` datetime REPLACE_IF_NOT_NULL NULL COMMENT "", `order_type` text REPLACE_IF_NOT_NULL NULL COMMENT "", `order_source` text REPLACE_IF_NOT_NULL NULL COMMENT "", `pay_time` datetime REPLACE_IF_NOT_NULL NULL COMMENT "", `ship_time` datetime REPLACE_IF_NOT_NULL NULL COMMENT "", `complete_time` datetime REPLACE_IF_NOT_NULL NULL COMMENT "", `deadline_time` datetime REPLACE_IF_NOT_NULL NULL COMMENT "", `cancel_time` datetime REPLACE_IF_NOT_NULL NULL COMMENT "", `cost_price` bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "", `price` bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "", `num` bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "", `total_amount` bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "", `total_discount` bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "", `refund_num` bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "", `refund_amount` bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "", `shipped_num` bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "", `delivery_amount` bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "", `city` text REPLACE_IF_NOT_NULL NULL COMMENT "", `detail_address` text REPLACE_IF_NOT_NULL NULL COMMENT "", `region` text REPLACE_IF_NOT_NULL NULL COMMENT "", `area_code` bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "", `province` text REPLACE_IF_NOT_NULL NULL COMMENT "", `receiver_name` text REPLACE_IF_NOT_NULL NULL COMMENT "", `receiver_phone` text REPLACE_IF_NOT_NULL NULL COMMENT "", `pay_amount` bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "", `pay_invoice` text REPLACE_IF_NOT_NULL NULL COMMENT "", `points_send` bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "", `points_limit` bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "", `coupon_amount` bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "", `coupon_name` text REPLACE_IF_NOT_NULL NULL COMMENT "", `order_archive` text REPLACE_IF_NOT_NULL NULL COMMENT "", `refund_kind` text REPLACE_IF_NOT_NULL NULL COMMENT "", `item_system` text REPLACE_IF_NOT_NULL NULL COMMENT "", `member_contact_name` text REPLACE_IF_NOT_NULL NULL COMMENT "", `member_contact_phone` text REPLACE_IF_NOT_NULL NULL COMMENT "", `note` text REPLACE_IF_NOT_NULL NULL COMMENT "", `note2` text REPLACE_IF_NOT_NULL NULL COMMENT "", `ship_Ids` text REPLACE_IF_NOT_NULL NULL COMMENT "", `refund_ids` text REPLACE_IF_NOT_NULL NULL COMMENT "", `gift_num` bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "", `gift_amount` bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "", `order_update_time` datetime REPLACE_IF_NOT_NULL NULL COMMENT "", `order_item_update_time` datetime REPLACE_IF_NOT_NULL NULL COMMENT "", `order_deleted` text REPLACE_IF_NOT_NULL NULL COMMENT "", `order_item_deleted` text REPLACE_IF_NOT_NULL NULL COMMENT "", `update_time` datetime REPLACE_IF_NOT_NULL NULL COMMENT "" ) ENGINE=OLAP AGGREGATE KEY(`id`, `oid`, `iid`, `biz_create_time`, `app_id`, `store_id`, `user_id`, `spu_id`, `spu_spec_id`) COMMENT "交易订单事实表" PARTITION BY RANGE(`biz_create_time`)() DISTRIBUTED BY HASH(`id`) BUCKETS 10 PROPERTIES ( "replication_allocation" = "tag.location.default: 3", "dynamic_partition.enable" = "true", "dynamic_partition.time_unit" = "DAY", "dynamic_partition.time_zone" = "Asia/Shanghai", "dynamic_partition.start" = "-2147483648", "dynamic_partition.end" = "3", "dynamic_partition.prefix" = "p", "dynamic_partition.replication_allocation" = "tag.location.default: 3", "dynamic_partition.buckets" = "16", "dynamic_partition.create_history_partition" = "true", "dynamic_partition.history_partition_num" = "400", "dynamic_partition.hot_partition_num" = "0", "dynamic_partition.reserved_history_periods" = "NULL", "in_memory" = "false", "storage_format" = "V2" ); CREATE TABLE `dwd_trade_pay` ( `id` bigint(20) NULL COMMENT "", `oid` bigint(20) NULL COMMENT "", `app_id` bigint(20) NULL COMMENT "", `store_id` bigint(20) NULL COMMENT "", `user_id` bigint(20) NULL COMMENT "", `pay_create_time` datetime NULL COMMENT "", `pay_invoice` text REPLACE_IF_NOT_NULL NULL COMMENT "", `token` text REPLACE_IF_NOT_NULL NULL COMMENT "", `pay_amount` bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "", `pay_channel` text REPLACE_IF_NOT_NULL NULL COMMENT "", `channel_pay_amount` bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "", `pay_points` bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "", `pay_time` datetime REPLACE_IF_NOT_NULL NULL COMMENT "", `pay_plat` text REPLACE_IF_NOT_NULL NULL COMMENT "", `pay_resource` text REPLACE_IF_NOT_NULL NULL COMMENT "", `pay_status` text REPLACE_IF_NOT_NULL NULL COMMENT "", `pay_update_time` datetime REPLACE_IF_NOT_NULL NULL COMMENT "", `deleted` text REPLACE_IF_NOT_NULL NULL COMMENT "", `note` text REPLACE_IF_NOT_NULL NULL COMMENT "", `create_time` datetime REPLACE_IF_NOT_NULL NULL COMMENT "", `update_time` datetime REPLACE_IF_NOT_NULL NULL COMMENT "" ) ENGINE=OLAP AGGREGATE KEY(`id`, `oid`, `app_id`, `store_id`, `user_id`, `pay_create_time`) COMMENT "支付事实表" PARTITION BY RANGE(`pay_create_time`)() DISTRIBUTED BY HASH(`id`) BUCKETS 10 PROPERTIES ( "replication_allocation" = "tag.location.default: 3", "dynamic_partition.enable" = "true", "dynamic_partition.time_unit" = "DAY", "dynamic_partition.time_zone" = "Asia/Shanghai", "dynamic_partition.start" = "-2147483648", "dynamic_partition.end" = "3", "dynamic_partition.prefix" = "p", "dynamic_partition.replication_allocation" = "tag.location.default: 3", "dynamic_partition.buckets" = "16", "dynamic_partition.create_history_partition" = "true", "dynamic_partition.history_partition_num" = "400", "dynamic_partition.hot_partition_num" = "0", "dynamic_partition.reserved_history_periods" = "NULL", "in_memory" = "false", "storage_format" = "V2" ); CREATE TABLE `dwd_trade_refund_detail` ( `refund_id` bigint(20) NULL COMMENT "", `refund_create_time` datetime NULL COMMENT "", `oid` bigint(20) NULL COMMENT "", `iid` bigint(20) NULL COMMENT "", `app_id` bigint(20) NULL COMMENT "", `store_id` bigint(20) NULL COMMENT "", `user_id` bigint(20) NULL COMMENT "", `invoice` text REPLACE_IF_NOT_NULL NULL COMMENT "", `operator_id` bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "", `ship_fee` bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "", `num` bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "", `amount` bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "", `cash_amount` bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "", `points_add` bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "", `points_reduce` bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "", `used_redride` bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "", `pay_amount` bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "", `pay_plat` text REPLACE_IF_NOT_NULL NULL COMMENT "", `refund_reason` text REPLACE_IF_NOT_NULL NULL COMMENT "", `note` text REPLACE_IF_NOT_NULL NULL COMMENT "", `ship_company` text REPLACE_IF_NOT_NULL NULL COMMENT "", `ship_no` text REPLACE_IF_NOT_NULL NULL COMMENT "", `ship_time` datetime REPLACE_IF_NOT_NULL NULL COMMENT "", `receive_type` text REPLACE_IF_NOT_NULL NULL COMMENT "", `refund_type` text REPLACE_IF_NOT_NULL NULL COMMENT "", `refund_kind` text REPLACE_IF_NOT_NULL NULL COMMENT "", `initiate` text REPLACE_IF_NOT_NULL NULL COMMENT "", `refund_success_time` datetime REPLACE_IF_NOT_NULL NULL COMMENT "", `refund_status` text REPLACE_IF_NOT_NULL NULL COMMENT "", `refund_update_time` datetime REPLACE_IF_NOT_NULL NULL COMMENT "", `deleted` text REPLACE_IF_NOT_NULL NULL COMMENT "", `create_time` datetime REPLACE_IF_NOT_NULL NULL COMMENT "", `update_time` datetime REPLACE_IF_NOT_NULL NULL COMMENT "" ) ENGINE=OLAP AGGREGATE KEY(`refund_id`, `refund_create_time`, `oid`, `iid`, `app_id`, `store_id`, `user_id`) COMMENT "退款" DISTRIBUTED BY HASH(`refund_id`) BUCKETS 10 PROPERTIES ( "replication_allocation" = "tag.location.default: 3", "in_memory" = "false", "storage_format" = "V2" ); CREATE TABLE `dim_sku` ( `id` bigint(20) NOT NULL COMMENT "", `spu_id` bigint(20) NULL COMMENT "", `spu_spec_id` bigint(20) NULL COMMENT "", `spu_create_time` datetime NULL COMMENT "", `key1` text REPLACE_IF_NOT_NULL NULL COMMENT "", `value1` text REPLACE_IF_NOT_NULL NULL COMMENT "", `key2` text REPLACE_IF_NOT_NULL NULL COMMENT "", `value2` text REPLACE_IF_NOT_NULL NULL COMMENT "", `key3` text REPLACE_IF_NOT_NULL NULL COMMENT "", `value3` text REPLACE_IF_NOT_NULL NULL COMMENT "", `spu_spec_code` text REPLACE_IF_NOT_NULL NULL COMMENT "", `price` bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "", `market_price` bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "", `cost_price` bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "", `points_limit` bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "", `points_send` bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "", `weight` bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "", `gift_ride_id` bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "", `gift_ride_name` text REPLACE_IF_NOT_NULL NULL COMMENT "", `gift_ride_amount` bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "", `gift_ride_content` text REPLACE_IF_NOT_NULL NULL COMMENT "", `app_ids` text REPLACE_IF_NOT_NULL NULL COMMENT "", `app_names` text REPLACE_IF_NOT_NULL NULL COMMENT "", `store_id` bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "", `brand_id` bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "", `brand_name` text REPLACE_IF_NOT_NULL NULL COMMENT "", `brand_type` text REPLACE_IF_NOT_NULL NULL COMMENT "", `cate_id` bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "", `group_ids` text REPLACE_IF_NOT_NULL NULL COMMENT "", `group_names` text REPLACE_IF_NOT_NULL NULL COMMENT "", `supplier_id` bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "", `supplier_name` text REPLACE_IF_NOT_NULL NULL COMMENT "", `spu_kind` text REPLACE_IF_NOT_NULL NULL COMMENT "", `channel` text REPLACE_IF_NOT_NULL NULL COMMENT "", `spu_title` text REPLACE_IF_NOT_NULL NULL COMMENT "", `spu_sub_title` text REPLACE_IF_NOT_NULL NULL COMMENT "", `spu_share_title` text REPLACE_IF_NOT_NULL NULL COMMENT "", `start_num` int(11) REPLACE_IF_NOT_NULL NULL COMMENT "", `limit_channel` text REPLACE_IF_NOT_NULL NULL COMMENT "", `limit_menthod` text REPLACE_IF_NOT_NULL NULL COMMENT "", `limit_num` int(11) REPLACE_IF_NOT_NULL NULL COMMENT "", `delivery_method` text REPLACE_IF_NOT_NULL NULL COMMENT "", `spu_system` text REPLACE_IF_NOT_NULL NULL COMMENT "", `labels` text REPLACE_IF_NOT_NULL NULL COMMENT "", `settle_method` text REPLACE_IF_NOT_NULL NULL COMMENT "", `include_all_shops` text REPLACE_IF_NOT_NULL NULL COMMENT "", `exclude_shops` text REPLACE_IF_NOT_NULL NULL COMMENT "", `refund_kind` text REPLACE_IF_NOT_NULL NULL COMMENT "", `spu_status` text REPLACE_IF_NOT_NULL NULL COMMENT "", `up_date` datetime REPLACE_IF_NOT_NULL NULL COMMENT "", `down_date` datetime REPLACE_IF_NOT_NULL NULL COMMENT "", `last_up_date` datetime REPLACE_IF_NOT_NULL NULL COMMENT "", `last_down_date` datetime REPLACE_IF_NOT_NULL NULL COMMENT "", `effect_start_time` datetime REPLACE_IF_NOT_NULL NULL COMMENT "", `effect_end_time` datetime REPLACE_IF_NOT_NULL NULL COMMENT "", `take_effect_hours` bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "", `effect_days` bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "", `spu_update_time` datetime REPLACE_IF_NOT_NULL NULL COMMENT "", `deleted` text REPLACE_IF_NOT_NULL NULL COMMENT "", `create_time` datetime REPLACE_IF_NOT_NULL NULL COMMENT "", `update_time` datetime REPLACE_IF_NOT_NULL NULL COMMENT "" ) ENGINE=OLAP AGGREGATE KEY(`id`, `spu_id`, `spu_spec_id`, `spu_create_time`) COMMENT "SKU维度表" DISTRIBUTED BY HASH(`id`) BUCKETS 10 PROPERTIES ( "replication_allocation" = "tag.location.default: 3", "in_memory" = "false", "storage_format" = "V2" ); * 相关Issue： </div> -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org --------------------------------------------------------------------- To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org For additional commands, e-mail: commits-h...@doris.apache.org
