2

Scenario

  1. Two servers: ApplicationServer1 and MySQLServer1
  2. MySQL remote database server is connected to ApplicationServer1 through an SSH tunnel (kept alive using autossh)

The problem

Since I separated the database from the application, the latter slowed down a lot. SSH tunnel is used in the server stack to increase security. Setting up a VPN (such as Tinc) is an alternative, but it seems to slow down much more than the SSH tunnel.

So...

How can I speed up the connection between ApplicationServer1 and MySQLServer1?

Could Redis or Memcached, installed on ApplicationServer1, be a solution?

Thank you all.

BlackOut
  • 29
  • 2
  • The ssh tunnel is going to be a lot slower than any _reasonable_ VPN. – Michael Hampton Feb 18 '17 at 21:41
  • @MichaelHampton Could you explain why I should prefer VPN to SSH tunnel? – BlackOut Feb 18 '17 at 23:01
  • Other than it being slow? – Michael Hampton Feb 18 '17 at 23:03
  • Off-topic, but by separating the application and the database, you've actually *reduced* reliability. With both the database and the application on a single server, you only needed that one server to work. With the database on one server and the application running on another server, you now need *both* servers to work for your system to be available. Now, you're about twice as likely that a hardware failure will cause an outage. (This actual *drop* in reliability that results in moving to multiple servers does assume no clustering or other redundancy is in use.) – Andrew Henle Feb 18 '17 at 23:04
  • @MichaelHampton Sorry, I mean, technically. How is VPN faster than an SSH tunnel? – BlackOut Feb 18 '17 at 23:05
  • @AndrewHenle Sorry, I see more pros than cons in splitting servers. On the other hand, in fact, now I have more resources dedicated to my services and I can easily scale horizontally. Better to not put all the eggs in one basket, I think – BlackOut Feb 18 '17 at 23:16
  • @BlackOut it is absolutely the correct thing to do to keep the database on its own server. The question is, define "slow." *Which part* is "slow?" Has round trip time increased? (Run `SELECT NOW(6);` as two queries. What's the difference in microseconds?) Or is it the time to pull data across the wire? Try to establish the nature of the slowdown. I use OpenVPN TLS over UDP in a scenario where the ancient app is in Earl's Unreliable Cloud Hosting Company of Northern Virginia and the db is in AWS us-east-1. Internet ping is surprisingly good ~1ms, db ping from app server over OpenVPN ~3ms. – Michael - sqlbot Feb 18 '17 at 23:55
  • @BlackOut *I see more pros than cons in splitting servers.* True, there are advantages either way. *Better to not put all the eggs in one basket, I think* But not when **ALL** your baskets need to work. When both servers need to be working to provide a service, using two servers makes things *less* reliable. Most people fail to grasp that, because they think "Two cars makes it more likely I won't be unable to get to work because my car died." But owning two cars is a redundant cluster. If you have to take both cars to work, you'd be *more* likely to wind up stuck at home with one dead car. – Andrew Henle Feb 19 '17 at 19:55

1 Answers1

2

How can I speed up the connection between ApplicationServer1 and MySQLServer1?

By keeping them on the same high-speed network.

I know that's not what you want to hear, but it's the right solution. Application servers should not be separated from their backing servers by more than a millisecond or two at the very top end.

Could Redis or Memcached, installed on ApplicationServer1, be a solution?

Sure, but any of those solutions will require an extensive re-write of your application, and will really only help with read-only operations. Write operations will still be as slow as you're experiencing now.

I'm not sure what situation transpired to make you separate these two servers, but again, the answer to your question is: move the servers closer together.

EEAA
  • 108,414
  • 18
  • 172
  • 242
  • Both servers are in the same datacenter, using the same (private) network. So, they are already next to each other. – BlackOut Feb 18 '17 at 21:25
  • Oh, well in that case, why not use IPsec instead of SSH? What bandwidth/latency are you getting through the SSH tunnel? – EEAA Feb 18 '17 at 21:26
  • 3
    Further: why do you need to tunnel this traffic at all? If you need encryption, just use MySQL's native TLS support. – EEAA Feb 18 '17 at 21:27
  • Latency on average (for one of the most expensive query) is 0.749ms – BlackOut Feb 19 '17 at 12:10
  • Yeah, the SSH tunnel is used only for encryption. I don't trust the idea of having my services exposed. – BlackOut Feb 19 '17 at 12:13
  • Do then firewall them and use TLS. – EEAA Feb 19 '17 at 12:15