数据量大的查询，直接崩了

U_1653644474324_2809 · 2022年06月23日 06:50

–申请容量数
WITH tb AS (
SELECT
a.ps_org_no as ps_org_no,
a.scop_type scop_type,
a.cons_sort_code AS cons_sort_code,
a.contract_cap AS contract_cap,
b.state AS state,
CASE
WHEN a.scop_type = ‘01’ THEN a.stat_cycle
WHEN a.scop_type = ‘02’ THEN b.stat_cycle
END AS stat_cycle
FROM
(
select
‘01’ scop_type,
–申请规模
ps_org_no,
app_no,
contract_cap,
case
when cons_sort_code like ‘01%’ then ‘01’
when cons_sort_code like ‘02%’ then ‘02’
when cons_sort_code like ‘03%’ then ‘03’
end as cons_sort_code,
date_format(handle_time, ‘%Y%m’) as stat_cycle
from
s_app
where
(
cons_sort_code like ‘01%’
or cons_sort_code like ‘02%’
or cons_sort_code like ‘03%’
)
and handle_time >= ‘2021-01-01 00:00:00’
and date_format(handle_time, ‘%Y%m%d’) <= curdate() + 0
union
select
‘02’ scop_type,
–完成规模
ps_org_no,
app_no,
contract_cap,
case
when cons_sort_code like ‘01%’ then ‘01’
when cons_sort_code like ‘02% ’ then ‘02’
when cons_sort_code like ‘03% ’ then ‘03’
end as cons_sort_code,
date_format(handle_time, ‘%Y%m’) as stat_cycle
from
arc_s_app
where
(
cons_sort_code like ‘01%’
or cons_sort_code like ‘02%’
or cons_sort_code like ‘03%’
)
) a
join (
select
item_key,
state,
date_format(end_date, ‘%Y%m’) as stat_cycle
from
indywf_instances_cur
where
pro_name in (
‘101’,
‘109’,
‘102’,
‘110’,
‘104’,
‘111’
)
and item_type = ‘01’
and state in (‘COMPLETE’, ‘ACTIVE’)
) b on b.item_key = a.app_no
)
SELECT
a.ps_org_no as ORG_NO,
a.scop_type DIM_VAL_CODE1,
a.cons_sort_code AS DIM_VAL_CODE2,
ifnull(SUM(a.contract_cap), 0) as DATA_VALUE
FROM
tb a
where
a.stat_cycle >= ‘202201’
and a.scop_type = ‘01’ --申请规模
and a.state in (‘COMPLETE’, ‘ACTIVE’) --获取的是当月的申请规模的归档与在途
group by
a.ps_org_no,
a.scop_type,
a.cons_sort_code
union
SELECT
a.ps_org_no as ORG_NO,
a.scop_type DIM_VAL_CODE1,
a.cons_sort_code AS DIM_VAL_CODE2,
ifnull(SUM(a.contract_cap), 0) as DATA_VALUE
FROM
tb a
where
a.stat_cycle >= ‘202201’
and a.stat_cycle <= date_format(curdate(), ‘%Y%m’)
and a.scop_type = ‘02’ --完成规模
and a.state = ‘COMPLETE’
group by
a.ps_org_no,
a.scop_type,
a.cons_sort_code
union
SELECT
a.ps_org_no as ORG_NO,
a.scop_type DIM_VAL_CODE1,
a.cons_sort_code AS DIM_VAL_CODE2,
ifnull(SUM(a.contract_cap), 0) as DATA_VALUE
FROM
tb a
where
a.stat_cycle <= date_format(add_months(curdate(), -1), ‘%Y%m’)
and a.scop_type = ‘01’
and a.state in (‘COMPLETE’,‘ACTIVE’) --上月的申请规模与归档规模
AND a.stat_cycle >= date_format(
date_format(add_months(curdate(), -1), ‘%Y-01-01’),
‘%Y%m’
)
group by
a.ps_org_no,
a.scop_type,
a.cons_sort_code
union
SELECT
a.ps_org_no as ORG_NO,
a.scop_type DIM_VAL_CODE1,
a.cons_sort_code AS DIM_VAL_CODE2,
ifnull(SUM(a.contract_cap), 0) as DATA_VALUE
FROM
tb a
where
a.stat_cycle <= date_format(add_months(curdate(), -1), ‘%Y%m’)
and a.scop_type = ‘02’
and a.state = ‘COMPLETE’
AND a.stat_cycle >= date_format(date_format(add_months(curdate(), -1), ‘%Y-01-01’),’%Y%m’)
group by
a.ps_org_no,
a.scop_type,
a.cons_sort_code
查询申请容量数，一共三张表，每个表1亿数据，直接查崩了，
报错如下：

JiangLai · 2022年06月23日 06:55

您好，方便的话请发一下fe.warn日志文件

U_1653644474324_2809 · 2022年06月23日 07:11

fe.warn.log日志里面的报错如下：

JiangLai · 2022年06月23日 07:15

您好，看日志内容是端口冲突了，按照提示修改一下吧

U_1653644474324_2809 · 2022年06月23日 07:19

你的意思是把另一个占用19010的程序找出来，并修改吗？

U_1653644474324_2809 · 2022年06月23日 07:24

咋修改呢？我查看的端口占用情况如下：

JiangLai · 2022年06月23日 07:26

fe.conf文件里面找到edit_log_port进行修改

U_1653644474324_2809 · 2022年06月23日 07:35

我配置文件里面已经修改了

然后我也重启了

但为啥在web页面看到的还是19010呢

JiangLai · 2022年06月23日 07:43

很可能不是一个集群

U_1653644474324_2809 · 2022年06月23日 07:44

不应该啊，访问的web页面的ip，跟我修改的配置的ip是一模一样的

U_1653644474324_2809 · 2022年06月23日 07:51

这是在web页面看到的ip，
这是ifconfig命令查看的ip，都是一样的

而且公司也只有一个starrocks的测试集群

U_1653644474324_2809 · 2022年06月23日 08:02

改完端口之后，再重新执行，fe.warn.log又报错

JiangLai · 2022年06月23日 08:07

麻烦3个fe的日志都发下，fe启动不起来端口肯定是设置不成功的，页面自然也不会刷新。

U_1653644474324_2809 · 2022年06月23日 08:18

这是第二台机器的 fe.warn.log

第三台机器有很多个 fe.warn.log

我截图的是fe.warn.log

JiangLai · 2022年06月23日 08:59

看下第二台的be 10719是否启动了？

U_1653644474324_2809 · 2022年06月23日 09:04

这个怎么看的，用命令行能看吗？现在web页面都进不去了

U_1653644474324_2809 · 2022年06月23日 09:10

我自己看日志，我怀疑是fe启动的时候mysql数据库没连接上导致

U_1653644474324_2809 · 2022年06月23日 10:40

启动fe的时候一直报错 wait catalog to be ready. FE type: UNKNOWN. is ready: false，是不是表示要清楚 meta里面的文件？

dongquan · 2022年06月23日 11:29

一共几台be几台fe?集群是哪个版本？show frontends；看下结果

U_1653644474324_2809 · 2022年06月24日 10:45

一共2台fe和3台be，版本2.0.1，
查询sql如下：
–申请容量数

WITH tb AS (

SELECT

    a.ps_org_no as ps_org_no,

    a.scop_type scop_type,

    a.cons_sort_code AS cons_sort_code,

    a.contract_cap AS contract_cap,

    b.state AS state,

    CASE

        WHEN a.scop_type = '01' THEN a.stat_cycle

        WHEN a.scop_type = '02' THEN b.stat_cycle

    END AS stat_cycle

FROM

    (

        select

            '01' scop_type,

            --申请规模

            ps_org_no,

            app_no,

            cast(contract_cap as int) contract_cap,

            case

                when cons_sort_code like '01%' then '01'

                when cons_sort_code like '02%' then '02'

                when cons_sort_code like '03%' then '03'

            end as cons_sort_code,

            date_format(handle_time, '%Y%m') as stat_cycle

        from

            s_app

        where

            (

                cons_sort_code like '01%'

                or cons_sort_code like '02%'

                or cons_sort_code like '03%'

            )

            and handle_time >= '2021-01-01 00:00:00'

            and date_format(handle_time, '%Y%m%d') <= curdate() + 0

        union

        select

            '02' scop_type,

            --完成规模

            ps_org_no,

            app_no,

            cast(contract_cap as int) contract_cap,

            case

                when cons_sort_code like '01%' then '01'

                when cons_sort_code like '02% ' then '02'

                when cons_sort_code like '03% ' then '03'

            end as cons_sort_code,

            date_format(handle_time, '%Y%m') as stat_cycle

        from

            arc_s_app

        where

            (

                cons_sort_code like '01%'

                or cons_sort_code like '02%'

                or cons_sort_code like '03%'

            )

    ) a

    join (

        select

            item_key,

            state,

            date_format(end_date, '%Y%m') as stat_cycle

        from

            indywf_instances_cur

        where

            pro_name in (

                '101',

                '109',

                '102',

                '110',

                '104',

                '111'

            )

            and item_type = '01'

            and state in ('COMPLETE', 'ACTIVE')

    ) b on b.item_key = a.app_no

)

SELECT

a.ps_org_no as ORG_NO,

a.scop_type DIM_VAL_CODE1,

a.cons_sort_code AS DIM_VAL_CODE2,

ifnull(SUM(a.contract_cap), 0) as DATA_VALUE

FROM

tb a

where

a.stat_cycle >= '202201'

and a.scop_type = '01' --申请规模

and a.state in ('COMPLETE', 'ACTIVE') --获取的是当月的申请规模的归档 与 在途

group by

a.ps_org_no,

a.scop_type,

a.cons_sort_code

union

SELECT

a.ps_org_no as ORG_NO,

a.scop_type DIM_VAL_CODE1,

a.cons_sort_code AS DIM_VAL_CODE2,

ifnull(SUM(a.contract_cap), 0) as DATA_VALUE

FROM

tb a

where

a.stat_cycle >= '202201'

and a.stat_cycle <= date_format(curdate(), '%Y%m')

and a.scop_type = '02' --完成规模

and a.state = 'COMPLETE'

group by

a.ps_org_no,

a.scop_type,

a.cons_sort_code

union

SELECT

a.ps_org_no as ORG_NO,

a.scop_type DIM_VAL_CODE1,

a.cons_sort_code AS DIM_VAL_CODE2,

ifnull(SUM(a.contract_cap), 0) as DATA_VALUE

FROM

tb a

where

a.stat_cycle <= date_format(add_months(curdate(), -1), '%Y%m')

and a.scop_type = '01'

and a.state in ('COMPLETE','ACTIVE') --上月的申请规模与归档规模

AND a.stat_cycle >= date_format(

    date_format(add_months(curdate(), -1), '%Y-01-01'),

    '%Y%m'

)

group by

a.ps_org_no,

a.scop_type,

a.cons_sort_code

union

SELECT

a.ps_org_no as ORG_NO,

a.scop_type DIM_VAL_CODE1,

a.cons_sort_code AS DIM_VAL_CODE2,

ifnull(SUM(a.contract_cap), 0) as DATA_VALUE

FROM

tb a

where

a.stat_cycle <= date_format(add_months(curdate(), -1), '%Y%m')

and a.scop_type = '02'

and a.state = 'COMPLETE'

AND a.stat_cycle >= date_format(date_format(add_months(curdate(), -1), '%Y-01-01'),'%Y%m')

group by

a.ps_org_no,

a.scop_type,

a.cons_sort_code

一共3张表，s_app,ar_s_app,indywf_instances_cur,每张表1亿的数据量，
exec_mem_limit 设置为 8589934592，
最后查询又报错 query_timeout

这是fe.warn.log日志里面的截图