...

1. Connect to the Tomcat Manager, click Server Status in the upper right corner.
   
2. In the Server Status screen, check the memory user by TOMCAT: Free memory, Total memory, Max memory
   

For Semarchy v4.xxDM:

• Max Maximum Memory -Xmx value must be at least 4 GoGb.
• The OS should have at least 8 Go Gb of RAM.

Database Server

Server Requirements

...

Many to many relations are designed as a dedicated entity, which implies storage and processing overhead: More complex SQL is automatically generated when querying and manipulating data from the two entities related by a many-to-many relation.

...

The Data Integration process (using an ETL, ESB etc.) loads data into , or consumes data from the xDM data locations. It causes sometimes a large part of the delay in the data chain.
When assessing performance issues, make sure to separate the Data Integration Time (before you Submit data to xDM) from the Certification Process Time (when you actually Submit data to xDM) when reviewing the complete data processing time.

...

This query should help you identify specific phases or tasks in the certification process that take most of the time. The following sections gives give you tips for optimizing these phases.

...

Built-in database functions are usually extremely optimized, whereas user-defined functions are scripts that run once for each line. A poorly coded function has a dramatic impact on the performeceperformance. 

...

By default, plugins process one record at a time. It is possible to launch several thread threads to process multiple records at the time time, reducing the number of read/writes interactions with the database. This is performed by configuring the Thread Pool Size in the Enricher or Validation.
A typical value between 4~8 is sufficient is in most cases.

Warnings:

• Using a Thread Pool Size greater than 1 means that the plug-in in thread-safe. xDM built-in plug-ins are thread safe, but user-designed plug-ins might not be.
• Increasing the Thread Pool Size increate the application server load as it processes multiple instances of the plugins at the same time.
• When calling to an external service with the plugin, pay attention to possible limitation limitations or throttling limit limits of the service.

• When piping plugin enrichers using the PARAM_AGGREGATE_JOB_PLUGIN_ENRICHERS job paramterparameter, make sure to align the thread pool size in the chain of enrichers.

...

• Email Validator: The email plug-in uses a local cache for known domain names to avoid repeating useless MX Records lookup for domain name validation. This cache is populated during the first plug-in execution using MX Record Lookup for each email domain in the dataset. Subsequent execution favor favors the cache over the MX Lookup.
  • Avoid dropping the table storing the cache.
  • Review the Offline Mode and Processing Mode parameters for tuning the use of this cache.
• Lookup Enricher: This enricher uses a query or table for looking up information for each enriched record.
  • Make sure that access to the table and that the query should be fast (<40ms/call), or use the Cache Lookup Data parameter to load the lookup table in memory. This second option is suitable for reasonably small tables (If is a tradeoff between the application memory load and the database query speed).
• Phonetic transformation: Avoid using custom metaphone functions. Use the Text plugin enricher with METAPHONE Phonetic Transformation instead.

Matching

Overmatch

A common cause of a performance bottleneck in the Matching phase is Overmatching.
Overmatching consist in creating large clusters of matching records. As an example, a 1000 records cluster means 1M matching pairs to consider (records in the DU table), making this cluster impossible to manage (manually or automatically).

...

If you run or profile the matching and/or binning rules using SQL, you can identify which part of the rule causes the issue. Another thing you can do is query the DU table and compare it to the MI table to see if there is overmatching. 

select STD_PRIMARY_PHONE, count(*) bin_size
from MI_CONTACT
group by STD_PRIMARY_PHONE
order by 2 desc

Solution

• Avoid using attributes containing default or placeholder values in binning or matching expressions. Null is always different from anything, so null values are usually not an issue.
  Typical causes of default/placeholder values: Replacing non-existing values by spaces, dummy or default values.
• Fix wrong data (for example: Replace placeholder value by an enriched value) or fix the rule to handle properly the placeholder/wrong data.

Match Rules Issues

The following issues are a common source of performance in the matching process.

• Using Transformations in Matching Rules
  Avoid functions transforming data (SOUNDEX, UPPER, etc. included) in match/binning rules.
  • Reasons:
    • May cause an issue on the Indexes. These functions is are performed for every time the record is compared.  
  • Solution
    • Materialize these values into attributes via enrichers
• Use Fuzzy Matching Functions with Care
  • Distance and Distance Similarity functions are the most costly functions.
    Sometimes, materializing a phonetized value with enricher then comparing with equality gives functionally equivalent results.
• Very Large Complex Rules
  
  • Avoid one big matching rule.
  • Each rule should address a functionnally functionality consistent with a set of attribute data.
• Avoid joining to parent entities
  • If you have large data sets, you may have very bad performance if your match rule looks up the parent entity to find attribute values for matching.  
  • For example, this match rule Record1.Account.CustomerNumber = Record2.Account.CustomerNumber took 4 hours to run for 1 million records. When we updated the child entity to enrich the CustomerNumber from the parent into an attribute on the child entity, the match time went down to 2 minutes. Another option is to use Record1.FID_Account = Record2.FID_Account if it satisfies business requirements.
• Consider Indexing
  • For very large volumes, adding an index on the significative significant columns involved in the binning, then one index for the columns matching rule.
    e.g.: 
    create index SUSR_<indexName> on MI_<entity> (<columns involved matching, with those having more distinct values first>, B_BATCHIDPUBID, B_BRANCHIDSOURCEID, B_CLASSNAME, B_PUBID, B_SOURCEID, <columns involved matching, with those having more distinct values first>);
    -- Remove BranchID for v4.0 and above);

  • Have you done truncate and full reload operations recently?
    One of the standard maintenance operations, in this case, is to rebuild all of the indexes with:

    alter index [index name] rebuild online; /* Oracle syntax */
    The code to do this for all indexes can be easily generated using a select on the user_indexes view.

  • To rebuild the indexes on the entire database in PostgreSQL, you can use the Postgres statement

    REINDEX DATABASE database_name; /* PostgreSQL syntax */

    
    If you need to just reindex a specific table you can use
    REINDEX TABLE table_name;  /* PostgreSQL syntax */

• Detect over matching possibilities:
  • To detect potential huge clusters, this query should let you know if your query needs to do more exact matching for binning to reduce cluster sizes when performing exact matches (replace the column names with the columns you are matching on):

    /* For SQL Server */
    select stdaddress1, stdzip_code, count(*) as cnt
    from mi_contact
    group by stdaddress1, stdzip_code
    order by count(*) desc

  • Use this query if you're comparing cluster sizes and total number of calls to SEM_EDIT_DISTANCE: 

    /* For SQL Server */
    select sum(cnt * cnt)
    from (
    select stdaddress1, stdzip_code, count(*) as cnt
    from mi_contact
    group by stdaddress1, stdzip_code
    ) derived
    
    

• SEM_EDIT_DISTANCE_SIMILARITY and SEM_EDIT_DISTANCE functions on SQL Server performs poorly (as of v5.2): 
  • If you are using the SEM_EDIT_DISTANCE_SIMILARITY or SEM_EDIT_DISTANCE functions in your match rules, it could be performing poorly on SQL Server. One of our customer migrating from PostgreSQL to SQL Server was facing huge performance regression during the migration (

    Jira Legacy
    server System JIRA serverId 7db41d32-f010-38fc-b966-a5c06dc46fba key SUPPORT-9524

    ).
  • The workaround he found is interesting:
    • He found a CLR procedure on the web for the Levenshtein distance calculation (in this case, this one : https://github.com/DanHarltey/Fastenshtein)
    • He has compiled the c# code in a dll and install it on the SQL Server database
    • He has created a database function with this script :

      
      

      Code Block

      sp_configure 'clr enabled',1 RECONFIGURE EXEC sp_configure 'show advanced options',1; RECONFIGURE; EXEC sp_configure 'clr strict security',0; RECONFIGURE; CREATE ASSEMBLY FastenshteinAssembly FROM 'E:\INTEND_INSTALL\CLR\Fastenshtein.dll' WITH PERMISSION_SET = SAFE CREATE FUNCTION SEM_LEVENSHTEIN_CUST(@value1 [nvarchar](MAX),@value2 [nvarchar](MAX)) RETURNS[int] AS EXTERNAL NAME[FastenshteinAssembly].[Fastenshtein.Levenshtein].[Distance] GO

      
      

    • Then, he has declared SEM_LEVENSHTIEN_CUST in xDM and has used it in the match rules, instead of native SEM_EDIT_DISTANCE function.
    • Results : the Matching rule execution is completed in a few seconds instead of many minutes (for only 4 000 records)

• SEM_NGRAMS_SIMILARITY function on SQL Server performs poorly (as of v5.0):
  • If you are using the SEM_NGRAMS_SIMILARITY function in your match rules, it could be performing poorly on SQL Server due to the lack of support for native matching functions in SQL Server. Hilaire came up with an alternative version of this function that should allow you to process the same number of calls roughly 10 times faster (same algorithm, but less nested function calls and optimized code)

  • Compile the function and note that there is the USR_ prefix. Call the function USR_SEM_NGRAMS_SIMILARITY directly in the match rules.

    Code Block

    /* For SQL Server only: 10x faster execution of the SEM_NGRAMS_SIMILARITY on SQL Server */ create or alter function [dbo].[USR_SEM_NGRAMS_SIMILARITY] ( @P_STRING1 nvarchar(max), @P_STRING2 nvarchar(max), @P_NGRAM_LEN int = 2 ) RETURNS int AS BEGIN DECLARE @C1 int, @C2 int, @RES int DECLARE @P_TAB1 SEM_SPLIT_TBL, @P_TAB2 SEM_SPLIT_TBL INSERT INTO @P_TAB1 SELECT * from [dbo].SEM_SPLIT_NGRAMS(@P_STRING1, @P_NGRAM_LEN); Set @C1 = @@ROWCOUNT INSERT INTO @P_TAB2 SELECT * from [dbo].SEM_SPLIT_NGRAMS(@P_STRING2, @P_NGRAM_LEN); Set @C2 = @@ROWCOUNT select @RES = case when @C1 = 0 or @C2= 0 then 0 else cast(2.0 * 100 * count(*) / (@C1 + @C2) as int) end from @P_TAB1 L1 inner join @P_TAB2 L2 on L1.STR = L2.STR RETURN @RES; END GO

    
    

  • To estimate the time required to execute the step, you can compute the theoretical number of calls to SEM_NGRAMS_SIMILARITY using the binning expression of your first matching rule based on SEM_NGRAMS_SIMILARITY as follows:

    select sum (CNT * CNT) as NB_CALLS 
    from (
    select STD_PRIMARY_PHONE, count(*) as CNT
    from MI_CONTACT
    group by STD_PRIMARY_PHONE
    ) derived

Issues in Other Certification Phases

...

• Symptom: "ORA-01467: Sort Key too long" issue
  
  • Solution: alter session set "_windowfunc_optimization_settings" = 128; in session initializing for the connection pool in the datasource configuration.

  • Example:

    Code Block
    language xml
    <Resource name="jdbc/SEMARCHY_MDM" username="DATA_LOCATION_USER" password="DATA_LOCATION_PASSWORD" url="jdbc:oracle:thin:@rdmagdev.lcke3gss.eu-west-1.rds.amazonaws.com:1521:ORCL" auth="Container" factory="org.apache.tomcat.jdbc.pool.DataSourceFactory" type="javax.sql.DataSource" driverClassName="oracle.jdbc.OracleDriver" maxActive="8" maxIdle="8" minIdle="0" maxWait="15000" initialSize="1" defaultAutoCommit="false" validationQuery="select 1 from dual" testOnBorrow="true" logValidationErrors="true" timeBetweenEvictionRunsMillis="45000" validationInterval="60000" initSQL="alter session set "_windowfunc_optimization_settings" = 128" />

    
    

Publish Certified Golden Data

...

Applications reflect sizing issues that may exist in the application server or database tiers. Make sure to review the Server Configuration and Sizing section before proceeding.

Collections

Slow Collections

...

Collections slow to display may have many causes, inluding:

• Filtering & Sorting
  • Design-time or user-defined filters and sort operations tax the database.
  • For large datasets, consider disabling user filtering and Sorting if not strictly necessary. When filtering/search, make sure to enable search methods functionally useful and reasonable for the performances (e.g: Full text on an entity with a lot of columns is not a good idea).
• Number of different entities involved to compose the collection (looking up data through the references)
  • Number of computed columns
    • Using PL/SQL or Using SemQL
      Optimize SemQL or PL/SQL code.

  Business Views

  ...

  • • Looking up for values in other entities through the references means as many database joins when accessing the data.
  • Computed columns
    • These columns typically use PL/SQL (PL/pgSQL) or SemQL code to compute their values.
    • Make sure to optimize the expressions and code. For example, avoid retrieving data using cursors instead a set-based approach. 

  Business Views

  Business Views or forms slow to display may have many causes, including: 

  • Slow Embedded Collections (see previous paragraph)
  • To many Embedded Collections Too many embedded collections in the form : when a view is displayed, each embedded collection generate a SQL call to the database. So try to move not necessary Moving embedded collections in tabsto secondary tabs as transitions should be considered then.

  Custom Search

  Filter data by using a custom Search can be costly in many cases : 

  • Using FDN_ columns instead of FID_ columns in the SemQL condition
  • Using function transforming data (SOUNDEX, UPPER, etc. included) may cause an issue on the Indexes
  • To complex SemQL condition with .

  
  

  Others TIPS

  ORACLE statistics

  Be sure that the ORACLE stat gathering are not turned off in the Semarchy process.

  LOGGING

  Turning off logging can really speed up processing. But that means that no Semarchy activity event is logged.

  Explain Plans

  Collect an explain plan and analyze it to understand if there is a query that is taking too long.

  1. Identify the step that is taking a very long time from the integration job logs. 
  2. Get the query. 
  3. Run an explain plan in your SQL Client.
     1. For Oracle Explain Plan.  
     2. For PostgreSQL Explain Plan.
  4. Send to Semarchy support for help analyzing the explain plan performance.