mysqld 99% cpu usage = hang

C-speed

Hey,
I have a rather strange problem.. I'm running pretty large php/mysql database on a rather large website.
Every 10-12 hours, mysqld will hang. When I check the CPU usage in TOP, it is always stuck at 99%. I then kill the mysqld process, and everything will resume as normal.
Mysql CPU usage is usually around 60-80%... but when it hangs, its always at 99% and when i check the .err mysql log file, it doesn't show any errors.

Last night, I just happened to be watching TOP when mysqld hung, and CPU usage was normal, but slowly would ramp up to 99% and as soon at it hit 99%, mysqld hung and brought the website to a halt.

One thing that may or may not be of significance is that all the mysql tables are InnoDB - they were converted from myiasm using Alter Table.

I have no clue what is causing this... Here's is the specs on the box:

dual p4 2.1 with 2g Ram
php 4.2.3
mysql 3.23
apache 1.3
FreeBSD-Stable

Any ideas or suggestions would be great! Thanks.

ultraslacker

Are you using bsd?

sglane

You should check the processlist of mysql before you do any thing. You can do this by the following commands

mysqladmin -uusername -ppassword -hhost processlist

I wrote a script a couple years ago to monitor the servers on the system. including apache, mysql, memory usage and users logged in. Maybe this can help you. Basically you run it with an argument which is the time to refresh. It is in an infinate loop (press Ctrl-C to end it) with a sleep command (with the number of seconds you passed in). Make sure you put your username and password in for the mysqladmin command so it doesn't prompt you. Also, don't forget to chmod it to 755 and put it in /usr/bin/. That way you can just type it in from anywhere. It's crude, but it works for me. Here the script is:

#!/bin/sh
if [ $1 > 0 ]
then
while ( x=1 ); do
clear;
connections_on_80=netstat -a -n | grep ":80 " | wc -l;
apache=ps auxwwf | grep httpd | wc -l;
echo;
echo +----------+;
echo |\ \ Apache \ |;
echo +----------+;
echo \ \ Threads $apache;
echo \ \ Connections $connections_on_80;
echo;
echo +-------+;
echo | \ Who \ |;
echo +-------+;
w;
echo;
echo +--------+;
echo | \ Free \ |;
echo +--------+;
free;
echo;
echo +---------+;
echo | \ MySQL \ |;
echo +---------+;
mysqladmin -h mysql -uroot -pMAKE.SURE.YOU.PUT.YOUR.PASSWORD.IN.HERE processlist;
sleep $1;
done;
else
echo "Usage: $0 [SECONDS]";
echo " $0 3";
echo " $0 1";

fi

C-speed

Sorry.. forgot that bit of info.. yes FreeBSD-stable.

I also periodically check the processlist via "SHOW PROCESSLIST" in mysql.. and i've also noticed that every 15min or so, a new process shows up in the ROLLBACK state, and stay there indefinately... but when i check back a few hours later, the longer running processes in the ROLLBACK state would dissapear from the list - ie. there would be 8 in the ROLLBACK state, i come back a few hours later, and there would only be 2 on the list:

Kill 17907 cspeed localhost regdb Query 7389 NULL ROLLBACK
Kill 18115 cspeed localhost regdb Query 7363 NULL ROLLBACK
Kill 31316 cspeed localhost regdb Query 5821 NULL ROLLBACK
Kill 31658 cspeed localhost regdb Query 5821 NULL ROLLBACK
Kill 36253 cspeed localhost regdb Query 5322 NULL ROLLBACK
Kill 44861 cspeed localhost regdb Query 4368 NULL ROLLBACK
Kill 57627 cspeed localhost regdb Query 2954 NULL ROLLBACK
Kill 60878 cspeed localhost regdb Query 2602 NULL ROLLBACK
Kill 66261 cspeed localhost regdb Query 2007 NULL ROLLBACK
Kill 69933 cspeed localhost gmgdb Query 1581 NULL ROLLBACK
Kill 81246 cspeed localhost gmgdb Query 258 NULL ROLLBACK

Not sure if this is relavent or not...

ultraslacker

I have dealt with the same issue on bsd and have yet to resolve it. The problem is due to linuxthreads. I've tried recompiling mysql with all sorts of options to no avail.

There are some options (I opted for a holding pattern while I move the data to postgresql).

Have you tried 5.0rc? It may have better threading implementation.

An interesting read about the problem and options

C-speed

Thanks for the replies guys... i was able to catch mysql crash again just now... its like clock work.. every 10hours or so, it hits 99% and will crash.

This time, just before it crashed, i was able to get a processlist, and there was around 20-25 processes in the ROLLBACK state, some of which have been like that for thousands of seconds.

Once it crashes and i kill the mysqld process, everything goes back to normal and ALL of the ROLLBACK'd processes are gone.

This is getting extremely frustrating now... I'm thinking of just setting up a crontab to restart mysql every 8hours or so.. but even if that works, its a pretty retarded way to work around the problem.

ultraslacker

You may want to run bsd in linux compatible mode (or try a linux box)

C-speed

Ok.. i'll give that a try..

Also, not sure if this means anything either.. but I've noticed that as the crash hour approaches.. msyqld slowly starts to consume more and more CPU time. Where normally it will average around 60-80% even at peak times... it will slowly rise higher and higher.. to the point its at 90% CPU usage at off-peak times.... (then as the crash hour DOES finally come, it will suddenly ramp up to 99%). Pretty strange.

I was thinking maybe its because my innodb tables were pretty big? The database is 200megs... and i even tried purging tons of old data from the database, yet the size of the db reported remained the same... is that normal? (as reported by phpMyadmin)

Is there a way to OPTIMIZE TABLES on innodb tables?

ultraslacker

Hop on google groups and check for mysql + high cpu usage + bsd.

200 megs is a drop in the bucket and I do not think innodb is a factor.

C-speed

Thank ultra.. i really appreciate your suggestions... I'll look into that...

I just took a peak at the process list... its about 8 hours of runtime since i last had to restart mysqld... and cpu usage is at 90% and traffic is only 50% that of peak times..

Anyways.. normally about now, there would be quite a few processes in the ROLLBACK state.. however today, it actually shows the queries that are stuck:

Kill 33855 cspeed localhost regdb Killed 27935 Sending data SELECT networth
FROM userINV_table, userID_table
WHERE userID_table.userID = userINV_t
Kill 37082 cspeed localhost regdb Killed 27373 Sending data SELECT dispName, cash, networth, turns, models, modelHAP, crewID, admin, online, ban, email2, textme
Kill 37086 cspeed localhost regdb Killed 27373 Sending data SELECT networth
FROM userINV_table, userID_table
WHERE userID_table.userID = userINV_t

I tried to kill them, but they continue to show up on the list. Would this suggest that those queries are bad somehow? (ie. needs to be recoded somehow?) Would those be the queries that are being ROLLBACK'd, but for some reason today, it showing the actual query and not rolling back?

(pulling hair out)!

C-speed

ultra - you are the man!

I found this from google:

http://www.hostbaby.com/misc/mysql-openbsd.html

It looks like this person has the EXACT same problem I'm experiencing.. I want to give his solution a try, however I'm a complete linux n00b (first time setting up a server).

So, I'm not sure if all his steps apply or are the same, since he was using OpenBSD and i'm using FreeBSD (ie. the first 2 steps in his guide).

Thoughts?