The realtime spec specifies minute granularity and the query I’m running is a simple TopN query that specifies day granularity for the full range of data.
What is the size of each segment you are creating? You can find this information in the coordinator console (http://coordinator_ip:port). Druid’s parallelization model uses 1 core to scan 1 segment, and we typically recommend segments that are ~5M rows and around 250-700mb in size. Segments that are too small will introduce overhead as many passes are required to scan the data.
This results in almost all of the segments being loaded and aggregated, which is to be expected. This is roughly 100 million events, so 20 seconds is still a good bit better than a SQL-based GROUP BY+ORDER BY query.
This seems very slow. Druid is very finicky around configuration as misconfiguration can lead to dramatically reduced performance.
My concern is that at the scaling rate I’m seeing, we won’t be able to handle more than maybe a month of data before query times become too long to use in real-time.
I’m very curious as to what kinds of nodes affect the loading/aggregation of segments during query execution so we can plan out our hardware needs.
Historical nodes scan segments and do a first level merging of the results of the segments they’ve scanned. Broker nodes do a second level merging of results from historical/realtime nodes.
As I understand it, any of the historical nodes that have segments within the interval are given the query by the broker to run. I can see some evidence of this by looking at the system load during a fresh run of a query; the historical nodes are all maxing out the CPU.
The broker then caches the resulting segments and, since segments are immutable, it can reuse those segments as long as the only thing that changes about the query is the interval. This is a really neat feature, as most of our queries are going to be relatively static with adjustable timescales and granularities.
Based on this, I’m thinking it makes sense to have a bunch of general-purpose VMs as historical nodes and one or two beefy VMs with a ton of memory as the brokers. On the other hand, it might be that we’ve structured our schema poorly. Is our data simply too granular? How are other people handling these volumes of data?
There’s some general guidelines of performance tuning here: http://druid.io/docs/latest/operations/performance-faq.html and we have some example performance configs here: http://druid.io/docs/latest/configuration/production-cluster.html.
I’d be curious to know how you’ve initially set things up to get an idea of where inefficiencies may lie. Also, if you haven’t had the chance, you may be interested in: http://druid.io/docs/latest/misc/evaluate.html