Re: Point at which WAN latency affects perceived app response?

> Does anyone have any experience with the effect of WAN latency on
> applications accessing an Oracle database over a WAN?
>
> We are using Oracle Server 9ir2 (9.2.0.7.0) as our back end database
> for a packaged 2-tier client/server business application. The
> development tool and network APIs for the application are fairly
> obscure, but they must touch on SQL*Net eventually as the Oracle client
> is required.
>
> We have two sites. Each has an Oracle database for the portion of the
> app that serves that site's business function. But of course, people
> from A need to access the primary application for B, and people from B
> need to access the primary application for A.
>
> To this point we have served the cross-site requirements by installing
> both sets of software at both sites, and simply accessing the Oracle
> data directly across the WAN. Brutal and perhaps not elegent, but
> simple and straightforward (particularly compared to an experiment with
> Citrix).
>
> We are now in the process of replacing our T1 leased line between the
> sites with an MPLS-based network. Each primary site A and B has a 3
> Mbps MPLS port. There are a few other minor sites that connect, none
> that run the business apps.
>
> Bandwidth on the MPLS connection is good: transferring large files of
> random numbers I see close to full port thruput between the sites.
> E-mail, web, etc work fine.
>
> But when we attempted to bring up the Oracle-based apps the results
> were not so good. Perceived performance got worse by a factor of 4,
> and stopwatch timings confirm that the new configuration is slower by
> at least a factor of 3.
>
> Looking at ping times, I find the following for the two different
> connections:
>
> Packet size Leased Line MPLS
> =========== =========== =========
> 256 6 ms 48 ms
> 512 11 ms 55 ms
> 1024 17 ms 69 ms
> 2048 26 ms 84 ms
>
> Now, clearly less latency is better than more latency. Still, 60 ms
> latency between sites is considered quite good on most WANs of any size
> and I would think most Oracle users would be in that range or higher.
> Would 60 ms be sufficient to cause the application to appear to the end
> users as noticably slow?
>
> I have been trying to watch the connection with Ethereal, and one thing
> I noticed was that there seem to be a lot of fragemented packets in the
> 100-300 byte range. Other tools confirm that the non-fragmented MTU
> for the link is 1500 bytes (same as the leased line). Does it make
> sense that either the server or the router would break larger packets
> up that small?
>
> My knowledge in this area is like the Platte River (wide and shallow),
> so any input would be appreciated.
>
> sPh
>

Hi,

We had some problems with issues like this a year back when we were transferring an applications from a LAN to a WAN (our latency went from 0/1 to 7/8 ms). I must say the idea of moving to a latency of such a change based on the experience we have had is a cause for concern and you are right to question it.

In the cases we had, both an Oracle Forms and an MS Access front end application doing thousands of "tiny" network packets to communicate with the Oracle Databases were going awfully slow on the new WAN.

We actually used etherpeek on the test client systems with the problems, one of the main keys in our investigation was the "number" of network packets transmitted during the application activity and we had to find a method to reduce this. In effect (with some room for movement) the number of network packets multiplied by the latency was the delay we faced the before and after on the transfer from LAN to the WAN.

We tested changing the Oracle Arraysize, this had a positive impact but the problematic applications could not use it. We tested with SDU/TDU server & client changes, this had a tiny impact but nothing that could assist with the real problems as we were looking at. Note : thousands of tiny packets and a larger MTU/TDU/SDU would mean little as we were sending 95% of network packets already smaller than 1.5K and small packets seems to be your issue from what you have written.

In the end the MS Access application was repaired by converting external table based queries into a single Oracle View and also amending the Prefetch count to 20000 on the ODBC settings which had the effect of a network buffer.
For the Oracle Forms application a re-write of the application was required to remove the tiny looped database lookups (sigh).

In both cases after the changes the applications performed much better on the old LAN than the same copy moved onto the new WAN but this was similar to the older unchanged application performance and thus was acceptable to the business.

Basically latency is a killer if your application is not WAN friendly test, test and if in doubt test.

I hope the information helps and good luck with it.

Regards,
Ian Received on Fri Sep 15 2006 - 15:25:41 CDT