ClickHouse / ClickHouse/ClickHouse
Lightweight deletes are extremely slow when index_granularity is small (e.g. 128)
- Dominant language
- C++
- Stars
- 49.9k
- Forks
- 9k
- Avg merge
- 21h 32m
- Merged PRs (30d)
- 515
Description
**Describe the situation**
The lightweight deletes are slow when index_granularity is small (in our test case: 128). The largest part has 100 million rows, a delete takes about 14 seconds, while a similar select with settings max_threads=1 takes only 2 seconds.
After debugs on differences of LWD and SELECT
By comparing the serial reading data between LWD and SELECT, we find that:
- For SELECT, MergeTreeReaderWide::readRows() is called to read most max_block_size (65409) rows.
- For LWD, MergeTreeReaderWide::readRows() is called to read most index_granularity (128) rows. This is the current behavior of MergeTreeSequentialSource, which read ONE mark range every time.
The same delete takes about 1s after increasing the index_granularity from 128 to default 8192.
Would you mind to explain why MergeTreeSequentialSource read one mark range from table's data?Will there be any negative consequences if MergeTreeSequentialSource try to read multiple mark ranges?
**How to reproduce**
CREATE TABLE test_lwd_128
(
id UInt64,
a_str String,
b int
) ENGINE = MergeTree ORDER BY id SETTINGS index_granularity = 128;
insert into test_lwd_128 select number, number, number from numbers(100000000);
-- merge is also slow: Elapsed: 18.818 sec
optimize table test_lwd_128 final;
delete from test_lwd_128 where id=2 and a_str='2';
```
0 rows in set. Elapsed: 11.814 sec.
```
Contributor guide
Assessment
This issue has not been assessed yet.