locked
MIrroring Hurting Performance RRS feed

  • Question

  • Hi all,
    I have a problem where synchronous mirroring without witness is causing a lot of system overhead.
    The db has a heavy number of inserts and deletes frequently due the which the log is heavy. This is hosted on SQL server 2005 standard edition(x64).
    All these transaction are very slow with the mirroring on, but become very fast when mirroring is paused.
    Another issue is that after I have recently re setup the mirro, the mirror server seems to take a lot of time to commit the logs resulting iin high unrestored log there.
    It is a 4 physical processor server. Would any change in affinity mask help me by any chance?
    Does anyone have any clue about these problems? Are they product limitations or a possible bug?

    Thanks in advance!

    Regards
    Akhil
    Thursday, June 18, 2009 4:35 PM

Answers

  • I don't know how I'd never discovered that Standard Edition doesn't have asynchronous mirroring - "MSDN Forums - where you learn something every day" :)

    Quality of Service is a networking concept that allows you to prioritise some types of traffic over others, to try to guarantee a level of performance.  For example, Voice Over IP (VOIP) telephone traffic is important, otherwise your conversation will be lagged and the person on the other end will sound jittery.  Email, web, or file transfer traffic, on the other hand, can be delayed by an extra second or two - no one will notice.  Synchronous mirroring is in the same category as VOIP traffic - any delays, and your primary system will slow down.  That's about the limits of my QoS knowledge - you may have to talk to your networking guys to find out if they can prioritise your mirroring traffic.

    You can roughly see how much traffic you are sending across the network by looking at the size of the transaction logs.  If you're taking log backups at 15 minute intervals, and the log backup is 100 MB uncompressed, then you're sending 100 MB every 15 minutes from the database alone, which may not be sustainable on your link. 

    And, as Aaron mentioned, what is the ping time from one server to another?  That's the minimum time for the request to be sent and received again, not including any processing time on the mirror.

      
    • Marked as answer by Mac_dba Wednesday, June 30, 2010 1:29 PM
    Thursday, June 25, 2009 2:16 AM
    Answerer

All replies

  • You didn't mention how capable your mirror server is....

    Are your servers connected by a very fast network connection?  You may be experiencing network latency.  Ping the mirror from the principal - a transaction will never be delayed by less than the time it takes for a round-trip ping.


    Aaron Alton | thehobt.blogspot.com MCITP: Database Administrator
    Friday, June 19, 2009 12:52 AM
  • My apologies for missing out on the detals.

    Both the servers are equal 3.06 Ghz Intel 5100 series, 8 GB RAM.

    The servers connected through a Wide Area Network via T! connection of 3 Mbps.

    I have checked that the mirrored server is not loaded in terms of resources since this is the only database that is experiencing such heavy transactions, so I dont find too many reasons thhat it would be the server itself.

    One interesting thing through, is that I saw a Wait Type of Network_Async_IO wait type on the insertion and deletion process in the activity monitor, while troubleshooting. The wait mosly seems to go away when mirror is paused.

    The problem of mirroring slowing down transactions has existed since long, but the problem of unrestored log is fairly recent.

    Friday, June 19, 2009 6:00 AM
  • If both servers are identical, with identical disk configuration and database file layouts, then it's most likely network related, especially if your most common wait type is Network_Async_IO.  That explains why the wait type goes away when the mirror is paused - you're not waiting on the transaction to be committed on the mirror.

    If this has only just started to be an issue, it's possible something has changed on the network.  Do you have a Quality of Service set up to prioritise mirroring traffic?  Other options could be to compress the network, either using networking technologies or SQL Server 2008.

    Alternatively, you can run in high performance mode, but you've mentioned that the synchronisation is now taking a while, so this may mean your mirror may start to fall behind.

    Modifying Affinity settings would only be necessary if you were suffering from a lack of CPU resources.
    Monday, June 22, 2009 12:29 AM
    Answerer
  • Thanks for your reply.

    I am not really sure what is meant by Quality of Service and to my knowledge, I don't think I have anything like that implemented here.
    Could you please let me know how to get that done.

    I am using SQL Server 2005 Standard (x64), so I dont think I have the option of asynchronous mirroring ( i did thing about it earlier).
    Wednesday, June 24, 2009 10:41 AM
  • I don't know how I'd never discovered that Standard Edition doesn't have asynchronous mirroring - "MSDN Forums - where you learn something every day" :)

    Quality of Service is a networking concept that allows you to prioritise some types of traffic over others, to try to guarantee a level of performance.  For example, Voice Over IP (VOIP) telephone traffic is important, otherwise your conversation will be lagged and the person on the other end will sound jittery.  Email, web, or file transfer traffic, on the other hand, can be delayed by an extra second or two - no one will notice.  Synchronous mirroring is in the same category as VOIP traffic - any delays, and your primary system will slow down.  That's about the limits of my QoS knowledge - you may have to talk to your networking guys to find out if they can prioritise your mirroring traffic.

    You can roughly see how much traffic you are sending across the network by looking at the size of the transaction logs.  If you're taking log backups at 15 minute intervals, and the log backup is 100 MB uncompressed, then you're sending 100 MB every 15 minutes from the database alone, which may not be sustainable on your link. 

    And, as Aaron mentioned, what is the ping time from one server to another?  That's the minimum time for the request to be sent and received again, not including any processing time on the mirror.

      
    • Marked as answer by Mac_dba Wednesday, June 30, 2010 1:29 PM
    Thursday, June 25, 2009 2:16 AM
    Answerer