Skip to main content
Delphix

Masking Commit Size, Feedback Size and Row Limit (KBA6387)

 

KBA

KBA# 6387

At a Glance  

Available in versions Commit Size, Feedback Size: All versions
Row Limit: From 6.0.4.0
Commit Size Commit Size sets the batch size in UPDATE and INSERT step. It sets the number of records between Commits. 
  • Default: 10,000
  • Adjust only as directed. 
Row Limit Row Limit sets the number of records that will be fetched in a SELECT on each call from the Record Set. 
  • Default: 20,000
  • Adjust only as directed. 
Feedback Size Feedback Size has no performance impact. It will set the frequency of writing a log entry into the logs.
  • Default: 50,000
  • Increase when the number of masked records is large.
  • Set to 10,000 when collecting performance statistics (run for around 5 min). Then reset the value.

Applicable Delphix Versions

Click here to view the versions of the Delphix engine to which this article applies
Major Release All Sub Releases
6.0 6.0.0.0, 6.0.1.0, 6.0.1.1, 6.0.2.0, 6.0.3.0, 6.0.3.1, 6.0.4.0

5.3

5.3.0.0, 5.3.0.1, 5.3.0.2, 5.3.0.3, 5.3.1.0, 5.3.1.1, 5.3.1.2, 5.3.2.0, 5.3.3.0, 5.3.3.1, 5.3.4.0, 5.3.5.0 5.3.6.0, 5.3.7.0, 5.3.7.1, 5.3.8.0, 5.3.8.1, 5.3.9.0

5.2

5.2.2.0, 5.2.2.1, 5.2.3.0, 5.2.4.0, 5.2.5.0, 5.2.5.1, 5.2.6.0, 5.2.6.1

5.1

5.1.0.0, 5.1.1.0, 5.1.2.0, 5.1.3.0, 5.1.4.0, 5.1.5.0, 5.1.5.1, 5.1.6.0, 5.1.7.0, 5.1.8.0, 5.1.8.1, 5.1.9.0, 5.1.10.0

5.0

5.0.1.0, 5.0.1.1, 5.0.2.0, 5.0.2.1, 5.0.2.2, 5.0.2.3, 5.0.3.0, 5.0.3.1, 5.0.4.0, 5.0.4.1 ,5.0.5.0, 5.0.5.1, 5.0.5.2, 5.0.5.3, 5.0.5.4

4.3

4.3.1.0, 4.3.2.0, 4.3.2.1, 4.3.3.0, 4.3.4.0, 4.3.4.1, 4.3.5.0

4.2

4.2.0.0, 4.2.0.3, 4.2.1.0, 4.2.1.1, 4.2.2.0, 4.2.2.1, 4.2.3.0, 4.2.4.0 , 4.2.5.0, 4.2.5.1

4.1

4.1.0.0, 4.1.2.0, 4.1.3.0, 4.1.3.1, 4.1.3.2, 4.1.4.0, 4.1.5.0, 4.1.6.0

Row Size Settings

There are three Row Size settings on Masking Jobs. The following parameter settings define the number of rows between each action:

  • Row Limit - SELECT Fetch records.
  • Commit Size - Records in each batch between commits.
  • Feedback Size - the frequency of log entries in the log files.

Commit Size

The Commit Size determines how many rows there are in an Update or Insert batch. It is the number of records sent to the database between transaction commits. 

 The best Commit Size value depends on:

  • Database Type.
  • Database Performance and Configuration.
  • Masking Job Configuration.
  • To avoid Locks or Deadlocks.
  • Unique Constraints (if no other options are available).

Default value

For most masking jobs the default value is optimal and does not need to be changed. The default value is blank (10,000). 

When to change?

The following examples offer times when the value might need to be adjusted: 

  • The resources will fill up:
    • MS SQL: the 'logfile' size will determine how many updates/inserts can be held before a commit. 
    • Oracle: the undo segment will fill up. 
    • Other databases have different requirements and limitations. 
  • The 'Commit Size' might need to be set smaller to avoid deadlocks.
  • Performance:
    • A large number might increase performance.
  • There are some unique cases identified by Support or Technical Services. 

Effects

How does the value affect the masking job?

  • Performance vs Resources:
    • Smaller Commit Size:
      • Increases the commit frequency:
        • Reduces the number of resources required on the database. 
        • Degrades the performance.
    • Larger Commit Size:
      • Decrease the commit frequency:
        • Increases the number of resources required in the database.
        • No or little impact on performance. 

Consider running a few jobs with different Commit Size values and plotting the run time to determine which value is optimal. Often, the best way forward is to leave this value unchanged. If needed, consult with Delphix support or Technical Services. 

Example - MS SQL

On MS SQL we can clearly see the performance hit when Commit Size is too small and the Resource hit when the Commit Size is too large. The sweet spot here is 10,000 - lower degrades performance and higher increases the Transaction Log size. 

Masking Optimal Commit Size ExecTime Transaction Log Size.png

Feedback Size

Default value

For most masking jobs the default value is optimal and does not need to be changed. The default value is blank (50,000). 

When to change?

The Feedback Size should be adjusted in these scenarios: 

  • If the number of masked rows is large, then increase the Feedback Size to 500,000 or even 5,000,000.
  • Performance testing - when specifically collecting data for performance tuning - set this value to 10,000.
    • The job execution should be around 5 min (or too many logs are created).
    • Make sure to set the Feedback Size back to the original value. 

The Feedback Size defines how frequently logs are written to the log files. These values are a guideline and one way to determine the size is that the logs should preferably fit into one log file. 

Database Size Number of Records Feedback Size
Performance Test - 10,000
Small to medium ~ 5,000,000  50,000 (default)
Large Up to 500,000,000 500,000
Very large Over  500,000,000 5,000,000

Effects

This value does NOT affect performance. It is only related to how frequent logs are written to the logs. Values set too low will cause a large number of logs to be collected, which could ultimately affect the masking engine. 

Row Limit

The Row Limit is a new feature (first available in 6.0.4.0). This value determines how many rows are fetched from the database SELECT record set per network call. 

The best Row Limit value depends on:

  • Database Type.
  • Database Performance and Configuration.
  • Masking Job Configuration.
  • Avoiding Deadlocks.

Default value

For most masking jobs the default value is optimal and does not need to be changed. The default value is blank (20,000). 

When to change?

This is a new parameter and the value has been increased on Oracle (from 10 to 20,000). You may need to lower this value.

Here are some examples when this value might need to be adjusted: 

  • Performance
    • If the performance is bottlenecked on SELECT. The adjustment depends on the current value and the root cause. 
    • Network latency has an impact here. 
  • Locks and Deadlocks
    • A lower value could potentially reduce the chances of locks. 

Effects

This value will affect performance and the behavior of the masking job. Often, the best way forward is to leave this value unchanged. If needed consult with Delphix support or Technical Services. 

User Interface

Masking Job Configuration Popup   

The Job Configuration is set when the Masking Job is either:

  • Created
  • Edited

Example

The image below is from a 6.0.4.0 engine. The fields detailed in the KBA are highlighted with their default values. These are normally blank when a job is created.  

Masking UI - Commit, Feedback, Row Limit.png

Issues

Known issues are:

  • Row Limit (Fetch Size) set too low on Oracle causing performance issues on low latency networks. Bug DLPX-69851 and fixed in 6.0.4.0.
  • Feedback Size cause logs to be too large and slow start of masking jobs. Bug DLPX-68842 and fixed in 6.0.2.0.

Resolution

Most solutions to any issue related to these configurations is normally to set the value to its default value.