0

I am a newbie to Linux OS and I had trouble on my drupal based sites which had turned out to be really slow. I am using 2 separate servers for hosting and MYSQL. On preforming the top command, I got the following result

top - 06:34:26 up 17:54,  1 user,  load average: 4.54, 4.98, 5.36

Tasks:  79 total,   1 running,  78 sleeping,   0 stopped,   0 zombie
Cpu(s): 74.8%us, 23.6%sy,  0.0%ni,  0.0%id,  0.0%wa,  0.0%hi,  1.7%si,  0.0%st
Mem:   4044336k total,  3963584k used,    80752k free,    21760k buffers
Swap:  1477940k total,        0k used,  1477940k free,  3530868k cached

PID   USER      PR  NI  VIRT  RES    SHR  S   %CPU  %MEM    TIME+  COMMAND
1077  mysql     20   0  502m  181m   4124 S    98.8  4.6   549:02.28 mysqld

The CPU usage for mysql process is 99.7%. Does it mean that my processors are maxing out. What can be done to resolve this problem? Do I need to add more cores? However, on the contrary the load averages are low.

Im an absolute newbie and have picked up most jargon from books. Shall appreciate if someone can help.

Loveleen Kaur
  • 115
  • 7

6 Answers6

3

Try this ultra-handy flowchart to identify your problem (although I'd guess you're CPU-bound):alt text

From Major Hayden's blog.

Andy
  • 5,230
  • 1
  • 24
  • 34
0

If this server MySQL, who uses all the memory?... MySQL use 181 Mb of memory. It is very small.

  1. Look: SHOW FULL PROCESSLIST\G;
  2. Look log file(mysql and you web-server)
  3. Use mysqltuner.pl(http://mysqltuner.pl/mysqltuner.pl), to tune my.cnf.
  4. Optimizing at the Hardware Level(add memory and cpu)
bindbn
  • 5,211
  • 2
  • 26
  • 24
  • Hi Bindbn, True, its not the memory, but is it the CPU Usage thats not able to process it well? Unfortunately though I have access to most data, Im unable to analyse it well. Could you please elaborate on it. I need to identify the symptoms first and then take action. Could you tell me how I should proceed and analyse. Perhaps you could direct me to any post or book so that I could identify the cause of the problem. – Loveleen Kaur Oct 01 '10 at 12:24
  • Thanks Bindbn ! Perhaps due to my ignorance I wasn't able to fully understand your indications. Now after a rigorous 2 day analysis, your steps make perfect sense.. – Loveleen Kaur Oct 04 '10 at 03:34
0

You should check out why MySQL is so loaded, if you already have 2 or 4 cores that's probably not your problem. A good, cleverly optimized db system will be able to use much more ram before maxing out CPU time. Some things to look at before thinking about hardware upgrades are:

  • table indexes
  • slow queries
  • tmp tables settings
  • (edited) OPTIMIZE TABLE
  • (edited) database fragmentation (see marcoc's answer)
  • enough I/O capacity on the system

I am just guessing, but it seems that your system is spending too much time on the db itself (not much in I/O and not at all swapping or the like), so you probably should check out your indexes... maybe the db is just busy scanning tables because it hasn't the proper indexes to use for the queries you run (if that's the case, adding hardware won't help)

After that, if you still need more horsepower, I'd add more ram to the system, maybe up to 4GiB per core (ie, 16GiB if you run 4 cores), but that really depends on your dataset size and access patterns (mostly read, mostly write, etc...)

Luke404
  • 5,826
  • 4
  • 47
  • 58
  • Luke404, you've given a wonderful answer to what should be done to reduce the load. But what I seek to understand is that 'is my interpretation that load on CPU is a lot and needs to be reduced' is correct? I first need to find out if the problem lies in CPU usage, memory or I/O related. – Loveleen Kaur Oct 01 '10 at 12:43
  • You don't actually "need to reduce cpu usage", you could just let the system run as it is. What you need is to get more performance out of it, tuning the system is one way to get more performance, lower cpu usage could be a collateral effect of that optimization. Or maybe you'll stay at current load levels after tuning but getting more performance out of it. Details really depend on what your MySQL is doing. – Luke404 Oct 01 '10 at 23:29
  • After analyzing the slow queries log, I deduced that the slowest ones are these having multiple JOINS. I suppose I need to rewrite all slow queries again. – Loveleen Kaur Oct 02 '10 at 05:57
  • ...and/or to add proper indexes to the tables you're joining (use EXPLAIN) – Luke404 Oct 02 '10 at 08:09
0

The CPU usage for mysql process is 99.7% for ONE of your processors. If you have more than one processor (as indicated by "Does it mean that my processors are maxing out), then there must be something else that is CPU-bound as well?

This thread explains this very well: confused-by-cpu-values-in-unix-top-command

Community
  • 1
Avada Kedavra
  • 1,294
  • 2
  • 13
  • 19
  • I executed this command for finding the number of processors execdigital@EXECLINUX2:~$ cat /proc/cpuinfo | grep 'processor' |wc -l and I got the result as 1 – Loveleen Kaur Oct 01 '10 at 13:07
  • So, it means that there is just one processor and its maxing out..isnt it? – Loveleen Kaur Oct 01 '10 at 13:07
  • If you press "1" while being in top, you will see the load per CPU. If all of your CPUs are maxed out I think you have only two options: optimize the site, or upgrade your hardware. – Avada Kedavra Oct 01 '10 at 13:17
  • There is only one CPU and on pressing 1, I get the following value Cpu0 : 62.5%us, 15.5%sy, 0.0%ni, 17.7%id, 0.4%wa, 0.3%hi, 3.7%si, 0.0%st – Loveleen Kaur Oct 01 '10 at 13:24
0

You wrote that the site

had turned out to be really slow

since when? Did it happen gradually?

If the site became gradually slower, then it could also be a DB fragmentation issue. In this case, or if you cannot create indexes, consider also a quite trivial dump/restore.

marcoc
  • 748
  • 4
  • 10
  • The sites have become slow suddenly. We had tested it for the CDN for sometime after which it was taken off them. Also, its a drupal based magazine site with multiple posts being added everyday. Also, it is picking up a lot of data from external RSS feeds. Could this be a cause of fragmentation. However, this slow down has begun recently. The entries were being made for a long time. – Loveleen Kaur Oct 01 '10 at 12:55
0

I think strange mysql using only a small RAM 4.6% and almost the whole system RAM being used without running as TOP says 1 process running but that's not the cause of your CPU overloaded.

If the problem is recent and the site old, I would check if the CPU is not overheated and slowed down by BIOS. I had a similar problem and the machine was extremely slow and using almost 100% of CPU on both cores as soon as the CPU was slowed down because of temperature. You can see the overheating with tail -f /var/log/syslog and probably in BIOS setup if you restart the machine and check cpu temperature in BIOS setup. In my case, BIOS was reporting cpu temperature above 75oC (reached 95oC after some testings running 2 virtual machines at the same time). One simple test you can do without shutting down the machine is to open the case and use a fan (a normal fan for people, not for cpu) directly on the machine, temperature should drop quickly and cpu overload will stop (the machine speed up).

I just dismounted and cleaned the cpu cooler (it was almost totally clogged) and it solved the problem with BIOS reporting cpu temperatures below 55oC, fast machine and no overloaded cpus.

laurent
  • 2,055
  • 16
  • 14