Skip to main content
Delphix

Guidelines for Setting the Commit Size (KBA1026)

 

 

Setting the Commit Size 

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

Commit Size: Default Value

For most masking jobs the default parameter doesn't need to be changed. The best is to leave this value blanks (default). 

Commit Size: When does this value need to be changed? 

 The optimal Commit Size value depends on:

  • Database Type
  • Database Performance and Configuration
  • Masking Job Configuration

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

  • The resources will fill up depending 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. 
  • The commit Size might need to be set smaller to avoid deadlocks.
  • Performance:
    • A large number might increase performance.

Commit Size: Effects  

How does the value affect the masking job?

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

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

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 - lower will here degrade performance and higher will increase the Transaction Log size. 

Calc_Job_Config_Commit%2BSize_ExecTime_TransL_Size.png

User Interface

Masking Job Configuration Popup  

The Job Configuration is set when the Masking Job is:

  • Created; or
  • Edited

The Default (blank) value is 10,000 rows between each commit. To change this value, open the Job Configuration popup and type a new value and save. 

Masking UI - Job Configuration - Commit Size highlighted.png