Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question

3 Answers 3

up vote 1 down vote accepted

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)

share|improve this answer

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.

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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