Delayed durability and impact on mirroring

Objective

To Test the delayed durability feature with mirroring in high performance and high safety mode. The idea is to confirm what the performance improvement is of the transaction and if it has any benefit in high latency networks.

Test Scenario

We have two databases configured with mirroring in high safety mode, which will require that commits happen at the secondary first. One database called “DalayedTEST” has delayed durability enabled with FORCED mode. Then I have configured performance counters to check latency and performance of each database each second, I have added mirroring, transaction, lock and physical disk counters so we can compare the performance when using delayed durability or not in a mirrored environment. Then we are going to switch mirroring to high performance and see its behaviour as well. No Witness is configured

Using SQLQueryStress tool I am loading 5000 transactions to a log table with 50 threads enabled. This is equivalent to 50 persons loading 5000 records to the same table at the same time. Should be a good test to analyze behaviour of the databases.

Normal Database Test (Full Transactions) High Safety Mode

Counters

Mirroring

Locks and Transactions

Physical Disk

Failover

A failover happens 30 seconds after starting the test. The database is able to failover and record count 98850.

Delayed Database Test (Delayed Transactions) High Safety Mode

Counters

Mirroring

Locks and Transactions

Physical Disk

Failover

A failover happens 30 seconds after starting the test. The database is able to failover and record count 165757.

Conclusion – High Safety Mode

Mirroring High Safety Mode

Notes:

Having delayed transactions enabled in a database with high safety mirroring improves performance under high contention scenarios. In this case having lots of transactions running at the same time and requesting for the same table object proved to be better, as the execution time was faster when using delayed transactions.

When checking the mirroring behaviour one can see that the mirroring log queue is bigger with delayed transactions. The difference is that with full transactions the queue is processed sequentially, which decreases the queue while the transaction completes. As a result, you will see a high spike at the beginning of the stress test which is the full queue , and then it decreases in time. When using delayed transactions one can see spikes spread evenly every 20 seconds or so, this means that one batch is processed, and then other batch is processed, and this process carries on until the queue is empty.

So having a sequential mirror queue processing vs. batch processing makes a difference in latency? It is clear that transaction delay is 3 times worse with delayed transactions as it will processes batches in a cyclic way, which will saturate more at the endpoint on every iteration and also keep the transaction waiting for a commit acknowledgement from the mirroring partner. So, having delayed transactions with high contention, it is faster locally as it has less logical reads/writes, but slows down the mirroring communication as it works in batches.

But total execution time was better with delayed transactions?

Having delayed transaction improves execution time but increases mirroring delay, so it is simply a matter of doing the math. The local processing was so much better that it compensated for the slower mirroring transaction delay and in general terms is faster in this scenario (I used a local network), however if you add other variables to the stress test, such as a high latency network for the mirroring, the end result might not be compensated and the total execution time can end up being around the same or worse.

Now, let´s see the numbers with a high performance mode:

Normal Database Test (Full Transactions) High Performance Mode

Counters

Mirroring

Locks and Transactions

Physical Disk

Delayed Database Test (Delayed Transactions) High Performance Mode

Counters

Mirroring

Locks and Transactions

Physical Disk

Conclusion High Performance Mode

Mirroring High Performance Mode

Notes:

Having delayed transactions means we have a database with high performance mirroring that is not improving performance, despite the fact that indicators show less locking and less logical reads. Seems the mirroring queue was not being handled properly. The mirroring queue is considerably bigger when using delayed transactions and despite the fact that we are working in high performance mode the general execution time is the worst in this test environment. After I realized this I ran other tests to see if the results were the same, and generally speaking the total execution time is almost the same, a difference of 4-10 seconds tops.

General Conclusion

When using delayed transactions in all the tests we can conclude that consistently the mirroring queue, and the amount of data transferred is considerably larger than the one produced by full transactions.

When using high safety mode, it seems delayed transactions have a better disk performance, which compensate the higher mirroring/network values and results in a faster execution time. This situation can change if the network has high latency producing the same or worse performance.

When using high performance mode it seems delayed transactions have an ok to good disk performance, but it is not good enough to compensate the higher mirroring/network values, and results in the same or slower execution time.

I would recommend enabling delayed transactions only to fix high contention rates. Also avoid using it in high latency or very busy networks when mirroring high performance environments. These recommendations are based on a small test and in a controlled environment and should not necessarily extend to all environments out there. The rule of thumb is that using delayed transactions does impact mirroring performance, which by itself is a technology known to introduce certain performance issues depending on the system, so do not take the decision to use delayed transactions lightly and test first in a staging environment before rolling into production.

Discover more about our expertise in SQL Server.