DX Infrastructure Management

Expand all | Collapse all

Why are CABI reports so slow to run?

Jump to Best Answer
  • 1.  Why are CABI reports so slow to run?

    Posted 21 days ago
    Hello,
    Just wondering if there are any optimization tips for getting CABi to generate reports quicker?
    UIM's CABI is the slowest reporting tool I ever used compared to other solutions that are out there.
    I'm trying to run a Group Trend Report based on last 3 days, on just 6 boxes basically CDM metrics (cpu, disk, memory, in/out-bound traffic) and its been running for 15 min with the Please Wait...

    Other larger reports take up to 45 min - 1hr+ to run based on 1 month data. Any tips here to get this working more efficiently?

    ------------------------------
    Daniel Blanco
    Enterprise Tools Architect
    Alphaserve Technologies
    ------------------------------


  • 2.  RE: Why are CABI reports so slow to run?

    Posted 21 days ago
    I had a ticket open for this and support suggested to change the CABI and WASP memory parameters and this made the response time much better.
    UMP wasp:

     <opt>
    java_mem_max = -Xmx6g
    java_mem_init = -Xms2g
    java_stack_size = -Xss64m
    min_perm_size = -XX:PermSize=512m
    max_perm_size = -XX:MaxPermSize=1024m
    java_gc_conc_marc_sweep = -XX:+UseConcMarkSweepGC
    java_cms_classunloading_enable = -XX:+CMSClassUnloadingEnabled
    </opt>
    CABI:
    <opt>
    java_mem_max = -Xmx6g
    java_mem_init = -Xms2g
    dump_memory = -XX:+HeapDumpOnOutOfMemoryError
    </opt>




  • 3.  RE: Why are CABI reports so slow to run?

    Posted 20 days ago
    You might also check out the below KB
    https://ca-broadcom.wolkenservicedesk.com/external/article?articleId=118729


    ------------------------------
    Gene Howard
    Principal Support Engineer
    Broadcom
    ------------------------------



  • 4.  RE: Why are CABI reports so slow to run?

    Posted 20 days ago
    Edited by SHAWN WALSH 20 days ago
    This does not answer why they are slow, but scheduling reports is always a good practice if you do not need realtime data.  Which it sounds like that is the case if you are reporting for a month's worth of data.
    You can schedule the reports to run in the background at regular intervals and view the completed reports that way.


  • 5.  RE: Why are CABI reports so slow to run?

    Posted 20 days ago
    Thanks but this didn't help at all.
    I bumped up the memory as indicated above which I already had in place.
    Did full stop/start on robot and then tried another report.
    Looking at a group with 26 robots
    Duration: last month
    Metrics: 5 metrics:  (cpu/disk/mem PCT and in/out traffic)
    Aggregation: Component
    View: Group by Metric
    Sort: Desc
    Top N:  Top 20
    Display: Both

    Started the report at 4:43PM and its still running...
    This should be seriously < 20s.

    ------------------------------
    Daniel Blanco
    Enterprise Tools Architect
    Alphaserve Technologies
    ------------------------------



  • 6.  RE: Why are CABI reports so slow to run?

    Posted 17 days ago
    Hi Daniel,


    These log settings may help:

    Monitor the <CABI HOME>/apache-tomcat/logs/catalina.out
    You should see the database queries and how long they took.

    If it is a database slowness issue or a poorly written query, this may be helpful.
    You can copy/paste the query and run it in SQL/Oracle to see where the bottleneck is (if any).
    MS SQL can provide client side statistics / execution plan.
    Oracle can do the same with the show execution plan

    Also, there could be blocking or something preventing the query from running quickly.

    How many hops are between the Jaspersoft server and the UIM's database server (traceroute)?
    I have seen issues when the Jaspersoft server is not on the same subnet as the database server.
    Jaspersoft will try to grab all of the rows at once.  It does not do paging when fetching a large data set and a little bit of a network delay can cause a real slow down for reports.


  • 7.  RE: Why are CABI reports so slow to run?

    Posted 16 days ago
    Hi Shawn,
    Thanks for the tips. Just FYI for everyone else, to get to the LOG settings you have to log into CABi directly with the superuser account. You won't see the "Log Settings" under manage if you logged in with a regular user.

    Shawn, I also don't see this:  <CABI HOME>/apache-tomcat/logs/catalina.out anywhere. Searched the entire Nimsoft folder.
    If you can tell me where this file is? Is it the cabi.log or wasp.log instead?

    The DB is running on flash storage so I know it's crazy fast with very high IOPs capacity.
    I'm thinking it's really the reports SQL Query that isn't' optimized at all.

    The DB is on the same ESX host, same subnet as the cabi reporting box. There's only 1 hop from box to box.

    ------------------------------
    Daniel Blanco
    Enterprise Tools Team Architect
    DBlanco@alphaserveit.com
    ------------------------------



  • 8.  RE: Why are CABI reports so slow to run?
    Best Answer

    Posted 16 days ago
    different but still might be helpful
    $\Nimsoft\probes\service\wasp\webapps\cabijs\WEB-INF\logs\jasper*

    Shawn's may be specific to linux
    for windows search for stdout.log

    ------------------------------
    Support Engineer
    Broadcom
    ------------------------------



  • 9.  RE: Why are CABI reports so slow to run?

    Posted 16 days ago
    It should also log the queries to jasperserver.log under probes/service/wasp/webapps/WEB-INF/logs/


  • 10.  RE: Why are CABI reports so slow to run?

    Posted 16 days ago
    Thanks that's the right file but now how do I tell when the query started and ended. I cleared out the log file right before I hit "Apply" to run the report and I see a massive SQL Query:
    2019-11-19 11:56:50,120 DEBUG JRJdbcQueryExecuter,group trend main report subreports #1:362 - SQL query string:  if object_id('tempdb..#GTR_MV_PS_NO') is not null 2019-11-19 11:56:50,120 DEBUG JRJdbcQueryExecuter,group trend main report subreports #1:362 - SQL query string:  if object_id('tempdb..#GTR_MV_PS_NO') is not null  begin  drop table #GTR_MV_PS_NO  end   SELECT     sqd.qos as qos,  ccim.ci_metric_type+'|'+sqd.qos as  met_type,  COALESCE(cimd.met_description,'...') as met_description,    ccim.ci_metric_type as metric_type,      cimd.unit_type as unit_type,      sqd.table_id as table_id  into #GTR_MV_PS_NO   FROM       S_QOS_DATA sqd   JOIN S_QOS_DEFINITION sqdef on sqd.qos_def_id = sqdef.qos_def_id     JOIN CM_CONFIGURATION_ITEM_METRIC ccim on sqd.ci_metric_id = ccim.ci_metric_id   LEFT JOIN CM_CONFIGURATION_ITEM_METRIC_DEFINITION cimd on ccim.ci_metric_type = cimd.met_type   JOIN CM_CONFIGURATION_ITEM cci on ccim.ci_id = cci.ci_id   JOIN CM_DEVICE cd on cci.dev_id = cd.dev_id OR cd.cs_id IN ('-100')   JOIN CM_COMPUTER_SYSTEM ccs ON cd.cs_id = ccs.cs_id   JOIN CM_GROUP_MEMBER cgm ON cd.cs_id = cgm.cs_id AND cgm.grp_id=455   WHERE ccim.ci_metric_type+'|'+sqd.qos IN ('1.1:3|QOS_DISK_USAGE_PERC','1.3:5|QOS_PROCESS_CPU','1.6:2|QOS_MEMORY_PERC_USAGE','2.1.1:2|QOS_NETWORK_OUTBOUND_TRAFFIC','2.1.1:1|QOS_NETWORK_INBOUND_TRAFFIC')  SELECT * FROM (  SELECT     Avg(vn.samplevalue) avg_value,    qos,  met_type,    met_description,  metric_type,  unit_type FROM VN_QOS_DATA_0002 vn   JOIN #GTR_MV_PS_NO sqd on sqd.table_id = vn.table_id  WHERE vn.samplevalue != -987654321   AND CAST(vn.sampletime as datetime) >= DATEADD(week, -1, CURRENT_TIMESTAMP)  GROUP BY   met_type,    qos,  met_description,  metric_type,  unit_type  UNION  SELECT     Avg(vn.samplevalue) avg_value,    qos,  met_type,    met_description,  metric_type,  unit_type FROM VN_QOS_DATA_0040 vn   JOIN #GTR_MV_PS_NO sqd on sqd.table_id = vn.table_id  WHERE vn.samplevalue != -987654321   AND CAST(vn.sampletime as datetime) >= DATEADD(week, -1, CURRENT_TIMESTAMP)  GROUP BY   met_type,    qos,  met_description,  metric_type,  unit_type  UNION  SELECT     Avg(vn.samplevalue) avg_value,    qos,  met_type,    met_description,  metric_type,  unit_type FROM VN_QOS_DATA_0010 vn   JOIN #GTR_MV_PS_NO sqd on sqd.table_id = vn.table_id  WHERE vn.samplevalue != -987654321   AND CAST(vn.sampletime as datetime) >= DATEADD(week, -1, CURRENT_TIMESTAMP)  GROUP BY   met_type,    qos,  met_description,  metric_type,  unit_type  UNION  SELECT     Avg(vn.samplevalue) avg_value,    qos,  met_type,    met_description,  metric_type,  unit_type FROM VN_QOS_DATA_1318 vn   JOIN #GTR_MV_PS_NO sqd on sqd.table_id = vn.table_id  WHERE vn.samplevalue != -987654321   AND CAST(vn.sampletime as datetime) >= DATEADD(week, -1, CURRENT_TIMESTAMP)  GROUP BY   met_type,    qos,  met_description,  metric_type,  unit_type  UNION  SELECT     Avg(vn.samplevalue) avg_value,    qos,  met_type,    met_description,  metric_type,  unit_type FROM VN_QOS_DATA_1317 vn   JOIN #GTR_MV_PS_NO sqd on sqd.table_id = vn.table_id  WHERE vn.samplevalue != -987654321   AND CAST(vn.sampletime as datetime) >= DATEADD(week, -1, CURRENT_TIMESTAMP)  GROUP BY   met_type,    qos,  met_description,  metric_type,  unit_type  ) MAIN WHERE met_type IS NOT NULL  ORDER BY avg_value desc 

    But then a min or two later it spits out :
    2019-11-19 12:06:13,018 DEBUG JRJdbcQueryExecuter,group trend main report subreports #3:152 - DB is Microsoft SQL Server version Microsoft SQL Server 2012 - 11.0.3000.0 (11/0)
    2019-11-19 12:06:13,080 DEBUG JRJdbcQueryExecuter,group trend main report subreports #3:156 - driver is SQLServer version 5.1.4.000169 (F000304.U000132) (5/1)
    2019-11-19 12:06:13,080 DEBUG JRJdbcQueryExecuter,group trend main report subreports #3:160 - jdbc 4/0
    Then I see another large SQL Query for the 2nd, and then 3rd, and 4th QOS Metric..
    I also see this:
    2019-11-19 12:07:46,792 ERROR RenderViewExceptionInterceptor,http-nio-80-exec-3807:55 - Internal server error occurred. Please contact your system administrator.2019-11-19 12:07:46,792 ERROR RenderViewExceptionInterceptor,http-nio-80-exec-3807:55 - Internal server error occurred. Please contact your system administrator.org.apache.jasper.JasperException: org.apache.tiles.impl.CannotRenderException: JSPException including path '/WEB-INF/jsp/templates/aboutBox.jsp'. at org.apache.jasper.servlet.JspServletWrapper.handleJspException(JspServletWrapper.java:638) at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:514)

    So again I started the same report on 26 robots, 1 week of data on 5 metrics: cpu,mem,disk traffic in/out and its been running since 11:57AM. Its been now over 25 min...



    ------------------------------
    Daniel Blanco
    Enterprise Tools Team Architect
    DBlanco@alphaserveit.com
    ------------------------------



  • 11.  RE: Why are CABI reports so slow to run?

    Posted 16 days ago
    Just FYI I do not see any entries for the JSControlledJdbcQueryExecuter so I don't have any time entries for the queries in this log file. Its set to DEBUG.
    Are these entries in a different log file?



    ------------------------------
    Daniel Blanco
    Enterprise Tools Team Architect
    DBlanco@alphaserveit.com
    ------------------------------



  • 12.  RE: Why are CABI reports so slow to run?

    Posted 16 days ago
    FYI it finally finished at around 12:30.. So ~33min to run 1 week report.. on 26 robots looking at 5 metrics...

    ------------------------------
    Daniel Blanco
    Enterprise Tools Team Architect
    DBlanco@alphaserveit.com
    ------------------------------



  • 13.  RE: Why are CABI reports so slow to run?

    Posted 16 days ago
    Of course it works fast here :)
    Can we run those queries with "Display Actual Execution Plan"?
    For example:

    if object_id('tempdb..#GTR_MV_PS_NO') is not null begin drop table #GTR_MV_PS_NO end
    SELECT sqd.qos as qos, ccim.ci_metric_type+'|'+sqd.qos as met_type, COALESCE(cimd.met_description,'...') as met_description, ccim.ci_metric_type as metric_type, cimd.unit_type as unit_type, sqd.table_id as table_id into #GTR_MV_PS_NO
    FROM S_QOS_DATA sqd JOIN S_QOS_DEFINITION sqdef on sqd.qos_def_id = sqdef.qos_def_id
    JOIN CM_CONFIGURATION_ITEM_METRIC ccim on sqd.ci_metric_id = ccim.ci_metric_id
    LEFT JOIN CM_CONFIGURATION_ITEM_METRIC_DEFINITION cimd on ccim.ci_metric_type = cimd.met_type
    JOIN CM_CONFIGURATION_ITEM cci on ccim.ci_id = cci.ci_id JOIN CM_DEVICE cd on cci.dev_id = cd.dev_id OR cd.cs_id IN ('-100')
    JOIN CM_COMPUTER_SYSTEM ccs ON cd.cs_id = ccs.cs_id JOIN CM_GROUP_MEMBER cgm ON cd.cs_id = cgm.cs_id AND cgm.grp_id=455
    WHERE ccim.ci_metric_type+'|'+sqd.qos IN ('1.1:3|QOS_DISK_USAGE_PERC','1.3:5|QOS_PROCESS_CPU','1.6:2|QOS_MEMORY_PERC_USAGE','2.1.1:2|QOS_NETWORK_OUTBOUND_TRAFFIC','2.1.1:1|QOS_NETWORK_INBOUND_TRAFFIC')



    SELECT * FROM ( SELECT Avg(vn.samplevalue) avg_value, qos, met_type, met_description, metric_type, unit_type FROM VN_QOS_DATA_0002 vn
    JOIN #GTR_MV_PS_NO sqd on sqd.table_id = vn.table_id WHERE vn.samplevalue != -987654321
    AND CAST(vn.sampletime as datetime) >= DATEADD(week, -1, CURRENT_TIMESTAMP) GROUP BY met_type, qos, met_description, metric_type, unit_type
    UNION SELECT Avg(vn.samplevalue) avg_value, qos, met_type, met_description, metric_type, unit_type FROM VN_QOS_DATA_0040 vn
    JOIN #GTR_MV_PS_NO sqd on sqd.table_id = vn.table_id WHERE vn.samplevalue != -987654321 AND CAST(vn.sampletime as datetime) >= DATEADD(week, -1, CURRENT_TIMESTAMP)
    GROUP BY met_type, qos, met_description, metric_type, unit_type UNION
    SELECT Avg(vn.samplevalue) avg_value, qos, met_type, met_description, metric_type, unit_type FROM VN_QOS_DATA_0010 vn
    JOIN #GTR_MV_PS_NO sqd on sqd.table_id = vn.table_id WHERE vn.samplevalue != -987654321
    AND CAST(vn.sampletime as datetime) >= DATEADD(week, -1, CURRENT_TIMESTAMP)
    GROUP BY met_type, qos, met_description, metric_type, unit_type
    UNION SELECT Avg(vn.samplevalue) avg_value, qos, met_type, met_description, metric_type, unit_type
    FROM VN_QOS_DATA_1318 vn JOIN #GTR_MV_PS_NO sqd on sqd.table_id = vn.table_id WHERE vn.samplevalue != -987654321
    AND CAST(vn.sampletime as datetime) >= DATEADD(week, -1, CURRENT_TIMESTAMP) GROUP BY met_type, qos, met_description,
    metric_type, unit_type UNION SELECT Avg(vn.samplevalue) avg_value, qos, met_type,
    met_description, metric_type, unit_type FROM VN_QOS_DATA_1317 vn JOIN #GTR_MV_PS_NO sqd on sqd.table_id = vn.table_id
    WHERE vn.samplevalue != -987654321 AND CAST(vn.sampletime as datetime) >= DATEADD(week, -1, CURRENT_TIMESTAMP)
    GROUP BY met_type, qos, met_description, metric_type, unit_type ) MAIN WHERE met_type IS NOT NULL
    ORDER BY avg_value desc