| Groups | Blog | Home |
sql server replication : Bad replication performance in SQL2k5 on links with high latency
I tested replication performance on slow network channels, for SQL2k and
SQL2k5. And quite disapplointed by SQL2k5 results... Network channels were simulated, ping time 200 ms between distributor and subscriber, unlimited bandwidth. I tested how many transactions replication can deliver to subsciber. All transactions are nearby same, they represent typical transactions in my system. Publisher, distributor and subsciber are all located on different computers. In order to check capabilities of hardware, on each of the computers I run tests to check speed of how many transactions per second it may insert into database with turned on replication. For each computer, result was ~112 transactions per second, both for SQL2k and SQL2k5. Next I started to measure replication delivery rate, how many transactions per second distributor will be able to deliver to subscriber. In SQL2k, replication in such condition was able to deliver 9 transactions per second. The best result was obtained with 1 sec polling interval for log reader and distribution agent, 64kb packet size for distribution agent. No more differences from default profile. In case if turn off network delay, rate of inserting transactions on publisher become same as rate of delivering - replication start to deliver ~112 transactions per second. For SQL2k5, results were around 5 transactions per second. The best result was obtained on 1 sec polling interval for log reader and distribution agent, 64kb packet size for distribution agent, 3 SubscriptionStreams. No more differences from default profile. If turn off network delays, it again deliver at same rate as it inserting on publisher, about 112 transactions per second. Network delay simulator was tested several times to check is it really simulate network delays as expected. All tests showed correct work of network delay simulator. For simulating network delays, was used Shundra Cloud. The question is - Is the replication in SQL2k5 really lost performance in comparison with SQL2k? Is anything can be done to improve replication
the distribution agents in SQL 2005 has autosense network link technology
built into them. They will close down when they detect the link going down and start up again when it is reconnected. I suspect you are bumping into this. I would open a support incident with MS to see if you can disable this behavior or troubleshoot it further. -- Hilary Cotter Looking for a SQL Server replication book? http://www.nwsu.com/0974973602.html Looking for a FAQ on Indexing Services/SQL FTS http://www.indexserverfaq.com [quoted text, click to view] "andsm" <andsm@discussions.microsoft.com> wrote in message news:D153A24B-63DF-45A6-A4B4-22A00221CA29@microsoft.com... >I tested replication performance on slow network channels, for SQL2k and > SQL2k5. And quite disapplointed by SQL2k5 results... > Network channels were simulated, ping time 200 ms between distributor and > subscriber, unlimited bandwidth. > I tested how many transactions replication can deliver to subsciber. All > transactions are nearby same, they represent typical transactions in my > system. > Publisher, distributor and subsciber are all located on different > computers. > In order to check capabilities of hardware, on each of the computers I run > tests to check speed of how many transactions per second it may insert > into > database with turned on replication. For each computer, result was ~112 > transactions per second, both for SQL2k and SQL2k5. > Next I started to measure replication delivery rate, how many transactions > per second distributor will be able to deliver to subscriber. > In SQL2k, replication in such condition was able to deliver 9 transactions > per second. The best result was obtained with 1 sec polling interval for > log > reader and distribution agent, 64kb packet size for distribution agent. No > more differences from default profile. > In case if turn off network delay, rate of inserting transactions on > publisher become same as rate of delivering - replication start to deliver > ~112 transactions per second. > For SQL2k5, results were around 5 transactions per second. The best result > was obtained on 1 sec polling interval for log reader and distribution > agent, > 64kb packet size for distribution agent, 3 SubscriptionStreams. No more > differences from default profile. If turn off network delays, it again > deliver at same rate as it inserting on publisher, about 112 transactions > per > second. > Network delay simulator was tested several times to check is it really > simulate network delays as expected. All tests showed correct work of > network > delay simulator. For simulating network delays, was used Shundra Cloud. > > The question is - Is the replication in SQL2k5 really lost performance in > comparison with SQL2k? Is anything can be done to improve replication > delivery rate in SQL2k5?
But network, during the tests, is not goes down - it is always work, at
constant ping time. [quoted text, click to view] "Hilary Cotter" wrote:
> the distribution agents in SQL 2005 has autosense network link technology > built into them. They will close down when they detect the link going down > and start up again when it is reconnected. I suspect you are bumping into > this. > > I would open a support incident with MS to see if you can disable this > behavior or troubleshoot it further. > > -- > Hilary Cotter > > Looking for a SQL Server replication book? > http://www.nwsu.com/0974973602.html > > Looking for a FAQ on Indexing Services/SQL FTS > http://www.indexserverfaq.com > > > > "andsm" <andsm@discussions.microsoft.com> wrote in message > news:D153A24B-63DF-45A6-A4B4-22A00221CA29@microsoft.com... > >I tested replication performance on slow network channels, for SQL2k and > > SQL2k5. And quite disapplointed by SQL2k5 results... > > Network channels were simulated, ping time 200 ms between distributor and > > subscriber, unlimited bandwidth. > > I tested how many transactions replication can deliver to subsciber. All > > transactions are nearby same, they represent typical transactions in my > > system. > > Publisher, distributor and subsciber are all located on different > > computers. > > In order to check capabilities of hardware, on each of the computers I run > > tests to check speed of how many transactions per second it may insert > > into > > database with turned on replication. For each computer, result was ~112 > > transactions per second, both for SQL2k and SQL2k5. > > Next I started to measure replication delivery rate, how many transactions > > per second distributor will be able to deliver to subscriber. > > In SQL2k, replication in such condition was able to deliver 9 transactions > > per second. The best result was obtained with 1 sec polling interval for > > log > > reader and distribution agent, 64kb packet size for distribution agent. No > > more differences from default profile. > > In case if turn off network delay, rate of inserting transactions on > > publisher become same as rate of delivering - replication start to deliver > > ~112 transactions per second. > > For SQL2k5, results were around 5 transactions per second. The best result > > was obtained on 1 sec polling interval for log reader and distribution > > agent, > > 64kb packet size for distribution agent, 3 SubscriptionStreams. No more > > differences from default profile. If turn off network delays, it again > > deliver at same rate as it inserting on publisher, about 112 transactions > > per > > second. > > Network delay simulator was tested several times to check is it really > > simulate network delays as expected. All tests showed correct work of > > network > > delay simulator. For simulating network delays, was used Shundra Cloud. > > > > The question is - Is the replication in SQL2k5 really lost performance in > > comparison with SQL2k? Is anything can be done to improve replication > > delivery rate in SQL2k5? > >
I would open a support incident with Microsoft on this one.
-- Hilary Cotter Looking for a SQL Server replication book? http://www.nwsu.com/0974973602.html Looking for a FAQ on Indexing Services/SQL FTS http://www.indexserverfaq.com [quoted text, click to view] "andsm" <andsm@discussions.microsoft.com> wrote in message news:D9A495E2-6AD2-480E-B78F-A999A3781FC6@microsoft.com... > But network, during the tests, is not goes down - it is always work, at > constant ping time. > > "Hilary Cotter" wrote: > >> the distribution agents in SQL 2005 has autosense network link technology >> built into them. They will close down when they detect the link going >> down >> and start up again when it is reconnected. I suspect you are bumping into >> this. >> >> I would open a support incident with MS to see if you can disable this >> behavior or troubleshoot it further. >> >> -- >> Hilary Cotter >> >> Looking for a SQL Server replication book? >> http://www.nwsu.com/0974973602.html >> >> Looking for a FAQ on Indexing Services/SQL FTS >> http://www.indexserverfaq.com >> >> >> >> "andsm" <andsm@discussions.microsoft.com> wrote in message >> news:D153A24B-63DF-45A6-A4B4-22A00221CA29@microsoft.com... >> >I tested replication performance on slow network channels, for SQL2k and >> > SQL2k5. And quite disapplointed by SQL2k5 results... >> > Network channels were simulated, ping time 200 ms between distributor >> > and >> > subscriber, unlimited bandwidth. >> > I tested how many transactions replication can deliver to subsciber. >> > All >> > transactions are nearby same, they represent typical transactions in my >> > system. >> > Publisher, distributor and subsciber are all located on different >> > computers. >> > In order to check capabilities of hardware, on each of the computers I >> > run >> > tests to check speed of how many transactions per second it may insert >> > into >> > database with turned on replication. For each computer, result was ~112 >> > transactions per second, both for SQL2k and SQL2k5. >> > Next I started to measure replication delivery rate, how many >> > transactions >> > per second distributor will be able to deliver to subscriber. >> > In SQL2k, replication in such condition was able to deliver 9 >> > transactions >> > per second. The best result was obtained with 1 sec polling interval >> > for >> > log >> > reader and distribution agent, 64kb packet size for distribution agent. >> > No >> > more differences from default profile. >> > In case if turn off network delay, rate of inserting transactions on >> > publisher become same as rate of delivering - replication start to >> > deliver >> > ~112 transactions per second. >> > For SQL2k5, results were around 5 transactions per second. The best >> > result >> > was obtained on 1 sec polling interval for log reader and distribution >> > agent, >> > 64kb packet size for distribution agent, 3 SubscriptionStreams. No >> > more >> > differences from default profile. If turn off network delays, it again >> > deliver at same rate as it inserting on publisher, about 112 >> > transactions >> > per >> > second. >> > Network delay simulator was tested several times to check is it really >> > simulate network delays as expected. All tests showed correct work of >> > network >> > delay simulator. For simulating network delays, was used Shundra Cloud. >> > >> > The question is - Is the replication in SQL2k5 really lost performance >> > in >> > comparison with SQL2k? Is anything can be done to improve replication >> > delivery rate in SQL2k5? >> >> >>
Don't see what you're looking for? Try a search.
|
April 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
October 2008
| home · blog · groups | Questions? Comments? Contact the dn |