0

I'm experience extremely slow page loading times caused by PHP taking forever to connect to a remote MySQL database.

I'm in Italy, and the server is in France. It's taking about 10s to make a connection.

The server is running Ubuntu 14.04.

I've tried disabling DNS lookups in MySQL by adding skip-name-resolve to my.cnf, and adding my connection's hostname to /etc/hosts, but I'm still experiencing the same problem.

Is there something else I could try?

nkkollaw
  • 1,947
  • 1
  • 19
  • 29
  • is ther a reason to host like this? –  Aug 23 '16 at 21:58
  • @Dagon: yes, we need to share a DB between a few devs and this is the easiest solution we found – nkkollaw Aug 23 '16 at 22:03
  • did you check WHERE the slowdown is occuring? Maybe mysql is fine, and it's the dns server causing the problem. the mysql client library has to do its own DNS lookup to resolve `frenchserver.com` so it can initiate the connection. – Marc B Aug 23 '16 at 22:06
  • Do other services like SSH take as long? Is it faster over an SSH tunnel? – tadman Aug 23 '16 at 22:08
  • Have you tried using the plain IP and not the hostname? – Charlotte Dunois Aug 23 '16 at 22:09
  • @Charlotte Dunois: I'm using the IP address to connect to the DB, but I don't know if the server looks up my IP to see if I'm allowed to connect. – nkkollaw Aug 23 '16 at 22:10
  • Sounds like replication would be a good solution. You'd have a replica locally so you wouldn't have to connect to the remote server. – Dave Aug 23 '16 at 22:10
  • @tadman: it's fast if using SSH to connect – nkkollaw Aug 23 '16 at 22:12
  • @Dave yes, but that would take a long time for us to set up. If I could get the connection to be a little better we'd be already set. – nkkollaw Aug 23 '16 at 22:13
  • @nbrogi Try over an SSH tunnel. Maybe that will solve the DNS problem, plus the added security doesn't hurt. – tadman Aug 23 '16 at 22:15
  • @tadman: I can't, it's not a custom app, but Moodle. I can't change how the connection is implemented. – nkkollaw Aug 23 '16 at 22:18
  • Remote SQL connections are notorious for being slow. Still, 10s is too long. Try `/sbin/traceroute server` from your client machine (the one running php), to see what's wrong. If that's OK, try a packet sniffer like Wireshark to monitor the traffic to your server. Are you getting lots of timeouts? NAKs and retransmits? With respect, if you don't know what I'm talking about here, you probably should put another MySQL instance in your local location. – O. Jones Aug 23 '16 at 22:19
  • An SSH tunnel can do port forwarding so all you'd need to do is change the local MySQL configuration to point to that instead. No re-programming necessary. – tadman Aug 23 '16 at 22:20
  • @tadman: I see. Like http://stackoverflow.com/a/16138417/2178206, uh? Thanks for putting me in the right direction, I'm trying this. – nkkollaw Aug 23 '16 at 22:22
  • @nbrogi Exactly like that. Be sure to set up something to keep your SSH tunnel alive if you're taking this approach, they can fail intermittently, but a simple supervisor script can fix this. – tadman Aug 23 '16 at 22:27
  • @tadman: Just tried and it works, but through PHP it's still super-slow. No idea why. – nkkollaw Aug 23 '16 at 22:28

0 Answers0