ClickHouse / ClickHouse/ClickHouse

A few VERY long strings in lowcardinality column can lead to significant performance degradation

Open
#24,280 1 comment 0 reactions 0 assignees View on GitHub
comp-datatype-wrapper external memory minor performance
Dominant language
C++
Stars
49.9k
Forks
9k
Avg merge
21h 32m
Merged PRs (30d)
515

Description

Test:

```sql
DROP TABLE IF EXISTS lc_memory_issue SYNC;

create table lc_memory_issue (
a LowCardinality(String),
number UInt64,
partitionid UInt8
)
engine=MergeTree
ORDER BY (a,number)
PARTITION BY partitionid;

INSERT INTO lc_memory_issue select 'token_' || hex(1000000 + rand() % 1000) as a, number, number%5 from numbers_mt(25000000);
optimize table lc_memory_issue final;

SELECT uniq(a) FROM lc_memory_issue

/* 1 rows in set. Elapsed: 0.131 sec. Processed 25.00 million rows, 54.26 MB (190.85 million rows/s., 414.24 MB/s.)

query_duration_ms: 129
read_rows: 25000000
read_bytes: 54262320
memory_usage: 0
*/
```

Everything is ok till now.

Now let's 'pollute' lowcardinality column with few VERY long strings.

```sql
insert into lc_memory_issue select 'token_' || hex(1000000 + rand() % 1000) || repeat(repeat('\0',100000),40), 0, arrayJoin(range(5)) from numbers(20);

SELECT uniq(a) FROM lc_memory_issue
/* 1 rows in set. Elapsed: 0.260 sec. Processed 25.00 million rows, 8.05 GB (96.16 million rows/s., 30.98 GB/s.)

query_duration_ms: 258
read_rows: 25000100
read_bytes: 8054300726
memory_usage: 615270194
*/

OPTIMIZE TABLE lc_memory_issue FINAL;

SELECT uniq(a) FROM lc_memory_issue
/* 1 rows in set. Elapsed: 1.970 sec. Processed 25.00 million rows, 185.45 GB (12.69 million rows/s., 94.12 GB/s.)

query_duration_ms: 1968
read_rows: 25000100
read_bytes: 185453425273
memory_usage: 1552862566
*/
```

As you can see the query becomes more than 10 times slower, requires much more RAM, and thinks it read 200Gb of data (which doesn't sound correct).

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.