June 18, 2009 at 10:16 am
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.
Does anyone have any clue about these problems? Are they product limitations or a possible bug?
Thanks in advance!
Regards
Akhil
June 18, 2009 at 10:39 am
In synchronous mode, the commits must occur on the mirror before they can occur in the primary. So, if you do not have a very fast and stable network connection between the two servers you are going to suffer performance issues.
This can work very well if you have a fast and stable network, and a server that can process the transactions quickly. My guess is that you have a slow network connection and a mirror server that cannot process the transaction fast enough.
Can you switch over to asynchronous mode? Or, is this system being used as an HA/DR system?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 19, 2009 at 12:08 am
Thanks for your reply.
These servers are connected through WAN T1 connection, meaning they are geographically apart. Both are Invel Xeon, 8 GB RAM. The mirror is just a DR server
The principal server loaded, but just about 60%. The mirror server is not loaded at all since there is just this one DB that experiences heavy inserts and deletes.
We do have connection trouble at times but even when monitoring it actively, when everything seems to be going ok, the mirroring slows down deletes and inserts.
Another strange thing is I found a wait type of Network_Async_IO on this process even when mirroring does not really have too much to send.
The wait type mostly disappears when mirroring is paused.
If there is a network issue (just an assumption), how do I verify my NIC configuration for half/duplex connection?
June 19, 2009 at 9:55 am
With your setup - I would never set up mirroring in synchronous mode. With a geographically separated system you would need a dedicated network to make sure you could get the response times required for a DR server.
Your options are limited:
1) Upgrade the network to handle the load you are sending.
2) Change the mirroring to asynchronous mode.
With option 1 - you need to make sure you can complete the transactions on the mirror very fast. Any delays in sending the data, processing the request, or sending the response back to the prinicipal is going to show up in your application as a performance issue.
With option 2 - you run the risk of losing data on the mirror. Transactions that don't commit, are lost over the network, or don't get commited when the primary system crashes.
So, which of the two options above do you need. If this is for a DR scenario - you are not going to be able to use Option 2. That means you really need to upgrade the network to handle the load as well as making sure the network is very reliable.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply