Why aren't my MySQL connections closing?

Unsettling the variable does not close the connection.

You need to see what your gimmieDB returns.
The same way you have gimmieDB you need one that you release the connection.

You might be looking at the wrong part I.e. You may have a different code that opens he connection and never closes it.

All it does is instantiate a MySQLi object with the connection, if there is an error, log it with fLog, and otherwise returns the object.

I should be able to (later) use the close() method of that returned object to close it, right?

/**
 * Creates a MySQLi object for the database
 * @return mysqli A MySQLi object with a connection to the system's database.
 **/

function gimmieDB()
{
    $dbDatabase = '[REDACTED]';
    $dbUser = '[REDACTED]';
    $dbPass = '[REDACTED]';
    $dbServer = 'localhost';

    $m = new mysqli($dbServer,$dbUser,$dbPass,$dbDatabase);
    if($m->connect_error) {
        fLog('gimmieDB',$m->connect_error);
    }

    return $m;
}

Select allOpen in new window

... and if you notice the bottom, the first line is close(), the SECOND line is unset().

FWIW, I never bother to call the Close() methods -- the PHP script terminator will close the connections for you.  And I've never had an "excess" of open connections.
http://php.net/manual/en/mysqli.close.php

If your connections are persistent, you may need to close them yourself.  Also, if you run the gimmeDB() method repeatedly, it will create a new connection each time.  So, 15 calls to the function gives 15 connections.  Usually only one connection is needed.  This is another argument for getting a connection object and using dependency injection to pass the connection object into any other methods that need to use the connection.

@ray:

You probably recognize this function from a previous (and valuable) conversation we have had.

I only call it once at the top of a given script, and everything in the script uses that single connection object - it is passed as a dependency injection to all classes, functions, and methods.

Then, it dies when we get to the bottom of the calling script (page).

Until I saw these "excess" connections, I wasn't doing the close() either because (as it states in the docs) unless you specify a persistent connection, it will close it for you.

This is why I am confused. "The Google" says those are concurrent connections. But, the server got REALLY busy earlier today (an unusual onslaught of people signed in all at once) and it started throwing the too many connections error. So, I went looking for stray uses of the gimmieDB() function to see if I was accidentally instantiating some extra connections. I found some, and one in particular (in our billing script) was racking up hundreds of connections because I accidentally had it in a loop (instead of just above the loop).

While trying to track this down, I looked at the MySQL stats for connections. The first time I looked (before restarting MySQL) it has 132,000,000 connections. That's outrageous.

my.cnf has "max_connections = 100" so I am not sure HOW it could get that high unless it was a historical count. That's very believable for this particular server.

Running on a shared server, with everything humming along smoothly, here is what a MySQLi object looks like:

object(mysqli)#2 (19) {
  ["affected_rows"]=>
  int(0)
  ["client_info"]=>
  string(75) "mysqlnd 5.0.10 - 20111026 - $Id: c85105d7c6f7d70d609bb4c000257868a40840ab $"
  ["client_version"]=>
  int(50010)
  ["connect_errno"]=>
  int(0)
  ["connect_error"]=>
  NULL
  ["errno"]=>
  int(0)
  ["error"]=>
  string(0) ""
  ["error_list"]=>
  array(0) {
  }
  ["field_count"]=>
  int(0)
  ["host_info"]=>
  string(25) "Localhost via UNIX socket"
  ["info"]=>
  NULL
  ["insert_id"]=>
  int(0)
  ["server_info"]=>
  string(14) "5.5.45-MariaDB"
  ["server_version"]=>
  int(50545)
  ["stat"]=>
  string(147) "Uptime: 6591442  Threads: 1  Questions: 94391244  Slow queries: 2  Opens: 316968  Flush tables: 2  Open tables: 400  Queries per second avg: 14.320"
  ["sqlstate"]=>
  string(5) "00000"
  ["protocol_version"]=>
  int(10)
  ["thread_id"]=>
  int(2958085)
  ["warning_count"]=>
  int(0)
}

Select allOpen in new window

Here is what SHOW STATUS LIKE %onn% returns.  The Connections value increments as I rerun the script.

stdClass Object
(
    [Variable_name] => Aborted_connects
    [Value] => 52058
)
stdClass Object
(
    [Variable_name] => Connections
    [Value] => 2958086
)
stdClass Object
(
    [Variable_name] => Max_used_connections
    [Value] => 48
)
stdClass Object
(
    [Variable_name] => Ssl_client_connects
    [Value] => 0
)
stdClass Object
(
    [Variable_name] => Ssl_connect_renegotiates
    [Value] => 0
)
stdClass Object
(
    [Variable_name] => Ssl_finished_connects
    [Value] => 0
)
stdClass Object
(
    [Variable_name] => Threads_connected
    [Value] => 1
)

Select allOpen in new window

Random thought... Where is this web site hosted?

The connection counters in statistics as you supposed is based on when mysql was started. That duration is also included in the statistics. Total Connections could reach that high as you pointed out because you had a process that was looping and initiating a new connection per loop.

Unfortunately, determining the issue at hand is a going forward event, by guestimating what might have happened. Tuning the mysql setup /db/query using everytool available.


http://dev.mysql.com/doc/refman/5.5/en/innodb-multiple-tablespaces.html

Depending on your DB use/need.

Is it a continuously growing  db?

@ray

Dedicated server. Custom built by us.

@arnold

No, the database is not continuously growing other than what is to be expected. It's been in service for years, and is linux, so has not been rebooted for well over a year. The 132M connections is reasonable for it over that period of time.

So, since we have (collectively) figured out that this is not the current connection list, WHERE is the current / active / open connection list?

At this point, I just want to watch it to make sure that I don't have something else chewing up resources so this problem doesn't return. (It hasn't returned yet, and it has had heavy use over the last 24 hours... nothing in syslog and nothing in the php error logs or mysql logs).

@arnold:

Then this means my problem is solved. SHOW PROCESSLIST is (has been) rather barren since I fixed the other problem. It now has only the occasional (and expected) connection and processes.

I'd recommend you use the referenced tools to see if they provide any suggestion based on the current long collected stats.
You could set the long query log just to capture an event should it occur.

The repetitive opening of the connections might be one, the time of year, end of month, end of quarter, etc. trigger some usage that is not seen through out the rest of the month/quarter, year that might manifest the same issue next time.
..

Good idea