Clickhouse:DB::异常:超出内存限制(用于查询)

Question

当Clickhouse查询的内存用完时，该怎么办？你不能只是提高记忆力，对吧？内存也有限制，如何配置硬盘？

SELECT
UserID,
Title
FROM 
(
SELECT
L.UserID,
L.Title
FROM tutorial.hits_v1 AS L
INNER JOIN tutorial.hits_v2 AS R ON L.UserID = R.UserID
) AS T
ORDER BY UserID ASC
LIMIT 10

#user.d/abc.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>350000000</max_memory_usage>
<max_memory_usage_for_user>350000000</max_memory_usage_for_user>
<max_bytes_before_external_group_by>100000000</max_bytes_before_external_group_by>
<max_bytes_before_external_sort>100000000</max_bytes_before_external_sort>
</default>
</profiles>
</yandex>

Answer 1

• 避免使用巨大的表作为JOIN的右表："ClickHouse采用<right_table>并在RAM"中为其创建哈希表
• 将查询限制应用于子查询，而不是外部查询

试试这个：

SELECT L.UserID, L.Title
FROM tutorial.hits_v1 AS L
INNER JOIN (
SELECT UserID
FROM tutorial.hits_v2
/* WHERE .. */
LIMIT 10) AS R ON L.UserID = R.UserID
ORDER BY UserID

或

SELECT UserID, Title
FROM tutorial.hits_v1
WHERE UserID IN (SELECT UserID FROM tutorial.hits_v2 /* WHERE .. */ LIMIT 10)
ORDER BY UserID

Answer 2

如果您有余量，请在执行查询之前增加最大内存：

SET max_memory_usage = 8000000000;

在我的情况下，将其设置为8GB解决了这个问题。