I have a problem with MySQL connections on our DB servers.
We have 3 identical servers (8 gb RAM, 2x quad core processors), generally quite quick…
Since an global update made to our website, we have started maxing out our connections on the DB - so we know where the problem is coming from (queries from our PHP pages on the webserver - and one not running through our connection manager that cleanly opens and closes non persistant connections) but we are stuck on correcting the problems with the information provided by MySQL.
When we manage to connect to the database after several retries due to having reached the maximum connection limit, we run “show status where variable_name like ‘Threads_connected’;” and get a number between 198 and 200 (we currently have 200 concurrent connections).
We initially thought that there was an errant query or update that was locking the others and causing a query and connection backlog - but when executing “show processlist” there is only 1 query running: my “show processlist”.
So we have 199 opened connections + my current session and no running queries to explain the connection backlog.
So 3 questions:
- Is there a way to get a detailed connection listing rather than a total?
- Is there any way to either kick off the connections without restarting MySQL?
- Is there any way to get detailed information about a specific connection (like host, username, time of connection, name of calling app - wishfull thinking ) - or anything else?