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

Default value

The default value (10,000) is for most masking jobs the optimal value and does not normally need to be changed. For some versions, the value will be blank which is the same as 10,000. 

Impact on performance 

The graph below shows the impact of Commit Size on Performance and what is the Sweet Spot.  

The Sweet Spot is 10,000 and it can be safely reduced without a larger negative impact on performance (between 500 to 50,000 is normally safe). If the Commit Size is increased, usage of resources (such as Transaction Log on SQL Server) increases, as does the chance of job errors such as deadlock. 

Notes:

• The Transaction Log size is linear - since the Y-axis is linear and the X-axis is logarithmical the graph will show this spike at the end.
• For the Performance (rpm) - the performance when Commit Size is 1 is in this example 42 times slower than peak performance. 

Illustration 

The best way to illustrate the Commit Size and the Row Limit is to show the masking job's interaction with a database. The illustration below is a simplified view and shows a masking job in progress.

Databases store records in blocks or pages (there can be other terms), these are illustrated by the rectangles. The white ones contain records that are not yet masked. The grey ones are records that are read but not yet updated. These are buffered in the masking engine and the max number of records that can be in the engine is (from version 6.0.4 forward) controlled by the Row Limit feature.

The Batch Update and Commit Size controls the Update (IP) (Insert for OTF). The Batch Update enables the JDBC to send the data in a batch and the Commit Size defines how many rows are in each batch before it is committed. 

Effects

The effects of changing Commit Size (as also seen above):

• Smaller Commit Size:
  • Reduces the number of resources required on the database. 
  • Degrades the performance.
  • Reduces the chance of errors (such as deadlocks).
• Larger Commit Size:
  • Increases the number of resources required in the database.
  • No or little impact on performance. 
  • To eliminate Unique Constraints if this can not be dropped/disabled. 

When to change?

This is a guide to when this value might need to be adjusted: 

• Resources fill up - reduce Commit Size:
  • 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. 
     
• Reduce the chance of deadlocks - reduce Commit Size. 
   
• Resolve Unique Constraint error - set Commit Size to the same size as the number of rows in the table. 
  • If the constraint cannot be dropped/disabled this might be the only option. 
     
• Performance - a smaller or larger value might improve performance.
  • Test and plot to determine optimal value.

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). 

Effects

The effects of changing Row Limit:

• Smaller Row Limit:
  • Reduces the amount of data in the engine per masked object. 
  • This reduces the chance of Out of Memory. 
  • Could also reduce the chance of errors (such as deadlocks).
• Larger Row Limit:
  • Could increase the chance of Out of Memory.
  • Could increase performance. 

When to change?

This is a guide to when this value might need to be adjusted:

• Masking Out Of Memory errors - reduce the Row Limit.
  • This is especially important on Large file masking jobs where each row has a large number of characters.
  • Large OTF DB jobs might also benefit from a reduced Row Limit.
  • It might be better to look at the size of each row - does all that data need to be transferred?
  • Start dropping the value by 10 times. 
     
• Performance - larger value might improve 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. 
     
• Performance Investigation - see below for more information for specific investigation settings. 
   
• Locks and Deadlocks:
  • Note: for MS SQL, check KBA6319 (see below for link).
  • A lower value could potentially reduce the chances of locks. 

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. 

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 (in worst-case scenarios crash the engine). 

Warning:

If Feedback Size is set too small on large tables. This can cause very large logs and can cause the engine to crash.