Skip to main content
Delphix

KBA1026 Masking Commit Size Guidelines

 

Guidelines for setting the Commit Size

The Commit Size determines how many rows are updated/inserted to the database before the transaction is committed. The default Commit Size is 10,000 and is represented as blank value in the Masking Job GUI.  

Default Value - usually optimal  

For most masking jobs the default parameter doesn't need to be changed. A change might improve the performance, at most a few percent.

When does this value need to be changed?

If Commit Size needs to be changed. What decides what value to use depends on:

  • Database Type
  • Database Configuration
  • Masking Job Configuration

 

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

  • The resources will fill up, this depends on Database Type and Database Configuration:
    • 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. 
       
  • Commit Size might need to be set smaller in order to avoid deadlocks
     
  • Masking Job configuration - for example:
    • No. of Streams, this value defines how many tables are masked at the same time. With many streams the logfile can fill up quicker. 
Effects 

How is the value affecting the the masking job;

  • Performance vs resources:
    • Smaller Commit Size: Increases the commit frequency and:
      • degrades the performance.
      • reduces the amount of resources required on the database. 
    • Larger Commit Size: Decrease the commit frequency and:
      • increase the amount of resources required on the database.
      • will slight degrade the performance. 

The best is to run a few Commit Size values and plot the run time to determine which value is optimal. 

For the Database Configuration, please consult your database administrator to determine the most suitable value for your site.

Masking Job Configuration Popup 

Example of Performance vs Resources - 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. 

Calc_Job_Config_Commit Size_ExecTime_TransL_Size.png