【详述】线上执行routine load任务，多个大表同时进行，有的入库速度正常，有的入库速度很慢，只有几十w条几分钟内。
任务信息
CREATE ROUTINE LOAD linkcool_4kmiles_db_pro.t_amzdb_campaign_automation_log ON t_amzdb_campaign_automation_log
COLUMNS
(
Id,
UserId,
AccountId,
Site,
ActionType,
ObjectType,
RuleName,
CampaignName,
BeforeValue,
AfterValue,
BudgetReference,
StartDate,
EndDate,
Editor,
CreateTime,
AdGroupName,
TargetingName,
RuleType,
AdautoruleId,
CampaignId,
AdGroupId,
TargetId,
BelongDate,
TargetingType,
MatchType,
AdLabelId,
BeijingTime,
BatchId,
FailureMsg,
state,
KeywordId,
NegativeTargetId,
NegativeKeywordId,
autoTargetId,
Sku,
Asin,
AdId,
SmartLabel,
FailureMsgStr,
LogExt,
TargetingNameEn,
sr_op,
__op = sr_op
)
PROPERTIES
(
“format” = “json”,
“jsonpaths” = “[”$.data.Id","$.data.UserId","$.data.AccountId","$.data.Site","$.data.ActionType","$.data.ObjectType","$.data.RuleName","$.data.CampaignName","$.data.BeforeValue","$.data.AfterValue","$.data.BudgetReference","$.data.StartDate","$.data.EndDate","$.data.Editor","$.data.CreateTime","$.data.AdGroupName","$.data.TargetingName","$.data.RuleType","$.data.AdautoruleId","$.data.CampaignId","$.data.AdGroupId","$.data.TargetId","$.data.BelongDate","$.data.TargetingType","$.data.MatchType","$.data.AdLabelId","$.data.BeijingTime","$.data.BatchId","$.data.FailureMsg","$.data.state","$.data.KeywordId","$.data.NegativeTargetId","$.data.NegativeKeywordId","$.data.autoTargetId","$.data.Sku","$.data.Asin","$.data.AdId","$.data.SmartLabel","$.data.FailureMsgStr","$.data.LogExt","$.data.TargetingNameEn","$.srOp"]",
“desired_concurrent_number” = “3”,
“max_batch_interval” = “60”
)
FROM KAFKA
(
“kafka_broker_list” = “xxxx”,
“kafka_topic” = “t_amzdb_campaign_automation_log”,
“property.security.protocol” = “SASL_SSL”,
“property.sasl.mechanism” = “SCRAM-SHA-512”,
“property.sasl.username” = “xxxx”,
“property.sasl.password” = “xxxx”,
“property.group.id” = “t_amzdb_campaign_automation_log_sr_group”,
“kafka_partitions” = “0,1,2”,
“kafka_offsets” = “OFFSET_END,OFFSET_END,OFFSET_END”
);
表信息：
CREATE TABLE t_amzdb_campaign_automation_log_test (
Id bigint(20) NOT NULL COMMENT “”,
BelongDate date NOT NULL COMMENT “所属站点日期”,
UserId int(11) NOT NULL COMMENT “”,
AccountId int(11) NOT NULL COMMENT “”,
Site varchar(65533) NOT NULL COMMENT “”,
ActionType int(11) NULL DEFAULT “0” COMMENT “1:add, 2:detele, 3:edit, 4:distribute”,
ObjectType int(11) NOT NULL DEFAULT “0” COMMENT “1:budget，2：bid,3:targeting,4:search term,5:Negative targeting 6 :rule”,
RuleName varchar(65533) NULL COMMENT “”,
CampaignName varchar(65533) NULL COMMENT “”,
BeforeValue varchar(65533) NULL COMMENT “”,
AfterValue varchar(65533) NULL COMMENT “”,
BudgetReference int(11) NULL DEFAULT “0” COMMENT “1【上周同日/same day last week】 2【上周日均/average daily of last week】 3【上月日均/average daily of last month】 4【固定金额/certain budget】”,
StartDate date NULL COMMENT “”,
EndDate date NULL COMMENT “”,
Editor varchar(65533) NULL COMMENT “”,
CreateTime datetime NOT NULL COMMENT “”,
AdGroupName varchar(65533) NULL COMMENT “”,
TargetingName varchar(65533) NULL COMMENT “”,
RuleType int(11) NULL DEFAULT “0” COMMENT “规则类型”,
AdautoruleId int(11) NULL DEFAULT “0” COMMENT “规则Id”,
CampaignId bigint(20) NULL DEFAULT “0” COMMENT “”,
AdGroupId bigint(20) NULL COMMENT “”,
TargetId bigint(20) NULL COMMENT “”,
TargetingType int(11) NULL DEFAULT “0” COMMENT “投放类型 1 ||关键词 ||Keyword || || 3 ||类目/商品 ||Categories/products || || 2 ||投放定位组 ||Targeting group || || 4 ||否定关键词 ||Negative keyword || || 5 ||否定品牌/商品 ||Negative brands/products || ||”,
MatchType varchar(65533) NULL COMMENT “”,
AdLabelId int(11) NULL DEFAULT “0” COMMENT “自动化标签id”,
BeijingTime datetime NULL COMMENT “北京时间，在修改和添加规则的时候才有值”,
BatchId varchar(65533) NULL COMMENT “”,
FailureMsg varchar(65533) NULL COMMENT “”,
state int(11) NULL COMMENT “”,
KeywordId bigint(20) NULL COMMENT “关键词投放Id”,
NegativeTargetId bigint(20) NULL COMMENT “否定投放Id”,
NegativeKeywordId bigint(20) NULL COMMENT “否定关键词投放Id”,
autoTargetId bigint(20) NULL COMMENT “否定投放Id”,
Sku varchar(65533) NULL COMMENT “sp，sd执行的sku”,
Asin varchar(65533) NULL COMMENT “sbv的执行asin”,
AdId bigint(20) NULL COMMENT “AdId”,
SmartLabel int(11) NULL DEFAULT “0” COMMENT “0是默认， 1是高效词，2是低效词”,
FailureMsgStr varchar(65533) NULL COMMENT “错误信息翻译”,
LogExt varchar(65533) NULL COMMENT “日志拓展字段”,
TargetingNameEn varchar(65533) NULL COMMENT “投放名称-英文版本”
) ENGINE=OLAP
PRIMARY KEY(Id, BelongDate, UserId, AccountId, Site)
COMMENT “OLAP”
PARTITION BY date_trunc(‘day’, BelongDate)
DISTRIBUTED BY HASH(Id)
PROPERTIES (
“replication_num” = “1”,
“datacache.enable” = “true”,
“storage_volume” = “builtin_storage_volume”,
“enable_async_write_back” = “false”,
“enable_persistent_index” = “true”,
“persistent_index_type” = “LOCAL”,
“compression” = “LZ4”
);
【背景】做过哪些操作？
【业务影响】
【是否存算分离】是
【StarRocks版本】3.2.6
【集群规模】3fe（1 follower+2observer）+ 3cn
【表模型】主键模型
【附件】
cn.INFO日志
I0513 13:13:41.859357 391 plan_fragment_executor.cpp:493] Fragment 5e99ac8a-e1b7-448a-8f68-71f10101843c:(Active: 30s719ms, non-child: 0.01%)
- InstanceAllocatedMemoryUsage: 6.76 GB
- InstanceDeallocatedMemoryUsage: 6.97 GB
- InstancePeakMemoryUsage: 2.93 MB
- MemoryLimit: 2.00 GB
- RowsProduced: 310.74K
OlapTableSink:(Active: 964.943ms, non-child: 3.14%)- TxnID: 1935371
- IndexNum: 1
- ReplicatedStorage: true
- AutomaticPartition: true
- AutomaticBucketSize: 0
- AllocAutoIncrementTime: 21.989us
- CloseWaitTime: 608.953ms
- OpenTime: 2.505ms
- PrepareDataTime: 49.107ms
- ConvertChunkTime: 238.971us
- ValidateDataTime: 21.450ms
- RowsFiltered: 0
- RowsRead: 310.74K
- RowsReturned: 310.74K
- RpcClientSideTime: 1s574ms
- RpcServerSideTime: 1s306ms
- RpcServerWaitFlushTime: 0.000ns
- SendDataTime: 302.818ms
- PackChunkTime: 171.635ms
- SendRpcTime: 100.489ms
- CompressTime: 0.000ns
- SerializeChunkTime: 39.435ms
- WaitResponseTime: 98.038ms
FILE_SCAN_NODE (id=0):(Active: 29s752ms, non-child: 96.85%)
- BytesRead: 0
- IOTaskExecTime: 0.000ns
- IOTaskWaitTime: 0.000ns
- NumDiskAccess: 0
- PeakMemoryUsage: 0
- RowsRead: 0
- RowsReturned: 310.74K
- RowsReturnedRate: 10.44 K/sec
- ScanTime: 30s107ms
- ScannerQueueCounter: 1
- ScannerQueueTime: 3.046us
- ScannerThreadsInvoluntaryContextSwitches: 0
- ScannerThreadsTotalWallClockTime: 0.000ns
- MaterializeTupleTime(*): 0.000ns
- ScannerThreadsSysTime: 0.000ns
- ScannerThreadsUserTime: 0.000ns
- ScannerThreadsVoluntaryContextSwitches: 0
- TotalRawReadTime(*): 0.000ns
- TotalReadThroughput: 0.00 /sec
DataSource:- DataSourceType: FileDataSource
- FileScanner: 0
- CastChunkTime: 25.570ms
- CreateChunkTime: 3.556ms
- FileReadCount: 310.74K
- FileReadTime: 1s502ms
- FillTime: 0.000ns
- MaterializeTime: 4.778ms
- ReadTime: 0.000ns
- ScannerTotalTime: 30s106ms
I0513 13:10:09.189026 391 tablet_sink_index_channel.cpp:757] OlapTableSink txn_id: 1934650 load_id: 843a3343-f556-4de1-bd3c-c2352e4b71cc commit 11 tablets: 2156197,2156269,2156193,2156047,2156049,2156045,2156265,2156195,2156189,2156191,2156267
I0513 13:10:09.189043 391 tablet_sink_sender.cpp:332] Olap table sink statistics. load_id: 843a3343-f556-4de1-bd3c-c2352e4b71cc, txn_id: 1934650, add chunk time(ms)/wait lock time(ms)/num: {2109096:(0)(0)(1)} {2109095:(10288)(0)(141)} {2109133:(0)(0)(1)}
I0513 13:10:09.189332 391 plan_fragment_executor.cpp:493] Fragment 843a3343-f556-4de1-bd3c-c2352e4b71cd:(Active: 1m, non-child: 0.01%)
- InstanceAllocatedMemoryUsage: 12.47 GB
- InstanceDeallocatedMemoryUsage: 12.86 GB
- InstancePeakMemoryUsage: 2.95 MB
- MemoryLimit: 2.00 GB
- RowsProduced: 574.47K
OlapTableSink:(Active: 9s027ms, non-child: 14.86%)- TxnID: 1934650
- IndexNum: 1
- ReplicatedStorage: true
- AutomaticPartition: true
- AutomaticBucketSize: 0
- AllocAutoIncrementTime: 35.661us
- CloseWaitTime: 653.856ms
- OpenTime: 2.374ms
- PrepareDataTime: 93.566ms
- ConvertChunkTime: 513.374us
- ValidateDataTime: 41.834ms
- RowsFiltered: 0
- RowsRead: 574.47K
- RowsReturned: 574.47K
- RpcClientSideTime: 10s788ms
- RpcServerSideTime: 10s288ms
- RpcServerWaitFlushTime: 0.000ns
- SendDataTime: 8s275ms
- PackChunkTime: 310.456ms
- SendRpcTime: 173.848ms
- CompressTime: 0.000ns
- SerializeChunkTime: 59.693ms
- WaitResponseTime: 7s907ms
FILE_SCAN_NODE (id=0):(Active: 51s722ms, non-child: 85.13%)
- BytesRead: 0
- IOTaskExecTime: 0.000ns
- IOTaskWaitTime: 0.000ns
- NumDiskAccess: 0
- PeakMemoryUsage: 0
- RowsRead: 0
- RowsReturned: 574.47K
- RowsReturnedRate: 11.11 K/sec
- ScanTime: 55s981ms
- ScannerQueueCounter: 3
- ScannerQueueTime: 44s311ms
- ScannerThreadsInvoluntaryContextSwitches: 0
- ScannerThreadsTotalWallClockTime: 0.000ns
- MaterializeTupleTime(*): 0.000ns
- ScannerThreadsSysTime: 0.000ns
- ScannerThreadsUserTime: 0.000ns
- ScannerThreadsVoluntaryContextSwitches: 0
- TotalRawReadTime(*): 0.000ns
- TotalReadThroughput: 0.00 /sec
DataSource:- DataSourceType: FileDataSource
- FileScanner: 0
- CastChunkTime: 48.428ms
- CreateChunkTime: 6.832ms
- FileReadCount: 574.47K
- FileReadTime: 2s806ms
- FillTime: 0.000ns
- MaterializeTime: 8.979ms
- ReadTime: 0.000ns
- ScannerTotalTime: 55s980ms