Best Practice: Masking Performance (Database) (KBA1561)

Startup Time 

This is a silent performance killer as it is before we see any masked rows. It is the time it takes to prepare masking algorithms:

• Cause: Large Secure Lookup and Mapping Algorithms.
  • Remedy: Try to use a smaller number of lookup values in these algorithms. 

Read Performance 

The is the SELECT statement that reads the data from the database. Mostly, this is a minor factor in the whole masking operation. 

• Cause 1: Large number of columns with large data objects.
  • Remedy: Use Custom SQL to reduce the amount of data read from the database (ex. SELECT 1 as MaskCol....)
• Cause 2: Use of complex Custom SQL.
  • Remedy: If used, simplify the Custom SQL as much as possible. Look out for nested SELECT statements.
• Cause 3: Network performance - especially latency. 
  • Remedy: Check with your Network team to improve latency. 

Transformation Performance 

This is usually a key part in performance investigation. The key factors here are: 

• Cause 1: The number of fields masked together with Algorithms used.
  • Algorithms with fewer lookups perform better. 
  • Remedy 1: Try to use simple algorithms with few lookups as possible.
  • Remedy 2: Lookout for Segment Mapping - use few segments and Numeric lookups. 
• Cause 2: Masking engine performance.
  • Remedy 1: Ensure sufficient number of CPUs on the masking engine. 
  • Remedy 2: Use Streams in the Job Configuration to perform masking of tables in parallel. 
    • DO NOT USE TO HIGH NUMBER AND ENSURE ENOUGH RAM TO USE IT.
    • WATCH OUT FOR DEADLOCKS.
• Cause 3: Java Garbage Collection (GC). 
  • Remedy 1: Ensure optimal algorithms (see Cause 1) and an appropriate amount of Job Memory.
  • GC will always happen. It is part of JAVA, but: 
    • Too much Job Memory = longer time to perform GC.
    • Too little Job Memory = more frequent GC. 

Update Performance 

This is another key aspect of performance: 

• Cause 1: Database Performance
  • Remedy: Ensure that the database has enough power to change all columns and rows with the masked data.
    • Factor 1: Storage IO 
    • Factor 2: CPUs
    • Factor 3: RAM to cache data.
• Cause 2: Indexes and Triggers on Masked Columns and Tables
  • Always look out for masked columns with indexes (in the Inventory and on the database) and Triggers (on the database). 
  • Remedy: In Job Configuration, select Drop Indexes and Disable Triggers. It might be needed to perform this using Pre-Scripts or on the database.  
• Cause 3: Index for Update statement 
  • A Unique Index is needed to look up the row in the Update statement. It should be Unique, Indexed, and not be masked. 
  • Remedy: It might be needed to create one if the normal Unique ID is being masked.
    • MS SQL: The masking engine will create DMS_ROW_ID.
    • Oracle: Use ROWID.
• Cause 4: Use parallelism in Job Configuration;
  • Remedy 1: Set Updates Threads to 2 or 4
  • Remedy 2: Set Streams to 2, 4 or 8.
  • Remedy 3: Run concurrent masking Jobs. 
  • Watch out for Deadlocks. 
• Cause 5: Network performance - especially latency. 
  • Remedy: Check with your Network team to improve latency. 
• Cause 6: Casting of values
  • Casting doesn't usually have an impact. If it happens on large objects and they are indexed, this can slow down the job significantly. 
  • Remedy: Check execution plan on the database. Make changes to improve casting and use of indexes. 

Startup Time - Duration 

For most of the time, this is not large enough to affect the overall performance of a masking job. The startup time depends on the algorithm used and the number of rows in the lookup data.

Masking Time - Duration 

Usually, the number of records to mask is fixed and as such, we need to consider how fast we can perform the masking operation - that is the number of rows per minute (rpm). Usually, this number is specified in hundreds of thousands or millions. 

What is acceptable performance depends greatly on how many fields are being masked and how many tables at the same time.

In most cases, good performance starts around 500,000 rpm, a normal optimal performance is around 1 million rpm (or 1 Mrpm). With a very fast database server, highly optimized networks and fine-tuned masking jobs, we have seen jobs running in the excess of 8 million rpm.

Masking Engine 

On the masking engine, make sure that the following items are as optimized as possible:

• Masking Server - raw power:
  • Ensure that the server has plenty of fast cores (CPUs) - it is better to have a few fast CPUs than many slow CPUs.
  • Lots of cache memory.
  • Memory.
     
• Masking Jobs - efficiency
  • Masking algorithms:
    • Fast algorithms or custom ones from Professional Services. 
    • Mask only key columns as each masking operation adds to the degradation of the performance.
  • Rule Set:
    • Select an index for row lookup (for Oracle and DB2 use rowid).
    • Be mindful of long nested Custom SQL statement and filters.
  • Job configuration:
    • Masking method: In Place
    • Batch Update - selected.
    • Bulk Data - not selected. 
    • Drop Indexes - selected or do this manually.
    • Disable Triggers and Constraints - selected.

Note:

Min, Max Memory, Commit Size and Feedback Size have not been selected above. Memory should only be defined to load the lookup values (more is NOT always faster), Commit Size is likely optimal or near optimal using 10,000 (default value), and Feedback Size is not affecting performance - it sets the logging frequency. .

• Job Configuration and parallelism: 
  • Update Threads - important: 
    • Defines the number of Update Statements running at the same time.
    • The database and the masking engine need to be able to handle the extra load.
    • Verify what works (2, 3, 4...).
    • Larger is not always the fastest - 1 can sometimes be the fastest. 
  • No. of Streams - important:
    • Defines the max number of Tables being masked at the same time.
    • The database and the masking engine need to be able to handle the extra load.
    • Verify what works (2, 3, 4...).
    • Larger is not always the fastest - 1 can sometimes be the fastest. 
  • Multiple Jobs - 
    • The job can be broken down into multiple jobs.
       
• Database Server  - raw power
  • Check with DBA for optimal settings for
    • Storage performance
    • RAM, CPU and Cache.
  • On Oracle: check AWR report for tuning.
  • For deep dive - run one or more SQL traces.
     
• Network Performance
  • Latency - minimize for small and large packets.
    • Use ping from database server to the masking server.
  • Hops - minimize the number of hops to the server. 
    • Use traceroute from database server to the masking server to check. 
  • Throughput - Use Delphix Engine CLI throughput test.
     
• Database Configurations
  • Turn off database logging.
  • For Oracle:
    • Provision with 3 x 5GB online redo logs (minimum) to avoid pause when transaction logs wraparound
    • Provision in NOARCHIVELOG mode to reduce transaction log IO. 
    • Always check initialization parameters inherited from the parent, remove any expensive checks or irrelevant settings:
      • DB_BLOCK_CHECKSUM default is TYPICAL (default)
      • DB_BLOCK_CHECKING default is OFF (default)
      • DB_LOST_WRITE_PROTECT default is NONE (default)
      • DB_ULTRA_SAFE default value is OFF (default)
      • PARALLEL_DEGREE_POLICY to AUTO
      • PARALLEL_MAX_SERVERS to default, which is PARALLEL_THREADS_PER_CPU * CPU_COUNT * concurrent_parallel_users * 5
      • PARALLEL_EXECUTION_MESSAGE_SIZE to maximum (32768)
      • Use non-blocking commits for Masking: 
        • COMMIT_WAIT=NOWAIT
        • COMMIT_LOGGING=BATCH