row_number()分析函数很慢

persistperson · 2022年08月16日 08:09

【详述】row_number()分析函数很慢
【业务影响】
【StarRocks版本】2.3.0
【集群规模】 3fe +4be
【机器信息】四台机器，每台机器配置(48c，251g，6块ssd盘)
表结构如下：
CREATE TABLE mid_ship_site_seq (
sub_ship_id bigint(20) NULL COMMENT “”,
ent_site_min_tm datetime NULL COMMENT “”,
ship_id int(11) NULL COMMENT “”,
unit_tm datetime NULL COMMENT “”,
scan_site int(11) NULL COMMENT “”,
cur_site_typ smallint(6) NULL COMMENT “”,
stip_nxt_site int(11) NULL COMMENT “”,
lod_scan_tm datetime NULL COMMENT “”,
lod_db_tm datetime NULL COMMENT “”,
lod_hdvr_id varchar(30) NULL COMMENT “”,
lod_hdvr_ent_site_tm datetime NULL COMMENT “”,
uld_hdvr_out_site_tm datetime NULL COMMENT “”,
nxt_scan_site int(11) NULL COMMENT “”,
nxt_site_typ int(11) NULL COMMENT “”,
uld_scan_tm datetime NULL COMMENT “”,
uld_scan_tm_30m datetime NULL COMMENT “卸车扫描时间加半小时时间格式”,
uld_scan_dt_30m datetime NULL COMMENT “卸车扫描时间加半小时日期格式”,
uld_db_tm datetime NULL COMMENT “”,
uld_hdvr_id varchar(30) NULL COMMENT “”,
pre_scan_site int(11) NULL COMMENT “”,
pre_site_typ smallint(6) NULL COMMENT “”,
actl_ent_site_tm datetime NULL COMMENT “”,
actl_out_site_tm datetime NULL COMMENT “”,
actl_ent_site_tm_3h datetime NULL COMMENT “实际进站时间+3小时时间格式”,
actl_ent_site_dt_3h datetime NULL COMMENT “实际进站时间+3小时日期格式”,
delv_tm datetime NULL COMMENT “”,
rcv_site int(11) NULL COMMENT “”,
rcv_site_typ smallint(6) NULL COMMENT “”,
rcv_tm datetime NULL COMMENT “”
) ENGINE=OLAP
DUPLICATE KEY(sub_ship_id, ent_site_min_tm)
COMMENT “补充站点装卸车信息”
DISTRIBUTED BY HASH(ship_id) BUCKETS 48
PROPERTIES (
“replication_num” = “1”,
“in_memory” = “false”,
“storage_format” = “DEFAULT”,
“enable_persistent_index” = “false”
);

CREATE TABLE ods_sub_rtng_wid_unit (
sub_ship_id bigint(20) NULL COMMENT “子单号”,
ent_site_min_tm datetime NULL COMMENT “进站最小时间”,
unit_tm datetime NULL COMMENT “开单时间”,
site_seq int(11) NULL COMMENT “站点顺序”,
ship_id int(11) NULL COMMENT “主单号”,
actl_pre_scan_site int(11) NULL COMMENT “实际上一站”,
actl_pre_site_typ smallint(6) NULL COMMENT “上一站机构类型”,
scan_site int(11) NULL COMMENT “当前站点”,
cur_site_typ smallint(6) NULL COMMENT “当前站点类型”,
actl_nxt_scan_site int(11) NULL COMMENT “”,
actl_nxt_site_typ smallint(6) NULL COMMENT “”,
stip_nxt_site int(11) NULL COMMENT “”,
lod_scan_tm datetime NULL COMMENT “”,
lod_db_tm datetime NULL COMMENT “”,
lod_hdvr_id varchar(30) NULL COMMENT “”,
uld_scan_tm datetime NULL COMMENT “”,
uld_db_tm datetime NULL COMMENT “”,
uld_hdvr_id varchar(30) NULL COMMENT “”,
actl_ent_site_tm datetime NULL COMMENT “”,
actl_out_site_tm datetime NULL COMMENT “”,
stip_out_site_tm datetime NULL COMMENT “”,
delv_tm datetime NULL COMMENT “”,
rcv_site int(11) NULL COMMENT “”,
rcv_site_typ smallint(6) NULL COMMENT “”,
rcv_tm datetime NULL COMMENT “”
) ENGINE=OLAP
DUPLICATE KEY(sub_ship_id, ent_site_min_tm)
COMMENT “”
DISTRIBUTED BY HASH(ship_id) BUCKETS 48
PROPERTIES (
“replication_num” = “1”,
“in_memory” = “false”,
“storage_format” = “DEFAULT”,
“enable_persistent_index” = “false”
);

执行如下insert into select 语句使用row_number函数耗时10s，不使用row_number函数耗时2s,数据量大概1700w,并行度24，开启CBO优化器。同样的语句在greenplum里也是耗时10s左右，starrocks还有提升空间吗？
因为还要使用各种lead和lag函数，就会变的更慢。
profile.txt (24.6 KB)
explain costs.txt (7.6 KB)
insert into ods.ods_sub_rtng_wid_unit
(
unit_tm,
site_seq,
ent_site_min_tm,
ship_id,
sub_ship_id,
actl_pre_scan_site,
actl_pre_site_typ,
scan_site,
cur_site_typ,
actl_nxt_scan_site,
actl_nxt_site_typ,
stip_nxt_site,
lod_scan_tm,
lod_db_tm,
lod_hdvr_id,
uld_scan_tm,
uld_db_tm,
uld_hdvr_id,
actl_ent_site_tm,
actl_out_site_tm,
delv_tm,
rcv_site,
rcv_site_typ,
rcv_tm
)
select unit_tm
,row_number() over(partition by sub_ship_id order by ent_site_min_tm) site_seq
,ent_site_min_tm
,ship_id
,sub_ship_id
,scan_site actl_pre_scan_site
,cur_site_typ actl_pre_site_typ
,scan_site
,cur_site_typ
,scan_site actl_nxt_scan_site
,cur_site_typ actl_nxt_site_typ
,stip_nxt_site --规定下一站
,lod_scan_tm
,lod_db_tm
,lod_hdvr_id
,uld_scan_tm
,uld_db_tm
,uld_hdvr_id
,actl_ent_site_tm
,actl_out_site_tm
,delv_tm
,rcv_site
,rcv_site_typ
,rcv_tm
from ods.mid_ship_site_seq a
;

shemplle · 2022年08月16日 08:27

你好，看了下profile，

            SORT_NODE (id=2):(Active: 6s430ms[6430864737ns], % non-child: 57.31%)
               - SortKeys: 1: sub_ship_id ASC, 2: ent_site_min_tm ASC
               - SortType: All
               - BuildingTime: 609.275ms
               - ChunksSorter: 6s163ms
               - MergingTime: 4s701ms
               - OutputTime: 115.213ms
               - PeakMemoryUsage: 628.81 MB
               - RowsReturned: 4.37711M (4377110)
               - RowsReturnedRate: 680.64K /sec
               - SortingTime: 678.890ms

确实是开窗函数这一步占用了，大量的时间，这个地方我们2.3后续版本也会对一些开窗函数做优化的，sql层面的修改应该提升不了什么性能。
所以目前这个sql只能等待下后续版本了。

LIANGCHAOHUA · 2022年08月16日 13:24

您好，查看profile
发现MergingTime: 5s377ms 数值特别高，是刚插入完大量的数据，内部存在多个rowset没有合并，导致查询时内部需要做相应操作，从而影响结果集输出。
等后台数据合并完成后，再进行查询，速度就会提高了，请检验一下，谢谢！

LIANGCHAOHUA · 2022年08月17日 02:37

请问这里的并行度你是如何设置的？

persistperson · 2022年08月17日 03:26

全局并行度是12
执行这个insert语句的时候是单独设置会话级并行度：
set parallel_fragment_exec_instance_num = 24;

persistperson · 2022年08月17日 03:32

我建表的时候使用的是duplicate模型，怎么会存在数据在合并？duplicate模型insert的时候不是追加吗？
而且我这一个insert语句是打算写在微批调度里的，如果我需要等待数据合并才能用，本质上还是慢啊，我的目的是想提高插入的速度，并且后续的语句能快速的使用刚插入的表。

persistperson · 2022年08月17日 03:36

现在我又全局设置了并行度为24，insert语句里有7个开窗函数，1800w数据插入耗费31s

LIANGCHAOHUA · 2022年08月17日 04:19

parallel_fragment_exec_instance_num设置太大可能会负增益，先从2，4，8尝试一下会不会有提高。

LIANGCHAOHUA · 2022年08月17日 04:30

需要把这个参数打开
enable_pipeline_engine=ture

LIANGCHAOHUA · 2022年08月17日 04:52

我指产生 mid_ship_site_seq这个表时，如果每次insert的batch不够大，会产生很多rowset，明细模型也是需要把多个rowset进行合并，相当于把多个小文件合成一个大文件，加快扫描的效率。

persistperson · 2022年08月18日 05:50

插入mid_ship_site_seq这个表也是一次性插入1700w数据，想问一下查询速度会受到cumulative_compaction_num_threads_per_disk 这个参数的影响吗目前设置是2.

LIANGCHAOHUA · 2022年08月18日 06:22

cumulative_compaction_num_threads_per_disk参数是关于cumulative compaction负责将多个最新导入的 rowset 合并成较大的 rowset的线程数。默认值是2，在服务器负载正常的情况下是不会影响查询的。

【集群规模】 3fe +4be
【机器信息】四台机器，每台机器配置(48c，251g，6块ssd盘)
分桶数量 = BE节点数量 * CPU 核数/2 如果您是4个BE，那么分桶数建议值是4*48/2=96，设置更多的分桶会提高查询效率，请尝试一下，谢谢！