Clickhouse查询优化问题记录

Clickhouse查询优化问题记录

问题一：内存不足Memory limit (total) exceeded

在做37亿数据全表JOIN时报错Memory limit (total) exceeded。
sql语句如下

SELECT *
FROM reverse_order_line_10 AS rol
LEFT JOIN reverse_order_10 AS ro ON rol.reverse_order_id = ro.id
LEFT JOIN order_10 AS a ON ro.order_id = a.id
LIMIT 1

报错如下DB::Exception: Memory limit (for query) exceeded: would use 4.66 GiB (attempt to allocate chunk of 4221631 bytes), maximum: 4.66 GiB

原因

默认情况下，ClickHouse会限制group by使用的内存量，由users.xml文件中max_memory_usage参数控制。所以我们要么增加内存使用，要么降低并发（set max_threads=1）