大数据与流处理 / 系统课时
控制聚合与排序内存
控制聚合与排序内存
外部聚合和外部排序的目标不是让查询更快,而是在内存压力过高时把中间数据写入临时磁盘,避免查询直接撞上内存上限。本课配置两个关键阈值,并通过 ProfileEvents 验证聚合与排序是否真的发生了 spill。
学习目标
- 理解
max_memory_usage与外部聚合、外部排序阈值的职责边界。 - 配置
max_bytes_before_external_group_by与max_bytes_before_external_sort。 - 从
system.query_log验证临时文件写入,并判断内存、磁盘与耗时取舍。
先给查询留出明确内存边界
max_memory_usage 是单条查询在单台服务器上的内存上限。max_bytes_before_external_group_by 控制聚合状态达到一定规模后允许写临时数据,max_bytes_before_external_sort 对需要保留大量排序数据的场景执行类似控制。
外部处理阈值应明显早于查询总内存上限触发,因为查询除了聚合或排序本身,还需要读取缓冲、表达式计算、结果合并等内存。把 spill 阈值直接顶到 max_memory_usage 附近,可能还没来得及安全落盘就先触发内存异常。

用低阈值强制触发 spill
为了让实验在普通开发机上也容易观察,下面把两个 spill 阈值都设为 1 MiB,同时把单查询内存上限设为 256 MiB。聚合查询制造大量分组,排序查询对哈希值做全局排序;两者都用 FORMAT Null 避免输出百万行结果。
clickhouse-client --query_id spill-agg --query "SELECT number, count() FROM numbers_mt(1000000) GROUP BY number SETTINGS max_bytes_before_external_group_by = 1048576, max_memory_usage = 268435456 FORMAT Null"
clickhouse-client --query_id spill-sort --query "SELECT sipHash64(number) AS k FROM numbers_mt(1000000) ORDER BY k SETTINGS max_bytes_before_external_sort = 1048576, max_memory_usage = 268435456 FORMAT Null"
clickhouse-client --query "SYSTEM FLUSH LOGS"
clickhouse-client --query "SELECT query_id, if(query_id = 'spill-agg', ProfileEvents['ExternalAggregationWritePart'] > 0, ProfileEvents['ExternalSortWritePart'] > 0) AS spilled FROM (SELECT query_id, ProfileEvents, event_time FROM system.query_log WHERE type = 'QueryFinish' AND query_id IN ('spill-agg', 'spill-sort') AND event_time >= now() - INTERVAL 5 MINUTE ORDER BY event_time DESC LIMIT 1 BY query_id) ORDER BY query_id FORMAT TSV"预期输出
spill-agg 1
spill-sort 1这里的 1 表示对应的外部处理确实写过临时文件。若得到 0,先确认查询规模是否足以越过阈值,再检查是否命中了无需保留大量中间数据的优化路径;不要仅凭“设置已经写进 SQL”就认定 spill 生效。
阈值不是越低越安全
阈值过低时,聚合或排序会频繁生成临时片段,增加磁盘写入、读取和合并次数,查询延迟往往明显上升。阈值过高则会让更多中间状态长期驻留内存,在并发查询叠加时更容易逼近服务端可用内存。
调参时应先用代表性查询测出不 spill 时的峰值,再根据单机内存和预期并发为查询类别分配预算。随后逐步降低外部处理阈值,观察峰值内存、查询耗时和临时磁盘写入量,而不是直接套用固定字节数。在线交互查询和离线批处理通常也不应该共用同一套预算。
自建部署还要确认临时磁盘有足够容量和 IOPS。spill 把一部分压力从 RAM 转移到磁盘,如果临时盘空间不足、写延迟高或和数据盘争抢 I/O,查询虽然不再 OOM,却可能变成严重的磁盘瓶颈。
用查询日志做验收闭环
生产验收至少同时观察 memory_usage、query_duration_ms 和 ProfileEvents。其中 ExternalAggregationWritePart 与 ExternalSortWritePart 可以确认是否发生外部处理,相关字节计数则能反映写盘规模。对同一查询应保留一组“未 spill”和“允许 spill”的基线,比较资源而不是只比较成功与失败。
如果目标是降低峰值内存,验收条件应是峰值确实下降,并且新增磁盘 I/O 和延迟仍在业务允许范围内。如果查询本来就能稳定留在内存中,强行触发外部处理通常只会变慢;外部聚合和外部排序更适合作为容量保护机制,而不是默认性能优化手段。