Debug MySQLs "too many connections"

Question

I'm trying to debug an error I got on a production server. Sometimes MySQL gives up and my web app can't connect to the database (I'm getting the "too many connections" error). The server has a few thousand visitors a day and on the night I'm running a few cron jobs which sometimes does some heavy mysql work (Looping through 50 000 rows, inserting and deletes duplicates etc)

• The server runs both apache and mysql on the same machine
• MySQL has a pretty standard based configuration (max connections)
• The web app is using PHP

How do I debug this issue? Which log files should I read? How do I find the "evil" script? The strange this is that if I restart the MySQL server it starts working again.

Edit:

• Different apps/scripts is using different connectors to its database (mostly mysqli but also Zend_Db)

Answer 1

First, use innotop (Google for it) to monitor your connections. It's mostly geared to InnoDB statistics, but it can bet set to show all connections including those not in a transaction.

Otherwise, the following are helpful: Use persistent connections / connection pools in your web apps. Increase your max connections.

Answer 2

It's not necessarily a long-running SQL query.

If you open a connection at the start of a page, it won't be released until the PHP script terminates - even if there is no query running.

You should add some stats to your pages to find out the slowest ones, and the most-hit ones. Closing the connection early would help, if possible.

Answer 3

Try using persistent connections (mysql_pconnect), it will help reduce the server load caused by constantly opening and closing MySQL connections.

Answer 4

The starting point is probably to use mysqladmin processlist to get a list of the processes on the mysql server. The next step depends on what you find.