Yet we do know that there exists an impact. If it was suggested to place the application tier of a performance critical application on one continent and the database on another there would be an immediate concern about latency. We can measure the latency of a network connection using network tools, but this alone does not give us real data about what effect that latency will have on user experience.
I decided to delve into this a little further, to see first how we might measure the issue and then later to use those measurements to get a better understanding of what impact latency has.
Gathering the data
The tests were executed on three separate databases, each with the same hardware configuration and init.ora parameters. The first database, Local, was on the same machine as the test scripts. The second, LAN, was on a high performance local LAN which is expected to have minimal amounts of latency. The third, WAN, was over a Wide Area Network which has an expected latency of 10-12ms.
The test scripts were written in Perl, using the DBD::Oracle package. The timings were taken using the Time::HiRes package, which delivers timings in microseconds. The time measured was from the start to the end of each iteration.
SELECT 2000 rows, Fetch size 4 rows
|SELECT 2000 rows, fetch size 4, timings (s)|
SELECT 2000 rows, Fetch size 20 rows
|SELECT 2000 rows, fetch size 20, timings (s)|
SELECT 2000 rows, Fetch size 200 rows
|SELECT 2000 rows, fetch size 20,0 timings (s)|
SELECT 1 row, multiple executions
In this test the script executes a single SELECT statement returning one row from a table containing only one row. Each execution includes the statement parse. Along with the execution and fetch steps this is a total of three network To/From pairs per execution.
This test cuts down as much as possible any other factors than just network latency. For the WAN measurements only the first 500 results, reporting 1500 round trips, are shown. This was done purely to keep a reasonable scale for the chart.
As might be expected, there is a linear progression between the number of network packets and the elapsed response time. Even though the Local connection should show only a negligible latency the times become significant when there are very large numbers of interactions with the database. In the LAN test the drop in response time is steeper. The difference between the LAN and Local timings would probably not be significant for most applications, but where there are design faults which lead to large amounts of network traffic it could be an issue. With the WAN configuration the drop-off in response time becomes very significant quite quickly.
But do the tests reasonably match with the expected results? Although for Local and LAN there is no fixed expected latency the WAN configuration has, as I stated at the start, an expected round trip latency of 10-12 ms. Below are the figures for the difference between Local, LAN and WAN timings.
|Delta times between configuration tests (s)|
Network latency and network performance between the application tier and database tier can be a significant factor in response times. It has an effect that can be measured and these measurements can be used to feed information into business cases for improvements in infrastructure to reduce latency or the re-factoring of applications to reduce network round trips.
The second topic, that of application re-factoring, involves more than just network latency. It also includes other overheads that can have a far larger impact on overall performance. In a future blog I will return to the topic of how you might apply these measurements in real applications.