I have a mysql server which is occasionly returns the 'too many connections' error. I have worked with the server admin to set the mysql and server vars up to the best. We reckon it's down to a query which takes extremely long, then locks a table and thus backups others.
Was wondering what would be the best way to catch this problem?
Have you tried enabling the slow query log. It's output can be very useful for catching these types of queries.
If you're able to get to a terminal when you start running into this issue, run:
SHOW PROCESSLIST;
or
SHOW FULL PROCESSLIST;
Look for any queries that are taking their sweet time to finish that may be locking your tables.
However, 'too many connections' can also be a result of rogue code that is not properly closing the connections it is opening (as Dominic pointed out above).