-1

The server is not responding and the top output shows the following: mysql is consuming more than 500% of CPU

Will the server restart solve ths issue? Will I face the same problem after restart?

# top
top - 06:30:38 up 82 days, 17:43,  1 user,  load average: 117.87, 105.81, 85.65
Tasks: 328 total,  27 running, 299 sleeping,   0 stopped,   2 zombie
Cpu(s):  0.1%us, 99.4%sy,  0.0%ni,  0.0%id,  0.0%wa,  0.0%hi,  0.0%si,  0.5%st
Mem:  35847720k total, 35823272k used,    24448k free,      788k buffers
Swap:        0k total,        0k used,        0k free,    39552k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
15661 mysql     21   0 1192m 282m  904 R 550.8  0.8   1304:06 mysqld
  118 root      17  -5     0    0    0 D 69.4  0.0  27:03.12 kswapd0
26835 root      18   0 10992  936  416 R 62.8  0.0   0:13.51 top
26831 root      19   0 72872  864  224 R 37.2  0.0   0:07.18 php
26881 root      18   0 79944 1528  228 R 18.3  0.0   0:04.96 php

Update:

I added myisam key buffer to 8 GB, query cache to 100MB and table cache to 256 dynamically (without adding it to my.cnf) on a 34 GB box. This made the box unresponsive within a day.

I restarted the mysql service and the problem is still there.

Does the memory gets fragmented because of changing this setting dynamically?

Do I need to restart server?

3 Answers 3

1

You have two issues:

  1. You have no swap. This makes it impossible for the system to evict useless data from physical memory. This is what's causing kswapd to spin and sputter.

  2. Something is consuming large amounts of memory. Whatever it is, it's not in the snippet you showed.

2
  • No swap really isn't an 'issue' as much as a contributing factor to the speed of his problem.
    – Chris S
    Mar 5, 2012 at 3:12
  • It depends on exactly what his problem is. If his problem is that he has lots of useless data suck in memory, swap would solve it. However, if the amount of useless stuff is unbounded and increasing, then it would just delay it. Mar 5, 2012 at 3:19
3

Your box is out of memory and weird things are happening. You have ~34gb of ram, and almost all of it is used. The remaining free is likely reserved as a result of min_free_kbytes.

Since youre nearly out of memory, the kernel is probably going insane trying to defragment your memory and get some larger blocks free (see /proc/buddyinfo to get a map of what fragmentation looks like). So what happens when mysql requests memory is the kernel makes the process wait while it goes and defragments the memory (trying to get a large enough block free) thus sending the CPU time on the process through the roof.

So the real issue here is where has all your memory gone? You can sort by memory in top by pressing M (shift+m).

Edit (to answer your additional questions):
Server restart might help the issue, but it'll likely be temporary. Changing MySQL settings dynamically shouldnt have this effect (unless the code has some MAJOR bugs in it which isnt likely). So whether the setting was changed at run time, or from a cold start, you'll likely just end up in this situation again.
As for if those settings are the cause, I cant answer that. I'm not familiar with performance tuning of MySQL.

0

Check your storage subsystem. Is there a problem with the disks on the system?

3
  • I guess not. The server was working fine till recently and nothing was changed except mysql key buffer and table cache.
    – shantanuo
    Mar 5, 2012 at 1:37
  • Plus its at 0% wait. Whats more odd is that kswapd is using 69% CPU, but there is no swap...
    – phemmer
    Mar 5, 2012 at 1:42
  • It's not surprising that kswapd is having a hard time making free memory with no swap! Making some swap would make kswapd's job much easier. You need at least 2GB of swap or so to get any kind of sanity on a large system. Mar 5, 2012 at 2:53

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .