Hello everyone, I am a newbie in Apache Druid and big data. I am worried about the lack of performance. I have 21 columns; most are string, and one is geospatial data. All columns are indexed. My system specs are: 8 core, 32GB RAM and apache setup is single server, micro-quickstart. I have 5.6 billion rows in the data source, and the segment granularity is the hour. The total number of segments is 26200. Here is my query and it takes 4-5 min.

{
  "queryType": "scan",
  "dataSource": "mobility",
  "intervals": [
    "2024-01-01T00:00:00.000Z/2025-01-01T00:00:00.000Z"
  ],
  "columns": [
    "advertiserid"
  ],
  "filter": {
    "type": "selector",
    "dimension": "advertiserid",
    "value": "0104c0fe-b9b0-6e03-1b7f-f186d7f16b3e"
  }
}

Is it normal to take this much time?

this is basically equivalent to the SQL

SELECT advertiserid
FROM mobility
WHERE
  advertiserid = '0104c0fe-b9b0-6e03-1b7f-f186d7f16b3e'
  AND __time >= TIMESTAMP '2024-01-01 00:00:00'
  AND __time < TIMESTAMP '2025-01-01 00:00:00'

it would return every row containing that advertiserid

This was about measuring only the query time. So the actual query will be like that

SELECT "__time", "advertiserid", "coordinates", name 
FROM mobility
WHERE
  "advertiserid" IN ('0104c0fe-b9b0-6e03-1b7f-f186d7f16b3e','02a2dc7e-62db-480b-9387-61ba28988007','04b027f3-3e66-6d40-ae0c-c0f0f69903f2')
  AND "__time" >= TIMESTAMP '2024-01-01 00:00:00'
  AND "__time" < TIMESTAMP '2025-01-01 00:00:00'

We have some list of ids and need to retrieve geolocation and name based on advertiserid.

how much data does the query return? the query should run pretty efficiently assuming there are indexes on

advertiserid

(which would be there by default unless you switched them off). perhaps it's returning a very large amount of data?

return size is 340. Here is the screenshot of previous query

that takes 4–5 minutes? that's surprising, I would have expected much shorter

Yes, that's surprising to me, too. Just an assumption if the whole segments have 5M rows (around 130B total rows), so the query time takes this much or takes longer? In my dataset, the number of rows varies from 1 to 5M for each segment, but the average is 200K rows/segment, as you stated. When I try to compact them to 5M rows/segment, I got

Terminating due to java.lang.OutOfMemoryError: Java heap space

. I couldn't fix this issue, although I modified the middle manager's jvm config such that

-Xmx32g

,

-Xms8g

. However, this issue is another subject. Firstly, I need to check whether the druid performance is enough or not for me.

hmm, i'm curious why the task ran out of memory. it may work better if you use

bin/start-druid

to start Druid rather than the micro quickstart. that start script sizes memory pools automatically based on the amount of memory available on your machine

I will try this, and I will share the results. Thanks for your help by the way.

how did you do the ingest? i do think that reorganizing the segments would help a huge amount. two things i would recommend: • target segment size in the 3–5 million row range • partition by

hour

, cluster by

advertiserid

. this will limit the number of segments that need to be checked for each query that uses

advertiserid IN (...)

and will speed things up as well