You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@doris.apache.org by "wanqiang2046 (via GitHub)" <gi...@apache.org> on 2023/04/03 09:42:00 UTC
[GitHub] [doris] wanqiang2046 opened a new issue, #18349: 小批量更新的时候，BE节点CPU200%以上
wanqiang2046 opened a new issue, #18349:
URL: https://github.com/apache/doris/issues/18349
### Search before asking
- [X] I had searched in the [issues](https://github.com/apache/doris/issues?q=is%3Aissue) and found no similar issues.
### Version
doris-1.2.3-rc02-Unknown
### What's Wrong?
在用UNIQUE模型做业务处理，进行小批量（几千条数据）更新的情况下，CPU一直在200%以上，更新语句执行超级慢，另外日志打印也不正常。
这是我建表语句：
CREATE TABLE `dwd_bi_waybill_scan` (
`id` varchar(64) NOT NULL COMMENT '组合ID',
`waybill_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '运单tt_waybill创建时间',
`bu_id` bigint(20) NOT NULL COMMENT '业务ID',
`tb_id` smallint(6) NOT NULL DEFAULT "1" COMMENT '汇聚表类型，如1-扫描表，2-异常登记表',
`country_code` varchar(32) NOT NULL DEFAULT "" COMMENT '国家编码',
`waybill_code` varchar(64) NOT NULL DEFAULT "" COMMENT '运单编号',
`main_waybill_code` varchar(64) NULL DEFAULT "" COMMENT '主运单号',
`order_no` varchar(64) NULL DEFAULT "" COMMENT '订单号',
`order_time` datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT '下单时间',
`scan_type` int(11) NOT NULL COMMENT '扫描类型 1、揽收。2、发件。3、到件。4、派件。5、签收。-1、问题件。-2、异常件',
`sub_type` varchar(32) NULL DEFAULT "" COMMENT '子类型：无分类时同主扫描类型，异常签收、问题件有子类型',
`abnormal_sub_type` varchar(32) NULL DEFAULT "" COMMENT '异常子类明细:目前问题问题件有',
`operation_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '操作时间',
`operation_date` date NOT NULL COMMENT '操作时间',
`operator_code` varchar(64) NOT NULL DEFAULT "" COMMENT '操作人编号',
`site_code` varchar(64) NOT NULL DEFAULT "" COMMENT '操作网点编号',
`site_type` varchar(64) NULL DEFAULT "" COMMENT '操作网点类型:SL01-总部,SL02-财务中心,SL03-分拨中心,SL04-一级网点,SL05-二级网点',
`current_status` varchar(16) NULL COMMENT '运单状态',
`data_source` smallint(6) NOT NULL DEFAULT "6" COMMENT '数据源:1-K9, 6-订单中心',
`bl_return` tinyint(4) NOT NULL DEFAULT "0" COMMENT '是否退件:0-未退件, 1-退件',
`bl_return_sign` tinyint(4) NOT NULL DEFAULT "0" COMMENT '是否退件签收:0-未退件签收, 1-退件签收',
`bl_sign` tinyint(4) NOT NULL DEFAULT "0" COMMENT '是否签收:0-未签收, 1-已签收',
`operation_no` smallint(6) NULL DEFAULT "0" COMMENT '扫描历史操作次数',
`customer_code` varchar(100) NOT NULL DEFAULT "" COMMENT '客户代码',
`cod` decimal(15, 2) NULL DEFAULT "0.00" COMMENT '代收货款',
`order_type_code` varchar(8) NOT NULL DEFAULT "" COMMENT '订单类型:1快递,2零担,3整车',
`transport_type` varchar(8) NULL DEFAULT "" COMMENT '运输方式:TT01汽运,TT02空运,TT03海运,TT04海空运',
`package_type` tinyint(4) NULL DEFAULT "-1" COMMENT '包裹类型:-1默认 0经济 1标准 2标准特货 3无忧简易 4经济特货 5速达非埃邮泛非标准专线',
`order_product_type_code` varchar(8) NULL DEFAULT "" COMMENT '产品类型:ST01本地件,ST02国内直发,ST03海外仓发,ST04国际快递,ST05海外仓头程',
`picked_time` datetime NULL COMMENT '揽收时间',
`picked_site_code` varchar(64) NULL DEFAULT "" COMMENT '揽收网点编号',
`picked_site_type` varchar(64) NULL DEFAULT "" COMMENT '揽收网点类型',
`pre_site_code` varchar(64) NULL DEFAULT "" COMMENT '上一站网点编号',
`pre_site_type` varchar(64) NULL DEFAULT "" COMMENT '上一站网点类型',
`next_site_code` varchar(64) NULL DEFAULT "" COMMENT '下一站网点编号',
`next_site_type` varchar(64) NULL DEFAULT "" COMMENT '下一站网点类型',
`actor_code` varchar(100) NULL DEFAULT "" COMMENT '签收人/派件员',
`sign_time` datetime NULL COMMENT '正常签收时间',
`remark` varchar(128) NULL DEFAULT "" COMMENT '备注',
`enable_flag` tinyint(4) NOT NULL DEFAULT "0" COMMENT '有效标识:有效0, 删除1, 取消2',
`status` tinyint(4) NOT NULL DEFAULT "0" COMMENT '状态:0正常 1取消',
`bl_self` tinyint(4) NULL DEFAULT "0" COMMENT '是否自收自派:0否 1是',
`bl_last_return` tinyint(4) NULL DEFAULT "0" COMMENT '是否最后退件网点:0否 1是',
`bl_last_sign` tinyint(4) NULL DEFAULT "0" COMMENT '是否最后签收网点:0否 1是',
`bl_day_first_come` tinyint(4) NULL DEFAULT "0" COMMENT '是否当天第一次到件:0否 1是',
`bl_abnormal_before_delivery` tinyint(4) NULL DEFAULT "0" COMMENT '派件前是否登记了异常件:0否 1是',
`bl_last_dispatching` tinyint(4) NULL DEFAULT "0" COMMENT '是否是最新派件中的记录:0否 1是',
`duty_site` varchar(64) NULL DEFAULT "" COMMENT '责任网点',
`create_by` varchar(64) NULL DEFAULT "" COMMENT '创建者',
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_by` varchar(64) NULL DEFAULT "" COMMENT '更新者',
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
INDEX id_idx (`id`) USING BITMAP COMMENT 'id index'
) ENGINE=OLAP
UNIQUE KEY(`id`, `waybill_time`)
COMMENT '扫描信息'
PARTITION BY RANGE(`waybill_time`)
(PARTITION p202201 VALUES [('0000-01-01 00:00:00'), ('2022-02-01 00:00:00')),
PARTITION p202202 VALUES [('2022-02-01 00:00:00'), ('2022-03-01 00:00:00')),
PARTITION p202203 VALUES [('2022-03-01 00:00:00'), ('2022-04-01 00:00:00')),
PARTITION p202204 VALUES [('2022-04-01 00:00:00'), ('2022-05-01 00:00:00')),
PARTITION p202205 VALUES [('2022-05-01 00:00:00'), ('2022-06-01 00:00:00')),
PARTITION p202206 VALUES [('2022-06-01 00:00:00'), ('2022-07-01 00:00:00')),
PARTITION p202207 VALUES [('2022-07-01 00:00:00'), ('2022-08-01 00:00:00')),
PARTITION p202208 VALUES [('2022-08-01 00:00:00'), ('2022-09-01 00:00:00')),
PARTITION p202209 VALUES [('2022-09-01 00:00:00'), ('2022-10-01 00:00:00')),
PARTITION p202210 VALUES [('2022-10-01 00:00:00'), ('2022-11-01 00:00:00')),
PARTITION p202211 VALUES [('2022-11-01 00:00:00'), ('2022-12-01 00:00:00')),
PARTITION p202212 VALUES [('2022-12-01 00:00:00'), ('2023-01-01 00:00:00')),
PARTITION p202301 VALUES [('2023-01-01 00:00:00'), ('2023-02-01 00:00:00')),
PARTITION p202302 VALUES [('2023-02-01 00:00:00'), ('2023-03-01 00:00:00')),
PARTITION p202303 VALUES [('2023-03-01 00:00:00'), ('2023-04-01 00:00:00')),
PARTITION p202304 VALUES [('2023-04-01 00:00:00'), ('2023-05-01 00:00:00')),
PARTITION p202305 VALUES [('2023-05-01 00:00:00'), ('2023-06-01 00:00:00')),
PARTITION p202306 VALUES [('2023-06-01 00:00:00'), ('2023-07-01 00:00:00')),
PARTITION p202307 VALUES [('2023-07-01 00:00:00'), ('2023-08-01 00:00:00')),
PARTITION p202308 VALUES [('2023-08-01 00:00:00'), ('2023-09-01 00:00:00')),
PARTITION p202309 VALUES [('2023-09-01 00:00:00'), ('2023-10-01 00:00:00')),
PARTITION p202310 VALUES [('2023-10-01 00:00:00'), ('2023-11-01 00:00:00')),
PARTITION p202311 VALUES [('2023-11-01 00:00:00'), ('2023-12-01 00:00:00')),
PARTITION p202312 VALUES [('2023-12-01 00:00:00'), ('2024-01-01 00:00:00')))
DISTRIBUTED BY HASH(`id`, `waybill_time`) BUCKETS 8
PROPERTIES (
"replication_allocation" = "tag.location.default: 1",
"dynamic_partition.enable" = "true",
"dynamic_partition.time_unit" = "MONTH",
"dynamic_partition.time_zone" = "Etc/UTC",
"dynamic_partition.start" = "-2147483648",
"dynamic_partition.end" = "6",
"dynamic_partition.prefix" = "p",
"dynamic_partition.replication_allocation" = "tag.location.default: 1",
"dynamic_partition.buckets" = "8",
"dynamic_partition.create_history_partition" = "false",
"dynamic_partition.history_partition_num" = "-1",
"dynamic_partition.hot_partition_num" = "0",
"dynamic_partition.reserved_history_periods" = "NULL",
"dynamic_partition.storage_policy" = "",
"dynamic_partition.storage_medium" = "HDD",
"dynamic_partition.start_day_of_month" = "1",
"in_memory" = "false",
"storage_format" = "V2",
"enable_unique_key_merge_on_write" = "true",
"disable_auto_compaction" = "false"
);
更新语句：
UPDATE `dwd_bi_waybill_scan` SET bl_last_return = 0, bl_abnormal_before_delivery = 0, update_time = now() WHERE id = '1-u-73852595' AND waybill_time = '2023-03-28 08:48:33.0'
### What You Expected?
希望提升一下更新的基本速度，然后BE-CPU保持相对稳定状态。
### How to Reproduce?
_No response_
### Anything Else?
_No response_
### Are you willing to submit PR?
- [ ] Yes I am willing to submit a PR!
### Code of Conduct
- [X] I agree to follow this project's [Code of Conduct](https://www.apache.org/foundation/policies/conduct)
--
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-unsubscribe@doris.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [I] 小批量更新的时候，BE节点CPU200%以上 [doris]
Posted by "Ruees (via GitHub)" <gi...@apache.org>.
Ruees commented on issue #18349:
URL: https://github.com/apache/doris/issues/18349#issuecomment-1788313418
I encountered a similar issue in version 1.2.6, where the CPU of the be node reached 390%. This issue should be noted
--
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-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [I] 小批量更新的时候，BE节点CPU200%以上 [doris]
Posted by "yitizi (via GitHub)" <gi...@apache.org>.
yitizi commented on issue #18349:
URL: https://github.com/apache/doris/issues/18349#issuecomment-1999117528
我也用的 unique表，删除的情况也是，我使用delete form语句删除大约300万条数据，be cpu ide瞬间从80%掉到 10%。
--
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-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [doris] wanqiang2046 commented on issue #18349: 小批量更新的时候，BE节点CPU200%以上
Posted by "wanqiang2046 (via GitHub)" <gi...@apache.org>.
wanqiang2046 commented on issue #18349:
URL: https://github.com/apache/doris/issues/18349#issuecomment-1494006629
BE日志一直打印类似于这些日志：
r_tablet_size=2, res=[E-3115]
I0403 09:32:13.431612 639 engine_publish_version_task.cpp:186] finish to publish version on transaction.transaction_id=3324162, cost(us): 3, error_tablet_size=1, res=[E-3115]
I0403 09:32:13.431615 641 engine_publish_version_task.cpp:186] finish to publish version on transaction.transaction_id=3324179, cost(us): 3, error_tablet_size=1, res=[E-3115]
I0403 09:32:13.431620 639 engine_publish_version_task.cpp:186] finish to publish version on transaction.transaction_id=3324189, cost(us): 3, error_tablet_size=1, res=[E-3115]
I0403 09:32:13.431623 641 engine_publish_version_task.cpp:186] finish to publish version on transaction.transaction_id=3324163, cost(us): 3, error_tablet_size=1, res=[E-3115]
I0403 09:32:13.431635 639 engine_publish_version_task.cpp:186] finish to publish version on transaction.transaction_id=3324158, cost(us): 10, error_tablet_size=1, res=[E-3115]
I0403 09:32:13.431648 641 engine_publish_version_task.cpp:186] finish to publish version on transaction.transaction_id=3324185, cost(us): 3, error_tablet_size=1, res=[E-3115]
I0403 09:32:13.431656 641 engine_publish_version_task.cpp:186] finish to publish version on transaction.transaction_id=3324159, cost(us): 4, error_tablet_size=1, res=[E-3115]
I0403 09:32:13.431660 639 engine_publish_version_task.cpp:186] finish to publish version on transaction.transaction_id=3324194, cost(us): 4, error_tablet_size=1, res=[E-3115]
--
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-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [doris] wanqiang2046 commented on issue #18349: 小批量更新的时候，BE节点CPU200%以上
Posted by "wanqiang2046 (via GitHub)" <gi...@apache.org>.
wanqiang2046 commented on issue #18349:
URL: https://github.com/apache/doris/issues/18349#issuecomment-1494017379
BE的warn日志大量打印：
failed to publish version|signature=3326621|transaction_id=3326621|error_tablets_num=1|error=[E-3115]
W0403 09:48:50.326066 641 task_worker_pool.cpp:723] failed to publish version|signature=3326619|transaction_id=3326619|error_tablets_num=1|error=[E-3115]
W0403 09:48:51.318729 642 task_worker_pool.cpp:723] failed to publish version|signature=3326622|transaction_id=3326622|error_tablets_num=1|error=[E-3115]
W0403 09:48:51.321202 642 task_worker_pool.cpp:723] failed to publish version|signature=3326623|transaction_id=3326623|error_tablets_num=1|error=[E-3115]
W0403 09:48:52.322594 639 task_worker_pool.cpp:723] failed to publish version|signature=3326624|transaction_id=3326624|error_tablets_num=1|error=[E-3115]
W0403 09:48:53.320883 639 task_worker_pool.cpp:723] failed to publish version|signature=3326626|transaction_id=3326626|error_tablets_num=1|error=[E-3115]
W0403 09:48:54.325161 641 task_worker_pool.cpp:723] failed to publish version|signature=3326629|transaction_id=3326629|error_tablets_num=1|error=[E-3115]
W0403 09:48:55.318825 640 task_worker_pool.cpp:723] failed to publish version|signature=3326635|transaction_id=3326635|error_tablets_num=1|error=[E-3115]
W0403 09:48:55.338490 638 task_worker_pool.cpp:723] failed to publish version|signature=3326634|transaction_id=3326634|error_tablets_num=1|error=[E-3115]
W0403 09:48:55.339264 638 task_worker_pool.cpp:723] failed to publish version|signature=3326636|transaction_id=3326636|error_tablets_num=1|error=[E-3115]
--
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-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [doris] stalary commented on issue #18349: 小批量更新的时候，BE节点CPU200%以上
Posted by "stalary (via GitHub)" <gi...@apache.org>.
stalary commented on issue #18349:
URL: https://github.com/apache/doris/issues/18349#issuecomment-1559490596
Try use insert.
--
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-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org