doris 版本 2.1.3
现象:通过routine load 方式将数据从kafka导入到doris的TPS不及预期,有什么可以优化的参数调整能够提高写入TPS呢
目前 routine load的并发已经调整到了8.
整个集群的规模:
FE数量为:1,8核32G
BE数量为:7,8核64G
routine load 创建命令:
CREATE ROUTINE LOAD ld_tag_devices_unique_adx ON tag_devices
COLUMNS(bid_date_time,os,country,ifa,app_bundle,user_agent,last_ip,country=upper(if((length(`country`) != 2), 'BAD', `country`)),dev_id=if(length(ifa)>20, ifa, md5sum(last_ip,user_agent)),last_tag_datetime=`bid_date_time`,tag_value=`app_bundle`,tag_type='bundle'),
WHERE length(country)=2
PROPERTIES
(
"desired_concurrent_number"="8",
"max_batch_interval" = "40",
"max_batch_rows" = "1000000",
"max_batch_size" = "809715200",
"strict_mode" = "false",
"format" = "json",
"max_error_number" = "200000"
)
FROM KAFKA
(
"kafka_broker_list" = "xxxx:9092",
"kafka_topic" = "topicxxxx",
"property.kafka_default_offsets" = "OFFSET_END",
"property.group.id" = "gpxxxx"
);
CREATE TABLE `devices_unique` (
`country` VARCHAR(20) NULL,
`dev_id` VARCHAR(60) NULL,
`os` VARCHAR(20) NULL,
`model` VARCHAR(6000) NULL,
`make` VARCHAR(6000) NULL,
`bundle` VARCHAR(6000) NULL,
`language` VARCHAR(6000) NULL,
`user_agent` VARCHAR(6000) NULL,
`last_ip` VARCHAR(6000) NULL,
`osv` VARCHAR(6000) NULL,
`city` VARCHAR(6000) NULL,
`region` VARCHAR(6000) NULL,
`device_type` SMALLINT NULL,
`connection_type` SMALLINT NULL,
`last_date` DATE NULL,
`last_datetime` DATETIME NULL,
`ifa` VARCHAR(60) NULL,
`source` VARCHAR(2000) NULL,
INDEX idx_model (`model`) USING INVERTED COMMENT '',
INDEX idx_make (`make`) USING INVERTED COMMENT ''
) ENGINE=OLAP
UNIQUE KEY(`country`, `dev_id`)
COMMENT 'OLAP'
PARTITION BY LIST(`country`)
(PARTITION p_AGG1 VALUES IN ("AE","SA","BR","GB","UK","KR","MX","MY","BAD"),
PARTITION p_ID VALUES IN ("ID"),
PARTITION p_IN VALUES IN ("IN"),
PARTITION p_OTHER VALUES IN ("NG","SG","TK","NU","TV","ER","FK","MS","PN","BL","KI","VA","IO","WF","NR","PM","CK","AS","VU","SM","TC","LI","AX","MC","TO","SB","ST","FO","MH","GL","YT","GI","FM","VG","WS","PW","GG","CF","AD","AI","LS","VC","LC","AW","IM","JE","PG","NC","GW","GD","KM","KN","DJ","SZ","CV","BI","DM","KY","MO","FJ","TM","BW","GP","CW","UM","MQ","BS","IS","GQ","SS","AG","BT","BF","BM","MP","MW","PF","ME","GY","LR","NE","NA","BB","TL","SR","BQ","ZW","SL","SI","RE","CU","MG","GF","TD","MZ","BN","GM","CY","MT","HR","TT","CG","SK","BA","RW","VI","BJ","TG","GA","CR","SV","ML","GN","MR","HT","NI","MK","MF","ZM","TJ","PA","EE","AO","GU","TZ","UG","LU","KG","HN","BZ","LT","HU","CD","SD","AM","GR","JM","UZ","LV","KE","AT","PT","MV","SC","AZ","CM","SN","MN","MD","NO","BY","ET","SO","BG","AL","BH","NZ","UY","TN","IE","JO","DZ","MM","LA","SX","GH","BE","YE","CI","LK","CZ","PS","BO","CH","AF","RO","PY","VE","NF","DO","ES","OM","FI","PR","DK","SY","MU","LB","CL","IL","KH","SE","AU","KW","IR","LY","EC","MA","TR","NP","GE","QA","CO","AR","KZ","GT","HK","CN","EG","PE","BD","JP","PL","IT","UA","IQ","ZA","CA","RS","PK","FR","NL","TW","DE"),
PARTITION p_PH VALUES IN ("PH"),
PARTITION p_RU VALUES IN ("RU"),
PARTITION p_TH VALUES IN ("TH"),
PARTITION p_US VALUES IN ("US"),
PARTITION p_VN VALUES IN ("VN"))
DISTRIBUTED BY HASH(`dev_id`) BUCKETS 32
PROPERTIES (
"replication_allocation" = "tag.location.default: 1",
"min_load_replica_num" = "-1",
"is_being_synced" = "false",
"storage_medium" = "hdd",
"storage_format" = "V2",
"inverted_index_storage_format" = "V1",
"compression" = "ZSTD",
"enable_unique_key_merge_on_write" = "true",
"light_schema_change" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false",
"group_commit_interval_ms" = "10000",
"group_commit_data_bytes" = "134217728"
);