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 ( 56(84) bytes of data.
64 bytes from icmp_seq=1 ttl=64 time=0.145 ms
64 bytes from icmp_seq=2 ttl=64 time=0.157 ms
64 bytes from icmp_seq=3 ttl=64 time=0.157 ms
64 bytes from icmp_seq=4 ttl=64 time=0.144 ms
64 bytes from icmp_seq=5 ttl=64 time=0.121 ms
64 bytes from icmp_seq=6 ttl=64 time=0.122 ms
64 bytes from icmp_seq=7 ttl=64 time=0.163 ms
64 bytes from icmp_seq=8 ttl=64 time=0.115 ms
64 bytes from icmp_seq=9 ttl=64 time=0.484 ms
64 bytes from icmp_seq=10 ttl=64 time=0.156 ms

--- 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.

  • skip-name-resolve did the trick. Many thanks! – ceejayoz Jan 14 '11 at 14:29
  • @ceejayoz: Glad that's sorted out. You may want to deploy proper dns services (or fix the current one) as there are a lot of tools that do exactly the same thing and will incur the same slowdown – Joris Jan 14 '11 at 22:07

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-resolve in your my.cnf. If it is not there, add this line, restart the server and try again.

  • +1 and @ceejayoz you might consider using stunnel to encrypt MySQL traffic between servers just to be on the safe side: stunnel.org/examples/mysql.html – danlefree Jan 14 '11 at 8:58
  • By all means use a tunnel, but tunnelling just the mysql port and using non-persistent conenctions will have an adverse effect on the performance - either stunnel the port with persistent connections, or use VPN (e.g. ppp over stunnel) with non-persistent – symcbean Jan 14 '11 at 11:34
  • skip-name-resolve did the trick. Many thanks! – ceejayoz Jan 14 '11 at 14:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.