US and Worldwide: +1 (866) 660-7555
Results 1 to 4 of 4

Thread: Mondrian performance problem - Loading segments of result set

  1. #1
    Join Date
    Jun 2011
    Posts
    1

    Default Mondrian performance problem - Loading segments of result set

    Hello,
    I want to migrated from BI 3.10.0 (Mondrian 3.3.0) to 4.8.0 (Mondrina 3.5.0). And I have performance problem with any type of MDX query.
    I have this query
    Code:
    with member [Measures].[402 Nepomuk  potraviny] as 'Aggregate(Crossjoin({[Chain].[001].[402]}, {[Measures].[valueAvg]}))'
      member [Measures].[294 Štěnovice] as 'Aggregate(Crossjoin({[Chain].[001].[294]}, {[Measures].[valueAvg]}))'
      member [Measures].[Rozdil] as '([Measures].[402 Nepomuk  potraviny] - [Measures].[294 Štěnovice])'
    select NON EMPTY {[Measures].[402 Nepomuk  potraviny], [Measures].[294 Štěnovice], [Measures].[Rozdil]} ON COLUMNS,
      NON EMPTY {{([DateWeeks].[2011].[W1.2011] : [DateWeeks].[2011].[W52.2011])}} ON ROWS
     from [StoreState]
    where measure valueAvg is calculated member defined this
    Code:
    <CalculatedMember name="valueAvg" caption="NC" dimension="Measures" formatString="#,##0.00" formula="Avg(Descendants([Date].CurrentMember, [Date].[Day]), [Measures].[value])"/>
    Problem is that time of query was increased 10 times (from 6s to 63s). I enabled debug logging of Mondrian Engine and in log are separeted SQL query for each row of result instead one SQL query for all rows (in previous version)
    Mondrian 3.3.0:
    2013-04-15 15:01:31,826 DEBUG [mondrian.sql] 15: Segment.load: executing sql [select `dim_store`.`code` as `c0`, `dim_date`.`day_of_month_name` as `c1`, `dim_date`.`week_name` as `c2`, sum(`store_state`.`value2`) as `m0` from `dim_store` as `dim_store`, `store_state` as `store_state`, `dim_date` as `dim_date` where `store_state`.`fk_id_store` = `dim_store`.`id` and `dim_store`.`code` in ('294', '402') and `store_state`.`fk_id_date` = `dim_date`.`id` and `dim_date`.`week_name` in ('W1.2011', 'W10.2011', 'W11.2011', 'W12.2011', 'W13.2011', 'W14.2011', 'W15.2011', 'W16.2011', 'W17.2011', 'W18.2011', 'W19.2011', 'W2.2011', 'W20.2011', 'W21.2011', 'W22.2011', 'W23.2011', 'W24.2011', 'W25.2011', 'W26.2011', 'W27.2011', 'W28.2011', 'W29.2011', 'W3.2011', 'W30.2011', 'W31.2011', 'W32.2011', 'W33.2011', 'W34.2011', 'W35.2011', 'W36.2011', 'W37.2011', 'W38.2011', 'W39.2011', 'W4.2011', 'W40.2011', 'W41.2011', 'W42.2011', 'W43.2011', 'W44.2011', 'W45.2011', 'W46.2011', 'W47.2011', 'W48.2011', 'W49.2011', 'W5.2011', 'W50.2011', 'W51.2011', 'W52.2011', 'W6.2011', 'W7.2011', 'W8.2011', 'W9.2011') group by `dim_store`.`code`, `dim_date`.`day_of_month_name`, `dim_date`.`week_name`]
    Mondrian 3.5.0:
    2013-04-15 15:17:31,948 DEBUG [mondrian.sql] 12: Segment.load: executing sql [select `dim_store`.`code` as `c0`, `dim_date`.`day_of_month_name` as `c1`, `dim_date`.`week_name` as `c2`, sum(`store_state`.`value2`) as `m0` from `dim_store` as `dim_store`, `store_state` as `store_state`, `dim_date` as `dim_date` where `store_state`.`fk_id_store` = `dim_store`.`id` and `dim_store`.`code` in ('294', '402') and `store_state`.`fk_id_date` = `dim_date`.`id` and `dim_date`.`week_name` = 'W1.2011' group by `dim_store`.`code`, `dim_date`.`day_of_month_name`, `dim_date`.`week_name`]
    2013-04-15 15:17:32,703 DEBUG [mondrian.sql] 12: , exec 755 ms
    2013-04-15 15:17:32,704 DEBUG [mondrian.sql] 12: , exec+fetch 756 ms, 14 rows
    2013-04-15 15:17:33,016 DEBUG [mondrian.sql] 13: Segment.load: executing sql [select `dim_store`.`code` as `c0`, `dim_date`.`day_of_month_name` as `c1`, `dim_date`.`week_name` as `c2`, sum(`store_state`.`value2`) as `m0` from `dim_store` as `dim_store`, `store_state` as `store_state`, `dim_date` as `dim_date` where `store_state`.`fk_id_store` = `dim_store`.`id` and `dim_store`.`code` in ('294', '402') and `store_state`.`fk_id_date` = `dim_date`.`id` and `dim_date`.`week_name` = 'W2.2011' group by `dim_store`.`code`, `dim_date`.`day_of_month_name`, `dim_date`.`week_name`]
    2013-04-15 15:17:33,343 DEBUG [mondrian.sql] 13: , exec 328 ms
    2013-04-15 15:17:33,343 DEBUG [mondrian.sql] 13: , exec+fetch 327 ms, 14 rows
    ....
    2013-04-15 15:18:31,954 DEBUG [mondrian.sql] 63: Segment.load: executing sql [select `dim_store`.`code` as `c0`, `dim_date`.`day_of_month_name` as `c1`, `dim_date`.`week_name` as `c2`, sum(`store_state`.`value2`) as `m0` from `dim_store` as `dim_store`, `store_state` as `store_state`, `dim_date` as `dim_date` where `store_state`.`fk_id_store` = `dim_store`.`id` and `dim_store`.`code` in ('294', '402') and `store_state`.`fk_id_date` = `dim_date`.`id` and `dim_date`.`week_name` = 'W52.2011' group by `dim_store`.`code`, `dim_date`.`day_of_month_name`, `dim_date`.`week_name`]
    2013-04-15 15:18:31,995 DEBUG [mondrian.sql] 63: , exec 40 ms
    2013-04-15 15:18:31,995 DEBUG [mondrian.sql] 63: , exec+fetch 41 ms, 0 rows
    Full log of both query are as attachments (mondrian-3.3.0.log, mondrian-3.5.0.log)

    Is it problem in engine itself or I can change some property in mondrian.properties?


    Thanks for reply

    Petr Prochazka
    Last edited by pprochazka; 04-15-2013 at 12:06 PM.

  2. #2
    Join Date
    Dec 2011
    Posts
    11

    Default

    Hello,

    I have the same problem.
    I have opened http://jira.pentaho.com/browse/MONDRIAN-1831.
    Did you find any solution ? Do you still use the 3.3.0 ?

  3. #3

    Default

    Hi, I too have same problem with 3.5 version.
    here is the jira case http://jira.pentaho.com/browse/MONDRIAN-1803
    It is taking very long time and in my case i got wrong results with version 3.5.
    Then, I tried with qury.timeout and max.cellBatchSize values and changed the query pattern. It is loading large data sets if we put some more dimensions/members to the query to aggregate and sometimes getting GC overhead limit exceeded ( I m using 8Gig Ram). The execution time is not impressive, even with some sql scripts are faster than mdx.
    I m stuck with this execution time optimization.

  4. #4
    Join Date
    Jan 2013
    Posts
    531

    Default

    I think there may be multiple issues going on in this thread. One is likely related to mondrian.rolap.cellBatchSize. Query patterns like the one in the example below are likely the result of tripping this threshold, which causes separate, smaller segment load queries. In many cases this makes sense. It's better to lower the resource demand of Mondrian in some cases by constructing smaller batches. That said, if you're seeing significantly worse performance with 3.3 vs 3.4+, and you see a much larger number of SQL queries where most vary only by the specific attribute constraints, then it's worth experimenting with larger cellBatchSize values.

    LinuxBee-- if I'm not mistaken you're comparing 3.4 to 3.5, and I think your performance issue is in some way connected with compound slicers.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •