Slow deserialization of large datasets from ClickHouse
- Dominant language
- Rust
- Stars
- 20.8k
- Forks
- 2.1k
- Avg merge
- 1d 2h
- Merged PRs (30d)
- 181
Description
**Good SQL**
`select * from dataset limit 100` 1s
`select * from dataset limit 500` 4s
**Timeout error**
`select * from dataset limit 5000`
`select * from dataset`
**Underlying DBMS**
clickhouse
**Version:**
cubejs/cube:latest
**Additional context**
Hi, guys! I found strange behaviour, sorry, cannot provide any screens, logs due to NDA, but will try to desribe it.
We use Clickhouse as DBMS and we have huge table of trancactions, so when client asks data and uses transaction as dimension, query is pretty similar to `select *`
The main problem is when we select using small limit, everything works. When we try to select without limit (meaning default 10k), cubejs stacks and do not respond to /livez and /readyz. I think this problem lies somewhere inside processing of result of the query due to the facts below
1) Clickhouse query history as well as cubejs logs (Executing SQL -> perfoming query completed) show that response is fast. So, it is not underlying DMBS issue. Manual running query via Datagip quickly receives response too even for 100k rows.
2) I cant see any CPU or memory spikes for Cube.js pods, it just stacks(
3) Response time is directly proportional to limit size
4) Problem can be reproduced locally, so network/infrastructure problem seems to be unlikely
My theory is that problem is with deserializing result of query (cubejs asks `FORMAT JSON` as I can see) or suboptimal processing (e.g. loops or something like that).
Contributor guide
Assessment
This issue has not been assessed yet.