Unable to get result for thetaSketch count for mutil-value dimension having 10 milions uniques rows

Hi ,

Need support over below druid query result. Please suggest suitable hardware (in cluster or stand alone) or druid setting to get to the result .

I am using druid-0.18.1 version .

Problem statement: — Unable to extract thetaSketch count for multi-value dimension having 10 millions of unique row for a single day segment. Getting following error for below query.

For a day having total 24 segments , storing segment as hour granularity . Total segment size is 22 gb around .

Query:–

{
“queryType”: “groupBy”,
“dataSource”: {
“type”: “table”,
“name”: “test”
},
“intervals”: {
“type”: “intervals”,
“intervals”: [
“2020-07-08T00:00:00.000Z/2020-07-08T23:59:59.001Z”
]
},
“granularity”: {
“type”: “all”
},
“dimensions”: [
{
“type”: “default”,
“dimension”: “dimension_3”,
“outputName”: “d0”,
“outputType”: “STRING”
}
],
“aggregations”: [
{
“type”: “thetaSketch”,
“name”: “a0”,
“fieldName”: “unique_users”,
“size”: 16384
}
],
“context”: {
“timeout”: 2000000 , “maxOnDiskStorage” : 30147483641
}
}

//tried both memory base & disk spill

Error:–

Unknown exception / org.jboss.netty.channel.ChannelException: Channel disconnected / java.util.concurrent.ExecutionException / on host :8083

My hardware config —

1)- 1 amazon centos7 machine instance type m5.4xlarge for historical node

2)- 1 amazon centos7 machine instance type m5.4xlarge for query node (broker + router)

Historical JVM Config:–
-Xms40g
-Xmx40g
-XX:MaxDirectMemorySize=50g

Historical Runtime Config:–

HTTP server threads

druid.server.http.numThreads=200

Processing threads and buffers

druid.processing.buffer.sizeBytes=1000000000
druid.processing.numMergeBuffers=4
druid.processing.numThreads=15
druid.processing.tmpDir=/druid/druid-DB/var/druid/processing

Segment storage

druid.segmentCache.locations=[{“path”:"/druidService/druid-DB/var/druid/segment-cache",“maxSize”:2000000000000}]
druid.server.maxSize=2000000000000

Query cache

druid.historical.cache.useCache=true
druid.historical.cache.populateCache=true
druid.cache.type=caffeine
druid.cache.sizeInBytes=30000000000

druid.query.groupBy.maxMergingDictionarySize=20000000000
druid.query.groupBy.numParallelCombineThreads=8
druid.query.groupBy.intermediateCombineDegree=8
druid.server.http.maxSubqueryRows=2147483647

Broker JVM Config:–
-Xms40g
-Xmx40g
-XX:MaxDirectMemorySize=50g

Broker Runtime Config:–
druid.service=druid/broker
druid.plaintextPort=8082

HTTP server settings

druid.server.http.numThreads=90

HTTP client settings

druid.broker.http.numConnections=20
druid.broker.http.maxQueuedBytes=5000000
druid.server.http.maxQueryTimeout=2000000
druid.broker.http.readTimeout=PT60M

Processing threads and buffers

druid.processing.buffer.sizeBytes=100000000
druid.processing.numMergeBuffers=8
druid.processing.numThreads=2
druid.processing.tmpDir=/druidService/druid-DB/var/druid/processing

Query cache disabled – push down caching and merging instead

druid.broker.cache.useCache=false
druid.broker.cache.populateCache=false

Hi,

Please suggest proper hardware in cluster mode or druid config setting .

Any other errors in any other logs? How is the resource usage during the query? Have you followed these best practices for tuning? It seems like you have allocated a lot more heap than recommended… any particular reason why?

https://druid.apache.org/docs/latest/operations/basic-cluster-tuning.html

Thanks Rachel Pedreschi,

I need help or support , how many historical node in cluster & broker node required to get the query result of thetaSKetch count for a single day for a single multi-value dimension having value greater then 10M ?? . Segment size 22 gb . I tried with single with historical node 16 core & 60 gb .

I am using single historical node with 16 core machine with 60 gb . As per thump rule for historical heap (0.5GB * number of CPU cores) = 8 gb . With -XX:MaxDirectMemorySize=50g . (druid.processing.numThreads + druid.processing.numMergeBuffers + 1) * druid.processing.buffer.sizeBytes .

Historical JVM Config:–
-Xms8g
-Xmx8g
-XX:MaxDirectMemorySize=50g

Historical Runtime Config:–

druid.processing.buffer.sizeBytes=2000000000
druid.processing.numMergeBuffers=4
druid.processing.numThreads=15
druid.processing.tmpDir=/druid/druid-DB/var/druid/processing

Once i did not got result i tried with maximum heap memory in single historical node, wth config

Historical JVM Config:–
-Xms40g
-Xmx40g
-XX:MaxDirectMemorySize=50g

Historical Runtime Config:–

druid.processing.buffer.sizeBytes=1000000000
druid.processing.numMergeBuffers=4
druid.processing.numThreads=15
druid.processing.tmpDir=/druid/druid-DB/var/druid/processing

Even i did not get the result , tried with both memory & disk based spill . Getting error in historical log.
Unknown exception / org.jboss.netty.channel.ChannelException: Channel disconnected / java.util.concurrent.ExecutionException / on host :8083