[RESOLVED] Crash!

[ncracraf]

I launched a website in April (PHP with MySqL backend). With the exception of a couple minor hiccups, the site's been running very smoothly. The site provides small business ideas and related startup information, including small business type books from amazon.com.

This weekend amazon.com sent me an email saying that their Alexa service would crawl my site this week checking for broken links and the sort. The site has gone up and down all day (the up part is my rebooting the virtual server) with the following error:

mysql_connect(): 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

The line number corresponding to this error is:

$db_msb = mysql_connect($host, $user, $pwd) or trigger_error(mysql_error(),E_USER_ERROR);

This line is in an include file I call from most any page on the site (since most any page has some database component to it). A typical call would look something like (conn.php contains the db variables and connect string stated above):

include_once($_SERVER['DOCUMENT_ROOT'] . '/inc/priv/conn.php') ;  

$sql = "SELECT ID, BIZ_TITLE from listing WHERE STATUS = 1" ;
$rs = mysql_query($sql, $db_msb) or die(mysql_error()) ;
$row_rs = mysql_fetch_assoc($rs);

As I said, I've had no problems up to this point so I have to believe it's somehow related to amazon crawling the site. Our traffic has been pretty low since we just launched the site but we've been crawled by other bots without any noticeable issues.

I'm on a virtual dedicated server with GoDaddy running PHP 4.3.1.1 and MySQL 3.23.58. One thought, I don't explicitly close the mysql connection in any script. I think PHP closes connections when the script finishes but I could be mistaken.

Thanks for any help you can provide.

[Sxooter]

You don't need to explicitly close connections in PHP, they are closed automagically.

Since you're on a virtual dedicated server, you might be able to use mysql_pconnect to fix the problem.

Are there any errors showing up in your mysql logs? apache logs?

My guess is it's just too much load too fast for you itty bitty virtual server. It happens. How many requests per second are you getting from amazon? Cam you ask them to throttle their server?

[MarkR]

I would suggest that you've got Apache's MaxClients set too high.

Never set Apache MaxClients higher than you have the resources to handle. Setting it to 10 or 20 is usually sufficient for low traffic sites.

If you set Apache MaxClients low, then further requests will be delayed and possibly time out. If you set it high, you could take your whole machine down due to running out of ram. Plus you'll get errors like this.

Mark

[Sxooter]

in addition to MarkR's post, I'd add that you can change mysql's max connections.

Generally speaking, mysql's (or any database's) max connections should be greater than the max apache children setting.

[ncracraf]

Thanks Guys.

Amazon says they actually will only hit one page every two seconds. To me this is reasonable, even with an itty, bitty virtual box. That's why I thought perhaps I wasn't closing connections fast enough.

I didn't notice anything strange in the apache log and am still combing through the mysql log entries. I'll take a look at the settings and modify accordingly. Let me monitor the site for a couple days and will report back.

Thanks again.

[Sxooter]

If you want to test your server yourself, look into wget. Run it in mirror mode against your server and see if you can cause the crash yourself before amazon can.

[ncracraf]

I'm dumping the contents of the mysqld.log file as reference in this thread. I'm not convinced simply increasing the mem_pool_size will do the trick; I'm still stumbling around a bit with the connection parameters.

Sxooter - I'll use your advice with wget for load testing; I may also try a modified bot to hammer on it for a while. Thanks for the pointer.

BOF

060614 21:37:31 mysqld started
060614 21:37:31 InnoDB: Out of memory in additional memory pool.
InnoDB: InnoDB will start allocating memory from the OS.
InnoDB: You may get better performance if you configure a bigger
InnoDB: value in the MySQL my.cnf file for
InnoDB: innodb_additional_mem_pool_size.
060614 21:37:31 InnoDB: Database was not shut down normally.
InnoDB: Starting recovery from log files...
InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 5636056
InnoDB: Doing recovery: scanned up to log sequence number 0 5636056
060614 21:37:32 InnoDB: Flushing modified pages from the buffer pool...
060614 21:37:32 InnoDB: Started
/usr/libexec/mysqld: ready for connections
060614 23:30:09 Found invalid password for user: '###bugs'@'localhost'; Ignoring user
060614 23:30:09 Found invalid password for user: '###ads'@'localhost'; Ignoring user
060614 23:30:09 Found invalid password for user: '###'@'localhost'; Ignoring user
060615 23:30:03 Found invalid password for user: '###bugs'@'localhost'; Ignoring user
060615 23:30:03 Found invalid password for user: '###ads'@'localhost'; Ignoring user
060615 23:30:03 Found invalid password for user: '###'@'localhost'; Ignoring user
060616 23:30:07 Found invalid password for user: '###bugs'@'localhost'; Ignoring user
060616 23:30:07 Found invalid password for user: '###ads'@'localhost'; Ignoring user
060616 23:30:07 Found invalid password for user: '###'@'localhost'; Ignoring user
060617 23:30:03 Found invalid password for user: '###bugs'@'localhost'; Ignoring user
060617 23:30:03 Found invalid password for user: '###ads'@'localhost'; Ignoring user
060617 23:30:03 Found invalid password for user: '###'@'localhost'; Ignoring user

Status information:

Current dir: /var/lib/mysql/
Current locks:
lock: 87cd0d0:

EOF

[Sxooter]

So, are these innodb tables?

[ncracraf]

I have to admit, I've just taken the MySQL version and configuration that came with GoDaddy. As far as I can tell they use the InnoDB engine for their virtual dedication configurations. I'm fairly new to MySQL (and Linux) so I haven't poked around too much under the hood for fear of breaking something. I'm holding tight onto the proverbial hand of "...ain't broke don't fix it...". Of course, it just broke. If I don't make eye contact, maybe it'll just go away.

Anyway, as an interesting aside, I've seen two other posts on database crashes on GoDaddy occurring on June 14 (one in 2002, another in 2004). Mine crashed June 14, 2006. Perhaps it's Mr. Parson's birthday every other year and the company takes the day off.

So this reply is essentially full of empty calories while I continue to work on the connection settings.

[Sxooter]

Well, it might have been something like a shared storage array going down that day. Might have been a one time fluke.

[ncracraf]

I found out that GoDaddy had experienced a few network issues during the same time as my database and server crash. I'm starting to doubt the crash was due to excessive Amazon traffic. With little more to go on, I'm going to mark this resolved.

As an aside, and for risk of getting kicked out of the community 😉, I moved servers over the weekend from Linux to W2K (clears throat, moves closer to the door). Trying to run a business and manage a site in a new OS was getting the best of me. I've noticed that W2K is a little slower than Linux but is much easier for me to manage, tune, and troubleshoot. Hopefully one of these days I'll have more experience (or money to hire someone who does) with Linux and can move operations back where it belongs.

Thanks for the help.