Author: Sanjay Mishra

Reviewers: Prem Mehra, Mike Ruthruff, Michael Thomassy, Peter Byrne, Kaloian Manassiev, Burzin Patel, Juergen Thomas, Tom Davidson

Overview

This is the second part of the article “Database Mirroring Log Compression in SQL Server 2008 Improves Throughput” (http://sqlcat.com/technicalnotes/archive/2007/09/17/database-mirroring-log-compression-in-sql-server-2008-improves-throughput.aspx). The first part focused on synchronous database mirroring; the focus of the second part is asynchronous database mirroring.

With asynchronous database mirroring, committing a transaction doesn’t wait for the log records to be sent to the mirror. Transactions are committed once the log records are written to the log disk on the principal. Therefore, with asynchronous database mirroring, you will not observe significant impact on throughput resulting from the log compression feature.

With asynchronous database mirroring, log compression helps reduce the send queue. In this study, we took a customer workload (an application at a stock exchange that captures stock quotes and orders), with a high log generation rate (12 MB/sec) and ran performance tests with a pre-release build of SQL Server 2008. The results indicate reduced send queue with log compression.

Test Workload and Test Environment

The workload used for the tests described in this paper consists of a 20 GB database, and 20 concurrent, active user connections.

The test hardware consisted of 2 Unisys x64 servers with 16 processors and 64 GB RAM each. The storage was EMC Clariion SAN. The network between two servers is 1 Gbps, and we used network emulation software to simulate varied network bandwidth between the two servers.

The SQL Server 2008 prerelease build 10.0.1068.0 was used.

Asynchronous database mirroring was used for these tests.

Reduced Send Queue with Log Compression

With asynchronous database mirroring, transactions are committed once the log records are written to the log disk on the principal. The log records are then asynchronously sent to the mirror. Therefore, there could be some log records at the principal that have not yet been sent to the mirror. Unsent log that has accumulated on the principal is known as the send queue. This is reflected in the perfmon counter “Send Queue KB” of the “Database Mirroring” object.

The principal could be generating transaction log records at a rate faster than it can send these log records to the mirror over the network. Under lower bandwidth networks, the send queue can be more pronounced. With asynchronous database mirroring, the send queue represents the data that can be lost if the principal goes down. Therefore, reducing the send queue means reducing the potential data loss.

Compressing the log stream in SQL Server 2008 allows more transaction log records being packed in each packet send to the mirror, and thereby reduces the send queue.

With SQL Server 2008, the log stream is compressed by default. To measure the impact of log compression, we used the trace flag 1462, which disables log compression. Disabling log compression is equivalent of SQL Server 2005 behavior. Figure 1 illustrates the average send queue with and without log compression at various values of network bandwidth.

Figure 1: Reduced send queue with log compression

As shown in Figure 1, the log compression results in lesser send queue compared to disabling log compression.

Log Compression Ratio

Log compression ratio indicates the factor by which the log stream has been compressed. One way to express the log compression ratio is to divide the perfmon counter Log Bytes Sent/sec by the counter Log Compressed Bytes Sent/sec. Another way to express the log compression ratio is to compute it as a percent using the following formula:

(Log Bytes Sent/sec- Log Compressed Bytes Sent/sec)*100 / (Log Bytes Sent/sec)

Figure 2 illustrates the percent log compression ratio obtained in our test workload.

Figure 2: Log compression ratio

The compression ratio depends upon the application and the data it processes. The compression ratio is not an externally configurable parameter; it is an inherent property of the data. As you can observe in Figure 2, the percentage compression achieved in our test workload is around 73% -- the network bandwidth has no impact on the compression ratio.

The transaction log records generated by our test workload provide a very high percent compression ratio – 73%. Not all applications will exhibit as much compression. If the amount of compression achieved is less than 12.5%, then the compressed log stream will not be sent to the mirror; instead the uncompressed log stream will be sent. Sending the uncompressed log stream means that the mirror doesn’t need to uncompress the log stream it received, thereby saving some CPU resources (CPU cost of log stream compression is discussed in the next section) on the mirror.

Please note that while deciding whether to send the compressed log stream or the uncompressed log stream, SQL Server computes the percentage compression on a per packet basis – it doesn’t use the perfmon counters shown in Figure 2. The perfmon counters reflect the aggregated average of log bytes sent over time.

Cost of Log Compression

The benefits of log compression come with some processing cost. The log records are compressed on the principal before being sent to the mirror, where they are uncompressed before being applied to the mirror database. The extra tasks of compressing and uncompressing the log adds some processing overhead on the principal and mirror respectively, resulting in higher CPU usage. Figure 3 illustrates the CPU usage of the principal and mirror servers at various network bandwidths, with and without log compression.

Figure 3: CPU Usage with log compression

As illustrated in Figure 3, the CPU utilization with log compression is slightly more than that without log compression.

As discussed in the earlier section “Log Compression Ratio”, if the compression ratio achieved is less than 12.5%, then the uncompressed log stream will be sent to the mirror. In that case, the principal will incur the CPU overhead, but the mirror will not.

Time to Flush the Send Queue

With asynchronous database mirroring over a low bandwidth network, when you execute a log intensive task (a task that generates huge amount of transaction log), it may take a while for the send queue to die down after the task has finished. You could see the send queue rise during the log intensive task, and then coming down after the task is finished. If the send queue is monotonically increasing during the normal operations, then you may be having severe network capacity (network latency and/or network bandwidth) limitations for the given workload.

Log compression helps not only in reducing the send queue at any given time, it also helps reducing the time it takes for the send queue to die down after a log intensive task. Figure 4 illustrates the time it took to flush the send queue after a log intensive task over a 10Mbps network, with and without log compression. As it is obvious from Figure 4, the log compression feature in SQL Server 2008 drastically reduces this time.

Figure 4: Time to flush the send queue with 10 Mbps network bandwidth

Summary

SQL Server 2008 introduces a new feature that compresses the log stream sent from the principal to the mirror in a database mirroring configuration. Compressing the log stream with asynchronous database mirroring results in reduced send queue. Log compression causes the CPU utilization to increase on the principal as well as the mirror. The reduction in send queue as well as the increase in CPU load are both dependent upon the workload and the network capacity. It is recommended that you test with appropriate workload to estimate the expected impact of log compression in your environment.