This was first published on https://blog.dbi-services.com/database-cloud-service-performance-network (2015-09-13)
Republishing here for new followers. The content is related to the the versions available at the publication date
In previous posts, I’ve installed SLOB on the Cloud Services, measured IOPS and CPU. There’s another resource that is even more important in a cloud environment: network.
I’ve create a virtual instance by default where only ssh is opened: I don’t want to open other port, so I’m using it with ssh tunneling. Let’s see how to tunnel port 1521:
ssh -i ~/.ssh/cloud-fr/id_rsa -L9011:localhost:1521 140.86.3.101This connects to my VM on the cloud and forwards the port 1521 (my listener port on that VM) to my local host (my laptop) port 9011. Which mean that I can connect to my database from my laptop:
$ tnsping //localhost:9011 TNS Ping Utility for 64-bit Windows: Version 12.1.0.1.0 - Production on 12-SEP-2015 17:34:44 Copyright (c) 1997, 2013, Oracle. All rights reserved. Used parameter files: F:\ora\product\12.1.0\client_1\network\admin\sqlnet.ora Used HOSTNAME adapter to resolve the alias Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=9011))) OK (90 msec)Note that the time to ping is 90 milliseconds here. I’m in Switzerland and I’m connecting to the European cloud (in Amsterdam). If I do the same to the US cloud I have to cross the Atlantic:
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=9061))) OK (240 msec)But we will see that later.
I don’t want to type the above ssh line everytime I want to connect to the cloud, so I use put the following in my ~/.ssh/config file:
Host test-cloud-fr HostName 140.86.3.101 Port 22 User oracle IdentityFile ~/.ssh/cloud-fr/id_rsa LocalForward 9011 test-perf:1521 LocalForward 9012 test-perf:5500Then a simple ‘ssh test-cloud-fr’ will connect the the right IP address, with the oracle user, using the right rsa private key and forwarding the listener and the EM express ports.
If you connect with SQL Developer, you don’t need to do that. Check Jeff Smith blog about Connecting to the Oracle Cloud.
If you put your database in the cloud, you will put the application server there as well. But if you are in client/server with the client on your desktop, or if you are a developer and connect from your PC, then latency can be a concern. Here I’m selecting 90000 rows from the European cloud (the one with a 90 ms latency) to my local sqlplus:
SQL> set time on timing on autotrace trace stat 09:07:25 SQL> select * from dba_objects; 91718 rows selected. Elapsed: 00:05:15.72 Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 13000 consistent gets 0 physical reads 0 redo size 4439199 bytes sent via SQL*Net to client 67754 bytes received via SQL*Net from client 6116 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 91718 rows processed 09:12:46 SQL>5 minutes is a lot, but that can be improved a lot. SQL Plus default fetchsize is 15 which is very low. This is why there are 6116 roundtrips (=91718/15) and it takes on average 51 ms per roundtip. But if we increase the fetchsize:
09:12:46 SQL> set arraysize 5000 09:12:54 SQL> / 91718 rows selected. Elapsed: 00:00:07.01 Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 6991 consistent gets 0 physical reads 0 redo size 3870546 bytes sent via SQL*Net to client 698 bytes received via SQL*Net from client 20 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 91718 rows processed 09:13:04 SQL>Now getting 100000 rows in 7 seconds is correct. Remember that all default fetchsize (SQL Plus, jdbc, hibernate, etc) are too low. It is even more important with remote databases over WAN, such databases in the cloud.
I have run a tnsping from US cloud to EU cloud every second during one week, and the same in the opposite way. Here is the result:
The latency is quite stable around 170 and 180 milliseconds. Rarely over 200ms. Here are the only 3 peaks observed during that week:
Mon Sep 7 15:15:01 EDT 2015 US->FR OK (1880 msec) Fri Sep 11 15:00:18 EDT 2015 US->FR OK (410 msec) Wed Sep 9 19:00:16 EDT 2015 EU->US OK (530 msec)
If it stays like that, the latency is acceptable for a DataGuard in ASYNC. Oracle will provide Data Guard service, but if you want to put a Data Guard between US and EU it’s possible in maximum performance.
Finally, here is a small test to measure the bandwidth between the European Cloud Services and the US one I copy a big file with scp:
[oracle@test-cloud-us ~]$ scp test-cloud-fr:/u02/IOPS.dbf /dev/null IOPS.dbf 6% 5004MB 8.0MB/s 2:27:59 ETA8MB/s. Here doing the same but with compression:
[oracle@test-cloud-us ~]$ scp -C test-cloud-fr:/u02/IOPS.dbf /dev/null IOPS.dbf 7% 5564MB 17.4MB/s 1:08:02 ETABut this is only to give an idea.
The DBaaS is just a remote database. Latency is always something to consider: avoid to do unnecessary roundtrips when fetching data. When you will consider to move your databases to the cloud, you will have to think about how to transfer it. There are several options to consider to minimize downtime: Physical replication (Data Guard or Dbvisit standby), Logical replication (Golden Gate or Dbvisit replicate), data virtualization (Delphix), etc.