<div dir="ltr">On Tue, Nov 19, 2013 at 9:13 AM, Mike James <span dir="ltr"><<a href="mailto:mjames@profitpoint.com" target="_blank">mjames@profitpoint.com</a>></span> wrote:<br><div class="gmail_extra"><div class="gmail_quote">
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div style="font-size:14px;font-family:Calibri,sans-serif;word-wrap:break-word">
<div>I am using slony1 log-shipping to replicate some data to a remote location thru a VPN tunnel. A cron job running every minute applies the logs to the remote slave. So the latency is at least one minute. Is there a way to more precisely measure the latency
between the origin and slave?</div>
<div></div></div></blockquote></div><br></div><div class="gmail_extra">I'd say that things are less deterministic than that, unfortunately.</div><div class="gmail_extra"><br></div><div class="gmail_extra">The expected latency is actually more like 30 seconds, though there are cases that could induce arbitrarily more.</div>
<div class="gmail_extra"><br></div><div class="gmail_extra">"Why 30s?" is interesting...</div><div class="gmail_extra"><br></div><div class="gmail_extra">Consider the scenario where there are generally small sets of changes being applied, and replication is keeping up to date nicely. Suppose, further, there is one SYNC generated every second.</div>
<div class="gmail_extra"><br></div><div class="gmail_extra">With your "apply once per minute" approach, consider when logs are being applied at the top of the minute. There are 60 logs there, one from a minute ago, one from 59 seconds ago, and so forth. There will be one log that is just 1s old, so that the "latency" for that log will be just 1s, considerably better than 1m. On average, the latency, across the 60 logs, will be 30s.<br>
<br>This indicates that the number you're measuring as "1 minute latency" isn't an minimum (some logs will get applied almost instantly after generation, if you're lucky), or a mean (the mean is likely around 30s, probably a little more), but rather you are describing a "worst case latency." At worst, usually, you expect latency to be 1 minute.</div>
<div class="gmail_extra"><br></div><div class="gmail_extra">Unfortunately, that's not nearly the end of the story. By a suitable pattern of updates, one may induce arbitrary increases to that "worst case latency." </div>
<div class="gmail_extra"><br></div><div class="gmail_extra">It's pretty easy to do: I just have to set up a transaction on the origin that does a large amount of replicable work. </div><div class="gmail_extra"> BEGIN; UPDATE some_replicated_table SET [some change] WHERE [lots and lots of tuples are affected]. COMMIT;</div>
<div class="gmail_extra"><br></div><div class="gmail_extra">That set of work will go into the next SYNC that is generated immediately after the COMMIT. Supposing the set of updates takes 5 minutes to query from sl_log_{1,2} and assemble into a log, and it takes 10 minutes to load that on the remote slave, then we have just induced a latency of 15 minutes. (That it's 5 minutes and 10 minutes for those activities is purely made up on my part; it should nonetheless be easy to believe that if you COMMIT a transaction that touched a million tuples in replicated tables, it will take a while to process that. Increase the number of tuples as needed to get whatever result worries you most!)</div>
</div>