Debugging sleepy MySQL Connections

Question

I guess I have some application problems which are slowing down our whole app and will cause maybe damage and other problems.
The applications problems occur by many mysql connections which are sleeping.

The SHOW PROCESSLIST; output:

| 356058234 | Y  | X:39119 | D | Sleep   |  1442 |       | NULL             |
| 356058441 | Y  | X:39126 | D | Sleep   |  1442 |       | NULL             |
| 356059383 | Y  | X:46615 | D | Sleep   |  2049 |       | NULL             |
| 356059389 | Y  | X:46617 | D | Sleep   |  2052 |       | NULL             |
| 356065991 | Y  | X:39267 | D | Sleep   |  1442 |       | NULL             |
-------------  
452 rows in set (0.00 sec)

Also mysqlreport

__ Connections _________________________________________________________
Max used         8001 of 8000      %Max: 100.01
Total         356.07M    58.6/s

Which shows I am really running out of connections. Even by a limit of 8000. Here has to be something. But debugging such things can be troublesome.

Anyways I looked that Process IDs up with netstat for example like this netstat -ntp | grep :39267 which outputs:

tcp        0      0 IP_1:3306      IP_2:38727    VERBUNDEN   791/mysqld

So I go one step further and look up 791 which outprints me a long string (several hundred lines) which look like those ones here:

tcp        0      0 IP_1:3306      IP_2:34109    VERBUNDEN   791/mysqld      
tcp        0      0 IP_1:3306      IP_3:32864    VERBUNDEN   791/mysqld      
tcp        0      0 IP_1:3306      IP_3:37231    VERBUNDEN   791/mysqld      
tcp        0      0 IP_1:3306      IP_2:38727    VERBUNDEN   791/mysqld      
tcp        0      0 IP_1:3306      IP_2:36645    VERBUNDEN   791/mysqld

But what does this tell me? Which more informations should I collect to have a clue which actually causes that too many connections problem?

Further informations about the application:
We run code - which updates, inserts and deletes quite big on our Database - on several servers, which get those data by the forking Framework Gearman. We are running a MySQL INNODB Database which get used by our PHP 5.4 and Zend Framework driven application. OS is Linux(Debian).

Answer 1

The fact that most (all?) threads are in a state of Sleep makes me think your application has an open connection to the database, but is in between database requests.

You could confirm this by using Cacti with Percona Monitoring Plugins to monitor the trends of Threads_running versus Threads_connected. It's typical for the former to be a lot lower than the latter, but if its way lower on average, then you basically have idle database connections tying up resources unnecessarily.

A few things you could do:

• Connect to the database late. Zend Framework's database adapters (last I checked) don't actually connect until the first query, by default. You can override this and force it to connect early, but you should try to avoid this.

• Disconnect from the database early. It's customary in PHP to just leave resources hanging around because they'll get cleaned up automatically when the request ends. But this means your app retains a connection to the database server for some time when it doesn't need it, and when other app requests may be waiting for it. You should code your apps to disconnect from the database promptly, once they have fetched the last data they need.

• Minimize connect time. If you can connect to the database, quickly get all the data needed for a given page request, then disconnect promptly, you free up the connection resource on the server. This may mean refactoring your PHP code. Whatever you need to do to analyze data or format it for output can be done after you close the database connection.

• Don't connect unnecessarily. If your app can get the data it needs from memcached or APC or similar cache, then some of your page requests may not need to touch the database at all.

• Optimize queries. Another possibility is that some queries are long-running, and holding open threads in a state other than Sleep. In other words, actually executing a query. But if the rate of queries is consistently faster than the average speed at which they can complete, you tend to get long queues of connections piling up. Make your queries run faster.

  The methods for doing that are too many to recount here, but you can start learning about it here:

  • How to Design Indexes, Really
  • SQL Query Patterns, Optimized

Answer 2

After some long time of debugging and testing I finally found the mistake which caused those sleepy connections and will in the end result in a too many connections error.

I found out, that wenn I do a Zend_Db_Table_Select operation for example like this one:

// $this->tablename is instance of Zend_Db_Table_Abstract
$select = $this->tablename->select();
$select->where('id = ?', $id);

return $this->tablename->fetchAll($select)->toArray();

Zend lets the connection open till the script ends.
But because I use Gearman which means my PHP worker-scripts are used to run forever, the scripts never terminates and so those connections will left open as sleepy ones.
So in the end you have a thousand open but sleepy connections.

So how do we shutdown something like this? I made myself a __destruct() method which simply containes:

// both $this->useraddons and $this->db get set up in my __construct()
$this->useraddons->getDefaultAdapter()->closeConnection();
$this->db->closeConnection();

Every time I want to close the connection I just have to do a unset($myTableClass).