Database Mirroring - performance

• Andy sql

  SSCrazy Eights

  Points: 9497

  More actions

  March 11, 2008 at 11:10 am

  #179972

  I am running some benchmarking tests on a new Database Mirror. I have set up a High Safety Mirror with automatic failover (synchronous) and default encryption; the Principal and Mirror are physical servers and the Witness is a VMware virtual server. All MSSQL Servers are Enterprise Edition with SP2 (to be precise, the Principal and Witness are 9.00.3042 and the Mirror is 9.00.3054).

  I have read through the Microsoft TechNet article "Database Mirroring Best Practices and Performance Considerations", which suggests that running in High Safety should only add a relatively small overhead, perhaps as much as 50%, depending on the transaction. The article specifically says that bulk insert, index creation and index rebuild all generate a high log workload and thus high workload for the Mirror.

  Using the AdventureWorksDW database as the basis of the Mirror, I created a very simple table with 3 columns (int, nvarchar(50) and nvarchar(50)). There is no index, no primary key. I then execute a loop which inserts a new row. Setting the loop to insert 5000 rows takes 40-50 seconds. Inserting 1000 rows takes 10-12 seconds. Which I think is pretty slow...

  CPU usage on all 3 servers remains below 5%, and network utilisation below 1%; all servers have generous amounts of free physical RAM. To try and figure out where the problem lies, I ran Performance Monitor (object: Database Mirroring) against both the Principal and the Mirror:

  Principal

  ---------

  "Bytes Sent/sec" reaches a max of 280,000 Bytes/s

  "Sends/sec" reaches a max of 420.

  "Transactions/sec" reaches a max of 140.

  "Transaction Delay" reaches a max of 900 (not sure what the unit is, as the docs don't bother saying! I guess it is milliseconds).

  "Receives/sec" reaches a max of 550.

  "Send/Receive Ack Time" reaches a max of 460 (in a great example of doublespeak, this is measured in "Milliseconds packets waited in flow control per second.")

  "Log Bytes Flushed/sec" reaches a max of 82,000 Bytes/s.

  "Log Send Queue" remains at 0.

  "Redo Queue" remains at around 0 (a max of 3KB is measured at one point).

  Mirror

  ------

  Mostly a direct reflection of the Principal (i.e. Bytes Send on the Principal equate to Bytes Received on the Mirror)

  "Redo Queue" and "Log Send Queue" both remain at 0.

  "Redo Bytes/sec" reaches a max of 68,000.

  "Send/Receive Ack Time" reaches a max of 130.

  To compare this with a non-synchronously-mirrored database, I changed the mode to High Performance (asynchronous) and re-ran the insert script. It takes 3 seconds to insert 5000 rows. With no mirror on the database, the insert takes 2 seconds. According to my figures, a High Safety Mirror is 25 times slower than no mirror.

  Is this just an unfair test of Mirroring? If I were to do a bulk insert as a single transaction, I imagine that it could be a lot faster.

  Does anyone have ideas about where I should be looking to identify the bottleneck? Or am I simply hitting hard limits of the Mirror (surely not!) ????

  Any help appreciated,

  Andy