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

On LAMP production server I get the 'too many co开发者_如何学Pythonnnections' 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.)

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.

To get total number of concurrent connections allowed by MySQL you should check

show variables like 'max_connections'

and how many concurrent connections are being used currently can be measured by

show status like 'max_used_connections'

You should monitor these variables. If you need a commercial tool which monitors important MySQL metrics you can try MySQL Enterprise Monitor, MONyog etc..

SHOW STATUS doesn't display total connections: it displays current connections. You get the too many connections error message when the number of open connections (idle or not) surpases the limit established for the server.

From your comment about SHOW STATUS, I looks like you have many idle connections in your server. That points to misuse of persistent connections; some libraries that claim to support persistent connections do not handle them properly and they tend to open new connections even when there're idle connections available.

Find out what application is running such queries and configure it not to use persistent connections.

It might be easier for you to obtain the MySQL Admin, from that, you can easily monitor where the used connections are coming from. See here on this about the connections used...