May 12, 2011 at 12:44 am
Hello
I am having a performance problem with SQL 2005 mirroring. The two SQL servers are running in Windows Server 2003 (32 bit). The boxes have 8 CPU cores, 4GB of RAM and heaps of internal RAID 10 disk space. The network link between these servers is 1GB. The network link to the clients is 100MB.
These servers are dedicated solely to hosting a single database for a new system that we are about to deploy. The databases are mirrored in high availability configuration (synchronous transfers with a separate witness server). I did have encryption enabled in the endpoints but turned it off as part of my testing (which didn’t noticeably improve performance). The client application is developed in CS.NET and communicates with the database through the SQL Native Client.
The problem is that when mirroring is setup, the client application performs poorly. For example, an action that takes 5 seconds to complete with mirroring off, takes 45 seconds with mirroring on. This scenario is repeatable on completely different servers and workstations.
I have experimented with this and have found that performance improves significantly when I configure the client connection string to have no failover partner. This happens whether mirroring is on at the server or not. This surprises me as I would assume that the only reason that the client would want to communicate with the mirror server is if the principal was not contactable.
Can anyone shed some light on what’s happening and how I might go about fixing it?
Thanks in advance,
David
May 13, 2011 at 6:00 am
Hello,
A couple of thoughts. Where is your witness ? Is it on the same subnet as the Principal and Mirror? Try reproducing the problem without the witness. Also, take a look at the Perfmon counters: SQLServer: Database Mirroring* - lot of good counters there. That may give you more insight into what's going on.
May 13, 2011 at 11:38 am
1-Install the latest service pack for the SQL Server 2005
2-Is your network break after some interval ?
3-Have you define catalog in the connection String ?
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
May 16, 2011 at 12:48 am
Hello Craig
In production, the witness is in the same subnet as the principal and the mirror is in a different subnet (DR site). The link to this site is 1GB. However, in the test environment, all servers are in the same subnet. Both environments are displaying similar results.
As you suggested, I performed some more testing with the databases mirrored with no witness. This did not change the results from those that I posted previously. That is, no matter what happens at the server end, if the client is configured with a failover partner, performance drops considerably.
Can you suggest any specific Performance Monitor counters that I could look at to try to troubleshoot this problem?
Cheers,
David
May 16, 2011 at 1:01 am
Hello Syed
All servers in the production and test environments are running SP4 which I believe is the latest.
The performance problem happens straight away if the client is configured with a failover partner (doesn't matter how the servers are configured).
The database server and failover partner are defined in a custom XML file which is read by the client application.
Thanks,
David
May 16, 2011 at 1:49 am
I'm no expert on mirroring, but the issues you describe sound as though there is an issue with communications to the failover mirrored server.
I don't know for a fact that the client tries to contact the failover partner, but it's something I could imagine happening in order to check that both ends agree who is the principal. A delay in contacting the mirror could therefore cause the slowdowns you are witnessing (no pun intended).
May 17, 2011 at 6:45 am
d.payne (5/16/2011)
in a different subnet (DR site). The link to this site is 1GB.
When you say DR Site, does that mean across a WAN connection? If it is, check your latency. use ping -l4096 hostname to see what the latency looks like for 4k packets. Also , regarding the counters, take a look at:
http://msdn.microsoft.com/en-us/library/ms189931.aspx
for more detail on what each counter does. I suspect that if you monitor all servers at the same time, that may give you some insight.
May 17, 2011 at 8:27 am
Have you tried testing the mirroring in asynchronous mode without a witness? Same results?
You can then pull up the mirroring monitor, set thresholds for the unsent/unrestored log and you can then see if the mirroring simply can't keep up and if it is on the send or the receive. Off the top of my head I think you'll need to configure those thresholds on both instances.
By using synchronous, the app is taking longer to respond naturally because the transaction needs to be committed in both locations, but 45 seconds does seem a bit long.
Steve
May 17, 2011 at 1:28 pm
SQL Server Mirroring on Window 2003 servers can see all sorts of networking problems if you have installed Windows Server 2003 SP2, which auitomatically turns on something called the Scalable Networking Pack. It's a couple features added to WIndows Server 2003 in SP1, then automatically enabled when you install SP2.
Actually *all* SQL Servers can see networking problems because of that thing... random unexplained cluster failovers, 'network name no longer available' and 'connection forcibly closed by remote host' errors, etc. It affects network cards with the Broadcom 5708 chipset, but that nails a huge swath of the marketplace.
Mirroring is especially vulnerable.
It must be disabled with a couple registry hacks. You can see some of the articles detailing it, along with various kbase and SQLCAT articles discussing the 'feature' by searching technet:
-Eddie
Eddie Wuerch
MCM: SQL
May 18, 2011 at 8:19 am
Was that a typo? Does your server really have only 4 GB of RAM? Do you have the 3 GB parameter set or using AWE?
My minimum reccommendation for SQL Server is 2 GB RAM per CPU with a preference of 3 GB per CPU. At minimum, a 8 CPU server should have 16 GB RAM.
My expectation is that you are reaching a resource limitation on memory that is emphasized by the 2-phase commit of mirroring.
And yes, I am a mirroring expert. 😉
May 18, 2011 at 11:09 am
Robert Davis (5/18/2011)
Was that a typo? Does your server really have only 4 GB of RAM? Do you have the 3 GB parameter set or using AWE?My minimum reccommendation for SQL Server is 2 GB RAM per CPU with a preference of 3 GB per CPU. At minimum, a 8 CPU server should have 16 GB RAM.
My expectation is that you are reaching a resource limitation on memory that is emphasized by the 2-phase commit of mirroring.
And yes, I am a mirroring expert. 😉
Don't listen to Robert - he's just a hack! 😀
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 18, 2011 at 11:42 am
Robert Davis (5/18/2011)
And yes, I am a mirroring expert. 😉
I hope this isn't seen as hijacking the post, but as Robert is a mirroring expert, I would be interested to hear his opinion on this part of the OP's post (for my own education as much as David's)...
I have experimented with this and have found that performance improves significantly when I configure the client connection string to have no failover partner. This happens whether mirroring is on at the server or not. This surprises me as I would assume that the only reason that the client would want to communicate with the mirror server is if the principal was not contactable.
May 18, 2011 at 1:19 pm
The client does not check the failover partner unless it fails to login to the principal. Can you post a repro?
May 18, 2011 at 8:10 pm
Hello
In response to Eddie Wuerch, I had a look at the relevant registry settings on all servers in both the production and test environments and found that these were already set to disable the Scalable Networking Pack feature.
In response to Robert Davis, that is not a typo; the server has 4GB (four gigabytes) of RAM. This is a 32 bit box so we can't fit more RAM into it. Also, we don't have the 3GB or AWE feaures enabled.
As I stated previous, it doesn't matter how the servers are configured. I've tried all possible combinations of mirroring (high availability, high protection, high performance) and none of these has an impact on the results that I am seeing. The _only_ thing that makes a difference in performance is how the _client_ application is configured.
Also, something that I should have mentioned earlier (apologies), the poor performance can also occur when only a read operation is performed (the figures of 5 seconds and 45 seconds that I quoted earlier are timings of the client populating a picklist by reading data from the database). I would expect that for read operations, the 2-phase commit of mirroring would not come into play.
Also, I have not ruled out the possibility that the problem is being caused by something that the client application is doing. As such, I have contacted the developer of the system and explained the situation to them. They said that they will setup mirroring in their environment and try to replicate what I am seeing. Will let you know the outcome of this.
Cheers,
David
May 18, 2011 at 9:36 pm
Yes, the 2 phase commit would not affect a read operation.
What access protocol are you using? (SNAC, ODBC, JDBC?) Can you share your connection string (with any sensitive data obfuscated) for both with and without the mirror designated?
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply