4

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.

ceejayoz
  • 32,469
  • 7
  • 81
  • 105

2 Answers2

5

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.

Joris
  • 5,939
  • 1
  • 15
  • 13
  • `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
3

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.

j0nes
  • 945
  • 10
  • 25
  • +1 and @ceejayoz you might consider using stunnel to encrypt MySQL traffic between servers just to be on the safe side: http://www.stunnel.org/examples/mysql.html – danlefree Jan 14 '11 at 08: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