How can I improve query performance?

TROUBLESHOOTING Google Groups
#1

My team is using druid at bi report. We design a 10 dimensions and 100 metrics .I don’t know if Druid supports such that design, because I see that the number of metrics in many cases is less than or equal to the number of dimensions.
Currently, we have encountered very difficult query performance issues.

Our druid cluster: two 8-core CPUs and 16G RAM, one 8-core CPU and 32G RAM（Broker at）

Broker Configuration： druid.processing.numThreads=7， druid.processing.numMergeBuffers=7；jvm option： -Xmx20g and -XX:MaxDirectMemorySize=6g

Historical Configuration: druid.processing.numThreads=7；jvm option: -Xmx4g and -XX:MaxDirectMemorySize=2g

When I use jmeter for query testing(jmeter thread group set 10 thead and forever run), Broker’s cpu usage rate is around 400% and Historical’s cpu usage rate is around 400%，I don’t understand why the CPU utilization does not reach the bottleneck. What is the relationship between the CPU usage of the broker? What is the relationship between the CPU usage of the Historical?

Is Druid’s query throughput related to the Historical node? When I tested the throughput, I found that there were only 2tps, and there were only 2 positive Historical nodes.

I hope someone can help answer the doubts, thank you very much.

#2

Hi Yan,

If you have two 8-core CPUs then you should set druid.processing.numThreads=15. In most Druid workloads, the historical nodes do most of the work. In this case you should definitely see them maxing out their host machines’ CPUs.

Fwiw, Druid does support designs with smaller numbers of dimensions and larger numbers of metrics.

One thing to double-check when doing performance testing is if your segments are optimally sized. If you open up the coordinator console you can see information on each segment. They should generally be a few hundred MB to a GB in size for best performance.