clickhouse join 內存溢出
- 前言
- 排查步驟
- 查看sql
- 統計數據量
- 統計大小
- 優化索引
- 優化字段
- 增加內存
- 最終優化
前言
在一個離線工作流中任務報錯
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........
) Selectcolumn......
From B Left Join C iOn C.id = B.id;
統計數據量
B 表大概9000w
C 表大概5000w
統計大小
SELECTtable,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........
) Selectcolumn......
From B Left Join C iOn C.id = B.idSettings 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 算法，以達到最佳的性能和內存利用效果。
