ttl_merge_of_wide_rows_mem_usage

CREATE TABLE wide_rows2(`A` Int64, `D` Date, `S` String)
ENGINE = MergeTree PARTITION BY toYYYYMM(D) ORDER BY A
settings merge_with_ttl_timeout=300, materialize_ttl_recalculate_only = 1;

insert into wide_rows2 select number, toDate('2020-01-01')+ number%20, arrayMap(i-> cityHash64((number*i)%999), range(2000)) from numbers(1e5);
insert into wide_rows2 select number, toDate('2020-01-01')+ number%20, arrayMap(i-> cityHash64((number*i)%899), range(2000)) from numbers(1e5);
insert into wide_rows2 select number, toDate('2020-01-01')+ number%20, arrayMap(i-> cityHash64((number*i)%799), range(2000)) from numbers(1e5);
insert into wide_rows2 select number, toDate('2020-01-01')+ number%20, arrayMap(i-> cityHash64((number*i)%699), range(2000)) from numbers(1e5);
set materialize_ttl_after_modify=0;

alter table wide_rows2 modify ttl D + toIntervalDay(1335);

select count(), uniq(_part) from wide_rows2 where D <= '2020-01-10';
┌─count()─┬─uniq(_part)─┐
│  200000 │           4 │
└─────────┴─────────────┘

alter table wide_rows2 materialize ttl settings mutations_sync=2; 

SELECT
    event_time,
    event_type,
    merge_reason,
    rows,
    formatReadableSize(size_in_bytes) AS size,
    formatReadableSize(bytes_uncompressed) AS uncomp,
    formatReadableSize(peak_memory_usage) AS mem,
    merged_from,
    part_name
FROM system.part_log
WHERE (table = 'wide_rows2') AND (event_date >= today())
ORDER BY event_time DESC
LIMIT 1000
FORMAT PrettyCompactMonoBlock

23.3.8.21

text
┌──────────event_time─┬─event_type─┬─merge_reason───┬───rows─┬─size───────┬─uncomp─────┬─mem────────┬─merged_from───────────────────────────────────────────────────────────┬─part_name──────┐
│ 2023-09-06 21:58:41 │ MergeParts │ TTLDeleteMerge │ 200000 │ 1.79 GiB   │ 12.26 GiB  │ 997.80 MiB │ ['202001_1_1_0_5','202001_2_2_0_5','202001_3_3_0_5','202001_4_4_0_5'] │ 202001_1_4_1_5 │
│ 2023-09-06 21:58:21 │ MutatePart │ NotAMerge      │ 100000 │ 646.69 MiB │ 195.31 KiB │ 0.00 B     │ ['202001_4_4_0']                                                      │ 202001_4_4_0_5 │
│ 2023-09-06 21:58:21 │ MutatePart │ NotAMerge      │ 100000 │ 810.04 MiB │ 195.31 KiB │ 0.00 B     │ ['202001_3_3_0']                                                      │ 202001_3_3_0_5 │
│ 2023-09-06 21:58:21 │ MutatePart │ NotAMerge      │ 100000 │ 983.31 MiB │ 195.31 KiB │ 0.00 B     │ ['202001_2_2_0']                                                      │ 202001_2_2_0_5 │
│ 2023-09-06 21:58:21 │ MutatePart │ NotAMerge      │ 100000 │ 1.01 GiB   │ 195.31 KiB │ 0.00 B     │ ['202001_1_1_0']                                                      │ 202001_1_1_0_5 │
│ 2023-09-06 21:57:42 │ NewPart    │ NotAMerge      │ 100000 │ 646.69 MiB │ 0.00 B     │ 0.00 B     │ []                                                                    │ 202001_4_4_0   │
│ 2023-09-06 21:57:35 │ NewPart    │ NotAMerge      │ 100000 │ 810.04 MiB │ 0.00 B     │ 0.00 B     │ []                                                                    │ 202001_3_3_0   │
│ 2023-09-06 21:57:27 │ NewPart    │ NotAMerge      │ 100000 │ 983.31 MiB │ 0.00 B     │ 0.00 B     │ []                                                                    │ 202001_2_2_0   │
│ 2023-09-06 21:57:18 │ NewPart    │ NotAMerge      │ 100000 │ 1.01 GiB   │ 0.00 B     │ 0.00 B     │ []                                                                    │ 202001_1_1_0   │
└─────────────────────┴────────────┴────────────────┴────────┴────────────┴────────────┴────────────┴───────────────────────────────────────────────────────────────────────┴────────────────┘

23.8.2.7

┌──────────event_time─┬─event_type─┬─merge_reason───┬───rows─┬─size───────┬─uncomp─────┬─mem────────┬─merged_from───────────────────────────────────────────────────────────┬─part_name──────┐
│ 2023-09-06 21:59:10 │ MergeParts │ TTLDeleteMerge │ 200000 │ 404.26 MiB │ 11.38 GiB  │ 216.93 MiB │ ['202001_1_1_0_5','202001_2_2_0_5','202001_3_3_0_5','202001_4_4_0_5'] │ 202001_1_4_1_5 │
│ 2023-09-06 21:58:51 │ MutatePart │ NotAMerge      │ 100000 │ 810.02 MiB │ 195.31 KiB │ 2.24 MiB   │ ['202001_3_3_0']                                                      │ 202001_3_3_0_5 │
│ 2023-09-06 21:58:51 │ MutatePart │ NotAMerge      │ 100000 │ 1.01 GiB   │ 195.31 KiB │ 2.22 MiB   │ ['202001_1_1_0']                                                      │ 202001_1_1_0_5 │
│ 2023-09-06 21:58:51 │ MutatePart │ NotAMerge      │ 100000 │ 983.29 MiB │ 195.31 KiB │ 2.22 MiB   │ ['202001_2_2_0']                                                      │ 202001_2_2_0_5 │
│ 2023-09-06 21:58:51 │ MutatePart │ NotAMerge      │ 100000 │ 646.66 MiB │ 195.31 KiB │ 2.22 MiB   │ ['202001_4_4_0']                                                      │ 202001_4_4_0_5 │
│ 2023-09-06 21:58:27 │ NewPart    │ NotAMerge      │ 100000 │ 646.66 MiB │ 0.00 B     │ 0.00 B     │ []                                                                    │ 202001_4_4_0   │
│ 2023-09-06 21:58:21 │ NewPart    │ NotAMerge      │ 100000 │ 810.02 MiB │ 0.00 B     │ 0.00 B     │ []                                                                    │ 202001_3_3_0   │
│ 2023-09-06 21:58:15 │ NewPart    │ NotAMerge      │ 100000 │ 983.29 MiB │ 0.00 B     │ 0.00 B     │ []                                                                    │ 202001_2_2_0   │
│ 2023-09-06 21:58:08 │ NewPart    │ NotAMerge      │ 100000 │ 1.01 GiB   │ 0.00 B     │ 0.00 B     │ []                                                                    │ 202001_1_1_0   │
└─────────────────────┴────────────┴────────────────┴────────┴────────────┴────────────┴────────────┴───────────────────────────────────────────────────────────────────────┴────────────────┘

den-crane/ttl_merge_of_wide_rows_mem_usage.md