This message was deleted.

Can you share the long running query SQL/JSON?

@Sergio Ferragut, it is not easy to narrow it down to one query. According to the OCP memory metrics, the RAM usage is growing over time. On the one hand, we have a couple of scheduled queries (third-party software) that lead to fairly huge steps in the RAM metrics (e.g., from 9.7GB to 15.71 GB). On the other hand, we have a couple druid tasks which read constantly from kafka topics. The screenshot shows a common OCP RAM metric. The RAM usage decreases only on broker restarts with exit code 137 (out of memory): [Screenshot see next post] The scheduled SQLs are executed by a third-party software, which we unfortunately cannot(!) change. As far as we could analyze the SQLs, the (stupid?!?) software queries first all composite keys within a given time period. Followed by multiple queries which select blocks of rows with the prior returned ‘composite primary keys’. These SQL look as followed:

SELECT DISTINCT "__time",,"modelId","modelName","modelVersion","processInstanceCustomId","processInstanceIteration",... FROM "druid"."WorkflowProcessStepInstanceChange" WHERE
 (
              (( "__time" >= ? AND "__time" <= ? )) AND
                             (
                                           (("stepStatus" = 'Started') OR ("stepStatus" = 'Expired') OR ("stepStatus" = 'Completed') OR ("stepStatus" = 'Failed')OR ("stepStatus" = 'Waiting')OR ("stepStatus" = 'Stopped')))
                             AND(
                                           (("stepType" <> 'STEP') AND ("stepType" <> 'Business Rule Task')))
 )
 AND
 (
              ("processInstanceId"=? AND "stepId"=? AND "stepIteration"=? AND "stepStatus"=? AND "processInstanceIteration"=? ) OR
              ... UP to 64 times ...
              ("processInstanceId"=? AND "stepId"=? AND "stepIteration"=? AND "stepStatus"=? AND "processInstanceIteration"=? ) OR ("processInstanceId"=? AND "stepId"=? AND "stepIteration"=? AND "stepStatus"=? AND "processInstanceIteration"=? ))

Is that through JDBC. Let me look at the recent PRs. There was a recent bug fix on a memory leak for queries through JDBC.

Zou are right. We are using the avatica sql driver. I found https://github.com/apache/druid/pull/13259, but that should have been fixed with v24.0.1.

Yes. I found the same one, but you're already on 24.0.1 so this is probably different. Please create a bug report on https://github.com/apache/druid/issues

Hey @Sergio Ferragut, I made several heap dumps. The heap is constantly growing with every SQL statement. When viewing the biggest dominator classes the two biggest are org.apache.calcite.plan.volcano.VolcanoPlaner with a ‘Retained Heap’ of 36.17% and org.apache.druid.sql.SqlLifecycleManager with a retained heap of 28.08%. (Overall heap size in that dump: 4.1GB) In the same snapshot SqlLifecycleManager contains a hashMap with 967 entries each containing an org.apache.druid.sql.DirectStatement$ResultSet object. The VolcanoPlaner contains several colcano.RuleQueues. It seams that all the objects will not be removed from heap. Within the log of the broker, I do find only the following cache properties listed:

Druid.broker.cache.populateCache: false

Druid.broker.cache.useCache: false

According to https://druid.apache.org/docs/latest/configuration/index.html#broker-caching broker caching should be disabled by default. How can I reduce memory usage? Why can I find all executed sql statements in the heap although caching is disabled?

@Abhishek Agarwal 👆 seems like there is a memory leak that is different than https://github.com/apache/druid/pull/13259. @Cl A Us would you report a new issue and include the heap dump data?

There is a problem here. It looks like the fix was never actually backported to 24.0.1.

@Abhishek Agarwal, great. We did an upgrade of our dev cluster and it solved our heap problem. Thanks for your help.

It’s pretty strange since 24.0.1 wasn’t released yet when fix was merged to master. Maybe commit wasn’t picket to the release branch or something? (not sure how release branching works for Druid)

yes. we missed backporting the fix before release. There is a script that the release manager is supposed to run before the release. This script will find missing backports. We didn't run the script and didn't catch this commit.

thanks. understood

No. It doesn't contain that fix either. I think its safe to upgrade to 25.0.0. I am not aware of any issues with that version.