The query read more than 500000 nodes in memory.

[paulg97390631]

Hoping someone can guide me in the direction to resolve this one.

Our AEM Author instance recently starting logging 'memory' warnings/errors, similar to this:

02.03.2018 18:00:04.327 *WARN* [pool-9-thread-4] org.apache.jackrabbit.oak.query.FilterIterators The query read more than 500000 nodes in memory.

java.lang.UnsupportedOperationException: The query read more than 500000 nodes in memory. To avoid running out of memory, processing was stopped.

02.03.2018 18:00:04.327 *ERROR* [pool-9-thread-4] com.day.cq.wcm.webservicesupport.impl.statistics.ServicesStatisticsJob The query read more than 500000 nodes in memory. To avoid running out of memory, processing was stopped.

Any information you can provide would be greatly appreciated.

[navinkaushal]

Some of your code must be reading the jcr tree which has a lot node. Just try to figureout which query is that and apply some custom index on that query fields and try to limit the results. Then it should work.

[Mahamood · Accepted solution]

Hope you are aware about AEM 6.2 onward there is configuration available in AEM and threshold is setup to restrict any query execution which may cause impact to instance.

For AEM 6.0 - 6.2 versions, you can tune the threshold for node traversal via JVM parameters in the AEM start script to prevent large queries from overloading the environment. The recommended values are :

• -Doak.queryLimitInMemory=500000
  
• -Doak.queryLimitReads=100000

In AEM 6.3, the above 2 parameters are preconfigured by default, and can be modified via the OSGi QueryEngineSettings.

The warning message clearly indicates the operations which triggers the query is returning more results and you need to enable logs to fine tune which query get triggers and if any new indexes need to be added.  Here is the link Adobe provide for trouble shooting this type of issues.

Troubleshooting Slow Queries

Note , your instance may be working in past , but due to recent volume of data change or code change the query could bring more results which could be the cause.

Hope this will help.