Introduction

This Blog guides you to make your queries more faster or memory efficient in Clickhouse, a common open source Data Warehouse.

If you are facing the following errors :

DB::Exception: Memory limit (total) exceeded: would use xxx GiB 
(attempt to allocate chunk of xxx bytes), maximum: xxx GiB.

This means that your queries are consuming more RAM than that is available in CH server.

We will go by priority of simple to complex modifications you can do to your queries in order to optimise them to run in best complexity :

1. Calculate the query memory usage & score/estimate

Estimate (Before the query ran)

This will give you rough score of where you query is spending most time scanning data of which table.

Check this documentation for how to do it.

Our target is to reduce score overall to improve query performance by reducing the data/rows getting scanned.

Memory usage (After the query ran)

SELECT type,
       event_time,
       query_duration_ms / 1000 AS time_taken_seconds,
       result_rows,
       memory_usage,
       exception,
       query
FROM   system.query_log
WHERE  ( query LIKE '<QUERY_SUBSTRING>' )
       AND ( query NOT LIKE '%system.%' )
       AND ( query NOT LIKE '%information_schema.%' )
       AND ( query NOT LIKE 'SHOW create table%' )
ORDER  BY event_time DESC
LIMIT  10

• Replace <QUERY_SUBSTRING> with part of query string you ran.

• memory_usage shows you actual RAM used.

2. Only select columns that are required

• ClickHouse is a purely columnar warehouse, so only selecting columns that are required you give you best complexity.

• Avoid SELECT *

3. Add Join & Group By settings

Add following settings at the end of the query.

Optimise JOIN memory usage

• If you are facing the error ending with : While executing JoiningTransform. (MEMORY_LIMIT_EXCEEDED)

• If you are joining big tables settings join_algorithm = 'grace_hash', grace_hash_join_initial_buckets = 8

  • Grace hash provides an algorithm option that provides performant complex joins while limiting memory use.

  • If query is still failing with memory error, please increase the "grace_hash_join_initial_buckets" value

  • Only supports INNER and LEFT join

  • This will slow down queries a bit but will limit memory usage. Ex,

select * from default.table1 t1 join default.table2 t2 
on t1.id = t2.id 
settings join_algorithm = 'grace_hash', grace_hash_join_initial_buckets = 3

• If you are doing right join as well, then use

SETTINGS join_algorithm = 'full_sorting_merge', max_rows_in_set_to_optimize_join = 0, max_bytes_before_exte rnal_sort = 500000

max_bytes_before_external_sort value can be reduced to further optimise memory usage, but comes at the cost of speed & doesn't necessarily prevent out of memory errors.

Doesn't supports CROSS join

4. Optimise GROUP BY memory usage

• If you are facing the error ending with : While executing AggregatingTransform. (MEMORY_LIMIT_EXCEEDED)

• If you are performing group by over large tables, please use

max_bytes_before_external_group_by=10000000

The value is in bytes.

It restricts the memory usage of group by as it spills the data to disk & queries run slower.

If query is still failing with memory error, please reduce the max_bytes_before_external_sort

select col1, col2 
from dw.table 
order by col1 
settings max_bytes_before_external_sort=1000000000

5. Add order by key conditions on the query so to filter lesser data

You can read more about order by here How Clickhouse primary key works and how to choose it

• While ingesting data please select order by keys, within datazip ecosystem you get that as as field during ingestion itself :

  • These columns can not contain null values as CH doesn't support null order by keys

• You can use these columns in where & order by clauses in queries.

• You can also use these in join conditions
  For example:

select tab1.col1, tab1.col2, tab2.col1, tab2.col2 from tab1 join tab2 on tab1.id = tab2.id

-- You can optimise this like
select tab1_optimised.col1, tab1_optimised.col2, tab2_optimised.col1, tab2_optimised.col2 
from 
-- Filter before join only
(select col1, col2 from tab1 where tab1.orderkey1 > '2023-01-01') tab1_optimised
join 
-- Filter before join only
(select col1, col2 from tab2 where tab2.orderkey1 > 'xyz' and tab2.orderkey2 in (1, 2, 3)) tab2_optimised
on tab1_optimised.id = tab2_optimised.id

6. Pre-calculate query results and store in physical table.

• You should use Transformation framework to create materialised tables from queries

table, incremental-table1