Group & Join bottlenecked by single thread performance, not allocating all cores

[Slind14]

Test setup:

• clean Debian 9.9 installation
• clean clickhouse installation
• the only configuration change is adding a 0 to the max_memory setting
• data is stored in mem-disk
• 64 core system

Other things

• Available memory is far exceeding non-compressed data from required columns
• Hashing seems to work fine and not be the cause

For simplicity let us focus on the joins first:

Join across two merge tree engines (total time 11 seconds)

(join uses a subselect [..] ANY LEFT JOIN (SELECT uuid, sum(someValue) FROM rightTable GROUP BY uuid) r ON r.uuid = uuid [..], the join is high granular 1 to 1)

1. full allocation of CPU (probably initial streaming and aggregation)
2. around midterm the allocation drops close to 0 and the CLI progress bar freezes for a few seconds (probably the join)
3. full allocation of CPU (probably final aggregation)

Flame graph: https://gist.github.com/Slind14/4e3657379833a7ea4b2817a80686bd3b

Join across merge tree and join engine (total time 25 seconds)

same query just rightTableJoin instead of rightTable

1. only one core allocated for 15 seconds, no CLI progress bar
2. full CPU allocation and completion within 5 seconds

Flame graph: https://gist.github.com/Slind14/3a778bd0bd7f1c8e97dc229d6c74aaae

[amosbird]

case 1:

around midterm the allocation drops close to 0 and the CLI progress bar freezes for a few seconds (probably the join)

Most likely it's doing https://github.com/yandex/ClickHouse/blob/c1/dbms/src/Interpreters/Join.cpp#L514 which is single threaded.

case 2:

only one core allocated for 15 seconds, no CLI progress bar

If you only replace rightTable with rightTableJoin, that won't make any difference. However, if queries like [..] ANY LEFT JOIN rightTableJoin r ON r.uuid = uuid [..] run slow, that's strange.