Skip to content

Lower memory usage after reading from MergeTree tables#59290

Merged
alexey-milovidov merged 2 commits intoClickHouse:masterfrom
CurtizJ:lower-memory-usage
Jan 28, 2024
Merged

Lower memory usage after reading from MergeTree tables#59290
alexey-milovidov merged 2 commits intoClickHouse:masterfrom
CurtizJ:lower-memory-usage

Conversation

@CurtizJ
Copy link
Copy Markdown
Member

@CurtizJ CurtizJ commented Jan 27, 2024

Changelog category (leave one):

  • Performance Improvement

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

Lower memory usage after reading from MergeTree tables

@CurtizJ
Copy link
Copy Markdown
Member Author

CurtizJ commented Jan 27, 2024

I made a test with table with 100 parts and 30 columns by this script.

Memory usage for horizontal merge:

SELECT
    event_time,
    table,
    merge_algorithm,
    length(merged_from) AS num_source_parts,
    formatReadableSize(peak_memory_usage) AS memory_usage,
    formatReadableSize(bytes_uncompressed) AS bytes_uncompressed,
    formatReadableQuantity(rows) AS rows
FROM system.part_log
WHERE (table IN ('t_local_normal', 't_s3_normal')) AND (event_type = 'MergeParts') AND (part_name = 'all_1_100_1')

Before:

┌──────────event_time─┬─table───────┬─merge_algorithm─┬─num_source_parts─┬─memory_usage─┬─bytes_uncompressed─┬─rows──────────┐
│ 2024-01-27 16:24:46 │ t_s3_normal │ Horizontal      │              100 │ 9.10 GiB     │ 5.94 GiB           │ 10.00 million │
└─────────────────────┴─────────────┴─────────────────┴──────────────────┴──────────────┴────────────────────┴───────────────┘
┌──────────event_time─┬─table──────────┬─merge_algorithm─┬─num_source_parts─┬─memory_usage─┬─bytes_uncompressed─┬─rows──────────┐
│ 2024-01-27 16:21:17 │ t_local_normal │ Horizontal      │              100 │ 3.94 GiB     │ 5.94 GiB           │ 10.00 million │
└─────────────────────┴────────────────┴─────────────────┴──────────────────┴──────────────┴────────────────────┴───────────────┘

After:

┌──────────event_time─┬─table───────┬─merge_algorithm─┬─num_source_parts─┬─memory_usage─┬─bytes_uncompressed─┬─rows──────────┐
│ 2024-01-27 16:38:43 │ t_s3_normal │ Horizontal      │              100 │ 4.52 GiB     │ 5.94 GiB           │ 10.00 million │
└─────────────────────┴─────────────┴─────────────────┴──────────────────┴──────────────┴────────────────────┴───────────────┘
┌──────────event_time─┬─table──────────┬─merge_algorithm─┬─num_source_parts─┬─memory_usage─┬─bytes_uncompressed─┬─rows──────────┐
│ 2024-01-27 16:36:20 │ t_local_normal │ Horizontal      │              100 │ 3.05 GiB     │ 5.94 GiB           │ 10.00 million │
└─────────────────────┴────────────────┴─────────────────┴──────────────────┴──────────────┴────────────────────┴───────────────┘

@robot-ch-test-poll robot-ch-test-poll added the pr-performance Pull request with some performance improvements label Jan 27, 2024
@robot-ch-test-poll
Copy link
Copy Markdown
Contributor

robot-ch-test-poll commented Jan 27, 2024 •
edited by robot-ch-test-poll1
Loading

This is an automated comment for commit ef133bf with description of existing statuses. It's updated for the latest CI running

❌ Click here to open a full report in a separate page

Successful checks
Check nameDescriptionStatus
AST fuzzerRuns randomly generated queries to catch program errors. The build type is optionally given in parenthesis. If it fails, ask a maintainer for help✅ success
ClickBenchRuns [ClickBench](https://github.com/ClickHouse/ClickBench/) with instant-attach table✅ success
ClickHouse build checkBuilds ClickHouse in various configurations for use in further steps. You have to fix the builds that fail. Build logs often has enough information to fix the error, but you might have to reproduce the failure locally. The cmake options can be found in the build log, grepping for cmake. Use these options and follow the general build process✅ success
Compatibility checkChecks that clickhouse binary runs on distributions with old libc versions. If it fails, ask a maintainer for help✅ success
Docker server and keeper imagesThere's no description for the check yet, please add it to tests/ci/ci_config.py:CHECK_DESCRIPTIONS✅ success
Docs checkThere's no description for the check yet, please add it to tests/ci/ci_config.py:CHECK_DESCRIPTIONS✅ success
Fast testsThere's no description for the check yet, please add it to tests/ci/ci_config.py:CHECK_DESCRIPTIONS✅ success
Flaky testsChecks if new added or modified tests are flaky by running them repeatedly, in parallel, with more randomization. Functional tests are run 100 times with address sanitizer, and additional randomization of thread scheduling. Integrational tests are run up to 10 times. If at least once a new test has failed, or was too long, this check will be red. We don't allow flaky tests, read the doc✅ success
Install packagesChecks that the built packages are installable in a clear environment✅ success
Mergeable CheckChecks if all other necessary checks are successful✅ success
Performance ComparisonMeasure changes in query performance. The performance test report is described in detail here. In square brackets are the optional part/total tests✅ success
SQLancerFuzzing tests that detect logical bugs with SQLancer tool✅ success
SqllogicRun clickhouse on the sqllogic test set against sqlite and checks that all statements are passed✅ success
Stateful testsRuns stateful functional tests for ClickHouse binaries built in various configurations -- release, debug, with sanitizers, etc✅ success
Stateless testsRuns stateless functional tests for ClickHouse binaries built in various configurations -- release, debug, with sanitizers, etc✅ success
Stress testRuns stateless functional tests concurrently from several clients to detect concurrency-related errors✅ success
Style checkThere's no description for the check yet, please add it to tests/ci/ci_config.py:CHECK_DESCRIPTIONS✅ success
Unit testsRuns the unit tests for different release types✅ success
Check nameDescriptionStatus
CI runningA meta-check that indicates the running CI. Normally, it's in success or pending state. The failed status indicates some problems with the PR⏳ pending
Integration testsThe integration tests report. In parenthesis the package type is given, and in square brackets are the optional part/total tests❌ failure
Upgrade checkRuns stress tests on server version from last release and then tries to upgrade it to the version from the PR. It checks if the new server can successfully startup without any errors, crashes or sanitizer asserts❌ failure

@CurtizJ CurtizJ marked this pull request as ready for review January 27, 2024 17:41
@alexey-milovidov alexey-milovidov self-assigned this Jan 27, 2024
@alesapin
Copy link
Copy Markdown
Member

alesapin commented Jan 28, 2024

Wow

@alexey-milovidov
Copy link
Copy Markdown
Member

alexey-milovidov commented Jan 28, 2024

@CurtizJ, let's merge with master to run the perf tests again.

@CurtizJ
Copy link
Copy Markdown
Member Author

CurtizJ commented Jan 28, 2024 •
edited
Loading

Build failed with error and was not restarted automatically. I think it's a bad practice to fail the whole build and restart it because of failure in optional functionality (send traces to ci logs cluster).

Notice: Log Uploading profile data, path: %s, size: %s, query: %s /home/ubuntu/actions-runner/_work/_temp/build_check/profile.json 126487744 INSERT INTO build_time_trace
            (
                pull_request_number,
                commit_sha,
                check_start_time,
                check_name,
                instance_type,
                instance_id,
                file,
                library,
                time,
                pid,
                tid,
                ph,
                ts,
                dur,
                cat,
                name,
                detail,
                count,
                avgMs,
                args_name
            )
            SELECT 59290, 'ef133bfd47f2466afeaeb3453483ab9a5dae2ea6', '2024-01-28 15:35:51', 'package_release', 'c5.9xlarge', 'i-0b3431840dd73e4f3', *
            FROM input('
                file String,
                library String,
                time DateTime64(6),
                pid UInt32,
                tid UInt32,
                ph String,
                ts UInt64,
                dur UInt64,
                cat String,
                name String,
                detail String,
                count UInt64,
                avgMs UInt64,
                args_name String')
            FORMAT JSONCompactEachRow
WARNING:root:Received exception while sending data to https://p12uiq1ogd.us-east-2.aws.clickhouse-staging.com/ on 0 attempt: ('Connection aborted.', TimeoutError('The write operation timed out'))
INFO:root:Response content 'b'Code: 27. DB::Exception: Cannot parse input: expected \'[\' before: \'e_compressor","build_docker/utils/self-extracting-executable/pre_compressor.time-trace",1706456510933517,590810,591010,"X",3360876,15775,null,"RunPass","Greedy \': (at row 1)\n: \nRow 1:\nERROR: There is no \'[\' before the row.\n\n: While executing ParallelParsingBlockInputFormat. (CANNOT_PARSE_INPUT_ASSERTION_FAILED) (version 24.0.2.54351 (official build))\n''
INFO:root:Request headers '{'User-Agent': 'python-requests/2.31.0', 'Accept-Encoding': 'gzip, deflate', 'Accept': '*/*', 'Connection': 'keep-alive', 'X-ClickHouse-User': 'ci', 'X-ClickHouse-Key': '4HsJb8CKmF4o~', 'Content-Length': '116927680'}', body '<_io.BufferedReader name='/home/ubuntu/actions-runner/_work/_temp/build_check/profile.json'>'
Traceback (most recent call last):
  File "/home/ubuntu/actions-runner/_work/ClickHouse/ClickHouse/tests/ci/ci.py", line 1[197](https://github.com/ClickHouse/ClickHouse/actions/runs/7686606689/job/20945941685#step:9:198), in <module>
    sys.exit(main())
  File "/home/ubuntu/actions-runner/_work/ClickHouse/ClickHouse/tests/ci/ci.py", line 1047, in main
    _upload_build_profile_data(
  File "/home/ubuntu/actions-runner/_work/ClickHouse/ClickHouse/tests/ci/ci.py", line 748, in _upload_build_profile_data
    ch_helper.insert_file(url, auth, query, profile_data_file)
  File "/home/ubuntu/actions-runner/_work/ClickHouse/ClickHouse/tests/ci/clickhouse_helper.py", line 55, in insert_file
    ClickHouseHelper._insert_post(
  File "/home/ubuntu/actions-runner/_work/ClickHouse/ClickHouse/tests/ci/clickhouse_helper.py", line 106, in _insert_post
    raise InsertException(error)
clickhouse_helper.InsertException: Cannot insert data into clickhouse at try 1: HTTP code 400: 'Code: 27. DB::Exception: Cannot parse input: expected '[' before: 'e_compressor","build_docker/utils/self-extracting-executable/pre_compressor.time-trace",1706456510933517,590810,591010,"X",3360876,15775,null,"RunPass","Greedy ': (at row 1)
: 
Row 1:
ERROR: There is no '[' before the row.

: While executing ParallelParsingBlockInputFormat. (CANNOT_PARSE_INPUT_ASSERTION_FAILED) (version 24.0.2.54351 (official build))
'
Error: Process completed with exit code 1.
0s

@alexey-milovidov
Copy link
Copy Markdown
Member

alexey-milovidov commented Jan 28, 2024

Performance tests ok.

@alexey-milovidov alexey-milovidov merged commit caded18 into ClickHouse:master Jan 28, 2024
@robot-clickhouse-ci-1 robot-clickhouse-ci-1 added the pr-backports-created-cloud deprecated label, NOOP label Jan 31, 2024
@CheSema CheSema mentioned this pull request Mar 8, 2024
Closed
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Reviewers

@alexey-milovidov alexey-milovidov alexey-milovidov approved these changes

Assignees

@alexey-milovidov alexey-milovidov

Labels

pr-backports-created-cloud deprecated label, NOOP pr-performance Pull request with some performance improvements

Projects

None yet

Milestone

No milestone

Development

Successfully merging this pull request may close these issues.

5 participants

@CurtizJ @robot-ch-test-poll @alesapin @alexey-milovidov @robot-clickhouse-ci-1