Thanks for the suggestions. Here's what happened.
> Your pconnects are probably sleeping too long.
in /etc/my.cnf:
[mysqld] set-variable = wait_timeout=10
I tried this, and it was a mixed bag. On the one hand, the "Can't create thread..." error did indeed go away, and max_used_connections and the number of sleeping/persistent connections dropped down well below 250. However, making this change caused a new problem: on about every 50% of page loads, the following PHP warning would occur:
Warning: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) in /www/functions.inc on line 66
Line 66 of that file is a call to mysql_pconnect(). The MySQL user name and password hard coded in that file are definitely correct, since the error is only happening about 50% of the time and no one is modifying this file or the grant tables in between.
Interestingly, nothing gets logged to the MySQL error log when this PHP warning is generated. I'm not sure whether there are different logging levels in MySQL and if I need to make my logging more verbose or something, but at any rate I can't get any additional information about this error from the log.
If that doesn't take care of it, check your PHP version. Early (~ 2001) PHP4 had some buggy pconnect problems.
I'm using PHP 3.0.18. We have been using the same pconnect() code with this same version of PHP for years and this problem started happening out of nowhere only yesterday.
Last resort: Don't use pconnect unless you really --and I mean *really* need it. mysql_connect() is fast enough for just about everything.
I tried changing all PHP calls to mysql_pconnect() to mysql_connect() without making any other code changes. Then I restarted MySQL. These changes had basically no effect, i.e., both max_used_connections and the number of sleeping/persistent connections both shot right back up to their "ceiling" level of ~250. Any thoughts on this?
Thanks again for your help, Mike
P.S. For anyone who's interested, the whole thread so far is reprinted below:
On 31-Mar-2003 Mike William wrote: > After a routine restart of our MySQL 3.23 server today, we began getting > the following PHP error message during times of moderately high database > server load: > > Warning: MySQL Connection Failed: Can't create a new thread (errno 11). > If > you are not out of > available memory, you can consult the manual for a possible OS-dependent > bug in > /www/data/functions/common/auth.inc on line 33 > > Line 33 of the that file is a call to mysql_pconnect(). However, if I > comment out that line temporarily, the error message simply moves to > another line in another file that is also a call to mysql_pconnect(). If > I > comment out all of my mysql_pconnect()'s then the error goes away, > although > obviously this breaks my entire website. > > I am definitely not out of memory. top says: > > 02:38:23 up 45 days, 9:23, 3 users, load average: 0.04, 0.13, 0.16 > 211 processes: 208 sleeping, 1 running, 2 zombie, 0 stopped > CPU states: 18.5% user, 3.6% system, 0.0% nice, 77.9% idle > Mem: 2069204K total, 1724224K used, 344980K free, 52840K buffers > Swap: 0K total, 0K used, 0K free, 1486132K cached > > Also, the maximum number of OS threads set in my kernel is 14336. I > can't > see any evidence that MySQL is trying to exceed this value (see the > thread > status variables I pasted below). > > On a possibly related note, I have discovered the following fact. If I > restart MySQL, the error message goes away temporarily. Then, as my site > runs for a while, the number of open persistent connections to the > database > slowly increases from 0 up to 250. When it hits 250 or 251, the error > message suddenly returns. I do not understand the significance of the > number 250, although I have noticed that when I run a SHOW STATUS, the > value of max_used_connections rises from an initial value of 0 (when the > server starts) up to 251 (at which point the error message starts), and > furthermore this rise takes place at about the same rate as the rise in > the > number of persistent connections. I don't know whether this is important > or not, although I am intrigued by the fact that this problem only > happens > when max_used_connections and the number of open persistent connections > hits the 251 "ceiling." > > Here are some select variables from SHOW STATUS as they appear once the > server hits the wall and starts giving off this error message: > >| Max_used_connections | 251 | >| Connections | 60980 | >| Threads_cached | 5 | >| Threads_created | 4303 | >| Threads_connected | 86 | >| Threads_running | 1 | > > If anyone has any ideas, please let me know. Thanks! > Your pconnects are probably sleeping too long. in /etc/my.cnf: [mysqld] set-variable = wait_timeout=10
That'll get the connections to die off after 10 seconds. If that doesn't take care of it, check your PHP version. Early (~ 2001) PHP4 had some buggy pconnect problems. Last resort: Don't use pconnect unless you really --and I mean *really* need it. mysql_connect() is fast enough for just about everything. Regards, -- Don Read [EMAIL PROTECTED] -- It's always darkest before the dawn. So if you are going to steal the neighbor's newspaper, that's the time to do it. (53kr33t w0rdz: sql table query)
-- 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]
