Hitachi Vantara Pentaho Community Forums
Results 1 to 5 of 5

Thread: mondrian with big different response time on query very similar

  1. #1

    Default mondrian with big different response time on query very similar

    i have a query made with saiku plugin on pentaho 5.1 whit a big cross join ( 6 dimension and one of this have a high cardinality , about 50000 members ) .
    The query have on the filter axis a condition on a time dimension ( i filter on a month of a year ) .
    If a select the period ( 2014 February ) i get a response in about 20 seconds .
    If a select the period ( 2015 February ) i get a response in about 10 minutes .
    I'm trying to understand why a so different behavior .
    I have activated the mondrial_sql log and i have noted that in the first case mondrian exec a limited number of query .
    In the second case the number of query is very high .
    What can cause such a different behavior ?
    What can I look in the log file to make me understand ?

    thanks for any answer .

  2. #2
    Join Date
    Jan 2013
    Posts
    796

    Default

    Hi Claudio,
    For debugging purposes, try setting the property

    mondrian.rolap.cellBatchSize=2000000000

    It's possible that your large crossjoin is causing Mondrian to exceed the cell batch threshold, which causes it to chunk segment load queries.

    If that makes a difference report back. Also, can you post the MDX generated by Saiku for that fat crossjoin?

  3. #3

    Default

    your setting has made a big difference :
    the slow query ( 2015 february ) now take 52 secs instead of 10 minutes ( 22 secs if i repeat the query )
    thank you very much

  4. #4

    Default

    i have uploaded two zip files on google drive ( one for slow query and one for fast query ) that contains the sql and mdx log .
    here the links :
    https://drive.google.com/file/d/0B7v...ew?usp=sharing
    https://drive.google.com/file/d/0B7v...ew?usp=sharing

    in the file mondrian_mdx.log you found teh query generared fromo saiku ( the query is too big for to be attached to the post )

    thanks again for the info
    Last edited by claudio.tamietto; 03-28-2015 at 12:12 PM.

  5. #5
    Join Date
    Jan 2013
    Posts
    796

    Default

    Hmm, it's possible you uploaded the wrong logs. The "slow" log time shows 56 seconds MDX time, the fast log time just under 11 seconds.

    Leaving the cellBatchSize at 2 billion is not a safe option- it's okay for debugging purposes to confirm what's going on, but such a large number effectively turns off a safeguard that keeps Mondrian from exceeding available resources. The basic problem looks to be that the query is too complex to reduce the cell request count down to a reasonable number (below the 100K default setting), and you're stuck running many segment load queries.

    In an ideal world you could tune that MDX to be more efficient, but since you didn't generate it yourself out of luck. Your next best option is to play with the cellBatchSize, bringing it to a number that's high enough for your workload, but hopefully low enough that you avoid outstripping your resources.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.