I am hoping someone can shed some light on a jdbc performance issue for me.
I am working with Javaservlets 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).
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.
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.
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 188.8.131.52
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 184.108.40.206
6 10 ms 11 ms 10 ms vb1700.rar3.chicago-il.us.xo.net [220.127.116.11]
7 208 ms 220 ms 209 ms 18.104.22.168.ptr.us.xo.net [22.214.171.124]
8 24 ms 8 ms 13 ms 126.96.36.199.ptr.us.xo.net [188.8.131.52]
9 * * * Request timed out.
10 43 ms 46 ms 26 ms 184.108.40.206
11 27 ms 27 ms 27 ms 220.127.116.11
12 73 ms 72 ms 33 ms 18.104.22.168
13 81 ms 46 ms 144 ms 22.214.171.124
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
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.
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.
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.