greenplum（六） 现场常见由业务导致的故障

1 应用侧客户端发现执行函数调度时出现连接重置，需重新连接数据库的问题

问题现象:

问题代码:

select c.bcyc_id,c.acyc_id,c.eparchy_code, c.city_code, 'E0_001_01', count(distinct c.user_id) ,
                  coalesce(
                       (select count(b.user_id)
                        from dwctr.tc_usr_info_mon b
                        where b.acyc_id=271
                              and b.arr_user_flag=1
                              and b.city_code = c.city_code),0 ),
                              0,
                              0,
                  coalesce(
                       (select month_ywzx1
                        from dwapp.tf_fact_ywzx_city_mon b
                        where b.acyc_id =271-1
                              and b.ywzx_id = 'E0_001_01'
                              and b.city_code = c.city_code),0),
                  coalesce((select month_ywzx1
                         from dwapp.tf_fact_ywzx_city_mon b
                         where b.acyc_id =271-12
                               and b.ywzx_id = 'E0_001_01'
                               and b.city_code =c.city_code),0), row_number() over () as rowid
            from dwapp.detl_ywzx_user_zhlc_mon c
           where c.acyc_id=271
        group by c.acyc_id,c.bcyc_id,c.eparchy_code, c.city_code;

问题原因：

运营商经营数据分析系统会有很多的copy任务

其中b表和c表通过city_code关联，产生笛卡尔积，占用大量内存，而现有内存cached已使用较多（主要是应用copy任务导致，不释放）

运行该sql时，GP日志会报： “Unexpected internal error: Master process received signal SIGSEGV"”

当内存访问非法，触发SIGSEGV信号， 之后 调用默认的信号处理函数 ，产生core文件 ，然后关闭程序， 这时系统缺省动作是退出，就会报上述连接重置错误。

2 GP数据库hang现象，发现gp库登录连接不上或执行语句结果一直等待

问题现象:

应用客户端连接数据库 卡住，获取连接不上 查看表结构操作卡住、执行最简单sql语句结果集量小 语句出不来。

错误代码:

"xxx","bass_gp",p21935,th-715557088,"152.56.73.208","54949",2017-08-22 14:15:07 CST,96054518,con2633904,cmd152,seg73,,dx268848,x96054523,sx2,"LOG","00000","Extreme skew in the innerside of Hashjoin, nbatch 2, mintuples 1794, maxtuples 39766",,,,,,"insert into dwtmp.tmp_tc_cust_base_info_day( stat_

问题原因：

应用插入数据的语句有倾斜，然后手动cancel该语句，出现大量临时文件，导致触发

CST,96055236,con2633904,,seg73,,,x96055236,sx1,"PANIC","XX000"," ERRORDATA_STACK_SIZE exceeded 报错