Best practices that can improve the performance of Atrium Integrator job

Version 7
    Share:|

    This document contains official content from the BMC Software Knowledge Base. It is automatically updated when the knowledge article is modified.


    PRODUCT:

    BMC Atrium Core - CMDB


    COMPONENT:

    BMC Atrium Core


    APPLIES TO:

    BMC Atrium CMDB Suite



    PROBLEM:

     

    Processing of CI's is slow. Pentaho (AtriumIntegrator) data transformations using the Spoon or AI console runs more than 9 hours to insert 150000 records into CMDB Class.
    That's less then 5 records per second. Performance should be much higher. We expect something from 40 to 100 CIs or Relationships per second.

     


    SOLUTION:

     

    Legacy ID:KA359797

      

    Below is a list of BMC Best Practices that can improve the performance of Atrium Integrator:

    1)Create index entries on:

    The BMC_BaseElement class for keys that are used as primary keys

    Relationship keys for relationship data exchanges
    External data stores for exchanges with the External Data to CMDB data direction and for relationship data exchanges

    Likewise, From the AR server SQL-API-FILTER and with the help of LogAnalyzer tool or may be with the help of any Database specific tool, identify the Long running queries.  Following is one of the example of the query:
    Note: This query is only as an example reference, it would not run in your environment as is.

    WITH AR_SQL_Alias$1 AS (SELECT T3772.C536870926, T3772.C536870921, T3772.C536870916, T3772.C536870913, T3772.C536870919, T3772.C536870937, T3772.C1, ROW_NUMBER() OVER (ORDER BY T3772.C1 ASC) AS 'AR_RowNumber_Alias$1' FROM T3772 WHERE ((T3772.C536870994 = @P0) AND (T3772.C536870994 IS NOT NULL) AND (T3772.C536870994 <> @P1) AND (T3772.C536870921 IS NOT NULL) AND (T3772.C536870994 <> @P2))) SELECT AR_SQL_Alias$1.C536870926, AR_SQL_Alias$1.C536870921, AR_SQL_Alias$1.C536870916, AR_SQL_Alias$1.C536870913, AR_SQL_Alias$1.C536870919, AR_SQL_Alias$1.C536870937, AR_SQL_Alias$1.C1 FROM AR_SQL_Alias$1 WHERE (AR_SQL_Alias$1.AR_RowNumber_Alias$1 BETWEEN @P3 AND @P4)


    If the above query takes a long time to return the records, you have to work with your Database administrator to create an index on this table.  The index has to be created on the field mentioned in the WHERE clause. In the above example the index will be on T3772.C536870994

    2)Provide minimum and maximum values for the Fast Thread and List Thread options in the BMC Remedy AR System Administration Console. For more information about the Fast Thread and List Thread options and how to specify the number of threads, see the BMC Remedy Action Request System Configuration Guide.

    3)Add multiple copies of a step using the Spoon client transformation editor. For optimum performance, you can launch 20 copies of a step and measure the results in the Spoon analysis.

    4)Distribute a step to multiple processing flows when you want to split the transformation flow to multiple processing paths.

    5)When possible, run job and transformation steps in parallel to improve throughput. If dependencies exist, however, you cannot runs steps in parallel.

    6)Limit the columns that you map. The more columns you map, the slower the transformation runs.

    7)Use JavaScript and lookup steps sparingly as they can significantly increase the processing time.


    Predicting the effect of a specific change on Atrium Integrator performance is difficult, but configuration considerations can influence performance.
    Although it is easy to add field mappings or extra transformations for each field, remember that each item you add is compared against every record in your database. Seemingly minor additions could have a major impact if your database contains many records.

    For example, if you have 1200 records in your database and a change adds an additional half-second to the run time for each record, the data transfer might take an additional 10 minutes to run

     


    Article Number:

    000089192


    Article Type:

    Solutions to a Product Problem



      Looking for additional information?    Search BMC Support  or  Browse Knowledge Articles