ClickHouse / ClickHouse/ClickHouse
A few VERY long strings in lowcardinality column can lead to significant performance degradation
- 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
Assessment
This issue has not been assessed yet.