Hi All, We have a Druid cluster set up as follows: Node 1 – Master Server MySQL, Zookeeper, Coordinator, Overlord, Broker, Router Node 2 Data Server, Historical We’re currently handling 7 billion records in our Druid database. However, some queries are running indefinitely and eventually timing out. One example query that’s causing issues is:

SELECT COUNT(*)

FROM (

SELECT DISTINCT id

FROM table

WHERE responsetype IN ('Sent', 'SENT', 'failed')

)

We would really appreciate your help in tuning our Druid cluster for better performance.

Hi Ramdas, You are essentially doing a

select count(distinct id) from table where ...

However you are doing the exact distinct count, which does require a nested group-by. In your case it is likely high cardinality, which is contributing to the high query latency. Can I assume you also have maxOnDiskStorage set, which is allowing spillover, so the query will complete, it will just take longer to run? Don't know if you are aware, but if you don't need an exact count, Druid can use sketches for approximate count distinct, using my version of your query above ... just set the query context

"useApproximateCountDistinct": true

If you need an exact distinct count then it will be harder ... one thing you could try if your cardinality isn't orders of magnitude larger than the system will allow, is to shard your queries into non-overlapping subsets and add the results together, e.g.

select
 (select count(distinct id) from table 
   where responsetype IN ('Sent', 'SENT', 'failed') 
     and id < 'N')
 +
 (select count(distinct id) from table 
   where responsetype IN ('Sent', 'SENT', 'failed') 
     and id >= 'N')

Also, a few further comments: 1. Your example query would probably run more efficiently as

SELECT COUNT(DISTINCT id)
FROM table
WHERE responsetype IN ('Sent', 'SENT', 'failed')

2. Do you mean that you have exactly two nodes, and 7B records? If so, have you considered adding nodes? 3. Do you run autocompaction and automatic metadata cleanup? I don't know how many segments you have, but these can help performance.