Fix excessive number of files (leads to excessive memory usage) for external ORDER BY by azat · Pull Request #80777 · ClickHouse/ClickHouse

azat · 2025-05-24T21:07:30Z

Changelog category (leave one):

Bug Fix (user-visible misbehavior in an official stable release)

Changelog entry (a user-readable short description of the changes that goes to CHANGELOG.md):

Fix excessive number of files (leads to excessive memory usage) for external ORDER BY

In #72598 the behavior of max_bytes_before_external_sort has been changed, it has been switched to rely on the total memory usage of the query instead of the size of a sorting data.

But, the problem with this approach is that you can have a lot of memory used outside of sorting (FINAL, aggregation), and spilling sorting blocks to disk will not help reduce memory usage obviously, but it will make things even worse, because it will create lots of small files, that will require a lot of memory to merge them.

So this patch reverts the behavior of max_bytes_before_external_sort back, now it will rely on the sorting block size.

Fixes: #72598 (cc @alexey-milovidov )

…xternal ORDER BY In ClickHouse#72598 the behavior of `max_bytes_before_external_sort` has been changed, it has been switched to rely on the total memory usage of the query instead of the size of a sorting data. But, the problem with this approach is that you can have a lot of memory used outside of sorting (FINAL, aggregation), and spilling sorting blocks to disk will not help reduce memory usage obviously, but it will make things even worse, because it will create lots of small files, that will require a lot of memory to merge them. So this patch reverts the behavior of `max_bytes_before_external_sort` back, now it will rely on the sorting block size.

clickhouse-gh · 2025-05-24T21:08:12Z

Workflow [PR], commit [27f1ccb]

Set max_bytes_before_external_sort=min_external_sort_block_bytes=100Mi

The problem is that now both conditions should be met: - max_bytes_ratio_before_external_sort - max_bytes_before_external_sort So it is not possible to use max_bytes_ratio_before_external_sort=0.9, this will lead to MEMORY_LIMIT_EXCEEDED errors

src/Core/Settings.cpp

src/Processors/QueryPlan/SortingStep.cpp

Cherry pick #80777 to 25.5: Fix excessive number of files (leads to excessive memory usage) for external ORDER BY

…ssive memory usage) for external ORDER BY

Backport #80777 to 25.5: Fix excessive number of files (leads to excessive memory usage) for external ORDER BY

Cherry pick #80777 to 25.3: Fix excessive number of files (leads to excessive memory usage) for external ORDER BY

…ssive memory usage) for external ORDER BY

Cherry pick #80777 to 25.4: Fix excessive number of files (leads to excessive memory usage) for external ORDER BY

Fix rebase issue: - 20250529 ClickHouse#80777 - 20250326 ClickHouse#77729 - 20250308 ClickHouse#77269

azat added the pr-must-backport Pull request should be backported intentionally. Use this label with great care! label May 24, 2025

clickhouse-gh bot added the pr-bugfix Pull request with bugfix, not backported by default label May 24, 2025

azat mentioned this pull request May 24, 2025

Make max_bytes_before_external_sort limit depends on total query memory consumption #72598

Merged

azat added 3 commits May 25, 2025 13:44

tests: tune 03298_memory_limit_thresholds_combination

4b6f795

Set max_bytes_before_external_sort=min_external_sort_block_bytes=100Mi

tests: adjust 03339_native_reader_exact_rows

2a795af

Michicosun self-assigned this May 26, 2025

Michicosun reviewed May 27, 2025

View reviewed changes

src/Core/Settings.cpp Outdated Show resolved Hide resolved

src/Processors/QueryPlan/SortingStep.cpp Show resolved Hide resolved

azat added 2 commits May 27, 2025 16:43

Fix typo in documentation for max_bytes_ratio_before_external_sort

e4d14cf

Fix SortingStep::Settings created from QueryPlanSerializationSettings

27f1ccb

Michicosun approved these changes May 28, 2025

View reviewed changes

azat added this pull request to the merge queue May 28, 2025

Merged via the queue into ClickHouse:master with commit dd15c52 May 28, 2025
120 checks passed

azat deleted the fix-external-sort branch May 28, 2025 11:01

robot-clickhouse-ci-1 added a commit that referenced this pull request May 28, 2025

Merge pull request #80954 from ClickHouse/cherrypick/25.5/80777

39029ce

Cherry pick #80777 to 25.5: Fix excessive number of files (leads to excessive memory usage) for external ORDER BY

robot-clickhouse added a commit that referenced this pull request May 28, 2025

Backport #80777 to 25.5: Fix excessive number of files (leads to exce…

f3035f6

…ssive memory usage) for external ORDER BY

robot-clickhouse-ci-1 mentioned this pull request May 28, 2025

Backport #80777 to 25.5: Fix excessive number of files (leads to excessive memory usage) for external ORDER BY #80955

Merged

robot-clickhouse-ci-1 added the pr-synced-to-cloud The PR is synced to the cloud repo label May 28, 2025

azat added a commit that referenced this pull request May 28, 2025

Merge pull request #80955 from ClickHouse/backport/25.5/80777

4ae1eb5

Backport #80777 to 25.5: Fix excessive number of files (leads to excessive memory usage) for external ORDER BY

robot-clickhouse-ci-2 added a commit that referenced this pull request May 28, 2025

Merge pull request #80952 from ClickHouse/cherrypick/25.3/80777

f851f0d

Cherry pick #80777 to 25.3: Fix excessive number of files (leads to excessive memory usage) for external ORDER BY

robot-clickhouse added a commit that referenced this pull request May 28, 2025

Backport #80777 to 25.3: Fix excessive number of files (leads to exce…

d872dc2

…ssive memory usage) for external ORDER BY

robot-clickhouse-ci-2 mentioned this pull request May 28, 2025

Backport #80777 to 25.3: Fix excessive number of files (leads to excessive memory usage) for external ORDER BY #80968

Merged

robot-clickhouse-ci-2 added a commit that referenced this pull request May 28, 2025

Merge pull request #80953 from ClickHouse/cherrypick/25.4/80777

496ee2d

Cherry pick #80777 to 25.4: Fix excessive number of files (leads to excessive memory usage) for external ORDER BY

kyligence-git pushed a commit to Kyligence/ClickHouse that referenced this pull request Jun 9, 2025

Optimize sort spill (#490)

e34f9d1

Fix rebase issue: - 20250529 ClickHouse#80777 - 20250326 ClickHouse#77729 - 20250308 ClickHouse#77269

kyligence-git pushed a commit to Kyligence/ClickHouse that referenced this pull request Jun 10, 2025

Optimize sort spill (#490)

0e78f01

Fix rebase issue: - 20250529 ClickHouse#80777 - 20250326 ClickHouse#77729 - 20250308 ClickHouse#77269

kyligence-git pushed a commit to Kyligence/ClickHouse that referenced this pull request Jun 11, 2025

Optimize sort spill (#490)

bb9e2c2

Fix rebase issue: - 20250529 ClickHouse#80777 - 20250326 ClickHouse#77729 - 20250308 ClickHouse#77269

kyligence-git pushed a commit to Kyligence/ClickHouse that referenced this pull request Jun 12, 2025

Optimize sort spill (#490)

b878eac

Fix rebase issue: - 20250529 ClickHouse#80777 - 20250326 ClickHouse#77729 - 20250308 ClickHouse#77269

baibaichen added a commit to Kyligence/ClickHouse that referenced this pull request Jun 13, 2025

Optimize sort spill (#490)

fdb07a2

Fix rebase issue: - 20250529 ClickHouse#80777 - 20250326 ClickHouse#77729 - 20250308 ClickHouse#77269

kyligence-git pushed a commit to Kyligence/ClickHouse that referenced this pull request Jun 13, 2025

Optimize sort spill (#490)

a98b97b

Fix rebase issue: - 20250529 ClickHouse#80777 - 20250326 ClickHouse#77729 - 20250308 ClickHouse#77269

kyligence-git pushed a commit to Kyligence/ClickHouse that referenced this pull request Jun 14, 2025

Optimize sort spill (#490)

cfbb3d3

Fix rebase issue: - 20250529 ClickHouse#80777 - 20250326 ClickHouse#77729 - 20250308 ClickHouse#77269

kyligence-git pushed a commit to Kyligence/ClickHouse that referenced this pull request Jun 15, 2025

Optimize sort spill (#490)

8445041

Fix rebase issue: - 20250529 ClickHouse#80777 - 20250326 ClickHouse#77729 - 20250308 ClickHouse#77269

kyligence-git pushed a commit to Kyligence/ClickHouse that referenced this pull request Jun 16, 2025

Optimize sort spill (#490)

2b0c808

Fix rebase issue: - 20250529 ClickHouse#80777 - 20250326 ClickHouse#77729 - 20250308 ClickHouse#77269

kyligence-git pushed a commit to Kyligence/ClickHouse that referenced this pull request Jun 17, 2025

Optimize sort spill (#490)

086aa24

Fix rebase issue: - 20250529 ClickHouse#80777 - 20250326 ClickHouse#77729 - 20250308 ClickHouse#77269

kyligence-git pushed a commit to Kyligence/ClickHouse that referenced this pull request Jun 18, 2025

Optimize sort spill (#490)

b56024d

Fix rebase issue: - 20250529 ClickHouse#80777 - 20250326 ClickHouse#77729 - 20250308 ClickHouse#77269

kyligence-git pushed a commit to Kyligence/ClickHouse that referenced this pull request Jun 19, 2025

Optimize sort spill (#490)

2d6310c

Fix rebase issue: - 20250529 ClickHouse#80777 - 20250326 ClickHouse#77729 - 20250308 ClickHouse#77269

kyligence-git pushed a commit to Kyligence/ClickHouse that referenced this pull request Jun 20, 2025

Optimize sort spill (#490)

c1e6cf4

Fix rebase issue: - 20250529 ClickHouse#80777 - 20250326 ClickHouse#77729 - 20250308 ClickHouse#77269

kyligence-git pushed a commit to Kyligence/ClickHouse that referenced this pull request Jun 21, 2025

Optimize sort spill (#490)

9710daf

Fix rebase issue: - 20250529 ClickHouse#80777 - 20250326 ClickHouse#77729 - 20250308 ClickHouse#77269

kyligence-git pushed a commit to Kyligence/ClickHouse that referenced this pull request Jun 22, 2025

Optimize sort spill (#490)

77259f8

Fix rebase issue: - 20250529 ClickHouse#80777 - 20250326 ClickHouse#77729 - 20250308 ClickHouse#77269

kyligence-git pushed a commit to Kyligence/ClickHouse that referenced this pull request Jun 23, 2025

Optimize sort spill (#490)

17ac919

Fix rebase issue: - 20250529 ClickHouse#80777 - 20250326 ClickHouse#77729 - 20250308 ClickHouse#77269

kyligence-git pushed a commit to Kyligence/ClickHouse that referenced this pull request Jun 24, 2025

Optimize sort spill (#490)

db52cc1

Fix rebase issue: - 20250529 ClickHouse#80777 - 20250326 ClickHouse#77729 - 20250308 ClickHouse#77269

baibaichen added a commit to Kyligence/ClickHouse that referenced this pull request Jun 26, 2025

Optimize sort spill (#490)

99ab324

Fix rebase issue: - 20250529 ClickHouse#80777 - 20250326 ClickHouse#77729 - 20250308 ClickHouse#77269

baibaichen added a commit to Kyligence/ClickHouse that referenced this pull request Jun 27, 2025

Optimize sort spill (#490)

7e1b43e

Fix rebase issue: - 20250529 ClickHouse#80777 - 20250326 ClickHouse#77729 - 20250308 ClickHouse#77269

baibaichen added a commit to Kyligence/ClickHouse that referenced this pull request Jun 28, 2025

Optimize sort spill (#490)

cf87ae8

Fix rebase issue: - 20250529 ClickHouse#80777 - 20250326 ClickHouse#77729 - 20250308 ClickHouse#77269

baibaichen added a commit to Kyligence/ClickHouse that referenced this pull request Jun 29, 2025

Optimize sort spill (#490)

b732178

Fix rebase issue: - 20250529 ClickHouse#80777 - 20250326 ClickHouse#77729 - 20250308 ClickHouse#77269

kyligence-git pushed a commit to Kyligence/ClickHouse that referenced this pull request Jun 29, 2025

Optimize sort spill (#490)

080f911

Fix rebase issue: - 20250529 ClickHouse#80777 - 20250326 ClickHouse#77729 - 20250308 ClickHouse#77269

robot-clickhouse added the pr-must-backport-synced The `*-must-backport` labels are synced into the cloud Sync PR label Jul 2, 2025

baibaichen added a commit to Kyligence/ClickHouse that referenced this pull request Jul 3, 2025

Optimize sort spill (#490)

37ccf18

Fix rebase issue: - 20250529 ClickHouse#80777 - 20250326 ClickHouse#77729 - 20250308 ClickHouse#77269

baibaichen added a commit to Kyligence/ClickHouse that referenced this pull request Jul 4, 2025

Optimize sort spill (#490)

2f5f8b6

Fix rebase issue: - 20250529 ClickHouse#80777 - 20250326 ClickHouse#77729 - 20250308 ClickHouse#77269

baibaichen added a commit to Kyligence/ClickHouse that referenced this pull request Jul 5, 2025

Optimize sort spill (#490)

712db7c

Fix rebase issue: - 20250529 ClickHouse#80777 - 20250326 ClickHouse#77729 - 20250308 ClickHouse#77269

kyligence-git pushed a commit to Kyligence/ClickHouse that referenced this pull request Jul 6, 2025

Optimize sort spill (#490)

5d310d4

Fix rebase issue: - 20250529 ClickHouse#80777 - 20250326 ClickHouse#77729 - 20250308 ClickHouse#77269

baibaichen added a commit to Kyligence/ClickHouse that referenced this pull request Jul 7, 2025

Optimize sort spill (#490)

32da515

Fix rebase issue: - 20250529 ClickHouse#80777 - 20250326 ClickHouse#77729 - 20250308 ClickHouse#77269

kyligence-git pushed a commit to Kyligence/ClickHouse that referenced this pull request Jul 7, 2025

Optimize sort spill (#490)

ca9c4c0

Fix rebase issue: - 20250529 ClickHouse#80777 - 20250326 ClickHouse#77729 - 20250308 ClickHouse#77269

kyligence-git pushed a commit to Kyligence/ClickHouse that referenced this pull request Jul 8, 2025

Optimize sort spill (#490)

6dfa9a5

Fix rebase issue: - 20250529 ClickHouse#80777 - 20250326 ClickHouse#77729 - 20250308 ClickHouse#77269

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Fix excessive number of files (leads to excessive memory usage) for external ORDER BY#80777

Fix excessive number of files (leads to excessive memory usage) for external ORDER BY#80777
azat merged 6 commits intoClickHouse:masterfrom
azat:fix-external-sort

azat commented May 24, 2025

Uh oh!

clickhouse-gh bot commented May 24, 2025 •

edited

Loading

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

7 participants

Conversation

azat commented May 24, 2025

Changelog category (leave one):

Changelog entry (a user-readable short description of the changes that goes to CHANGELOG.md):

Uh oh!

clickhouse-gh bot commented May 24, 2025 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

7 participants

clickhouse-gh bot commented May 24, 2025 •

edited

Loading