clickhouse join内存溢出

前言

在一个离线工作流中任务报错

Code: 241. DB::Exception: Received from XXXXXX:9000. DB::Exception: Memory limit (for query) exceeded: would use 20.49 GiB (attempt to allocate chunk of 4413264 bytes), maximum: 20.49 GiB: (avg_value_size_hint = 43, avg_chars_size = 42, limit = 8192): (while reading column level_system_name): (while reading from part /data/clickhouse/clickhouse-server/store/eb1/eb151646-ab48-48e6-866b-f5bf913470a1/all_305_310_1/ from mark 432 with max_rows_to_read = 8192): While executing MergeTreeThread. (MEMORY_LIMIT_EXCEEDED)

接下来是排查步骤和处理方法

排查步骤

查看sql

SET max_memory_usage = 22000000000;
insert into  A(
    column........
) Select
	column......
From B 
    Left Join C i
      On C.id = B.id;

统计数据量

B 表大概9000w
C 表大概5000w

统计大小

SELECT
    table,
    formatReadableSize(sum(data_compressed_bytes)) AS compressed_size,
    formatReadableSize(sum(data_uncompressed_bytes)) AS uncompressed_size,
    formatReadableSize(sum(bytes_on_disk)) AS size
FROM system.parts
WHERE table = 'A'
GROUP BY table;

最开始只统计了 bytes_on_disk 发现两张表都不超过2G，就很纳闷内存都已经给到22G 了还是不够，后来才发现是压缩之后的，压缩前A 8G B 11G

优化索引

经过检查 关联条件都在索引，没办法优化了

优化字段

经过检查select 的字段下游都有用到，也无法优化

增加内存

想修改max_memory_usage 参数，但是无限制的加内存也不是解决办法

最终优化

修改了 join的算法

insert into  A(
    column........
) Select
	column......
From B 
    Left Join C i
      On C.id = B.id
      Settings join_algorithm='partial_merge';

下面是ChatGPT给出的join 算法解释

在 ClickHouse 中，JoinAlgorithm 参数指定了执行 JOIN 操作时使用的算法。这些算法可以通过设置来优化查询的性能和内存利用情况。以下是各个 JoinAlgorithm 的含义和作用：

1. **prefer_partial_merge**
   - 这个选项表示 ClickHouse 首选使用部分合并（partial_merge）算法来执行 JOIN 操作。
   - 部分合并算法会尽量在不需要将整个数据集加载到内存中的情况下执行 JOIN，因此在处理大数据量时可以减少内存的使用。
   - 如果部分合并算法不适用或无法使用，ClickHouse 会尝试使用其他适当的 JOIN 算法。

2. **hash**
   - 使用哈希 JOIN 算法来执行 JOIN 操作。
   - 哈希 JOIN 是一种内存密集型算法，适合处理较小的数据集或者对性能要求较高的场景，因为它通常比其他 JOIN 算法更快速。
   - 这种算法会将数据加载到内存中，使用哈希表来快速查找匹配的行。

3. **partial_merge**
   - 使用部分合并 JOIN 算法来执行 JOIN 操作。
   - 部分合并算法尝试在不需要加载整个数据集到内存中的情况下执行 JOIN，这在处理大数据量时可以节省内存和提高性能。
   - 它适合处理无序数据或者在其中一个表的数据较大时，可以减少内存压力。

4. **auto**
   - ClickHouse 自动选择适当的 JOIN 算法来执行操作。
   - 这是默认的选项，ClickHouse 会根据查询的具体情况和表的大小自动选择合适的 JOIN 算法，以达到最佳的性能和内存利用效果。