Re: bad too many connections error (os x)
Michael Winston [EMAIL PROTECTED] wrote: Okay, so the first thing to try is obviously enlarge the max_connections. Have you tried this? Yes. It's set to 400 (a number we will never reach unless there's some sort of logjam). max_connect_errors is set to 200. Is it a webserver backend database? Yes. PHP-generated pages. pconnect? Apache limits? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: bad too many connections error (os x)
Michael Winston [EMAIL PROTECTED] wrote: Okay, so the first thing to try is obviously enlarge the max_connections. Have you tried this? Is it a webserver backend database? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: bad too many connections error (os x)
On Sep 2, 2004, at 6:04 AM, Egor Egorov wrote: Michael Winston [EMAIL PROTECTED] wrote: Okay, so the first thing to try is obviously enlarge the max_connections. Have you tried this? Yes. It's set to 400 (a number we will never reach unless there's some sort of logjam). max_connect_errors is set to 200. Is it a webserver backend database? Yes. PHP-generated pages. Thanks, Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: bad too many connections error (os x)
Is it a too many connections or Host blocked because of many connection erros? If it is the later you have reached max_connect_errors and need to issue flush-hosts. -Original Message- From: Michael Winston To: [EMAIL PROTECTED] Sent: 9/1/04 11:02 AM Subject: bad too many connections error (os x) 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: bad too many connections error (os x)
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. Best of luck, --V -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: bad too many connections error (os x)
I think that there is an error in your code. I have had this problem with a small application I wrote: The problem arises when you don't send the quit command to mysql before dropping tcp. You must send a quit command in every case (if you opened a socket). I can not be more precise because i don't know what labguage you use (C, php, ...). There is a command to reenable connection; if my memory is good: mysqladmin -flush-hosts or something (see mysqladmin reference or help). Santino At 9:02 -0700 1-09-2004, 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: bad too many connections error (os x)
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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: bad too many connections error (os x)
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]
RE: bad too many connections error (os x)
Not if the host that is blocked is `localhost`. -Original Message- From: Michael Winston To: Victor Pendleton Cc: '[EMAIL PROTECTED] ' Sent: 9/1/04 11:24 AM Subject: Re: bad too many connections error (os x) This would make sense since they all the connections are coming from the same website. But if this is true, then why can't we connect using 'mysqladmin -uroot'? Shouldn't that work from any host? Thanks, Michael On Sep 1, 2004, at 9:08 AM, Victor Pendleton wrote: Is it a too many connections or Host blocked because of many connection erros? If it is the later you have reached max_connect_errors and need to issue flush-hosts. -Original Message- From: Michael Winston To: [EMAIL PROTECTED] Sent: 9/1/04 11:02 AM Subject: bad too many connections error (os x) 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: bad too many connections error (os x)
This would make sense since they all the connections are coming from the same website. But if this is true, then why can't we connect using 'mysqladmin -uroot'? Shouldn't that work from any host? Thanks, Michael On Sep 1, 2004, at 9:08 AM, Victor Pendleton wrote: Is it a too many connections or Host blocked because of many connection erros? If it is the later you have reached max_connect_errors and need to issue flush-hosts. -Original Message- From: Michael Winston To: [EMAIL PROTECTED] Sent: 9/1/04 11:02 AM Subject: bad too many connections error (os x) 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: bad too many connections error (os x)
This also may caused by the TTL of the http socket and the launched zombie forks by the http/php server ( the FIN_WAIT_2 problem!!). Thats what it happend to me . I dunno who waited for who, and became zombie, was it MySQL waiting for timeout or was socket wating to timeout. You can test this easily. Simultaneously issue same instance of the service in question from same network and watch the netstat and top -t. -- Aftab Jahan Subedar CEO/Software Engineer Subedar Technologies Subedar Baag Bibir Bagicha #1 North Jatrabari Dhaka 1204 Bangladesh tel://+88027519050 EMail://[EMAIL PROTECTED] - Directly to my notebook Alex Greg wrote: 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]
