FirstInputEmptyTime耗时很长

💬 StarRocks 用户问答
#1

【详述】问题详细描述：FirstInputEmptyTime时间很长，请问这个是什么原因导致

image
image605×832 17.4 KB

#2

发个完整的Profile？SQL中Union多吗？

#3

profile.txt (16.3 KB) 不多单表待条件查询

#4

卡在IO上了，RWID或BDYYID创建个bloomfilter index 试试？
BDYYID和RWID区分度高吗，哪个基数高，用哪个创建bloom filter index

#5

看Profile，好像已经创建了?

#6

“bloom_filter_columns” = “BDLX,BDFS,RWID,BDYYID” 这三个字段加了bloomfilter index；IO您这怎么看出来的，能给教一下么

#7

表结构发下， show create table

#8

你看下， 是不是数把倾斜的比较厉害，

#9

有些分桶数据量比较多，有些比较少？

#10

https://starrocks-public.oss-cn-zhangjiakou.aliyuncs.com/tools.tar.gz 用这个工具执行结果是空的

#11

image
image407×957 30.7 KB

#12

CREATE TABLE swk_comp_result (
ID varchar(65533) NOT NULL ,
CREATE_TIME datetime NOT NULL,
BDLX varchar(65533) NULL,
BDFS varchar(65533) NOT NULL,
BDDF decimal128(32, 10) NOT NULL ,
BDPM int(11) NOT NULL",
RWID varchar(65533) NULL ,
BDBIZID varchar(65533) NULL,
BDYYID varchar(65533) NULL,
BDWJID varchar(65533) NULL,
BDWJMC varchar(65533) NULL,
BDWJJCBH varchar(65533) NULL,
BDWJWZBH varchar(65533) NULL",
BDWJCCDZ varchar(65533) NULL,
BDWJCCLX varchar(65533) NULL,
BDMZSWID varchar(65533) NULL,
BDMZBIZID varchar(65533) NULL,
BDMZYBBH varchar(65533) NULL,
BDMZWJID varchar(65533) NULL,
BDMZWJMC varchar(65533) NULL,
BDMZWJJCBH varchar(65533) NULL,
BDMZWJWZBH varchar(65533) NULL,
BDMZWJCCDZ varchar(65533) NULL,
BDMZWJCCLX varchar(65533) NULL,
RYJCXXCJBH varchar(65533) NULL,
XM varchar(65533) NULL,
CYZJMC varchar(65533) NULL,
ZJHM varchar(65533) NULL,
GMSFHM varchar(65533) NULL,
XB varchar(65533) NULL,
HJD_DZMC varchar(65533) NULL,
XZD_DZMC varchar(65533) NULL,
CSD_DZMC varchar(65533) NULL,
CQJZD_DZMC varchar(65533) NULL,
BCJRYLBMC varchar(65533) NULL,
CSRQ datetime NULL,
CJSJ datetime NULL,
ZCSJ datetime NULL,
SBSJ datetime NULL,
AJBH varchar(65533) NULL,
JJBH varchar(65533) NULL,
XCKYBH varchar(65533) NULL,
AJMC varchar(65533) NULL,
AJXZ varchar(65533) NULL,
AJXZMC varchar(65533) NULL,
AJZT varchar(65533) NULL,
AJLBMC varchar(65533) NULL,
DXZPAJLBMC varchar(65533) NULL,
AJFABMC varchar(65533) NULL,
AYMC varchar(65533) NULL,
FAYMC varchar(65533) NULL,
FASJ datetime NULL,
FADZ varchar(65533) NULL,
FADZXZQHMC varchar(65533) NULL,
XYRZHLX varchar(65533) NULL,
XYR_ACCOUNT_ID varchar(65533) NULL,
XYR_ACCOUNT varchar(65533) NULL,
XYRNC varchar(65533) NULL,
GJC varchar(65533) NULL,
CJDWBM varchar(65533) NULL,
CJDWMC varchar(65533) NULL,
BDYQMC varchar(65533) NULL,
BDYQBB varchar(65533) NULL,
RDRYXM varchar(65533) NULL,
RDDWDM varchar(65533) NULL,
RDDWMC varchar(65533) NULL,
GXJG varchar(65533) NULL,
RDSJ datetime NULL,
BDSJ datetime NULL,
SJLY varchar(65533) NULL,
XTLY varchar(65533) NULL,
DWBM varchar(65533) NOT NULL,
DWMC varchar(65533) NOT NULL,
CREATE_XM varchar(65533) NULL,
CREATE_ID varchar(65533) NULL,
CREATE_TIME datetime NOT NULL,
UPDATE_TIME datetime NULL,
SFSC tinyint(4) NOT NULL,
HJD_XZQHDM varchar(65533) NULL,
HJD_XZQHMC varchar(65533) NULL,
SKBDJG tinyint(4) NULL
) ENGINE=OLAP
PRIMARY KEY(ID, CREATE_TIME)
PARTITION BY RANGE(CREATE_TIME)
(PARTITION p202112 VALUES [(‘2021-12-01 00:00:00’), (‘2022-01-01 00:00:00’)),
PARTITION p202201 VALUES [(‘2022-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’)),
PARTITION p202401 VALUES [(‘2024-01-01 00:00:00’), (‘2024-02-01 00:00:00’)),
PARTITION p202402 VALUES [(‘2024-02-01 00:00:00’), (‘2024-03-01 00:00:00’)),
PARTITION p202403 VALUES [(‘2024-03-01 00:00:00’), (‘2024-04-01 00:00:00’)),
PARTITION p202404 VALUES [(‘2024-04-01 00:00:00’), (‘2024-05-01 00:00:00’)),
PARTITION p202405 VALUES [(‘2024-05-01 00:00:00’), (‘2024-06-01 00:00:00’)),
PARTITION p202406 VALUES [(‘2024-06-01 00:00:00’), (‘2024-07-01 00:00:00’)),
PARTITION p202407 VALUES [(‘2024-07-01 00:00:00’), (‘2024-08-01 00:00:00’)),
PARTITION p202408 VALUES [(‘2024-08-01 00:00:00’), (‘2024-09-01 00:00:00’)),
PARTITION p202409 VALUES [(‘2024-09-01 00:00:00’), (‘2024-10-01 00:00:00’)),
PARTITION p202410 VALUES [(‘2024-10-01 00:00:00’), (‘2024-11-01 00:00:00’)),
PARTITION p202411 VALUES [(‘2024-11-01 00:00:00’), (‘2024-12-01 00:00:00’)),
PARTITION p202412 VALUES [(‘2024-12-01 00:00:00’), (‘2024-12-31 00:00:00’)))

DISTRIBUTED BY HASH(ID) BUCKETS 50
PROPERTIES (
replication_num = “2”,
bloom_filter_columns = “BDFS, RWID, BDYQMC, BDYYID”,
in_memory = “false”,
storage_format = “DEFAULT”,
enable_persistent_index = “true”
);

#13

请问您这在哪看的

#14

这个ID是UUID吗？

#15

机器内存充裕吗？

#16

第二次执行，是不是就快了？

#17

内存 128GB的还可以，不是一致慢，现在执行基本都再20s左右

#18

SELECT XM from swk_comp_result WHERE RWID =‘1a61ffe8-a8b0-41cd-ae86-6fc18bdfffe9’ and BDYYID = ‘50a1df37-20c1-425a-a95c-21710414a68c’ and BDFS =‘MATERIAL’ ; 这个执行多久

#19

当前原因已确定，机械盘，而且上面部署有ES，抢占了大量磁盘的IOPS，PageCache也会被ES冲刷。当前的解决方案是，统了两台内存比较大的，创建了colocate表，添加了PageCache，可以优化到0.3秒，规避了这个问题。

#20

感谢大神，已成解决问题

京ICP备2022026421号-1