Remote JDBC connection performance question

[Tyler Wassell]

Hello,
I am hoping someone can shed some light on a jdbc performance issue for me.

I am working with Java servlets running in Tomcat that connect via JDBC to a MySQL database. The Tomcat is running on one server, the database is running on another but are located in the same building on the same network. With this setup, a simple INSERT statement executed by the servlet against the DB runs very fast (1-4ms). If I ping the IP of my DB server from the Tomcat server, similar result (1ms round trip time).

My Problem:
I move the MySQL DB into the cloud. The DB is now located on Amazon Web servers somewhere in Virginia. Tomcat is located in Illinois. Tomcat and the DB are no longer in the same network. Now, when I execute the same query, it takes 400ms for Tomcat in Illinois to run the INSERT query on the DB in Virginia. If I ping the remote DB from the Tomcat server its about the same as the query (400ms) as expected.

My question:
If I have a servlet application that needs to make 5-10 SQL queries to the remote Db, I am looking at 5 seconds for the DB work alone (which is unacceptable), whereas when it was running locally, it was sub-second. Is there any solution for this scenario? I assume I am just dealing with bandwidth and latency limitations but it seems like this would be a problem for many developers. Or is it just understood that TC server and DB should be physically next to each other or in the same network when developing this type of app?

I have run the local setup for a long time and I am toying with the idea of moving the DB remote (not necessarily keeping it on Amazon, just testing it for now). Seems like it may not be possible if I need to run a few simple queries in under 1-2 seconds based on my tests.

Any insight is appreciated. Thanks!

[William P O'Sullivan]

Yes. Your application will take a major hit in performance when you moved your DB from your internal network to the cloud.

Run a tracert <host> (or traceroute <host>) and see how many hops the cloud DB takes versus local.

It would be great if you could post those here, so others can learn from your question.

Pat.

[Scott Selikoff]

So you moved the database into the cloud but not the application server? Why not move both together?

[Tyler Wassell]

Here is the traceroute:
Tracing route to ec2-107-21-238-01.compute-1.amazonaws.com [107.21.238.01]
over a maximum of 30 hops:

1 1 ms 1 ms 2 ms 72.16.168.123
2 201 ms 104 ms 139 ms 172.21.38.205
3 150 ms 89 ms 52 ms car02-s3-0-2.chcgilwb.cbeyond.net [192.168.43.90]
4 154 ms 133 ms 150 ms ccr00-v401.chcgilwb.cbeyond.net [192.168.42.41]

5 29 ms 45 ms 60 ms 209.117.165.77
6 10 ms 11 ms 10 ms vb1700.rar3.chicago-il.us.xo.net [216.156.0.161]

7 208 ms 220 ms 209 ms 207.88.14.194.ptr.us.xo.net [207.88.14.194]
8 24 ms 8 ms 13 ms 206.111.2.86.ptr.us.xo.net [206.111.2.86]
9 * * * Request timed out.
10 43 ms 46 ms 26 ms 65.120.78.82
11 27 ms 27 ms 27 ms 72.21.220.145
12 73 ms 72 ms 33 ms 72.21.222.139
13 81 ms 46 ms 144 ms 216.182.224.63
14 * * * Request timed out.
15 * * * Request timed out.
16 * * * Request timed out.
17 * * * Request timed out.
18 * * * Request timed out.
19 146 ms 216 ms 184 ms ec2-107-21-238-01.compute-1.amazonaws.com [107.2
1.238.01]

Trace complete.

The purpose of my testing and question was to see if I can separate the application from the DB. In practice, my users currently host the servlet applications on premise with their DB's. I am just testing to see if I can host the apps elsewhere (off premise) and still connect to their DB's. It's not looking good. Thank you both for your responses.

[Scott Selikoff]

In general the database and application server have to be in a related network. If the latency between the two is big, then the entire application can grind to a halt. I recommend moving everything to the cloud or nothing, as moving just one causes the kinds of latency issues you are seeing.

[Scott Selikoff]

Bear in mind there is also a serious security issue here. Generally, I never expose databases to the public internet. They are often behind a firewall in which only internal applications like an application server can access them. Exposing a database to public access of the entire internet is generally considered a bad design principal.

[William P O'Sullivan]

Yes Tyler, That traceroute is not looking good.

For local development and unit testing you should be okay, however once in production,
the closer the app server(s) are to the DB the better.

Pat.

[Tyler Wassell]

Thanks guys, this is about what I expected. Just wanted to hear it from some others. Much appreciated!