Server crash as the result of MySQL or Apache

[gboethin]

Hi all. I've maintained a Fedora server going on 4 years now, but am by no means an expert. That's why I'm here.

My server's primary purpose is web hosting. I host close to 80 sites on the server; all of them were ones that I setup. Every website is dynamically generated using MySQL 5.0.24, and Perl 5.8.6. PHP is used a tiny bit by a few sites, but for the most part, it's MySQL and Perl.

A few days ago I installed some PERL modules that were necessary for a new website I'm working on. While working on the site, and testing scripts, my server crashed a few times. Crashed meaning web pages of any website wouldn't display, email servers wouldn't serve email, and if I could manage to SSH in, it took forever and a day to actually get logged on (I had to have the server power-cycled 3 times).

I began monitoring the process using the 'top' program, and noticed that after a little while of running these new scripts, an 'httpd' process would apparently get hung, lasting for 3+ minutes and using about 99% CPU. I noticed that by manually killing the process things would start to run normal again. Restarting apache would solve the problem as well.

BTW, if I let this go on too long it would get to a point that I couldn't even execute commands in Putty, evidently because so much of the CPU was being taken up. At that point the only thing to do was to power-cycle the server.

Long story short, I decided to stop working with these new scripts, since they were causing my server to crash. I rebooted the server, and since then, have had three reoccurring crashes. Today I noticed the server had crashed when I went to download email and got no response from my mail server. I managed to SSH in to my server, though it took about 5 minutes, but this time didn't see any processes hung or using excessive CPU usage. I restarted apache, and after doing this, found I was able to load a website page, though it took a long time. I browsed to a page that has a very intensive MySQL query, and it took about 30 seconds for the page to load. So then I restarted MySQL, and instantly things were back to hunky dorry normal.

So my guess is that something's wrong with MySQL. Oddly, whatever it was today didn't cause it to show excessive CPU usage like I've seen it done before. But something sure seemed to be wrong, because after restarting MySQL things started zipping right along.

Here's some more info that might be useful. I had to rebuild a table of my most MySQL intensive website yesterday after the server crashed. This particular table is one where records are constantly being inserted, updated, deleted, etc.

I'm wondering if the problem I'm experiencing now couldn't be the result of corrupt data in one of my tables?

If anyone has any thoughts or suggestions I would greatly appreciate it.

Thanks for listening,
Gregg

[Kilo]

Have you configured MySQL to log slow queries? I don't want to belittle your knowledge here, but something like this in /etc/my.cnf under [mysqld] should do the trick:


log_slow_queries = /var/log/mysqld_slow-queries.log
long_query_time = 5

So long as MySQL can write to /var/log/mysqld_slow-queries.log you'll see if MySQL is taking too long over anything in particular.

[gboethin]

Thanks Kilo. Actually I hadn't thought of that, so thanks for suggesting it. I did actually end up solving the problem. It wasn't related to MySQL after all. I'd had to install some modules to work with the new site I was working. They had a really strange, illogical effect on some other Perl Modules. I have a few scripts that are on crontab that run every 2 minutes. These scripts use the modules that were affected by the installation of of the new modules. Something was happening every now and then that was evidently causing a perl process to use 100% cpu.

What I don't understand is why the names of the scripts weren't showing up as the processes there that were consuming all of the cpu. My best guess is that they were before I could get logged in. By the time I got logged in they'd been killed automatically, but had left the mysql process in some kind of loop or something like that.

At any rate, I disabled the crontab task with the scripts and the server has run perfectly fine for 2 days now. As soon as I get a chance I'm going to have to try to figure out the problem so I can get them running again.

Thanks for the suggestion. I know this would come in handy for troubleshooting MySQL problems.