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.)
http://www.mail-archive.com/[email protected]/msg00830.html
Take a look here mate - may be of some use.
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...
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.
A very powerful tool to monitor MySQL is innotop. You can find it here:
http://code.google.com/p/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.