两个表相交后后排序报内存不够,应该怎么处理呢?

发布于 4 个月前 作者 sandheart 829 次浏览 来自 问答

自学Clickhouse时,两个表相交后后排序报内存不够,应该怎么处理呢? set max_memory_usage=2000000000 配置最大内存使用2G也不行

SELECT L.URLDomain, L.RefererDomain FROM tutorial.hits_v1 AS L ALL INNER JOIN tutorial.hits_v2 AS R ON L.UserID = R.UserID ORDER BY L.UserID ASC LIMIT 10

Query id: 187f2f62-ef29-4e33-9c0d-5cd4d449fbd6

→ Progress: 8.92 million rows, 73.54 MB (27.59 million rows/s., 227.40 MB/s.) 49% Received exception from server (version 20.12.3): Code: 241. DB::Exception: Received from localhost:9000. DB::Exception: Memory limit (for query) exceeded: would use 2.08 GiB (attempt to allocate chunk of 268435456 bytes), maximum: 1.86 GiB.

0 rows in set. Elapsed: 0.736 sec. Processed 8.92 million rows, 73.54 MB (12.13 million rows/s., 99.93 MB/s.)

2 回复

我现在最的纠结就是:内存不够可以通过可以加更多的内存、优化SQL解决,但clickhouse毕竟是处理海量数据的,常规服务器也就128G或64G,但要查询的数据真的的很大,超过了机器内存应该怎么办?不能无限的扩内存吧,如果能把数据溢出到磁盘,就算慢一点,也没问题,至少结果可以出来;但模拟了几天,一直没有出现数据溢出到磁盘计算的现象;

user.xml <?xml version=“1.0”?> <yandex> <!-- Profiles of settings. --> <profiles> <!-- Default settings. --> <default> <!-- Maximum memory usage for processing single query, in bytes. --> <max_memory_usage>3000000000</max_memory_usage> <max_memory_usage_for_user>2000000000</max_memory_usage_for_user> <max_bytes_before_external_group_by>1000000000</max_bytes_before_external_group_by> <max_bytes_before_external_sort>100000000</max_bytes_before_external_sort> <!-- <max_threads>2</max_threads> --> <max_read_buffer_size>10000000</max_read_buffer_size> <max_compress_block_size>10000000</max_compress_block_size> <min_compress_block_size>10000000</min_compress_block_size> <!-- <max_bytes_in_join>1000000000</max_bytes_in_join> --> </default> </profiles> </yandex>

─字段名──类型────原始大小───压缩大小──────行数─* UserID * UInt64 * 67.70 MiB * 11.08 MiB * 8873898 * ───────────────────────────────────────────────*

UserID字段,没占多少空间呀,怎么2G的内存还不够用呢?

回到顶部