ClickHouse / ClickHouse/ClickHouse

Limit max_insert_threads by available memory.

Open
#59,112 6 comments 3 reactions 1 assignee Claimed by @yariks5s View on GitHub
comp-query-execution feature
Dominant language
C++
Stars
49.9k
Forks
9k
Avg merge
21h 32m
Merged PRs (30d)
515

Description

Setting `max_insert_threads` linearly increases insert speed and memory usage together.

```
create table insert_memory_usage (a UInt64, b UInt64, c UInt64, d UInt64) engine = Null;

:) insert into insert_memory_usage select number, number, number, number from numbers_mt(2e9) settings min_insert_block_size_rows=1e8, max_insert_threads=1

INSERT INTO insert_memory_usage
SETTINGS min_insert_block_size_rows = 100000000., max_insert_threads = 1
SELECT
number,
number,
number,
number
FROM numbers_mt(2000000000.)
SETTINGS min_insert_block_size_rows = 100000000., max_insert_threads = 1

Query id: 24364ebb-edeb-46b7-bdfe-2f0a73dc6a76

Ok.

0 rows in set. Elapsed: 1.965 sec. Processed 2.00 billion rows, 16.00 GB (1.02 billion rows/s., 8.14 GB/s.)
Peak memory usage: 762.94 MiB.

:) insert into insert_memory_usage select number, number, number, number from numbers_mt(2e9) settings min_insert_block_size_rows=1e8, max_insert_threads=9

INSERT INTO insert_memory_usage
SETTINGS min_insert_block_size_rows = 100000000., max_insert_threads = 9
SELECT
number,
number,
number,
number
FROM numbers_mt(2000000000.)
SETTINGS min_insert_block_size_rows = 100000000., max_insert_threads = 9

Query id: edeeb424-e1ea-45d2-af37-db9457466e09

Ok.

0 rows in set. Elapsed: 0.486 sec. Processed 2.00 billion rows, 16.00 GB (4.12 billion rows/s., 32.95 GB/s.)
Peak memory usage: 6.71 GiB.

:) insert into insert_memory_usage select number, number, number, number from numbers_mt(2e9) settings min_insert_block_size_rows=1e8, max_insert_threads=16

INSERT INTO insert_memory_usage
SETTINGS min_insert_block_size_rows = 100000000., max_insert_threads = 16
SELECT
number,
number,
number,
number
FROM numbers_mt(2000000000.)
SETTINGS min_insert_block_size_rows = 100000000., max_insert_threads = 16

Query id: 5ad750c3-61ad-4012-89ca-18f7bd3554ce

↗ Progress: 800.00 million rows, 6.40 GB (1.59 billion rows/s., 12.73 GB/s.) (1.7 CPU, 9.60 GB RAM)
Elapsed: 0.503 sec. Processed 800.00 million rows, 6.40 GB (1.59 billion rows/s., 12.73 GB/s.)
Peak memory usage: 8.94 GiB.

Received exception from server (version 24.1.1):
Code: 241. DB::Exception: Received from localhost:9000. DB::Exception: Memory limit (for query) exceeded: would use 11.18 GiB (attempt to allocate chunk of 800001008 bytes), maximum: 9.31 GiB.: While executing NumbersRange. (MEMORY_LIMIT_EXCEEDED)

```

Let's limit the number of insert threads looking at available memory.

Similar issue https://github.com/ClickHouse/ClickHouse/issues/58618

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.