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

Not sure if this is of any use to you but this link gives you a (temporary) good description of the issue you are experiencing as well as a --max_connect_errors setting which you could override:

http://developer.spikesource.com/wiki/index.php/Question%3Amysqladmin%5Fhost%5Fblocked%5Fbecause%5Fof%5Fmany%5Fconnection%5Ferrors

A user also responds and recommended trying the following on the command line:

> mysqladmin -u <user> -p<password> processlist

This will give you a list of your current mysql connections to get an idea for how many concurrent connections you are currently running. Again, these aren't solutions per say, but hopefully they get you a step closer to solving the dropped connection issue.

Answer 2

Did you try to manually close the connection? This seems to be a specific problem.

$db->closeConnection();

Ref: http://framework.zend.com/manual/en/zend.db.html

15.1.8. Closing a Connection

Answer 3

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.

Answer 4

Our Zend application has also been leaving quite a few connections open. They tend to come in spurts, but we never did figure out what was going on. We worked around the problem by setting max_connections high (which we needed for other reasons, anyway) and reducing wait_timeout to two hours. We could have gotten away with a much lower timeout, but didn't want to risk the real application once we stopped seeing failures.

Answer 5

first i'll ask: do you have any new core-dumps? (check apache error log).

second: from my grim experience having too many connections to the database usually means that you have too many apache processes not dieing properly. Usually this is caused by some long-running process, memory locks, etc. try to strace some of your apache processes and see if they hang somewere or do endless loops.

Unless you have a significant increase in traffic you should not suffer from too-many-connection problems

Answer 6

Don't think it's related to properly closing connection. http://dev.mysql.com/doc/refman/5.0/en/blocked-host.html says that it rather a problem with network layer.

• If the DB is located on other machine check the network connection problems between (cables, switches, firewalls)
• If the DB is on same machine try connecting using socket, not TCP.