Web Hosting Talk : Web Hosting Main Forums : Hosting Security and Technology : mysql 100% CPU usage

[Zeest]

After running for a while mysql suddenly starts using 100% CPU and my site goes down due to apache waiting with 150 sleeping processes.

Here is the top stats

PHP Code:

top - 00:20:47 up 12:01,  2 users,  load average: 1.11, 1.04, 0.95
Tasks: 200 total,   2 running, 198 sleeping,   0 stopped,   0 zombie
Cpu(s):100.0%us,  0.0%sy,  0.0%ni,  0.0%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:   1004152k total,   618132k used,   386020k free,   118436k buffers
Swap:  2000084k total,        0k used,  2000084k free,   282372k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
 2011 mysql     18   0  177m  43m 5684 S 99.9  4.5  86:51.60 mysqld
14877 root      15   0  2360 1216  860 R  0.3  0.1   0:00.11 top
    1 root      15   0  1948  648  556 S  0.0  0.1   0:00.97 init 


ps ax | grep apache -c
152

On restarting mysql everything goes back to normal.

Can someone tell me how I could go about rectifying this?

I'm sure the server can take the load, coz before this the site was on a way slower machine and worked flawlessly.

[Zeest]

Oh btw the configuration directives and files are the default ones. I've installed everything using apt

[anatolijd]

at least you have to see what makes mysql to use all cpu resources,
use this command to see what mysql is doing:

# mysqladmin processlist

[Tech4server]

remove mysql-server and install it again . Wierd as it is , had a same problem with my cient's server , no configuration would make it work normally , so i removed mysql and installed it again from source and it worked perfectly .

[Zeest]

So this time around, should I compile it myself from source?

[RBBOT]

What version of mysql are you using? I had this issue with 5.0.22 and 5.0.26 on an old server where CPU would jump to 100% approximately every 2-3 weeks despite no queries running against the database - show process list was empty and my admin session was the only connection. A restart was the only thing that would resolve it. I've not seen it happen since version 5.0.33 though.

[Zeest]

mysql -V
mysql Ver 14.12 Distrib 5.0.38, for pc-linux-gnu (i486) using readline 5.2

[Scott.Mc]

As you are using the default UNOPTIMIZED mySQL that is more than likely your problem.

cat /etc/my.cnf
free -m
cat /proc/cpuinfo
mysqladmin processlist

[ImageLeet]

mysqladmin processlist->check the open tables for account using the most.
sometimes ppl are running inefficient crons for posting stuff.

[Abu Mami]

Check for cronjobs that are accessing mySQL. When you find a script that's runniung around the same time as your 100%CPU, get to work optimizing the queries.

From experience...
- use the LIMIT clause to limit the size of queries
- use the JOIN clause to greatly optimize queries across tables
- make sure that indexes are defined

[kamyana]

can you post /etc/my.cnf ??

[Sys Admin]

You need to optimize your mysql & apache well.