By Franck Pachot

.
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.

Tuneling port 1521 over ssh

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.101

This 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:5500

Then 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.

Latency

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.

Latency under the Atlantic

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.

Bandwith under the Atlantic

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 ETA

8MB/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 ETA

But this is only to give an idea.

So what?

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.