We're attempting to scale our Drupal installations up and have decided on some dedicated MySQL boxes. Unfortunately, we're running into extreme slowness when we attempt to use the remote DB - page load times go from ~200 milliseconds to 5-10 seconds.
Latency between the servers is minimal - a tenth or two of a millisecond.
PING 10.37.66.175 (10.37.66.175) 56(84) bytes of data.
64 bytes from 10.37.66.175: icmp_seq=1 ttl=64 time=0.145 ms
64 bytes from 10.37.66.175: icmp_seq=2 ttl=64 time=0.157 ms
64 bytes from 10.37.66.175: icmp_seq=3 ttl=64 time=0.157 ms
64 bytes from 10.37.66.175: icmp_seq=4 ttl=64 time=0.144 ms
64 bytes from 10.37.66.175: icmp_seq=5 ttl=64 time=0.121 ms
64 bytes from 10.37.66.175: icmp_seq=6 ttl=64 time=0.122 ms
64 bytes from 10.37.66.175: icmp_seq=7 ttl=64 time=0.163 ms
64 bytes from 10.37.66.175: icmp_seq=8 ttl=64 time=0.115 ms
64 bytes from 10.37.66.175: icmp_seq=9 ttl=64 time=0.484 ms
64 bytes from 10.37.66.175: icmp_seq=10 ttl=64 time=0.156 ms
--- 10.37.66.175 ping statistics ---
10 packets transmitted, 10 received, 0% packet loss, time 8998ms
rtt min/avg/max/mdev = 0.115/0.176/0.484/0.104 ms
Drupal's devel.module timers show the database queries aren't running any slower on the remote DB - about 150 microseconds whether it's the local or the remote server. Profiling with XHProf shows PHP execution times that aren't out of whack, either.
Number of queries doesn't seem to make a difference - we seem the same 5-10 second delay whether a page has 12 queries or 250.
Any suggestions about where I should start troubleshooting here? I'm quite confused.
It seems your connections to the DB are slow due to some other reason. How fast is a simple command line connection? (ie, non-php)
My prime suspect would be failing (= timing out) dns lookups on the remote or local server; both forward and reverse lookups.
From this page: start mysqld with --skip-name-resolve
If that doesn't help, I would start analyzing the tcp connections (tcpdump/wireshark) to isolate the problem. (on top of my head: local dns lookups, firewall issues (overloaded NAT box somewhere?), packetloss on the link somewhere, ..)
When this problem is solved, you probably also want to make sure you have persistent DB connections in PHP.
The reason could be added delays when using name resolving in the MySQL server. For every connection, mysql checks the DNS name of the client, which will dramatically increase latency.
You can check if this is the case for you if you have the line
skip-name-resolvein your my.cnf. If it is not there, add this line, restart the server and try again.