We have a query that takes about 2 seconds to respond back 95% of the time. We also have a load balancer that does a round robin on 3 brokers.
We started to run some jmeter performance tests and we are seeing that the response time for the query is ticking up towards the 1 minute range as we increase the number of parallel requests being made. How do I configure/tune druid to get the response time back down close to 2 seconds when we start bombarding the system with parallel queries?
Here is the query that we are using:
SELECT advertiser_id AS “advertiser_id”, LOOKUP(CONCAT(campaign_id, ‘’), ‘dsp_media_and_bids_insertion_order_id’) AS “insertion_order_id”, country_id AS “country_id”, dma_id AS “dma_id”, (SUM(impression)) AS “impression”, (SUM(click)) AS “click”, (SUM(action)) AS “action”, ((SUM(click) * 100.0) / CAST(SUM(impression) AS DOUBLE)) AS “ctr”, (SUM(action) / CAST(SUM(impression) AS DOUBLE)) AS “ar”, (SUM(cost) * 1000.0 / CAST(SUM(impression) AS DOUBLE)) AS “advertiser_ecpm”, (SUM(cost) / CAST(SUM(click) AS DOUBLE)) AS “advertiser_ecpc”, (SUM(cost) / CAST(SUM(action) AS DOUBLE)) AS “advertiser_ecpa”, (SUM(cost)) AS “cost”, (SUM(complete_events) / CAST(SUM(impression) AS DOUBLE)) AS “complete_events_rate”, (APPROX_COUNT_DISTINCT_DS_HLL(user_id_sketch)) AS “user_id_sketch”, (APPROX_COUNT_DISTINCT_DS_HLL(household_person_id_sketch)) AS “household_person_id_sketch”, (APPROX_COUNT_DISTINCT_DS_HLL(household_id_sketch)) AS “household_id_sketch” FROM dsp_media_and_bids WHERE market_id IN () AND __time >= ‘2021-03-01 00:00:00’ AND __time <= ‘2021-03-31 23:59:59’ GROUP BY dma_id, country_id, advertiser_id, LOOKUP(CONCAT(campaign_id, ‘’), ‘dsp_media_and_bids_insertion_order_id’)
We pivot on the market_id for our jmeter tests. Here are our jmeter test results
10 threads with loop count of 100 - 20 seconds in 95th percentile
20 threads with loop count 100 - 23 seconds in 95th percentile
50 threads with loop count 100 - 39 seconds in 95th percentile
100 threads with loop count 100 - 54 seconds in 95th percentile
Our machines are spec’ed at 80 cores and 375 GBs of memory a piece.
Each of our 3 brokers have the following runtime configs settings:
Is there a config I should be looking at that I am missing to help increase the parallel processing of queries to make them faster?