Track MySQL connections between 2 servers

Question

We have an issue with a server running particularly slow at times but the CPU never goes very high. A sysadmin has looked into this and said that at any one time there are a massive number of connections from the MySQL port of Server A - which is running fine - and Server B - the server with the issues. The main symptoms seen on Server B are very slow page load times, seen predominantly by an inital hang before the page does anything, and slow connection to the Shell. Its worth pointing out this is not website/application specific - not caused by Javascript or anything like that as we have the same application running on 3 or 4 other same-spec servers with no issues at all.

What i want to do is try and track down the exact nature of these MySQL connections between the 2 servers. So, what the source is, what the destination is, what the purpose is. Should i look to enable the (currently disabled) general log? Is there something else i can try? Both servers are running CentOS (one on 4.8 and one on 5.5) and both on latest builds of WHM/cPanel with latest stable PHP/Apache versions.

Thanks.

Answer 1

Enabling the log is a good idea, you can also use the MySQL Administrator client to look at the open connections and see what they're doing when you experience the issue.

Although from your description I can think of one very common issue off the top of my head, that is a DNS issue that's causing MySQL to wait for a DNS timeout before processing an incoming connection. This usually manifests in what you describe in situations where the application opens a new MySQL connection for each request (instead of using a persistent connection pool).

Read more about this here: http://dev.mysql.com/doc/refman/5.0/en/dns.html

(skip_name_resolve in my.cnf is the option IIRC)

Answer 2

If you have two servers connecting, and you have slow 'connections', but once the thread connects things are fast, I would suspect that dns resolution of the remote host may be causing a problem.

skip-name-resolve

in your /etc/mysql/my.cnf (or equivalent file) in the [mysqld] section will prevent those DNS lookups.

Make sure that your grants are done using IPs rather than hostnames.

Answer 3

you can also try less intrusive approach [without enabling logs which come with performance overhead]. sniff the traffic using tcpdump [you can do it on either of sides of connection or in the middle - on some router or manageable switch with port mirroring].

tcpdump -s 1600 -w dump.cap -i eth0 host 10.1.2.3 and port 3306

analyze dump.cap - first with wireshark [at that stage you should be able to tell if it's plenty of parallel connections or not], and if needed - look at mk-query-digest which can give you good insight into queries coming either from mysql logs or tcpdump'ed traffic.