Zend Framework app not closing mysql connections properly

Question

I'm having a weird issue using Zend_Db's PDO adapter for managing my database connections. All of a sudden, I'm constantly hitting the connection limit on my MySQL server.

For whatever reason, every three or four hours or so I end up getting this error, and Apache craps out on every HTTP request with this error:

PHP Fatal error: Uncaught exception 'Zend_Db_Adapter_Exception' with message 'SQLSTATE[00000] [1129] Host 'my.internal.mysql.server.DNS.here' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'' in

The weird thing is, I haven't changed any code and traffic volumes are actually down from what they've been in the past, so I know my app shouldn't be generating more concurrent connections. The weirdest thing is I haven't changed code on any of the main app files in nearly a week and everything has ran swimmingly since.

Rebooting the mysql service or flushing the connections with mysqladmin flush-hosts does do the trick, and I'm sure increasing the max concurrent connections in my.cnf would also help alleviate some of these issues, but there seems to be a core problem with the application itself creating too many connections.

Any thoughts on how I can drill down and correlate the piece of code causing the hanging connection? Externally, all of the different pages of the web app load fine and with good speed.

Answer 1

I think one of your script is running an ultra long query or get stuck ?

The best solution would be to create a logging tool and use that to know where exactly the problem is.

The Zend_Log classes should help you to write that logging tool, obviously you have better to user a file writer and not a DB one. Since your problem is DB related you should create or modify the DB class to emit some logs.

I don't know the volumetric of your project but it might have some performance side effects.

Also playing with the long query feature of MySQL would probably give you some hint.

Hope you are going to find where is your problem.