Although we have indexes (property,lucene ) defined for most of the queries, few of the indexes are going in traversal
For Ex :
On Restart of AEM
*WARN* [Apache Sling Repository Startup Thread] org.apache.jackrabbit.oak.plugins.index.search.spi.query.FulltextIndex$FulltextPathCursor Index-Traversed 140000 nodes with filter Filter(query=SELECT * FROM [nt:base] WHERE ISDESCENDANTNODE([/content]) AND [cq:master] IS NOT NULL, path=/content//*, property=[cq:master=[is not null]])
Therefore it's taking a long time for restart.
|Numdocs count - 5725504||/oak:index/ntBaseLucene|
Property Field Info - /oak:index/ntBaseLucene cq:master 165543
But logs we see it's going for Index-Traversed although we have index defined.
What is the reason for Index Traversal ? This is happening for lower numdocs indexes also. How do we fix this ?
Solved! Go to Solution.
It's not clear to me if this is your query or its an OOTB query ? It's searching "nt:base". The Oak index definition suggest such query to have an index with the following properties:
- compatVersion = 2
- async = "async"
- jcr:primaryType = oak:QueryIndexDefinition
- evaluatePathRestrictions = true
- type = "lucene"
- name = "cq:master"
- propertyIndex = true
- notNullCheckEnabled = true
If the existing index does not have the suggested properties you may add it and rebuild the index to verify if it makes any improvements.
Oak chooses the indexer with the lowest estimated cost. (https://docs.adobe.com/content/help/en/experience-manager-65/deploying/deploying/queries-and-indexin...).
You can go to Tools > Operations > Dashboard > Diagnosis > Query Performance > Explain Query (/libs/granite/operations/content/diagnosistools/queryPerformance.html), execute you query and check costs for ntBaseLucene vs traversal.
If the cost for ntBaseLucene is higher, it might indicate that indexes are broken/not actual due to various reasons (such as improper instance shutting down during indexing) and thus triggering re-index might be considered for ntBaseLucene (set the reindex property to true within /oak:index/ntBaseLucene). It is best to re-index during quiet periods (for example, not during a large content ingest), and ideally during maintenance windows when AEM's load is known and controlled (https://docs.adobe.com/content/help/en/experience-manager-65/deploying/deploying/troubleshooting-oak...).
However, I guess querying is not only reason for slow instance starting up. While activation the MSM core bundle ("Day Communique 5 WCM Multi Site Management Core") invokes the query you mentioned - "SELECT * FROM [nt:base] WHERE ISDESCENDANTNODE([/content]) AND [cq:master] IS NOT NULL" and checks if found nodes exist (please see the stack trace in the thread com.day.cq.wcm.msm.impl.LiveCopyFinderProvider.buildBloomFilter - https://experienceleaguecommunities.adobe.com/t5/adobe-experience-cloud-ideas/msm-core-fails-to-star...). The query returns live copy configuration nodes (e.g. /content/we-retail/us/en/community/members/jcr:content/cq:LiveSyncConfig) and according to your message, the number of found nodes is large (>Numdocs count - 5725504) so processing them might introduce a delay. I'd also recommend reviewing the content structure to ensure that no unnecessary nodes are present (such as unnecessary pages + their live copies, etc.)
Hi @Andrei_Dantsou , Thanks for your inputs . We have given our inputs to Adobe Product Engineering as we have seen this issue happening only in 6.5 and not in 6.3 , with the same number of livecopy nodes present there. As this is a generic search query triggered during restart where result count is more than 10k, it goes for index-traversal. In future, this may be removed from startup msm core bundle activation to on-demand.