You are viewing a plain text version of this content. The canonical link for it is here.
Posted to oak-dev@jackrabbit.apache.org by Roy Teeuwen <ro...@teeuwen.be> on 2016/10/18 14:44:59 UTC
Oak query performance problem
Hello all,
I got a problem in oak concerning query performance for the following simple queries
SELECT * FROM [rep:ACL] WHERE ISDESCENDANTNODE([/content])
SELECT * FROM [rep:ACL] WHERE ISDESCENDANTNODE([/var])
I get the following exception:
java.lang.UnsupportedOperationException: The query read or traversed more than 150000 nodes. To avoid affecting other tasks, processing was stopped.
at org.apache.jackrabbit.oak.query.FilterIterators.checkReadLimit(FilterIterators.java:66)
at org.apache.jackrabbit.oak.spi.query.Cursors$TraversingCursor.fetchNext(Cursors.java:324)
at org.apache.jackrabbit.oak.spi.query.Cursors$TraversingCursor.next(Cursors.java:303)
at org.apache.jackrabbit.oak.query.ast.SelectorImpl.next(SelectorImpl.java:409)
at org.apache.jackrabbit.oak.query.QueryImpl$RowIterator.fetchNext(QueryImpl.java:773)
at org.apache.jackrabbit.oak.query.QueryImpl$RowIterator.hasNext(QueryImpl.java:798)
at org.apache.jackrabbit.oak.jcr.query.QueryResultImpl$3.fetch(QueryResultImpl.java:181)
at org.apache.jackrabbit.oak.jcr.query.QueryResultImpl$3.next(QueryResultImpl.java:207)
at org.apache.jackrabbit.oak.jcr.query.QueryResultImpl$3.next(QueryResultImpl.java:170)
at org.apache.jackrabbit.oak.jcr.delegate.SessionDelegate$SynchronizedIterator.next(SessionDelegate.java:694)
at org.apache.jackrabbit.oak.jcr.query.PrefetchIterator.next(PrefetchIterator.java:97)
at org.apache.jackrabbit.commons.iterator.RangeIteratorAdapter.next(RangeIteratorAdapter.java:152)
at org.apache.jackrabbit.commons.iterator.RangeIteratorDecorator.next(RangeIteratorDecorator.java:92)
at org.apache.jackrabbit.commons.iterator.NodeIteratorAdapter.nextNode(NodeIteratorAdapter.java:80)
at biz.netcentric.cq.tools.actool.helper.QueryHelper.getNodes(QueryHelper.java:128)
at biz.netcentric.cq.tools.actool.helper.QueryHelper.getRepPolicyNodes(QueryHelper.java:90)
at biz.netcentric.cq.tools.actool.dumpservice.impl.DumpserviceImpl.getACLDumpBeans(DumpserviceImpl.java:399)
Of course there is an oak:index on jcr:primaryType, so I really don’t see the reason why this could be such a hard query to search for nodes under a path that are of type rep:ACL?
(If you want more background, this query is used in the netcentric AC Tool to make a dump of all the existing rep policy ACL nodes)
Greetings,
Roy
Re: Oak query performance problem
Posted by Roy Teeuwen <ro...@teeuwen.be>.
Hey Thomas
Yeah I found that one :) I decided to make my own one to make sure not to run in to trouble when upgrading to a newer version. Thanks for all the info!
Greets,
Roy
> On 19 Oct 2016, at 08:27, Thomas Mueller <mu...@adobe.com> wrote:
>
> Hi,
>
> There is the "nodetype" index (/oak:index/nodetype) which is normally used
> for such queries. I would change that index.
>
> Regards,
> Thomas
>
>
> On 18/10/16 17:08, "Roy Teeuwen" <ro...@teeuwen.be> wrote:
>
>> Hey Thomas,
>>
>> Ok perfect, laying an oak:QueryIndexDefinition on propertyNames
>> jcr:primaryType and declaringNodeTypes rep:ACL solved the issue.
>> I presumed there already was an index for all the existing
>> jcr:primaryTypes :), not that you have to specifically have them in the
>> declaringNodeTypes
>>
>> Thanks!
>> Roy
>>> On 18 Oct 2016, at 16:53, Thomas Mueller <mu...@adobe.com> wrote:
>>>
>>> Hi,
>>>
>>>> I really don¹t see the reason why this could be such a hard query
>>>
>>>
>>> Who said it's a hard query? :-)
>>>
>>> Is the problem performance, or is the problem that you get an exception?
>>>
>>>
>>> If the problem is performance, then you need an index on the node type
>>> rep:ACL.
>>>
>>> If the problem is the exception: In your case the query engine is
>>> configured to fail the queries (the reason is written in the exception
>>> message). You can change the limit using the JMX bean
>>> "QueryEngineSettings". The default is Long.MAX_VALUE (virtually no
>>> limit)
>>> by the way.
>>>
>>> Regards,
>>> Thomas
>>>
>>>
>>>
>>> On 18/10/16 16:44, "Roy Teeuwen" <ro...@teeuwen.be> wrote:
>>>
>>>> Hello all,
>>>>
>>>> I got a problem in oak concerning query performance for the following
>>>> simple queries
>>>>
>>>> SELECT * FROM [rep:ACL] WHERE ISDESCENDANTNODE([/content])
>>>> SELECT * FROM [rep:ACL] WHERE ISDESCENDANTNODE([/var])
>>>>
>>>> I get the following exception:
>>>>
>>>> java.lang.UnsupportedOperationException: The query read or traversed
>>>> more
>>>> than 150000 nodes. To avoid affecting other tasks, processing was
>>>> stopped.
>>>> at
>>>>
>>>> org.apache.jackrabbit.oak.query.FilterIterators.checkReadLimit(FilterIte
>>>> ra
>>>> tors.java:66)
>>>> at
>>>>
>>>> org.apache.jackrabbit.oak.spi.query.Cursors$TraversingCursor.fetchNext(C
>>>> ur
>>>> sors.java:324)
>>>> at
>>>>
>>>> org.apache.jackrabbit.oak.spi.query.Cursors$TraversingCursor.next(Cursor
>>>> s.
>>>> java:303)
>>>> at
>>>>
>>>> org.apache.jackrabbit.oak.query.ast.SelectorImpl.next(SelectorImpl.java:
>>>> 40
>>>> 9)
>>>> at
>>>>
>>>> org.apache.jackrabbit.oak.query.QueryImpl$RowIterator.fetchNext(QueryImp
>>>> l.
>>>> java:773)
>>>> at
>>>>
>>>> org.apache.jackrabbit.oak.query.QueryImpl$RowIterator.hasNext(QueryImpl.
>>>> ja
>>>> va:798)
>>>> at
>>>>
>>>> org.apache.jackrabbit.oak.jcr.query.QueryResultImpl$3.fetch(QueryResultI
>>>> mp
>>>> l.java:181)
>>>> at
>>>>
>>>> org.apache.jackrabbit.oak.jcr.query.QueryResultImpl$3.next(QueryResultIm
>>>> pl
>>>> .java:207)
>>>> at
>>>>
>>>> org.apache.jackrabbit.oak.jcr.query.QueryResultImpl$3.next(QueryResultIm
>>>> pl
>>>> .java:170)
>>>> at
>>>>
>>>> org.apache.jackrabbit.oak.jcr.delegate.SessionDelegate$SynchronizedItera
>>>> to
>>>> r.next(SessionDelegate.java:694)
>>>> at
>>>>
>>>> org.apache.jackrabbit.oak.jcr.query.PrefetchIterator.next(PrefetchIterat
>>>> or
>>>> .java:97)
>>>> at
>>>>
>>>> org.apache.jackrabbit.commons.iterator.RangeIteratorAdapter.next(RangeIt
>>>> er
>>>> atorAdapter.java:152)
>>>> at
>>>>
>>>> org.apache.jackrabbit.commons.iterator.RangeIteratorDecorator.next(Range
>>>> It
>>>> eratorDecorator.java:92)
>>>> at
>>>>
>>>> org.apache.jackrabbit.commons.iterator.NodeIteratorAdapter.nextNode(Node
>>>> It
>>>> eratorAdapter.java:80)
>>>> at
>>>>
>>>> biz.netcentric.cq.tools.actool.helper.QueryHelper.getNodes(QueryHelper.j
>>>> av
>>>> a:128)
>>>> at
>>>>
>>>> biz.netcentric.cq.tools.actool.helper.QueryHelper.getRepPolicyNodes(Quer
>>>> yH
>>>> elper.java:90)
>>>> at
>>>>
>>>> biz.netcentric.cq.tools.actool.dumpservice.impl.DumpserviceImpl.getACLDu
>>>> mp
>>>> Beans(DumpserviceImpl.java:399)
>>>>
>>>> Of course there is an oak:index on jcr:primaryType, so I really don¹t
>>>> see
>>>> the reason why this could be such a hard query to search for nodes
>>>> under
>>>> a path that are of type rep:ACL?
>>>> (If you want more background, this query is used in the netcentric AC
>>>> Tool to make a dump of all the existing rep policy ACL nodes)
>>>>
>>>> Greetings,
>>>> Roy
>>>>
>>>>
>>>
>>
>
Re: Oak query performance problem
Posted by Thomas Mueller <mu...@adobe.com>.
Hi,
There is the "nodetype" index (/oak:index/nodetype) which is normally used
for such queries. I would change that index.
Regards,
Thomas
On 18/10/16 17:08, "Roy Teeuwen" <ro...@teeuwen.be> wrote:
>Hey Thomas,
>
>Ok perfect, laying an oak:QueryIndexDefinition on propertyNames
>jcr:primaryType and declaringNodeTypes rep:ACL solved the issue.
>I presumed there already was an index for all the existing
>jcr:primaryTypes :), not that you have to specifically have them in the
>declaringNodeTypes
>
>Thanks!
>Roy
>> On 18 Oct 2016, at 16:53, Thomas Mueller <mu...@adobe.com> wrote:
>>
>> Hi,
>>
>>> I really don¹t see the reason why this could be such a hard query
>>
>>
>> Who said it's a hard query? :-)
>>
>> Is the problem performance, or is the problem that you get an exception?
>>
>>
>> If the problem is performance, then you need an index on the node type
>> rep:ACL.
>>
>> If the problem is the exception: In your case the query engine is
>> configured to fail the queries (the reason is written in the exception
>> message). You can change the limit using the JMX bean
>> "QueryEngineSettings". The default is Long.MAX_VALUE (virtually no
>>limit)
>> by the way.
>>
>> Regards,
>> Thomas
>>
>>
>>
>> On 18/10/16 16:44, "Roy Teeuwen" <ro...@teeuwen.be> wrote:
>>
>>> Hello all,
>>>
>>> I got a problem in oak concerning query performance for the following
>>> simple queries
>>>
>>> SELECT * FROM [rep:ACL] WHERE ISDESCENDANTNODE([/content])
>>> SELECT * FROM [rep:ACL] WHERE ISDESCENDANTNODE([/var])
>>>
>>> I get the following exception:
>>>
>>> java.lang.UnsupportedOperationException: The query read or traversed
>>>more
>>> than 150000 nodes. To avoid affecting other tasks, processing was
>>>stopped.
>>> at
>>>
>>>org.apache.jackrabbit.oak.query.FilterIterators.checkReadLimit(FilterIte
>>>ra
>>> tors.java:66)
>>> at
>>>
>>>org.apache.jackrabbit.oak.spi.query.Cursors$TraversingCursor.fetchNext(C
>>>ur
>>> sors.java:324)
>>> at
>>>
>>>org.apache.jackrabbit.oak.spi.query.Cursors$TraversingCursor.next(Cursor
>>>s.
>>> java:303)
>>> at
>>>
>>>org.apache.jackrabbit.oak.query.ast.SelectorImpl.next(SelectorImpl.java:
>>>40
>>> 9)
>>> at
>>>
>>>org.apache.jackrabbit.oak.query.QueryImpl$RowIterator.fetchNext(QueryImp
>>>l.
>>> java:773)
>>> at
>>>
>>>org.apache.jackrabbit.oak.query.QueryImpl$RowIterator.hasNext(QueryImpl.
>>>ja
>>> va:798)
>>> at
>>>
>>>org.apache.jackrabbit.oak.jcr.query.QueryResultImpl$3.fetch(QueryResultI
>>>mp
>>> l.java:181)
>>> at
>>>
>>>org.apache.jackrabbit.oak.jcr.query.QueryResultImpl$3.next(QueryResultIm
>>>pl
>>> .java:207)
>>> at
>>>
>>>org.apache.jackrabbit.oak.jcr.query.QueryResultImpl$3.next(QueryResultIm
>>>pl
>>> .java:170)
>>> at
>>>
>>>org.apache.jackrabbit.oak.jcr.delegate.SessionDelegate$SynchronizedItera
>>>to
>>> r.next(SessionDelegate.java:694)
>>> at
>>>
>>>org.apache.jackrabbit.oak.jcr.query.PrefetchIterator.next(PrefetchIterat
>>>or
>>> .java:97)
>>> at
>>>
>>>org.apache.jackrabbit.commons.iterator.RangeIteratorAdapter.next(RangeIt
>>>er
>>> atorAdapter.java:152)
>>> at
>>>
>>>org.apache.jackrabbit.commons.iterator.RangeIteratorDecorator.next(Range
>>>It
>>> eratorDecorator.java:92)
>>> at
>>>
>>>org.apache.jackrabbit.commons.iterator.NodeIteratorAdapter.nextNode(Node
>>>It
>>> eratorAdapter.java:80)
>>> at
>>>
>>>biz.netcentric.cq.tools.actool.helper.QueryHelper.getNodes(QueryHelper.j
>>>av
>>> a:128)
>>> at
>>>
>>>biz.netcentric.cq.tools.actool.helper.QueryHelper.getRepPolicyNodes(Quer
>>>yH
>>> elper.java:90)
>>> at
>>>
>>>biz.netcentric.cq.tools.actool.dumpservice.impl.DumpserviceImpl.getACLDu
>>>mp
>>> Beans(DumpserviceImpl.java:399)
>>>
>>> Of course there is an oak:index on jcr:primaryType, so I really don¹t
>>>see
>>> the reason why this could be such a hard query to search for nodes
>>>under
>>> a path that are of type rep:ACL?
>>> (If you want more background, this query is used in the netcentric AC
>>> Tool to make a dump of all the existing rep policy ACL nodes)
>>>
>>> Greetings,
>>> Roy
>>>
>>>
>>
>
Re: Oak query performance problem
Posted by Roy Teeuwen <ro...@teeuwen.be>.
Hey Thomas,
Ok perfect, laying an oak:QueryIndexDefinition on propertyNames jcr:primaryType and declaringNodeTypes rep:ACL solved the issue.
I presumed there already was an index for all the existing jcr:primaryTypes :), not that you have to specifically have them in the declaringNodeTypes
Thanks!
Roy
> On 18 Oct 2016, at 16:53, Thomas Mueller <mu...@adobe.com> wrote:
>
> Hi,
>
>> I really don¹t see the reason why this could be such a hard query
>
>
> Who said it's a hard query? :-)
>
> Is the problem performance, or is the problem that you get an exception?
>
>
> If the problem is performance, then you need an index on the node type
> rep:ACL.
>
> If the problem is the exception: In your case the query engine is
> configured to fail the queries (the reason is written in the exception
> message). You can change the limit using the JMX bean
> "QueryEngineSettings". The default is Long.MAX_VALUE (virtually no limit)
> by the way.
>
> Regards,
> Thomas
>
>
>
> On 18/10/16 16:44, "Roy Teeuwen" <ro...@teeuwen.be> wrote:
>
>> Hello all,
>>
>> I got a problem in oak concerning query performance for the following
>> simple queries
>>
>> SELECT * FROM [rep:ACL] WHERE ISDESCENDANTNODE([/content])
>> SELECT * FROM [rep:ACL] WHERE ISDESCENDANTNODE([/var])
>>
>> I get the following exception:
>>
>> java.lang.UnsupportedOperationException: The query read or traversed more
>> than 150000 nodes. To avoid affecting other tasks, processing was stopped.
>> at
>> org.apache.jackrabbit.oak.query.FilterIterators.checkReadLimit(FilterItera
>> tors.java:66)
>> at
>> org.apache.jackrabbit.oak.spi.query.Cursors$TraversingCursor.fetchNext(Cur
>> sors.java:324)
>> at
>> org.apache.jackrabbit.oak.spi.query.Cursors$TraversingCursor.next(Cursors.
>> java:303)
>> at
>> org.apache.jackrabbit.oak.query.ast.SelectorImpl.next(SelectorImpl.java:40
>> 9)
>> at
>> org.apache.jackrabbit.oak.query.QueryImpl$RowIterator.fetchNext(QueryImpl.
>> java:773)
>> at
>> org.apache.jackrabbit.oak.query.QueryImpl$RowIterator.hasNext(QueryImpl.ja
>> va:798)
>> at
>> org.apache.jackrabbit.oak.jcr.query.QueryResultImpl$3.fetch(QueryResultImp
>> l.java:181)
>> at
>> org.apache.jackrabbit.oak.jcr.query.QueryResultImpl$3.next(QueryResultImpl
>> .java:207)
>> at
>> org.apache.jackrabbit.oak.jcr.query.QueryResultImpl$3.next(QueryResultImpl
>> .java:170)
>> at
>> org.apache.jackrabbit.oak.jcr.delegate.SessionDelegate$SynchronizedIterato
>> r.next(SessionDelegate.java:694)
>> at
>> org.apache.jackrabbit.oak.jcr.query.PrefetchIterator.next(PrefetchIterator
>> .java:97)
>> at
>> org.apache.jackrabbit.commons.iterator.RangeIteratorAdapter.next(RangeIter
>> atorAdapter.java:152)
>> at
>> org.apache.jackrabbit.commons.iterator.RangeIteratorDecorator.next(RangeIt
>> eratorDecorator.java:92)
>> at
>> org.apache.jackrabbit.commons.iterator.NodeIteratorAdapter.nextNode(NodeIt
>> eratorAdapter.java:80)
>> at
>> biz.netcentric.cq.tools.actool.helper.QueryHelper.getNodes(QueryHelper.jav
>> a:128)
>> at
>> biz.netcentric.cq.tools.actool.helper.QueryHelper.getRepPolicyNodes(QueryH
>> elper.java:90)
>> at
>> biz.netcentric.cq.tools.actool.dumpservice.impl.DumpserviceImpl.getACLDump
>> Beans(DumpserviceImpl.java:399)
>>
>> Of course there is an oak:index on jcr:primaryType, so I really don¹t see
>> the reason why this could be such a hard query to search for nodes under
>> a path that are of type rep:ACL?
>> (If you want more background, this query is used in the netcentric AC
>> Tool to make a dump of all the existing rep policy ACL nodes)
>>
>> Greetings,
>> Roy
>>
>>
>
Re: Oak query performance problem
Posted by Thomas Mueller <mu...@adobe.com>.
Hi,
> I really don¹t see the reason why this could be such a hard query
Who said it's a hard query? :-)
Is the problem performance, or is the problem that you get an exception?
If the problem is performance, then you need an index on the node type
rep:ACL.
If the problem is the exception: In your case the query engine is
configured to fail the queries (the reason is written in the exception
message). You can change the limit using the JMX bean
"QueryEngineSettings". The default is Long.MAX_VALUE (virtually no limit)
by the way.
Regards,
Thomas
On 18/10/16 16:44, "Roy Teeuwen" <ro...@teeuwen.be> wrote:
>Hello all,
>
>I got a problem in oak concerning query performance for the following
>simple queries
>
>SELECT * FROM [rep:ACL] WHERE ISDESCENDANTNODE([/content])
>SELECT * FROM [rep:ACL] WHERE ISDESCENDANTNODE([/var])
>
>I get the following exception:
>
>java.lang.UnsupportedOperationException: The query read or traversed more
>than 150000 nodes. To avoid affecting other tasks, processing was stopped.
> at
>org.apache.jackrabbit.oak.query.FilterIterators.checkReadLimit(FilterItera
>tors.java:66)
> at
>org.apache.jackrabbit.oak.spi.query.Cursors$TraversingCursor.fetchNext(Cur
>sors.java:324)
> at
>org.apache.jackrabbit.oak.spi.query.Cursors$TraversingCursor.next(Cursors.
>java:303)
> at
>org.apache.jackrabbit.oak.query.ast.SelectorImpl.next(SelectorImpl.java:40
>9)
> at
>org.apache.jackrabbit.oak.query.QueryImpl$RowIterator.fetchNext(QueryImpl.
>java:773)
> at
>org.apache.jackrabbit.oak.query.QueryImpl$RowIterator.hasNext(QueryImpl.ja
>va:798)
> at
>org.apache.jackrabbit.oak.jcr.query.QueryResultImpl$3.fetch(QueryResultImp
>l.java:181)
> at
>org.apache.jackrabbit.oak.jcr.query.QueryResultImpl$3.next(QueryResultImpl
>.java:207)
> at
>org.apache.jackrabbit.oak.jcr.query.QueryResultImpl$3.next(QueryResultImpl
>.java:170)
> at
>org.apache.jackrabbit.oak.jcr.delegate.SessionDelegate$SynchronizedIterato
>r.next(SessionDelegate.java:694)
> at
>org.apache.jackrabbit.oak.jcr.query.PrefetchIterator.next(PrefetchIterator
>.java:97)
> at
>org.apache.jackrabbit.commons.iterator.RangeIteratorAdapter.next(RangeIter
>atorAdapter.java:152)
> at
>org.apache.jackrabbit.commons.iterator.RangeIteratorDecorator.next(RangeIt
>eratorDecorator.java:92)
> at
>org.apache.jackrabbit.commons.iterator.NodeIteratorAdapter.nextNode(NodeIt
>eratorAdapter.java:80)
> at
>biz.netcentric.cq.tools.actool.helper.QueryHelper.getNodes(QueryHelper.jav
>a:128)
> at
>biz.netcentric.cq.tools.actool.helper.QueryHelper.getRepPolicyNodes(QueryH
>elper.java:90)
> at
>biz.netcentric.cq.tools.actool.dumpservice.impl.DumpserviceImpl.getACLDump
>Beans(DumpserviceImpl.java:399)
>
>Of course there is an oak:index on jcr:primaryType, so I really don¹t see
>the reason why this could be such a hard query to search for nodes under
>a path that are of type rep:ACL?
>(If you want more background, this query is used in the netcentric AC
>Tool to make a dump of all the existing rep policy ACL nodes)
>
>Greetings,
>Roy
>
>