Articles in this section

Tuning Druid for Large Result Sets

Gaurav
  • Updated
Follow

 

 

Here are some handpicked options for hardening Druid system when dealing with demanding scenario like group by on large datasets which may return a very large query response. This article also provides recommendation guideline for setting these parameters.

 

 

 

Querying a large dataset

 

Druid provides many tunable parameters at broker and query level. Following are list of advanced configurations currently supported by Druid. 

 

 

 

Property Description Default Recommendation
druid.server.http.defaultQueryTimeout Query timeout in millis, beyond which unfinished queries will be cancelled. 0 timeout means no timeout. To set the default timeout, see Broker configuration 300000 milli  
 druid.broker.http.readTimeout  The timeout for data reads from Historical servers and real-time tasks.  PT15M
  • leave it alone at first, but you may need to raise it if TTFB (Time to First Byte) is high
druid.router.http.readTimeout  timeout of inflight query responses. Router will terminate running queries if they run longer than this duration. PT8M 
  • Make sure this duration is enough for longest running query.
  • Handle partial query response at client side since http 200 response is already sent.
druid.broker.http.maxQueuedBytes

 

Maximum number of bytes queued per query before exerting backpressure on the channel to the data server. Similar to maxScatterGatherBytes, except unlike that configuration, this one will trigger backpressure rather than query failure. Zero means disabled.

 0 (disabled) 
  • Always set this parameter on broker. Try 10M (10000000) if you have <100 historicals; consider higher #'s if you have more historicals.
  • Make sure it fits within your heap
  • this setting is per query and so usage could be up to druid.server.http.numThreads * druid.broker.http.maxQueuedBytes)
druid.processing.buffer.sizeBytes

 

This specifies a buffer size for the storage of intermediate results. The computation engine in both the Historical and Realtime processes will use a scratch buffer of this size to do all of their intermediate computations off-heap. Larger values allow for more aggregations in a single pass over the data while smaller values can require more passes depending on the query that is being executed.

 auto (max 1GB) 
  • leave it alone at first, but you may consider raising it if you have spare memory 
druid.query.groupBy.maxMergingDictionarySize 

 

Maximum amount of heap space (approximately) to use for the string dictionary during merging. When the dictionary exceeds this size, a spill to disk will be triggered.

  100000000  
druid.query.groupBy.maxOnDiskStorage

 

Amount of space on disk used for aggregation, per query, in bytes. By default, this is 0, which means aggregation will not use disk.

 0 (disabled) 
  • always set this, on historicals.
  • Try 10GB at first if you have enough disk space, raise if needed.
  • This setting is per query hence actual usage could be up to druid.server.http.numThreads * druid.query.groupBy.maxOnDiskStorage 

 

 

 

Note: Please check your Druid version for support of these advanced options.

 

 

 

Downloading large dataset

 

 

 

Apply HTTP Compression 

 

Apache Druid (incubating) supports http request decompression and response compression, to use this,Activate by setting the Accept-Encoding: gzip header on your client requests.

 

This does not require server configuration.

 

Recommendation: consider this if network is more of a bottleneck than CPU (this trades off CPU for network). Not recommended for smaller result-sets since this option increases CPU usage on broker and on client.

 

 

 

Detect Truncated Responses

 

Truncated response can be detected by validating returned data. Each format type provides a way to detect if data is truncated. Let's see what each response type works in this scenario. Some of the response formats are better than others but it all depends on what implementation works with  external systems. 

 

Always set resultFormat if using SQL queries.

 

 

 

Query Type Parameter Value Content Type Format Type Detection Recommendation
SQL resultFormat array application/json 

 

JSON array of JSON arrays

 Validate Response JSON
  • Preferred for large result sets
SQL resultFormat arrayLines  text/plain

 

Like "array", but the JSON arrays are separated by newlines instead of being wrapped in a JSON array. This can make it easier to parse the entire response set as a stream, if you do not have ready access to a streaming JSON parser.

 Presence of one blank line in the end
  • Preferred for large result sets
  • make sure to implement a truncation checker
Native Druid resultAsArray  true application/json 

groupBy v2 queries now use an array-based representation of result rows, rather than the map-based representation used by prior versions of Druid. This provides faster generation and processing of result sets. Out of the box, this change is backwards-compatible. 

  Validate Response JSON
  • Always set this parameter.
  • Only supported in 3.1+
  • Documentation link resultsAsArray

 

 

Other Formats

Druid supports additional formats which are excluded from this KB article due to their relevance. These formats are:

  • resultFormat=[object, objectLines, csv] for SQL queries are excluded.
  • resultFormat=[list, compactedList] for Druid Native queries only apply for scan queries and not for GROUP BY hence excluded.

 

 

Additional Recommendations

 

  • Try avoiding "ORDER BY" if at all humanly possible.
  • Colocate clients to reduce network latency.
  • Avoid server going down.
  • Implement retry in case of error 500 or truncated response.

 

 

 

Further Reading

 

 

Related articles

Comments

0 comments

Please sign in to leave a comment.