memory overcommit being used while turned off in query settings

[nielsreijers]

The query below sets max_memory_usage_for_user=1_000_000, but needs more than that.
We also pass memory_overcommit_ratio_denominator_for_user=0, memory_overcommit_ratio_denominator=0 to turn off memory overcommit.

When doing this query repeatedly (up arrow, enter, up arrow, enter, etc), we see three different responses:

• OvercommitTracker decision: Memory overcommit isn't used. Waiting time or overcommit denominator are set to zero.
• OvercommitTracker decision: Memory overcommit has freed not enough memory.
• Ok. 0 rows in set.

It seems only the first response is correct, in the second and third the overcommit was not turned off.

Does it reproduce on the most recent release?

Reproduced on two different servers running 24.8.2.3 and 24.9.2.42. Unfortunately it doesn't seem to reproduce on fiddle.

I noticed it happens frequently for two users (default and a system user) that both don't have max_memory_usage_for_user set: running the query 5 times or so usually produces a case where the overcommit isn't used.

These are the same two users that are affected by the possibly related issue 72364.

Initially could not get it reproduced for two other users that did have this limit set, but after doing it about 20 times in a row in quick succession, it also returned "Ok. 0 rows in set." once.

Expected behavior

Trace of 5 consecutive attempts with different outcomes:

memory_overcommit_ratio_denominator_for_user=0, memory_overcommit_ratio_denominator=0 should always turn off memory overcommit.

Additional context

clickhouse-server :) SELECT x FROM (SELECT rand() as x FROM numbers() LIMIT 1024*1024) GROUP BY x HAVING count(*) > 1000 SETTINGS max_memory_usage_for_user=1_000_000, memory_overcommit_ratio_denominator_for_user=0, memory_overcommit_ratio_denominator=0

SELECT x
FROM
(
    SELECT rand() AS x
    FROM numbers()
    LIMIT 1024 * 1024
)
GROUP BY x
HAVING count(*) > 1000
SETTINGS max_memory_usage_for_user = 1000000, memory_overcommit_ratio_denominator_for_user = 0, memory_overcommit_ratio_denominator = 0

Query id: 3ba33b59-7037-4dec-9314-b4f68ed80714

← Progress: 130.82 thousand rows, 1.05 MB (1.27 million rows/s., 10.18 MB/s.)  12%
Elapsed: 0.103 sec. Processed 130.82 thousand rows, 1.05 MB (1.27 million rows/s., 10.18 MB/s.)
Peak memory usage: 32.26 MiB.

Received exception from server (version 24.9.2):
Code: 241. DB::Exception: Received from localhost:9440. DB::Exception: Memory limit (for user) exceeded: would use 234.10 MiB (attempt to allocate chunk of 0 bytes), current RSS 3.04 PiB, maximum: 976.56 KiB. OvercommitTracker decision: Memory overcommit has freed not enough memory.: While executing AggregatingTransform. (MEMORY_LIMIT_EXCEEDED)

clickhouse-server :) SELECT x FROM (SELECT rand() as x FROM numbers() LIMIT 1024*1024) GROUP BY x HAVING count(*) > 1000 SETTINGS max_memory_usage_for_user=1_000_000, memory_overcommit_ratio_denominator_for_user=0, memory_overcommit_ratio_denominator=0

SELECT x
FROM
(
    SELECT rand() AS x
    FROM numbers()
    LIMIT 1024 * 1024
)
GROUP BY x
HAVING count(*) > 1000
SETTINGS max_memory_usage_for_user = 1000000, memory_overcommit_ratio_denominator_for_user = 0, memory_overcommit_ratio_denominator = 0

Query id: a9d8ee3b-9226-486a-a480-62d5898cd4ac

Ok.

0 rows in set. Elapsed: 0.245 sec. Processed 1.05 million rows, 8.39 MB (4.27 million rows/s., 34.20 MB/s.)
Peak memory usage: 60.76 MiB.

clickhouse-server :) SELECT x FROM (SELECT rand() as x FROM numbers() LIMIT 1024*1024) GROUP BY x HAVING count(*) > 1000 SETTINGS max_memory_usage_for_user=1_000_000, memory_overcommit_ratio_denominator_for_user=0, memory_overcommit_ratio_denominator=0

SELECT x
FROM
(
    SELECT rand() AS x
    FROM numbers()
    LIMIT 1024 * 1024
)
GROUP BY x
HAVING count(*) > 1000
SETTINGS max_memory_usage_for_user = 1000000, memory_overcommit_ratio_denominator_for_user = 0, memory_overcommit_ratio_denominator = 0

Query id: 7c4f574e-9c1d-4ad5-96f5-f7d95379a8e8


Elapsed: 0.093 sec. 

Received exception from server (version 24.9.2):
Code: 241. DB::Exception: Received from localhost:9440. DB::Exception: Memory limit (for user) exceeded: would use 25.58 MiB (attempt to allocate chunk of 0 bytes), current RSS 3.04 PiB, maximum: 976.56 KiB. OvercommitTracker decision: Memory overcommit isn't used. Waiting time or overcommit denominator are set to zero.: While executing AggregatingTransform. (MEMORY_LIMIT_EXCEEDED)

clickhouse-server :) SELECT x FROM (SELECT rand() as x FROM numbers() LIMIT 1024*1024) GROUP BY x HAVING count(*) > 1000 SETTINGS max_memory_usage_for_user=1_000_000, memory_overcommit_ratio_denominator_for_user=0, memory_overcommit_ratio_denominator=0

SELECT x
FROM
(
    SELECT rand() AS x
    FROM numbers()
    LIMIT 1024 * 1024
)
GROUP BY x
HAVING count(*) > 1000
SETTINGS max_memory_usage_for_user = 1000000, memory_overcommit_ratio_denominator_for_user = 0, memory_overcommit_ratio_denominator = 0

Query id: 494f5d4f-bd9d-4e43-91c4-a535a2a2612d


Elapsed: 0.087 sec. 

Received exception from server (version 24.9.2):
Code: 241. DB::Exception: Received from localhost:9440. DB::Exception: Memory limit (for user) exceeded: would use 27.04 MiB (attempt to allocate chunk of 0 bytes), current RSS 3.04 PiB, maximum: 976.56 KiB. OvercommitTracker decision: Memory overcommit has freed not enough memory.: While executing AggregatingTransform. (MEMORY_LIMIT_EXCEEDED)

clickhouse-server :) SELECT x FROM (SELECT rand() as x FROM numbers() LIMIT 1024*1024) GROUP BY x HAVING count(*) > 1000 SETTINGS max_memory_usage_for_user=1_000_000, memory_overcommit_ratio_denominator_for_user=0, memory_overcommit_ratio_denominator=0

SELECT x
FROM
(
    SELECT rand() AS x
    FROM numbers()
    LIMIT 1024 * 1024
)
GROUP BY x
HAVING count(*) > 1000
SETTINGS max_memory_usage_for_user = 1000000, memory_overcommit_ratio_denominator_for_user = 0, memory_overcommit_ratio_denominator = 0

Query id: 8cf15dc9-14d8-40b2-bd1b-09cf33a6b91f

↙ Progress: 65.41 thousand rows, 523.27 KB (631.44 thousand rows/s., 5.05 MB/s.)  6%
Elapsed: 0.104 sec. Processed 65.41 thousand rows, 523.27 KB (631.44 thousand rows/s., 5.05 MB/s.)
Peak memory usage: 32.25 MiB.

Received exception from server (version 24.9.2):
Code: 241. DB::Exception: Received from localhost:9440. DB::Exception: Memory limit (for user) exceeded: would use 15.01 MiB (attempt to allocate chunk of 0 bytes), current RSS 3.04 PiB, maximum: 976.56 KiB. OvercommitTracker decision: Memory overcommit isn't used. Waiting time or overcommit denominator are set to zero.: While executing AggregatingTransform. (MEMORY_LIMIT_EXCEEDED)

clickhouse-server :)