Michael Winston wrote:
On Sep 1, 2004, at 9:10 AM, V. M. Brasseur wrote:
Michael Winston wrote:
Hi-
We've been running into a pretty serious problem for the past several versions of mysql 4.0 running on OS X (both client and server). Every once in a while we wake up to find the "too many connections" error coming up. There really aren't too many connections (we have our max set to 99) - it's the type of message that appears when a wrong password is used too many times (and I'm 100% sure this isn't happening).
Now, the problem is that once this message starts appearing we can't even connect with mysqladmin as root. That "extra connection" that mysql promises doesn't exist. The only way we can shut down mysql is to perform a 'kill -9' (then restart the server and repair all the tables).
And we can't reproduce this problem at will. This is driving us nuts.
Before I report this as a bug I wanted to know if anyone else has seen something like this or has any suggestions of how to narrow down the problem.
Thanks!
Michael
We've run into this problem ourselves, also using 4.0 but on a 64bit AIX. The problem we found was that some queries were firing off threads which never ended. These threads blocked other threads, which blocked other threads... A logjam resulted with all connections ended up being used by the offending threads.
The fix was to *ahem* fix our queries so they'd close their database connections once they were complete. You may wish to do a code inspection and verify that every open connection has a matching close.
Hmmm. All of our connections are coming from php-generated web pages. PHP automatically closes the connection at the end of the script. Unless I completely misunderstand how this stuff works. Plus, this problem only happens once every few weeks. If some of our queries are causing this, I would expect the problem to occur more often.
I'll look into this, though.
Thanks, Michael
You'll also find this problem if you have some badly-optimised queries, or writes that take a long time to run on a frequently-accessed table. For example, if you have a table that frequently accessed and run a slow update on it, any thread trying to read from that table will block. If you get more selects happening to that table coming in while it's still locked, your number of connections in use will shoot upwards rapidly until the slow update finishes and the table is unlocked.
Have a look in your slow query log (or turn it on if it's not enabled) to look for any queries like this.
Regards,
-- Alex
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
