groupBy v2: Multi-level merging of spill files

[gianm]

Currently groupBy v2 opens all spill files at the same time for merging. For queries with large result sets, this can lead to extreme numbers of open files. For cases like this we should do some kind of multi-level merging rather than opening them all at once.

[arpankhagram]

Hi Gianm,

We were also facing this issue while performing groupby using V2 when we are keeping the disk spill option.

We have remove disk spill option but have not been able to find a proper solution for running large groupBy queries.

Data Size : We are having around 62-65 metrics and 7-8 dimensions in each poll of every 15 mins and around 8 million records every 15 mins. So total there are 30-35 million records (events) every hour. Say we have 8 million network ports (subelements) for which we get performance metrics data every 15 mins.

Requirements : We have requirement to provide a feed with 24 hours data with few customized metrics and customized functions. Because of involvement of customized functions, we thought of doing them after getting the aggregated data through groupBy query. So the result of first groupBy query itself is 8 million where we divide the data into clean and dirty ( we have to fire another groupBy query individually for those dirty as part of customized functions and 1 combined query for those cleaned elements). Overall, it included a lot of queries being fired on Broker.

Issues
We are struggling to find a tuned parameters for our Historical node and Broker node - we have been facing a lot of issues.

1. Initially all our queries were getting timedout , when we reffered the documentation it says default timeout is 0 unlimited but it;s not behaving that way so we had to set the query timeout specifically. The exception grouping resource exhausted does not specifically help to identify what is the exact cause of the issue.

2. After setting the query timeout as 10 mins, most of our queries are not able to complete within the time frame.

3. We also wanted to fire multiple queries simultaneously but as soon as we tried to increase the connections and fire multiple queries simultaneously it was all started breaking after few mins.

Current configuration Historical
RAM - 64 GB
Core - 32 (dual core)
OS - Linux OEL6

Historical

runtime.properties
druid.service=druid/historical
druid.port=8083
druid.server.tier=h69

HTTP server threads

druid.server.http.numThreads=40

Processing threads and buffers

#druid.processing.buffer.sizeBytes=536870912
druid.processing.buffer.sizeBytes=1500000000
druid.processing.numThreads=19
druid.processing.numMergeBuffers=5

Segment storage

druid.segmentCache.locations=[{"path":"var/druid/segment-cache","maxSize":900000000000}]
druid.server.maxSize=900000000000
druid.historical.cache.unCacheable=["groupBy"]

Query cache

druid.historical.cache.useCache=true
druid.historical.cache.populateCache=true
druid.cache.type=local
druid.cache.sizeInBytes=2000000000

jvm.properties
-server
-Xms1g
-Xmx15g
-XX:NewSize=4g
-XX:MaxNewSize=4g
-XX:MaxDirectMemorySize=35g
-XX:+UseConcMarkSweepGC
-XX:+PrintGCDetails
-XX:+PrintGCTimeStamps
-Duser.timezone=UTC
-Dfile.encoding=UTF-8
-Djava.io.tmpdir=var/tmp
-Djava.util.logging.manager=org.apache.logging.log4j.jul.LogManager
-Dcom.sun.management.jmxremote.port=7092
-Dcom.sun.management.jmxremote.authenticate=false
-Dcom.sun.management.jmxremote.ssl=false

We were trying to find out what is going wrong with respect to memory settings through JMX port we were not able to track MaxDirectMemory anywhere.

Can you please help us in providing druid with correct configuration and why it;s behaving weird.

We upgraded to latest snapshot codebase around 15-20 days ago and we are going to try now once with the latest rc2 Release candidate.
Refer - #4025

[jihoonson]

Hi @arpankhagram, I'm looking at this problem.

3. We also wanted to fire multiple queries simultaneously but as soon as we tried to increase the connections and fire multiple queries simultaneously it was all started breaking after few mins.

Would you share more details about this problem?

Also, it would be great if you open a new issue for your problem.

[arpankhagram]

Hi @jihoonson The issue we were having was similar to what you have been discussing on #4134 - we earlier thought default value as 0 is no timeout and it waits for infinite time that seems not to be working as per our understanding so we decided to pass query timeout value in query context to set the actual timeout.

Also we were having very large filter in the query which was probably making the query very slow ( we were putting around 0.1 million filters for some of the queries.

@gianm Is this issue related to disk spill planned to be fixed in this release 0.10 ?

[gianm]

@arpankhagram no, I'm not aware of plans to fix the original disk spill issue.

[stale]

This issue has been marked as stale due to 280 days of inactivity. It will be closed in 2 weeks if no further activity occurs. If this issue is still relevant, please simply write any comment. Even if closed, you can still revive the issue at any time or discuss it on the dev@druid.apache.org list. Thank you for your contributions.

[stale]

This issue has been closed due to lack of activity. If you think that is incorrect, or the issue requires additional review, you can revive the issue at any time.