MySQL(i) "Too many connections" what to do?

Question

I'm writing a hugh MySQLi/PHP application and experience problems with my database, it seems that there are too many connections open (250) after running for a couple of hours.

I'm using a very fast external database server in my network. I'm reaching like 1000 questions per second and the server does not seem impressed (the load is close to 0).

In my application the MySQLi link is closed by the destructor of the database class (this seems to work properly).

I'm using prepared statements and have also a couple of daemons running with infinite while loops and some queries inside it (the loops are delayed with usleep() to prevent overuse and I have to notice that mysqli_connect() is only called once starting the daemon).

But it seems that I never close my prepared statements with stmt->close(). Under query stats in my database I can see that the number of stmt->close() questions is equal to the number of stmt->execute(). So can this be the problem and when do I have to close my stmt for example? I don't know where to find a solution for this problem.

Software versions

PHP 5.5 under CentOS 6.5 with MySQL 5.6

Answer 1

Here are some things to try:

First: in your infinite-loop daemon processes: close your connections before sleeping and open them again upon waking. Don't try to hold database connections open for a long time. There's all kinds of timeout logic in the client-server connection that may activate when you don't want it to and therefore give you unpredictable failures. Opening connections, using them, then closing them will avoid that.

Second: try using so-called persistent connections. In mysqli you can prepend p: to your hostname to do this. Read this: http://www.php.net/manual/en/mysqli.persistconns.php

Third: It is good practice to close() your prepared statements explicitly when you're done with them, and to reset() them between uses if you reuse them. The mysqli dtor is supposed to do this automatically, but it's still good practice

Fourth: You may want to configure your Apache or ngnix server software to spawn fewer instances and threads. These instances and/or threads are serially resuable resources, and Linux's TCP stack does a good job of queueing up connect requests for them. This should reduce the number of connections MySQL needs to handle.

Fifth: Do you need to change you MySQL's configuration to allow more than 250 connections? If you're loadbalancing your web traffic to lots of web servers, you may need to do that.

Congratulations on getting a lot of traffic! Now for some real fun. bwahahaha.