The 19c DML redirection is one of the key features in Active Data Guard 19c. This means that a lot of questions are coming to my e-mail for this one.
This blogpost will indicate how the 19c DML redirection feature works together with the Oracle Active Data Guard Far Sync instance and how this impacts performance.
The 19c New features guide, which you can find here, describes this feature as follows:
Incidental Data Manipulation Language (DML) operations can be run on Active Data Guard standby databases. This allows more applications to benefit from using an Active Data Guard standby database when some writes are required.19c new features guide
DML redirection helps in load balancing between the primary and standby databases. When incidental DML is issued on an Active Data Guard standby database, the update is passed to the primary database where it is executed. The resulting redo of the transaction updates the standby database after which control is returned to the application.
A few days ago I received an e-mail, that someone wanted to test this feature with a decent read/write workload on the Standby database.
Why is this not a particularly good idea? To answer that, we need to dig a little bit into both technologies. So buckle up! Here we go.
Depending on the swingbench profile to be run, this is NOT what 19c DML redirection has been designed for.It has been designed for read mostly and occasional DML.
Let me explain what happens.
It goes in 5 steps:
- The Client issues a DML against the Read Only Standby Database
- The standby notices it is DML and sends this DML towards the primary database using an internal Db-link
- The primary executes the DML (which then generates redo)
- This redo is a normal redo stream and together with the normal redo stream this is sent to the standby database
- The standby database applies the received redo stream and releases the lock on the session so the session can see the result.
Having that said, it is clear that running Swingbench on the standby will definitely have poor performance as actually all DML is performed on the primary (extra load on the primary AND the network). So if you want to run a load test, I would strongly recommend to do that against the primary database.
This is the normal situation with the standby close to the primary.
If we add the Far sync instance, the same principle applies.
The standby sends the redo to the primary which executes it.
Due to the nature of far sync, this far sync instance communicates in SYNC mode with the primary and ASYNC mode with the standby. For example, when a FS instance receives let’s say sequence# 10 from the primary, it will NOT send it immediately to the standby, but it will wait until sequence# 11 has been received before sending sequence#10. This way, we can guarantee you zero data loss at any distance, with a minimal impact on the production system.
If no redo has been received in a timely manner, the primary sends a kind of dummy-redo every second to the far sync instance so the far sync can forward the redo which it has received earlier.
Concluding on this all, means that when you add a far sync instance, it can slow down the performance a little bit for DML redirection but it should not slow down a lot.
As always, questions, remarks?
find me on twitter @vanpupi