Clickhouse: DB::Exception: Memory limit (for query) exceeded

Question

What should I do when I run out of memory for Clickhouse queries? You can't just crank up the memory, right? There is also a limit to memory, how to configure the hard disk?

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

• avoid using huge tables as a right table of JOIN: "ClickHouse takes the <right_table> and creates a hash table for it in RAM"
• apply query restrictions to sub-queries, not an outer one

Try this one:

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

or

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

Answer 2

If you have margin, increase max memory before executing the query:

SET max_memory_usage = 8000000000;

In my case setting it to 8 GB solved the issue.