Monitoring used connections on mysql to debug 'too many connections'

Question

On LAMP production server I get the 'too many connections' error from MYSQL occasionally, I want to add monitoring to find if the reason is that I exceed the max-connections limit.

My question: How can I query from mysql or from mysqladmin the current number of used connections? (I noticed that show status gives total connections and not the currently used ones.)

Answer 1

A very powerful tool to monitor MySQL is innotop. You can find it here:

https://github.com/innotop/innotop

In Debian Lenny, it is part of the package mysql-client-5.0 and I guess it is available for other distros as well. It is especially powerful in monitoring InnoDB internals, but provides general monitoring of the server as well.

In "Variables & Status" mode, it monitors the variables "Connections" and "Max_used_Connections" (among others). It displays absolute and incremental values - the latter might give you an idea about the current connections.

Since innotop provides a non-interactive mode, you can easily build fully automated monitoring by calling innotop from some customized scripts, nagios checks or whatever system you have.