Redshift大行数查询溢写磁盘致崩溃，磁盘占用异常求排查

问题根源分析 #

核心问题是严重数据倾斜导致单节点承担了远超阈值的数据量：虽然集群总磁盘空间充足，但单个DC2.xlarge节点的1.6TB HDD被倾斜数据的排序溢写占满；同时窗口函数的写法带来了不必要的全排序开销，进一步加剧了内存和磁盘压力。

1. 优化窗口函数逻辑，消除冗余排序 #

原SQL中first_value(_random) over (partition by subscriber_cd, mbr_cd order by _random desc rows between unbounded preceding and unbounded following)的作用是取每个subscriber_cd,mbr_cd分组中最大的_random值，完全可以用max()聚合替代窗口函数，避免全分组排序的巨大开销：

create table xstg_prof_MED_phase2b as
select t.*, agg.max_random
from xstg_prof_MED_phase2a t
inner join (
    select subscriber_cd, mbr_cd, max(_random) as max_random
    from xstg_prof_MED_phase2a
    group by subscriber_cd, mbr_cd
) agg on t.subscriber_cd = agg.subscriber_cd 
    and t.mbr_cd = agg.mbr_cd
order by subscriber_cd, mbr_cd;

2. 精准处理数据倾斜 #

第一步：定位倾斜键

先找出数据量异常大的分组，针对性处理：

select subscriber_cd, mbr_cd, count(*) as record_count
from xstg_prof_MED_phase2a
group by subscriber_cd, mbr_cd
order by record_count desc
limit 20;

第二步：拆分倾斜分组

对超大分组，添加随机后缀拆分成分组，分散到不同节点处理：

-- 示例：对倾斜键添加随机后缀，拆分10个子分组
create table xstg_prof_MED_phase2b as
with split_groups as (
    select 
        *,
        -- 对倾斜键添加随机标记，仅针对超大分组（可通过where过滤）
        concat(subscriber_cd, '_', cast(floor(rand() * 10) as string)) as temp_sub_cd,
        concat(mbr_cd, '_', cast(floor(rand() * 10) as string)) as temp_mbr_cd
    from xstg_prof_MED_phase2a
    -- 可添加where条件仅处理倾斜键，比如where subscriber_cd in ('倾斜键1', '倾斜键2')
),
agg_temp as (
    select temp_sub_cd, temp_mbr_cd, max(_random) as max_random
    from split_groups
    group by temp_sub_cd, temp_mbr_cd
)
select 
    s.subscriber_cd, s.mbr_cd, s._random, s.其他列, a.max_random
from split_groups s
inner join agg_temp a 
    on s.temp_sub_cd = a.temp_sub_cd 
    and s.temp_mbr_cd = a.temp_mbr_cd
order by subscriber_cd, mbr_cd;

第三步：调整任务并行度

针对AWS EMR环境，调整Shuffle和排序的并行参数：

• Hive：设置set mapreduce.reduce.tasks=80;（DC2.xlarge为4核，10节点建议设为节点数×核数×2）
• Spark：设置spark.sql.shuffle.partitions=120;（建议为节点数×核数×3）

3. 避免全局排序的不必要开销 #

如果最终表不需要全局完全有序，仅需分区或分组内有序，去掉最后的order by subscriber_cd, mbr_cd，改用distribute by + sort by：

create table xstg_prof_MED_phase2b as
select t.*, agg.max_random
from xstg_prof_MED_phase2a t
inner join (
    select subscriber_cd, mbr_cd, max(_random) as max_random
    from xstg_prof_MED_phase2a
    group by subscriber_cd, mbr_cd
) agg using(subscriber_cd, mbr_cd)
distribute by subscriber_cd, mbr_cd
sort by subscriber_cd, mbr_cd;

这样每个节点仅处理部分数据的排序，不会产生全局排序的海量磁盘溢写。

4. 节点级磁盘空间检查 #

确认每个DC2.xlarge节点的剩余磁盘空间，若单个节点剩余不足（比如因倾斜数据集中到某节点），可临时扩容节点磁盘，或通过数据重分布将倾斜数据分散到更多节点。

内容的提问来源于stack exchange，提问作者rlieberman0