Apache Druid

Do your segment granularities match between the ingestion and reindexing ... and are you explicitly specifying your intervals in the reindex spec for the granularitySpec?

```"granularitySpec": {
  "type": "uniform",
  "segmentGranularity": "DAY",
  "queryGranularity": "DAY",
  "rollup": true,
  "intervals": ["2023-03-16T00:00:00.000Z/2023-03-17T00:00:00.000Z"],
}, ```
There could be a locking issue if you leave out the intervals or put an empty set [] ... you should specify them both on the input side and the output side of the spec ... specifically this is the case where you are reindexing on a datasource that has actively running stream ingestion.

I think so yes,
• index_parallel:
```"granularitySpec": {
                "type": "uniform",
                "segmentGranularity": "MONTH",
                "queryGranularity": "DAY",
                "rollup": true,
                "intervals": [
                    "2023-02-15T00:00:00.000Z/2023-02-22T00:00:00.000Z"
                ]
            },```
• kinesis:
```"granularitySpec": {
        "type": "uniform",
        "segmentGranularity": "MONTH",
        "queryGranularity": "DAY",
        "rollup": true,
        "intervals": []
      },```

For the index_parallel :
```"context": {
        "forceTimeChunkLock": true,
        "useLineageBasedSegmentAllocation": true,
        "priority" : 80
    }```

Which data is getting "lost"?  The reindex data or the data that just streamed in for that time period?

Since you have appendToExisting=false, that's an overwrite of the time period, not an append ... are you running multiple backfills for the same time period, one set of data replaced by the next?

I think the data being lost is the one re-indexed

and yeah I do not want to append since it might result in duplicate data

What's your task duration and/or intermediate handover period on the Kinesis ingestion spec?  Is it something reasonable like an hour?

I would think the next thing to try is suspending the Kinesis ingestion while you run a reindex, to make sure the reindex is doing what you want by itself.   You do have rollup set to true, so you will be potentially eliminating records in the reindex ... so first make sure that is doing what you want.

The `intermediatePersistPeriod` is set to PT5M

the thing also is that I am indexing or backfilling past data, so even if I do intervals like `2023-02-15T00:00:00.000Z/2023-03-16T00:00:00.000Z` , why would I need to stop real time ingestion?

Not intermediatePersist ... that is just interim disk spills ... I was looking to see how often you are creating new segments ... so `taskDuration` and `intermediateHandoffPeriod`. (by default `intermediateHandoffPeriod` is set very high so that only taskDuration and maxRows/BytesSize are used as the threshold to build new segments ... so `taskDuration` may be the most relevant setting here.

Backfilling much older time chunks that real-time ingestion doesn't get near -- in that case there should be no contention at all between the two.  So maybe the issue is that you are doing the rollup, which can reduce record count?

ah all right, `intermediateHandoffPeriod` is `P2147483647D` and `taskDuration`  is set to `PT3600S`

I do not think the issue is the rollup since I confirm data is gone by querying. I have two cluster, one set up last year where I did not need to run any backfilling and another cluster set up two months or so ago and for that one I needed to backfill some data

Okay it looks like your real-time ingestion is set to the default values ... not an issue then.

I've never heard of any sort of reindexing or compaction losing data or segments.   I don't know if you accidentally ran two reindexing jobs at the same time, if that would be an issue.

The reindexing jobs -- are they reingesting data from outside of Druid or are they using existing datasources?   One thing you could try is to reindex into a "side" datasource and then compare the data there vs what's in the main datasource during that same time period.

Also, a side topic, have you been running auto-compaction to remove the basic segment fragmentation?   Since the real-time ingestion bits off a new segment each hour, in a given day you will have a minimum of 24 segments for that day ... if your volumes don't fill each of these segments to the limit then there should be some ability to consolidate segments here.

I have run the re-index jobs once at a time. I re-indexed data, checked it by querying the affected intervals and saw that data was there. Some days after or even week some user let me know that they do not see data for certain period, and casually that those periods were the ones I backfilled by ingesting data from S3.

Data goes to same datasource realtime ingestion is running on

Hmmm ... and your retention rules aren't knocking out any of this data?  And on the Druid console it shows segments are "Fully available" for this datasource?

retention should be for 1 year in this datasource

I'm kind of at a loss on this one ... if there is no immediate issue with segments or time chunk, it seems odd that they would just "disappear" after a week or so.   I don't know enough about the product internals yet to suspect anything else (I'm a SQL/Relational veteran but relatively new to Druid)

So my next thought would be to run a report periodically that shows counts by day in the datasource (and/or counts by other relevant dimensions) so you can see immediately when something changes unexpectedly ... and then check logs, etc to see if you can figure out what just happened.  And if you have enough disk space available, copy the data into a "backup" datasource on a nightly basis so if something does change you can try to figure out what the delta in the data is.

I should probably also ask questions like, what version are you running?   And please share your ingestion and reindex specs? etc.

Thanks.  John

Coming from my thread, is there any pattern to the dropped data?

I.e. I found that almost all of my data was dropped in the latter parts of the month by running a series of queries like this

```
SELECT  TIME_EXTRACT(__time, 'YEAR') AS yr, TIME_EXTRACT(__time, 'MONTH') AS mon, TIME_EXTRACT(__time, 'DAY') AS dt, COUNT(*)
FROM "datasource" where __time &gt;= '2022-12-29' and __time &lt; '2023-11-01'
GROUP BY TIME_EXTRACT(__time, 'DAY'), TIME_EXTRACT(__time, 'MONTH'), TIME_EXTRACT(__time, 'YEAR')   ORDER BY yr, mon, dt```

```
SELECT  TIME_EXTRACT(__time, 'DAY') AS dt, COUNT(*)
FROM "datasource" where __time &gt;= '2022-12-29' and __time &lt; '2023-11-01'
GROUP BY TIME_EXTRACT(__time, 'DAY')  ORDER BY dt```

That showed the pattern within the month: far more data in the first week or so

Screenshot 2023-03-29 at 15.48.28.png

Screenshot 2023-03-29 at 15.48.15.png

So for the first query, where I have data. `where __time &gt;= '2023-02-01' and __time &lt; '2023-03-29'` - the amount of data for the last couple of days in Feb (26th) missing, is minimal compared to beginning of March. And then, it seems data is being lost starting March 16th

Hmm. The fact that there is a bit of data on the 16th is interesting. Similarly with the 22nd. That's 7 days. I also wonder i the 200K or so records on the 16th are spread throughout the day, or clustered around a certain time period (e.g. the first hour of the day)

Even so, we also have a twin cluster, with exact same configuration, and it has way more data if I run same queries, not only for the missing days, but for existing ones it holds more data

That does make the problem being the compaction spec (like mine is) seem a little unlikely.

I have been tempted to re-index data setting `dropExisting` to true

basically because I think the issue got triggered when I removed one dimension from the realtime ingestion and I then triggered re-index too without that dimension we did not need anymore

I think at this point I would look at the segment list (from Druid console) around these timeframes, try to figure out if the segments for the "missing" times still exist but are overshadowed, looking for overlapping and/or tombstone segments that might be hiding some of the data.

I also don't use time_extract() ... I use time_floor() ... don't know if there is a difference, but worth trying time_floor() to get counts and see if there is a difference.

Finally, if this is continuing to happen, if you are not quite sure what data is going missing, then if you have enough space in your cluster I suggest copying out the current month that is still good, into a side datasource, and leave it there to use as a reference.

there could be a problem with your timestamp spec. so druid cannot determine the timestamp for any row.

if thats not the case, you may have multiple values for the dimension that you are partitioning on

or you have a filter in your spec that is rejecting all the input data

there is no filter and single dim column has single value.  most probably it has to be timestamp spec.

image.png

Try providing missing values as below for timestamp:

Can you post the spec that is generated (it is in the `Edit spec` stage of the wizard if that is what you are using)

Thanks <@U031A9D19NU> this helped. i gave the wrong time stamp spec, and it found no data from the input source, hence the issue.

What are the reasons MM can get blacklisted?
Is it failing jobs? if so, I think that failures dues to losing lock should not count
&gt;  Last completed task: 2023-03-23T09:55:47.461Z Blacklisted until: 2023-03-23T10:10:47.461Z

historical-settings.txt

broker_queries_issues.txt

broker-settings.txt

Do you have a sense of cpu utilization when this issue occurs. You may just be running out of resources.

Thank you for bringing that up. I saw no resource exhaustion. Historicals used no more than 1.5 CPU and broker even less, &lt; 0.5 CPU

As per RAM, it was stable on Historicals and there was a spike in brokers from 11 gb to 19 GB and a limit of 12GB per broker was set on kubernetes resource limit

<https://snapshots.raintank.io/dashboard/snapshot/NxLQqF2jBF1AxhTaBWC4ktFajUDg9jSi>

```Exception while processing queryId [553272ba-21ec-4655-bf43-704133b9bcbb] (org.apache.druid.query.ResourceLimitExceededException: Subquery generated results beyond maximum[100000])```
You can change this number in the config, but if this is being hit, the queires need to be rearchitected

Indeed we make intensive use of subqueries and JOINs to calculate more metrics out of the reported ones ..

I would start searching for this query and checking if it can be rewrite to use some form of aggregation where druid can use sketch or other approximate algorithms that uses way less ram. if they can't be changed, then this would need to be saved already calculated upfront for the dashboard

I understand that hitting that `rg.apache.druid.query.ResourceLimitExceededException: Subquery generated results beyond maximum[100000])` requires from reworking our queries but aside from this, what would explain the overall performance degradation? This limit is basically a guardrail to prevent from having performance issues.

I'm not 100% sure, but I think that broker (or router, I can't remember now) may retry the query a few times if a historicals fail, so that's why from a client (dashboard) perspective, you would see a timeout if all of the retries got the same resource error in end, and I think that, even if your LB (on k8s or amazon) has a timeout, the broker by default has not, and would keep running the query)

on my cluster I've set a default timeout of 60 secs for all queries, and only overwrite it on a query-by-query basis

`druid.server.http.defaultQueryTimeout=60000` I think it must be set both on and brokers

also, druid.broker.retryPolicy.numTries=1 so I don't think it's that is causing the cluster to be unresponsive :confused:

the broker may be a major bottleneck when subqueries are involved. Do you have a load testing script?

I was helping another customer a few months ago where union queries were causing time out issues though the historicals were not maxed out in terms of CPU

I did a load testing in the past to find out about concurrency capacity but the performance never got degraded in such a way. Once the test finish its 2min execution you could isue any querie withouth any issues ...  Let me fetch some queries

```SELECT "t1"."timestamp", "t2"."Source", "t2"."ParentId", "t2"." "
FROM 
(
SELECT TIME_FLOOR(__time, 'PT${period}') "timestamp", NodeId, ReceiveFromNodeId
FROM "sdk-${region}-realtime"
WHERE __time &gt;= '${__from:date:iso}' AND  __time &lt; '${__to:date:iso}'

  AND CustomerId = '${customerid}'
  AND ChannelId LIKE '${channelid}'
  AND NodeId = '${nodeid}'
  AND ReceiveFromNodeId != ''
  AND RepresentationId LIKE '${representationid}'
  AND MaskedStreamUri LIKE '${maskedstreamuri}'

) AS "t1"
INNER JOIN
(
SELECT TIME_FLOOR(__time, 'PT${period}') as "timestamp", NodeId as "ParentId", Source, AVG(BufferHealth) / 1000.0 " "
FROM "sdk-${region}-realtime"
WHERE __time &gt;= '${__from:date:iso}' AND  __time &lt; '${__to:date:iso}'

  AND CustomerId = '${customerid}'
  AND ChannelId LIKE '${channelid}'
  AND RepresentationId LIKE '${representationid}'
  AND MaskedStreamUri LIKE '${maskedstreamuri}'
  
GROUP BY 1, 2, 3
) AS "t2" 
ON "t1"."timestamp" = "t2"."timestamp" AND "t1".ReceiveFromNodeId = "t2"."ParentId"```

I think that, for now you can add more brokers to help with the joining, but you are paying the price of the join at each query, this will not scale. you can try enable caching on broker side (it's disabled by default) but that can only help if the dash send identical queries.
You should do peform this join in a previous stream (eg: using any engine u like, spark, or kafka stream (can do with query using ksql) so in  druid you can just consume from one topic, and even use roll-up choose different datasource depending on the time_floor you want) so your query will run in just a few ms

Thanks for your suggestion <@U038XPQFJHM> I'l take them into account. We were already using  the scheme/layout of realtime/historical datasources with different rollups  so we target one of them depending on the selected query time range/granularity. From documentation, I also had the feeling that our queries were pushing too far out of Druid so that kind of  confirmed our suspicions on that regards.

Lastly, didn't know that merging query partial results could be parallelized in more than on broker.

just one broker will be joining per query (on the classic engine), but if you have multiple queries, more brokers will help parallelize)

is there any reason to use like filters rather than in filters? If the fields are string fields in filters are way faster and that might help. A lot depends on how much comes to the broker from the left and right queries of the join

The queries are sent from  Grafana dashboards. Each of those conditions translate to a Grafana <https://grafana.com/docs/grafana/latest/dashboards/variables/|dashboard variable> so you can get a drop-down menu that let you filter by those.

However those filters doesn't have static values. They are obtained (via query) dynamically. WHat if you don't whant to filter by any of those attributes? Well. Gafana allows you to customise the value to send when you don't want to filter out by any of those. Sending "%" was the only workaround we could come up with

yeah, I usually don't use grafana sor I dont know if there's any way to do conditional templates like is on metabase,  but a hack I use is using 2 binds and doing something like this:

```AND ( CAST(? AS int) = 0 OR col IN (:cols:))```
and pass a 1 for ignore the IN condition (and use a fake value in the list, calcite will remove this expression entirely as it always will be false


but I wrote a custom sql placeholder  where I mix in both ? and :list-of-some-values: that will be replaced before sending to druid

That's a nice trick, Due to our filtering values distribution we had to chose `-1` to disable the IN filter and we couldn't inject two values but it worked. Thanks <@U038XPQFJHM>

```AND (CAST('${channelid}' AS int) = -1 OR ChannelId IN ('${channelid}'))```


Any ideas?  Do I have a corrupt segment or something?  I'm able to query the data for this time frame just fine...

Seems to be something with the segment efoe-search-started-events_2023-03-06T00:00:00.000Z_2023-03-07T00:00:00.000Z_2023-03-23T18:05:46.745Z_2.  Assuming your replication factor is &gt;1, you might try to find that segment on historicals, shut one down and delete it, start up, repeat on other, then try compaction again.  That will reload it from deep storage.  Maybe something wrong with that segment or a file missing, but I'm not sure.

You might be able to narrow down the search with `SELECT *  FROM sys.server_segments WHERE segment_id LIKE 'search-started-events_2023-03-06'`, eg.

When I set the maxRowsPerSegment to 1000000 (instead of 5000000) then it fails at `efoe-search-started-events_2023-03-06T00:00:00.000Z_2023-03-07T00:00:00.000Z_2023-03-31T00:26:42.716Z_10`

I notice logs lines like:
```Unzipped 115925121 bytes from [/data/tripstack/druid/deep-storage/segments/efoe-search-started-events/2023-03-06T00:00:00.000Z_2023-03-07T00:00:00.000Z/2023-03-06T00:24:42.084Z/1/86753203-74fe-450c-a75f-142ac8d80c4e/index.zip] to [/data/tripstack/druid/apache-druid-0.23.0/var/druid/task/compact_efoe-search-started-events_jbjgpnkb_2023-03-31T01:11:10.617Z/work/indexing-tmp/efoe-search-started-events/2023-03-06T00:00:00.000Z_2023-03-07T00:00:00.000Z/2023-03-06T00:24:42.084Z/1]```
And I also noticed that it always fails before unzipping segment number 4. So I'm going to try dropping that segment and :crossed_fingers:

Not if it's corrupt :wink:

I'm willing to part with a few million rows if it means I get compaction working again

So, unfortunately, this did not work. It fails at the same point, same offset ID

... So it's not failing working on that segment

```2023-03-31T01:31:50,739 ERROR [[compact_efoe-search-started-events_inkebabb_2023-03-31T01:23:40.174Z]-appenderator-persist] org.apache.druid.segment.realtime.appenderator.AppenderatorImpl - Incremental persist failed: {class=org.apache.druid.segment.realtime.appenderator.AppenderatorImpl, segment=efoe-search-started-events_2023-03-06T00:00:00.000Z_2023-03-07T00:00:00.000Z_2023-03-31T01:23:40.183Z_2, dataSource=efoe-search-started-events, count=4}
2023-03-31T01:31:50,743 ERROR [task-runner-0-priority-0] org.apache.druid.indexing.common.task.IndexTask - Encountered exception in BUILD_SEGMENTS.
net.jpountz.lz4.LZ4Exception: Error decoding offset 2154422 of input buffer```

I also noticed a little too late that the LocalDataSegmentPuller seems to unzip it just fine

Auto-compaction no longer works for this datasource because it keeps retrying the same day

Side note: I wonder why it unzips from deep storage twice

First by LocalDataSegmentPuller into `var/druid/task/{{datasource}}_{{task_id}}/work/` and then again by LocalDataSegmentPuller into `var/druid/task/{{datasource}}_{{task_id}}/work/indexing-tmp/`

Oh!  Well, good to hear.  I was thinking just clear and reload, but if dropping it is OK and it worked, then gtk.

It's loading from deep storage so I'm unsure what clear and reload would do

With strange issues, I usually re-ingest into a new datasource name. If an identical query works on the new datasource name, then I’ll just blow away the segment cache files.

Any pointers where i can read or understand it a bit more.  We are trying to improve the perf of our queries by  re-ordering the columns in such a way so that similar data co-locate.

For string dimensions, druid stores bitmaps for unique values that improve the filtering performance dramatically. The string dimension is dictionary encoded and searching a value within that dictionary does use binary search.

long dimensions on the other hand do not have bitmaps so there is definitely some slowness there

One trick that I have seen people use, is to store long columns as a string dimension instead. and thus reap the fruits of bitmap indexing when filtering on the said dimension.

there is work in progress that will obviate the need for this shenanigan in future druid release.

how does the filtering works (equal + range) on long column then ?  Binary search ? or it is just wild guess .

We are doing range based queries on the long column , i am guessing if we change it to string, it will have impact on perf ?

it will evaluate each row if that matches the filter you have passed.

yes. if you change it to string, you should see perf impact

sorry to trouble you again.  i have one more question.

so my segment has this  dimension spec -&gt; `__time, dim1, secondaryTime`.

*case 1:* Segment has only one value for dim1, hence secondaryTime would be in increasing order and data would be sorted completely in segment.

*case 2:* Segment has around 10-20 values for dim1, Even though secondaryTime would be increasing but the overall data would not be sorted, as data is sorted by dim1 first.

Amount of data/rows are same in both segment.

query -&gt; select count(*) from datasource  WHERE `secondaryTime &gt; 10000`

I see that queries returned faster result for  case-1 segment.

Does this mean if data is sorted, filter works faster  on long columns ?

one guy took around 450ms, and other one took 750ms.

hmm. don't know for sure. when the secondaryTime is sorted, there is more accurate branch prediction. Though I don't know if that's driving this difference or there is something else.

hmm, got it okay.

meanwhile chatgpt is on different level with it's knowledge about druid. it says druid using roaring bitmap for numerical columns :slightly_smiling_face:

heh, it lies, unless it is talking about numeric columns created with the nested column indexer :sweat_smile:

what is the size difference between the two cases?

the segment sizes? typically when data is sorted it also tends to compress better resulting in smaller segments

with long columns created by traditional means today, filtering happens doing a full scan

numeric columns created by the nested column indexer have value indexes, so a range filter does a binary search of the sorted value dictionary to find the start and ends of the range and then unions all of the bitmaps for those values together

string columns have similar behavior, but using a lexicographic ordering, so i think numeric values stored in strings doing numeric ranges fall back to a predicate index that instead matches every value in the dictionary

<https://github.com/apache/druid/pull/12830> has some details about the numeric range stuff

using indexes isn’t always faster when the range results in a ton of bitmap operations, the threshold i observed was around 200k values in the range before it got slower than using the matcher, so will probably update in the future to put some smarts around that

oh yea, and this <https://apachedruidworkspace.slack.com/archives/C0303FDCZEZ/p1678134115585529?thread_ts=1678090147.271949&amp;cid=C0303FDCZEZ>

that write-up is a bit string centric, but much of the same applies to the indexes used by json columns

thank you so much. This is what i was looking for to understand how filtering works for all sorts.

NumericRangeIndex -&gt; is this something going to implemented  for normal numerical columns as well ?

i want to add range-only indexes to numeric columns as an alternative to having an index per value to better serve higher cardinality numeric columns as well, but I haven’t got to it yet

the idea being we have an index for all the values in some range and then use a value matcher to filter down the values that fall within the buckets

ive been making some changes to the nested column indexer to make it into a ‘auto’ column indexer

since it needs to discover type/schema information within nested columns, it can do the same thing when the data isn’t nested to power schema auto discovery based ingestion, which means it will be writing out a long column when it processes only longs or string for only strings, etc

which means it will be able to be used for normal ingestion as well, just substitute ‘long’ for ‘json’ (or maybe ‘auto’ or ‘built-in’ or something) once we are done making the improvements

undecided if i am going to modify the existing numeric dimension indexers, or just add type coercion to the json indexer, so you can enforce that all the values are a LONG or whatever other type

sorry, but i did not get  it completely, not much familiar with the nested column indexer yet. I will go though the links you shared to understand it a bit more.

sorry i guess we got off track a bit, my guess would have been that because the data was better ordered, the smaller column size was able to be read faster, but if there is no difference then it is unlikely to be that

if you have the unzipped segment files, there is a `meta.smoosh` inside that has the byte sizes (in the form of offsets) for the column stuff

another question -&gt; Another experiment data size is same.

case 1: data is distributed in 240 segments.
case 2: data is distributed in 20 segments

`query -&gt; select count(*) from datasource  WHERE `secondaryTime &gt; 10000``

when i fire the same query on long column,  case 2 is faster. (this is also 2 times faster)

 does it mean lower number of segment will return result faster, even though the data set size is same.

the number of segments will impact the performance as well, if you run autocompaction if they are in the past and the data is not changing anymore
this will help reduce the number of segments down until they are large enough to the overhead of the number of segments be mininal

hmm, indeed i find in my experiments that when i ran queries on same data re-indexed in less number of segments, queries are running faster.

<@U031QS4LQV6> hi, in order to evaluate a HAVING clause, final aggregation must first be achieved and final aggregation is completed on the Broker. If you share you query perhaps we can recommend changes that would improve the overall performance and still achieve your objective.

sure <@U030K4UM3H7> <@U0411DE2SS0> we are not seeing any errors but cluster will be busy and causing slowness to small queries also

Oh, well sample query could help.  I guess not error, I was asking about this: "When we try to keep `forcelimitPushDown` to true it is failing saying this setting won’t applicable if we have having clause ."  What's the full message?

I agree with Sergio.  I just wondered what the query and HAVING clause were.

<@U0411DE2SS0> this is the sample query that failed

how about rewrite with:
```SELECT * FROM
( 
SELECT sum(metrics) as sum_metrics FROM "druid"."datasource"
WHERE ...
LIMIT 10
)
WHERE sum_metrics &gt; 0```
This should run the subquery with the pushdown and then resolve the "having" with the external WHERE clause.

still same error
Error: Unknown exception
Cannot force limit push down when a having spec is present.
org.apache.druid.java.util.common.IAE

That is weird, I tested the query on Apache Druid 25.0 and it worked. what version are you on? Another point for this particular query is that the LIMIT won't do anything because it produces a single result per segment which then gets merged in the broker to a single result, so there is nothing to limit.

Are you running from Druid Console? What is your query context?

OK, looking at it, it makes sense to me now.  We generally can't evaluate the HAVING at the historical, because we need the totals first.  For this particular query, it's logically possible, but the planner's not detailed enough to see that.

It's not going to work like that with forcing limit pushdown to historicals.  Druid can't properly evaluate a HAVING with partial results.  For this particular query, I might have a workaround...

actually, this is just an example to show the error, right, not a real query?  For a real query I probably won't have any workaround.  Do you have a real one?

if you need precise control over exactly what is and isn't pushed down, you could also use the native query API instead of SQL

since then you could provide different contexts to different subqueries, if you want

issue with the SQL approach is the context is applied to all queries and subqueries individually, and I think in your case it doesn't make sense for all of them

SQL also rewrites things; as you can see in an example earlier, it fused a `WHERE` on a subquery into a single query using `HAVING`. It does this kind of rewriting without considering context params like `forceLimitPushDown`

for testing we can use native API but our application use avatica sql to query druid

Is there a reason not to set forceLimitPushdown to false in the query context for these queries?  I think you could do that, and leave it true for other queries.

Best to avoid big time intervals, or at least filter with WHERE when possible to narrow down the data that needs to be read and aggregated.

yeah, after deleting the lookup table the problem is gone.

Add the lookups back and it starts to crashing again. The error message is  `Warning  Unhealthy  4m8s (x32 over 68m)  kubelet  Liveness probe failed: Get "<http://10.34.11.141:8081/status/health>": context deadline exceeded (Client.Timeout exceeded while awaiting headers)`

Ok, I increase the probes’ timeout to 30s and the problem seems to get resolved …

So adding big lookup will slowdown the coordinator …

that doesn't seem so big if it's only 6M.  Maybe some of the configuration, poll period, etc?  Idk, what's the lookup configuration?

Hi <@U0411DE2SS0>, I don’t know why adding the lookup would cause this problem. As a matter of fact, the coordinator crashed 218 times in these 2 days even I set the probe timeout to 30 seconds. In stead of error out from timeout, not the error becomes connection reset by peer:
```Liveness probe failed: Get "<http://10.34.9.152:8081/status/health>": read tcp 10.34.7.138:55684-&gt;10.34.9.152:8081: read: connection reset by peer```

Do you have any other lookups that work?  Can you show the lookup's definition?  (So we can see all the settings.)

<@U0411DE2SS0> the small lookups that only has a few rows would work.  The current one looks like this:

static map lookup, I'm surprised that's causing this issue.  I'll do a little research...

Thank you <@U0411DE2SS0> I can confirm that:
1. With a small lookup or no lookup, the coordinator is doing fine
2. With this lookup having 130k keys, then coordinator pod crashed &amp; restarted 400+ times over the past weekend.

Can you also attach the coordinator runtime.properties?  Are you also doing ingestions, and if so, how many running tasks do you have (you can look on the ingestion page of the console)?

runtime.properties

Hi <@U0411DE2SS0>, here is the runtime.properties of my coordinator. And cluster is not doing data ingestion at all. The cluster was 85% full. Before the crash, I also checked the disk space and CPU of the coordinator pod and there is nothing suspicious. Thanks!

Can you try without these settings and see if it makes a difference at all?

druid.coordinator.startDelay=PT10S
druid.coordinator.period=PT5S

druid.indexer.queue.startDelay=PT5S

Hi <@U0411DE2SS0>, sorry for the late response. I just realize that there is another config file for coordinator in /tmp/conf/druid/cluster/master/coordinator-overlord/ , and this file already has the above settings.

OK, I was saying take those settings out, and restart.  Those don't look like good settings to me.

You can refer to : <https://druid.apache.org/docs/latest/operations/metrics.html>

I see that for my queries, the query/segment/time takes the most time on the historical. What generally causes query segment time spikes and how can it be brought down?

High segment scan time could be due to scanning the bulk of the data in the segment.  Are these queries "heavy" group by queries that do not have highly selective filters on them?

Is there a high wait time as well?

Yeah most of the queries don't have filters on them, but the total datasource size isn't large (~150GB)
Is there a way to improve the speed of these queries? Would increasing the heap help? We have heap set to 10g currently

Wait time is negligible. Almost all of the query time is spent on query segment time

there’s no easy answer to ‘why are queries slow’ - it can be because the data model is incorrect, the query itself is written in an inefficient way, there’s insufficient hardware, etc

can you post the query you are trying to run and details about your cluster?

You can also book a time in the POC clinic <https://calendly.com/druidcommunity/clinic-poc>

1. The segment metadata contains the contiguous range of values for the dimension on which single_dim secondary partitioning is applied. This is utilized by brokers to prune segments when the query contains a value out of this range for the chosen dimension.
2. ``query/segments/count`` may help but it needs to be enabled

Alternatively, `query/segment/time`  has id, segment as its dimensions. It may help to check the count of segments for a given query id for this metric. (<https://druid.apache.org/docs/latest/operations/metrics.html#historical>)

buffer size  cannot be increased as the is 2 GB. You can try reducing max rows in memory and also the number of subtasks. What is the segment granularity? Can you paste the ingestion spec here?

Thanks for reply! I tried to reduce maxRowsInMemory 150000-&gt;100000 but didn’t work. below is tuningConfig
```    "tuningConfig": {
      "type": "hadoop",
      "workingPath": null,
      "partitionsSpec": {
        "type": "hashed",
        "numShards": null,
        "partitionDimensions": [],
        "partitionFunction": "murmur3_32_abs",
        "maxRowsPerSegment": 3000000
      },
      "shardSpecs": {},
      "indexSpec": {
        "bitmap": {
          "type": "roaring",
          "compressRunOnSerialization": true
        },
        "dimensionCompression": "lz4",
        "metricCompression": "lz4",
        "longEncoding": "longs",
        "segmentLoader": null
      },
      "indexSpecForIntermediatePersists": {
        "bitmap": {
          "type": "roaring",
          "compressRunOnSerialization": true
        },
        "dimensionCompression": "lz4",
        "metricCompression": "lz4",
        "longEncoding": "longs",
        "segmentLoader": null
      },
      "appendableIndexSpec": {
        "type": "onheap"
      },
      "maxRowsInMemory": 100000,
      "maxBytesInMemory": 0,
      "leaveIntermediate": false,
      "cleanupOnFailure": true,
      "overwriteFiles": false,
      "ignoreInvalidRows": false,
      "jobProperties": {
        "mapreduce.job.queuename": "tiara",
        "mapreduce.job.classloader": "true",
        "mapreduce.map.memory.mb": "2048",
        "mapreduce.map.java.opts": "-Xmx1638m -Djava.net.preferIPv4Stack=true -Duser.timezone=UTC -Dfile.encoding=UTF-8 -XX:+PrintGCDetails -XX:+PrintGCTimeStamps",
        "mapreduce.reduce.cpu.vcores": "1",
        "mapreduce.reduce.memory.mb": "20480",
        "mapreduce.reduce.java.opts": "-Xmx12288m -XX:MaxDirectMemorySize=6g -Djava.net.preferIPv4Stack=true -Duser.timezone=UTC -Dfile.encoding=UTF-8 -XX:+PrintGCDetails -XX:+PrintGCTimeStamps"
      },
      "combineText": false,
      "useCombiner": false,
      "numBackgroundPersistThreads": 0,
      "forceExtendableShardSpecs": false,
      "useExplicitVersion": false,
      "allowedHadoopPrefix": [],
      "logParseExceptions": false,
      "maxParseExceptions": 0,
      "useYarnRMJobStatusFallback": true,
      "awaitSegmentAvailabilityTimeoutMillis": 0
    }
  },
  "hadoopDependencyCoordinates": null,
  "classpathPrefix": null,
  "context": {
    "forceTimeChunkLock": true,
    "useLineageBasedSegmentAllocation": true
  }
}```

try reducing max rows per segment. Are indexing all the string fields?

you can also increase the number of mappers and reducers.

you need to add that in the job properties

<@U03BJAMQ4H5> hey I tried to reduce `maxRowsPerSegment` and succeeded! Seems like each segments were getting bigger and bigger. you saved my life thanks!

:+1:remember to turn  on auto compaction so that you don't end up with too many small segments.

<@U0344FW86DD> <@U03BJAMQ4H5> we are running into a similar issue. Your clarification here is helpful. One question though- our tasks are eventually succeeding. Why would it happen if the columns size is the issue?

You mean you see some attempt failures due to `Asked to add buffers[XYZ] larger than configured max[2,147,483,647]` which then eventually succeed after a retry?

Hi <@U0505JZT318>, where did you see that it did have that attribute? I looked at the docs for pydruid and did not find it.

in fact I searched for scripts that can load the data and I didn't find anything that's why I asked chatGPT it gave me this script !

I had ChatGPT make up stuff before :grin: 

You can submit an Http request to the router directly from python.

I think we need to open channel only for ChatGPT smart-looking lies :smile:
I asked GPT something about Druid and it invented some configurations that never existed anywhere.

```13:50:49.695 [main-EventThread] ERROR org.apache.curator.ConnectionState - Authentication failed```
How are you connecting to your broker?

Umm, not sure what you mean to ask, but i don't think there's any connectivity issue, as there's another datasource  in the same druid setup which is ingesting data from the same kafka topic and the ingestion/querying is working properly for that datasource.

are you ingesting from the same topic into two different sources?

please paste the ingestion spec of this source that is failing and the other source from the same topic that is succeeding

```ISE: Could not allocate segment for row with timestamp[2023-03-27T13:50:43.548Z]
	```
this usually means a metadata issue or a lock conflict.

take a look at <https://github.com/apache/druid/issues/9386>

Yeah, I read this one, but in my case, there isn't any existing segment which can be compacted, as this is a new datasource

you can try changing the spec with a new data source different from the one you are trying to ingest. Perhaps this data source name was used earlier and there is some metadata?

tried that as well, didn't work, the new datasource went into RUNNING state for a couple of seconds and then went to UNHEALTHY_TASKS state again, and the tasks were failing with the same error that I have posted

Will paste the spec too after a while, right now I don't have access to the machine which has the ingestion spec

only this specific data source is failing?

yes, the other datasource which is getting data from the same topic is working fine

hmm. I've never seen errors like `Could not allocate segment for row` on a fresh datasource, only on one with pre-existing data (when the pre-existing segment partitioning or `segmentGranularity` is somehow incompatible with newly added realtime segments)

I wonder if you have a `segmentGranularity` that doesn't support appends? It would need to be one of the standard ones like `hour` or `day` for realtime kafka ingest.

You may have issues with a `segmentGranularity` with a custom time zone or a period that isn't one of the standard ones

found this in the logs:

```2023-03-27T13:50:05,730 ERROR [KafkaSupervisor-reporting_shard0-Worker-1] org.apache.druid.indexing.seekablestream.supervisor.SeekableStreamSupervisor - Problem while getting checkpoints for task [index_kafka_reporting_shard0_31f5073b24d52a9_ejkmgick], killing the task
java.util.concurrent.ExecutionException: org.apache.druid.indexing.common.IndexTaskClient$TaskNotRunnableException: Aborting request because task [index_kafka_reporting_shard0_31f5073b24d52a9_ejkmgick] is not runnable
	at java.util.concurrent.FutureTask.report(FutureTask.java:122) ~[?:1.8.0_342]
	at java.util.concurrent.FutureTask.get(FutureTask.java:192) ~[?:1.8.0_342]
	at org.apache.druid.indexing.seekablestream.supervisor.SeekableStreamSupervisor.verifyAndMergeCheckpoints(SeekableStreamSupervisor.java:1581) ~[druid-indexing-service-0.19.0.jar:0.19.0]
	at org.apache.druid.indexing.seekablestream.supervisor.SeekableStreamSupervisor.lambda$verifyAndMergeCheckpoints$16(SeekableStreamSupervisor.java:1538) ~[druid-indexing-service-0.19.0.jar:0.19.0]
	at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) [?:1.8.0_342]
	at java.util.concurrent.FutureTask.run(FutureTask.java:266) [?:1.8.0_342]
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [?:1.8.0_342]
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [?:1.8.0_342]
	at java.lang.Thread.run(Thread.java:750) [?:1.8.0_342]
Caused by: org.apache.druid.indexing.common.IndexTaskClient$TaskNotRunnableException: Aborting request because task [index_kafka_reporting_shard0_31f5073b24d52a9_ejkmgick] is not runnable
	at org.apache.druid.indexing.common.IndexTaskClient.submitRequest(IndexTaskClient.java:322) ~[druid-indexing-service-0.19.0.jar:0.19.0]
	at org.apache.druid.indexing.common.IndexTaskClient.submitRequestWithEmptyContent(IndexTaskClient.java:220) ~[druid-indexing-service-0.19.0.jar:0.19.0]
	at org.apache.druid.indexing.seekablestream.SeekableStreamIndexTaskClient.getCheckpoints(SeekableStreamIndexTaskClient.java:253) ~[druid-indexing-service-0.19.0.jar:0.19.0]
	at org.apache.druid.indexing.seekablestream.SeekableStreamIndexTaskClient.lambda$getCheckpointsAsync$0(SeekableStreamIndexTaskClient.java:276) ~[druid-indexing-service-0.19.0.jar:0.19.0]
	... 4 more
2023-03-27T13:50:05,731 WARN [KafkaSupervisor-reporting_shard0-Worker-1] org.apache.druid.indexing.seekablestream.supervisor.SeekableStreamSupervisor - Clearing task group [1] information as no valid tasks left the group```


Hmm I'm not sure why you'd have problems with `SIX_HOUR`…

0.19 is also fairly old; I wonder if you could try the latest too. If that still doesn't work then that'd be interesting, especially if you have steps to reproduce the problem

<@U0344FW86DD> thanks for pointing out this error has something to do with segmentGranularity
I tried changing the segment granularity from SIX_HOUR to DAY and the error has magically disappeared

A realtime task ingests data and simultaneously serves it for queries. Once the ingestion duration has elapsed, it waits for the data to be loaded on historicals before stopping so that its data is available for queries.
It appears that your segments were created but they were not loaded on the historicals by the coordinator.
1.  It'd be helpful if you could check metrics such `segment/assigned/count` during this period to see if segments are being assigned by the coordinator. Coordinator logs would also be helpful
2. You could also check if the historicals are loading the segments if the coordinator has successfully assigned them

It's also possible that both assignment and loading are happening but slowly. You may want to check if
1.`coordinator/global/time` is high
2. replicationThrottleLimit and maxSegmentsInNodeLoadingQueue are too low

one additional note: you can read more about how the handoff works here in the docs: <https://druid.apache.org/docs/latest/design/architecture.html#indexing-and-handoff>

It looks like your tasks are getting stuck on step 4:

&gt; If the indexing task is a realtime task, then to ensure data is continuously available for queries, it waits for a Historical process to load the segment.

As <@U0319HD8HEC> mentioned, the most common reason for this is if your Coordinator is being slow to assign segments for some reason, or if your Historicals are unable to load them for some reason