How to find root cause for "too many connections" error in MySQL/PHP

Question

I'm running a web service which runs algorithms that serve millions of calls daily and run some background processing as well. Every now and than I see "Too many connections" error in attempts to connect to the MySQL box" for a few seconds. However this is not necessarily attributed to high traffic times or anything I can put my finger on.

I want to find the bottleneck causing it. Other than in the specific times this happens the server isn't too loaded in terms of CPU and Memory, and has 2-3 connections (threads) open and everything works smoothly. (I use Zabbix for monitoring)

Any creative ideas on how to trace it?

Answer 1

try to have an open mysql console when this happens and issue a

SHOW PROCESSLIST;

to see what queries are being executed. Alternatively you could enable logging slow queries (in my.cnf insert this line:

log-slow-queries=/var/log/mysql-log-slow-queries.log

in the [mysqld] section and use

set-variable=long_query_time=1

to define what's the minimum time a query should take in order to be considered slow. (remember to restart mysql in order for changes to take effect)

Answer 2

What MySQL table type are you using? MyISAM or InnoDB (or another one)? MyISAM will use table level locking, so you could run into a scenario where you have a heavy select running, followed by an update on the same table and numerous select queries. The last select queries will then have to wait until the update is finished (which in turn has to wait until the first - heavy - select is finished).

For InnoDB a tool like innotop could be useful to find the cause of the deadlock (see http://www.xaprb.com/blog/2006/07/31/how-to-analyze-innodb-mysql-locks/).

BTW The query that is causing the lock to occur should be one of those not in locked state.

Answer 3

The SHOW OPEN TABLES command will display the lock status of all the tables in MySQL. If one or more of your queries is causing the connection backlock, combining SHOW PROCESSLIST and the open tables should narrow it down as to exactly which query is holding up the works.

Answer 4

Old topic. However, I just had this issue and it was because I had a mysqldump script scheduled for 3 times per day. At these times, if my web application was also getting a fair amount of usage, all of the web application queries just queued themselves up on top of each other while the mysqldump was locking all of the tables in the database. The best option is to setup a replication slave on a separate machine, and take your backups from the slave rather than from the production server.