May 14, 2009 at 10:15 pm
We have setup many transactional and snapshot replications. Publisher has more CPU and RAM than the subscriber. Is it possible to have no latency in such scenario?
Please share your openions/experience about this.
What are the solutions for latency issue?
Thanks.
May 14, 2009 at 10:21 pm
I have not used replication but I don't see how you can get 0 latency between publisher and subscriber. The data needs to be updated in the source database, which means the transaction must be committed and written to the transaction log. The logreader has to read the log to identify which transactions need to be replicated, and these records written to the distribution database where it is then replicated to the target (subscriber) database. All this takes some amount of finite time, which is your latency.
May 14, 2009 at 11:34 pm
Thanks Lynn Pettis for your quick reply.
By default 30 seconds is the threshold.
I have increased it to 3 minutes. Still sometimes I get latency.
Thanks
May 15, 2009 at 7:30 am
What is the latency that you are getting. In my set up we get a latency of 2 to 3 sec. If your Distributor is sharing the same DB as your Publisher or Subscriber, you will get more latency if those servers are busy.
-Roy
May 15, 2009 at 7:35 am
Zero latency is impossible in the physical universe. I'm assuming you mean latency so small you can't measure it, which still wouldn't actually be zero.
It'll depend on the bandwidth of the connection and the amount of data going through it, more than just about any other single factor. Network latency is quite often a composite of distance, number of jumps on the route, and traffic load (since packets have to take turns).
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 15, 2009 at 7:43 am
what have you changed? And how much data is being replicated?
It depends on the transactions, but in most replication scenarios I've had, where we're moving 2-3 tables, we have seconds of latency. By the time you connect to the next server and view the data, or run a query, it's often there.
May 15, 2009 at 7:50 am
Thanks for responding.
I meant number of latencies. Not the time gap.
Many publications cross the 3 minutes threshold that we have set.
They cross the threshold many times in a day.
So we have large number of latencies.
May 15, 2009 at 11:21 am
Suresh B. (5/15/2009)
Thanks for responding.I meant number of latencies. Not the time gap.
Many publications cross the 3 minutes threshold that we have set.
They cross the threshold many times in a day.
So we have large number of latencies.
You need to be clearer in your concepts.
1. Latency is the time it takes to go between to points. So it IS a time gap!
2. As already expressed there is no such thing as 0 latency in the real world
3. Saying that you "have a large number of latencies" does not makes sense
Threshold is a limit you can cross or not. If your Agents are crossing the three minutes latency threshold you have to speed up your agents by ensuring your distribution agents can handle the load and your transaction log/lo-reader agent combination are correctly setup
Here are some tips:
1. Minimize the number of VLF on your database log files
2. Verify you agents are pooling at 5 secs or less
3. If with the above you can't get it any faster check for blocking or locking in the distribution database.
Good Luck
* Noel
May 15, 2009 at 11:48 am
Set up a tracer token as well, see where the times come back.
If you have specific publications, how much data is changing? It's possible that you might exceed the capacity of your links.
May 15, 2009 at 12:04 pm
One other thought to add to NoelD's suggestions, would be to use independent agents for those articles that need to be given "priority" in your replication scheme. To explain further, if you have 10 tables that you are replicating and 2 of those tables need to have their data replicated as quickly as possible then I would set up 3 publications, one for the non critical tables and 1 each for those critical tables. The publications for the critical tables should use independent agents. This will eliminate any bottleneck caused by the other non-critical data being replicated.
Now that I've said that, be careful as too many independent agents will cause you problems. It takes quite a few (not sure of the exact number) though.
Hope this is helpful.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
May 18, 2009 at 8:15 am
To maintain minimum latency, we need to identify the articals which will have more transactions, for those set of trasaction tables we need to set a different publisher apart from the regular tables which has min transactions.
Zero latency stands for mili seconds of operations to commit your changes to subscriber.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply