Tuning Druid for Large Result Sets

Gaurav

Updated August 30, 2019 23:08

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.