[Mondrian] Having some problems with query cancel

Pedro Salgueiro pedro.salgueiro at cortex-intelligence.com
Tue Nov 19 12:37:53 EST 2013


Thank you for your input!

After some more debug, I noticed that the
method MondrianOlap4jStatement.executeOlapQuery(String mdx) performs two
main tasks: 1) parseQuery(mdx) and 2) executeOlapQueryInternal(pair.left,
pair.right).
When I execute the cancelMethod() from another query, the executeOlapQuery
method is still executing the parseQuery. The problem is that the
openCellSet only gets assigned with a value when the
executeOlapQueryInternal method is executed.

I guess this is the cause why the cancel method doesn't work on some
queries. I guess that if the query spends a large amount of time in the
parseQuery step, the cancel will fail.

I tried forcing an artificial delay between the excecuteOlapQuey() and the
cancel() method, and results are the same.
I even tried to use the cancel inside a loop, stopping only when the query
is actually canceled.  This seemed to work, but I think the query is only
canceled very near to the end.
Running the query with no cancel takes about 38 seconds to run. The same
query with the cancel() method inside the loop takes about the same time to
be canceled, about 38 seconds.

My guess is that the MondrianOlap4jStatement.parseQuery(mdx) should check
in someway if the query has been canceled, otherwise, queries like these
ones will fail to be canceled.

We are trying a new approach: running each query inside a new thread, and
if the user cancels the query we kill the thread associated with the
desired query. So far it seems to do the job. Do you see any possible side
effects on Mondrian due to the use of this approach?

As for cancellation on the SQL drivers, that doesn't worries me too much.
Usually the SQL queries produced by Mondrian doesn't seems be very
expensive.

Also, in this particular case, we are using a single mdx query, so I guess
it's not related with some kind of concurrency problem.

Best regards,
Pedro Salgueiro


On Tue, Nov 19, 2013 at 4:37 PM, Luc Boudreau <lucboudreau at gmail.com> wrote:

> Hi Pedro,
>
> Thanks for looking into this issue.
>
> "Is it possible that we are trying to cancel the query when it's not
> ready to be stopped?"
>
> Possible. When we cancel the queries, we don't interrupt the thread, but
> we set a flag in Execution. We then check periodically, at certain
> checkpoints, the state of the cancelation flag and we break execution if
> needed. As far as I know, SleepUdf doesn't check that flag, so it won't be
> able to notice that the query was canceled.
>
> Another situation where a cancelation won't happen is when two MDX queries
> try to access the same segment. If query 1 gets canceled, we check if other
> MDX statements need that particular segment and will only cancel it if
> nobody cares about it anymore.
>
> It is also worth noting that we cannot enforce any kind of cancellation on
> the SQL drivers, other than calling cancel() and wishing for the best. Some
> drivers will return early, others will simply ignore the interruption flag
> on their thread (or whatever mechanism they use internally). This in turn
> blocks mondrian from checking the state of the MDX statement until the code
> returns from the driver.
>
> Back to your scenario. It is possible that the field openCellSet is not
> assigned a value if you call cancel() from another thread right after you
> have created an MDX statement. We don't have a mechanism in there to
> 'cancel later'. Try adding an artificial delay between the time you create
> your statement and the time you call cancel and add some System.out calls
> into it to figure out in what order stuff happens. If this is the cause, we
> can probably fix that.
>
> Lastly, if you see a spot in mondrian's code where the state of the
> execution should be checked but it isn't, please share your finding and
> we'll look into it.
>
> Luc
>
>
>
> On Mon, Nov 18, 2013 at 3:25 PM, Pedro Salgueiro <
> pedro.salgueiro at cortex-intelligence.com> wrote:
>
>> Hi,
>>
>> I have been playing with the method Statement.cancel() in order to cancel
>> some long running queries on user demand, which is not revealing to be an
>> easy task.
>>
>> Although in some queries, the cancel() method seems to be working
>> perfectly and an exception is thrown when the query is canceled, as it's
>> supposed to be, on other queries, nothing happens.
>> After some debugging, I reached to the conclusion that when a query
>> includes many specific members, we are not able to cancel it.
>>
>> If we execute the following query, we are able to cancel it with no
>> problems.
>>
>> WITH
>>> MEMBER [Measures].[Sleepy]
>>> AS SleepUdf([Measures].[Unit Sales])
>>
>> SELECT
>>> [Product].[Product Family].Members ON 0,
>>> [Measures].[Unit Sales],[Measures].[Sleepy] ON 1
>>> FROM [Sales]
>>
>>
>>
>> On the other hand, if we execute the following query with about 30 or 40
>> members, we are no longer able to cancel it:
>>
>> WITH
>>> MEMBER [Measures].[Sleepy]
>>> AS SleepUdf([Measures].[Unit Sales])
>>
>> SELECT
>>> {[Product].[Product Family].[Member 1], [Product].[Product
>>> Family].[Member 1], ..., [Product].[Product Family].[Member n]} ON 0,
>>
>> [Measures].[Unit Sales],[Measures].[Sleepy] ON 1
>>> FROM [Sales]
>>
>>
>> It's worth point out that SleepUdf is a user defined function which uses
>> a Thread.sleep(), forcing the queries to take longer to execute, inspired
>> by Mondrian's BasicQueryTest.java. This way we are able to properly test if
>> the cancel is working.
>>
>> I was looking at the cancel implementation in the MondrianOlap4jStatement
>> class, and if the openCellSet is null, nothing is done. What are the
>> conditions that would lead to a null openCellSet?
>>
>> The queries that we are not being able to cancel produces a large number
>> of SQL queries, and the cancel() method is executed while these SQL queries
>> are being executed. Is it possible that we are trying to cancel the query
>> when it's not ready to be stopped? Or maybethe cancel method encounters the
>> query in a state that is not possible to be stopped yet, maybe between the
>> execution of two SQL queries?
>>
>> In situations like this, is there any other approach to cancel the query?
>> Do you know any easy solution to solve this problem?
>>
>> Any help will be greatly appreciated,
>> Pedro Salgueiro
>>
>> _______________________________________________
>> Mondrian mailing list
>> Mondrian at pentaho.org
>> http://lists.pentaho.org/mailman/listinfo/mondrian
>>
>>
>
> _______________________________________________
> Mondrian mailing list
> Mondrian at pentaho.org
> http://lists.pentaho.org/mailman/listinfo/mondrian
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20131119/d40b90bc/attachment-0001.html 


More information about the Mondrian mailing list