https://linen.dev logo
Join Slack
Powered by
This message was deleted.
# troubleshooting
s
This message was deleted.
k
We currently do not have an 
UNNEST
, so there is no direct way to explode the multi-value dimensions AFAIK. One roundabout way I can think of is to use groupBy. https://druid.apache.org/docs/latest/querying/multi-value-dimensions.html#grouping You basically run a group by on the multi-value column and get the number of rows in the result.
Copy code
SELECT COUNT(*) FROM (
SELECT multi_value_column FROM your_datasource
GROUP BY 1
)
h
Thanks. That request is timing out...
k
Try increasing the query timeout in your context.
h
4 minutes and running 😄 it's a crazy expensive query, no? especially without any time limit?
k
Yeah, it definitely is expensive 😅 , especially if your datasource is big. It explodes the multiple values from each row and then groups on those distinct values. As a test, let's see if it gives the desired results. But I wouldn't advise running it in a prod setup as your other queries might get starved for resources.
h
curious
COUNT(DISTINCT) might just be good enough...
k
😄
Oops, my bad! 
DISTINCT
already does the explode for us 🤦
h
however, what DOES COUNT(DISTINCT) count? It's far closer to the workaround than to COUNT(*)
Great
k
How long did the 
COUNT(DISTINCT)
take?
h
that 3 column aggregated query took 30s
just count distinct is around the same time
k
Yeah, that should be expected as count distinct is the real work in the query. The rest is just summation. But I guess 30s is okay, given you have 1M rows.
h
the workaround took about 5 minutes
k
still not bad, given how much of a roundabout approach that was 😛
It was grouping on 48M unique values 😂
h
yeah, guess my segment size optimizations finally paid off 😄
k
Nice! 🙂
How did you go about doing these optimizations?
h
Had to migrate data and put some brain grease into configuring the reindex task correctly. and I set up compaction
so new data is eventually also put into month-sized segments
segment size is ~70k now
rows
k
interesting, a bigger number of rows per segment didn't work well enough? how many MBs is each segment now?
h
typically around 250MB
good enough for our use case. queries are much much faster than before when we had multiple tiny segments per day due to bad configuration
partitions, not segments
👍 1
still wondering where the discrepancy between workaround and COUNT(DISTINCT) is coming from...
k
oh, I thought you had even bigger segments earlier! Yeah, multiple tiny segments is very bad. I think you can still go bigger. You can try to push your segment size to 200k or 500k even, atleast as an experiment.
still wondering where the discrepancy between workaround and COUNT(DISTINCT) is coming from...
Maybe 
useApproximateCountDistinct
is set to true?
You can try to push your segment size to 200k or 500k even, atleast as an experiment.
Druid normally advises segment size of 2M to 5M rows, but given that row size seems big, you can experiment in incremental steps and try to push it to 1M.
h
It's a productive system, so no experiments 😄 I think everyone is happy with the much improved performance already. I was aiming at the 300-700MB segment size described in the docs
😛 1
💯 1
k
That's good to hear! Let us know if you run into any other issue.
h
useApproximateCountDistinct
I did not change it, so probably it's still default (true)
ok, but that's fine
thanks @kfaraz for your help!
👍 1
Made a PR for a clarification in the docs. Hope this is helpful. https://github.com/apache/druid/pull/13128
k
Great, thanks! Will review it in some time.
👍 1
2 Views
Open in Slack
PreviousNext
Powered by