Those numbers are on table level .. seen on Oracle .. fast environment, perfect network, SSD storage.
On virtulized enviroments (both masking and DB running on same VMware) I usually see above 2million rows/min.
The combined performance rows/minute (e.g. using parallel STREAMS) is obviously higher .. streams work well as they never cause contention on DB level.
Parallel Update threads have a high risk to cause contention (locks/deadlocks) - depending on DB platform.
Oracle has possibly the best transaction isolation to allow parallel updates however we found out that using parallel jobs instead (on same table) with the above mentioned where clause optimisation scales up at almost 100% - till you reach saturation of resources. So in an optimised environment you may run one job with "many small tables" .. plus lets say 4 jobs on same ruleset (copies) containing the very large tables (each having the where clause optimization) > you can run those 5 jobs in parallel to get the masking done as fast as possible without contention.
A performance of 50k rows/minute is not good and has usually reasons: for example the update fires triggers or indexes .. or the hardware is very poor .. the VM is over provisioned .. or the masking access the DB via WAN instead LAN / poor latency. Be careful about having SQL customisations like where conditions on columns that are not indexed / joins that cause poor performance. There are plenty of possibilities - but in almost 6 years of doing performance optimistation there were maybe 5 cases where the masking engine caused the poor performance. The majority of cases are related to indexes and performance issues caused by the DB config/resources.
Original Message:
Sent: 09-01-2023 08:25:25 AM
From: Kevin Bott
Subject: Performance experiences
Under optimum conditions I have seen performance reaching 6million rows/minute.
I would consider everything above 1 million rows/minute as good.
Everything below 500k rows/minute is not very good and to be investigated.
Can you add more context to those numbers? Is that "total" for a database? Or are those per thread/stream rates?
The worst I've seen (after optimizations) was 50k rpm per thread. BUT that was on Itanium2 hardware from ~2010. Thankfully that has been migrated to modern hardware.
------------------------------
Kevin Bott
Sr. Database Architect
Northwestern Mutual Life Insurance Company
Original Message:
Sent: 09-01-2023 03:09:44 AM
From: Tino Pironti
Subject: Performance experiences
The performance of masking is depending on 3 core factors (ordered in descending priority):
1) database server performance (depending on Disk IO, CPU, Memory, Indexing, Transaction logging ...)
2) network performance (bandwidth and especially latency!)
3) masking performance (depending on amount of masked columns per table, algorithms, protocol used, parallelism used)
Under optimum conditions I have seen performance reaching 6million rows/minute.
I would consider everything above 1 million rows/minute as good.
Everything below 500k rows/minute is not very good and to be investigated.
The most common reasons for poor performance are:
- Indexes on masked columns not being dropped
- DB server lacking resources (CPU, Mem, Disk IO)
The network latency has a significant impact, most people underestimate this.
It is relative rare that the masking engine is the cause of performance issues.
Depending on the platform there are varying options to improve, the best options exist for Oracle by using parallel jobs with SQL optimisation (rowid in combination with blocknumber to avoid contention)
When masking a table with 300 columns and 150 columns being masked it is understandable that the performance is limited.
Therefore we prefer to talk about troughput in MB/minute instead rows/min.
Minimum throughput is considered 500MB/min .. but usually significantly higher.
Parallel updates are a good option to improve performance for ON THE FLY jobs (don't use this option for IN PLACE jobs!).
If a database is optimised for READ/INSERT an INPLACE masking job is NOT a good idea.
This applies as well to platforms like HADOOP etc >> those are designed for SELECT/INSERT and not UPDATE.
If performance is a critical factor you may choose Delphix Hyperscale orchestration > this approach avoids the SQL (JDBC) interface
and uses instead DB platform specific BULK unload/load mechanisms that allow much higher performance by bypassing the SQL bottleneck.
Hyperscale extracts the data, then splits the "files" across a group of Delphix masking engines, and loads the masked files back into the database.
------------------------------
Tino Pironti
Masking SME
Technical Manager
Delphix
Original Message:
Sent: 08-31-2023 01:50:22 AM
From: Philipp Guehring
Subject: Performance experiences
Hi! Could anyone who is using Delphix Data Masking with several different platforms (e.g. Oracle, DB/2 zSeries, DB/2 iSeries, SQL Server, ...) please give some rough estimations on the average number of rows per second they are achieving during masking on these platforms?
------------------------------
Philipp Guehring
Test Env user
UniCredit Group
------------------------------