5

I have a Server (2x Hexa-Core Xeon E5649 2.53GHz w/HT with 32GB RAM and 20000 GB Bandwidth) running Ubuntu Server 12.04 LTS. The server runs LAMP and serves one website only, the estimated number of users is to be ~ 15,000 at the same time.

At the moment i have around 2000 users online each of them runs 50 MySQL queries (small values mostly select and insert) from the beginning until the end of the session. Server CPU Load is high at this number of connections while the RAM usage is almost 1GB out of 32GB its worth mentioning that the server was running very fast with no problems at all but am concerned about the load average. http://s12.postimage.org/z7hi6mz3h/photo.png

top - 03:02:43 up 9 min,  2 users,  load average: 50.83, 30.14, 12.83
Tasks: 432 total,   1 running, 430 sleeping,   0 stopped,   1 zombie
Cpu(s):  0.1%us,  0.2%sy,  0.0%ni, 66.5%id, 33.1%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:  32939992k total,  3111604k used, 29828388k free,    84108k buffers
Swap:  2048280k total,        0k used,  2048280k free,  1621640k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND                                                                                          
 2860 root      20   0 25820 2288 1420 S    3  0.0   0:11.18 htop                                                                                             
 1182 root      20   0     0    0    0 D    2  0.0   0:01.46 kjournald                                                                                        
 1935 mysql     20   0 12.3g 161m 7924 S    1  0.5 102:31.45 mysqld                                                                                           
   11 root      20   0     0    0    0 S    0  0.0   0:00.38 kworker/0:1                                                                                      
 1822 www-data  20   0  247m  25m 4188 D    0  0.1   0:01.81 apache2                                                                                          
 2920 www-data  20   0     0    0    0 Z    0  0.0   0:01.20 apache2 <defunct>                                                                                
 2942 www-data  20   0  247m  23m 3056 D    0  0.1   0:00.20 apache2                                                                                          
 3516 www-data  20   0  247m  23m 3028 D    0  0.1   0:00.06 apache2                                                                                          
 3521 www-data  20   0  247m  23m 3020 D    0  0.1   0:00.09 apache2                                                                                          
 3664 www-data  20   0  247m  23m 3132 D    0  0.1   0:00.09 apache2                                                                                          
 3674 www-data  20   0  247m  23m 3252 D    0  0.1   0:00.06 apache2                                                                                          
 3713 www-data  20   0  247m  23m 3040 D    0  0.1   0:00.09 apache2                                                                                          
    1 root      20   0 24328 2284 1344 S    0  0.0   0:03.09 init                                                                                             
    2 root      20   0     0    0    0 S    0  0.0   0:00.00 kthreadd                                                                                         
    3 root      20   0     0    0    0 S    0  0.0   0:00.01 ksoftirqd/0                                                                                      
    6 root      RT   0     0    0    0 S    0  0.0   0:00.00 migration/0                                                                                      
    7 root      RT   0     0    0    0 S    0  0.0   0:00.00 watchdog/0                                                                                       
    8 root      RT   0     0    0    0 S    0  0.0   0:00.00 migration/1                                                                                      
    9 root      20   0     0    0    0 S    0  0.0   0:00.00 kworker/1:0


root@server:~/codes# vmstat 1
procs -----------memory---------- ---swap-- -----io---- -system-- ----cpu----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa
19  0      0 29684012  86112 1689844    0    0    19   590  254  231 48  0 47  5
23  0      0 29704812  86128 1697672    0    0     4   320 11100 8121 77  1 22  0
33  0      0 29671044  86156 1705308    0    0     0  5440 13190 9140 95  1  4  0
33  3      0 29670088  86160 1706288    0    0     0 32932 12275 7297 99  0  1  0
35  0      0 29693456  86188 1710724    0    0     4   676 12701 7867 98  1  1  0
^C

I have not changed any of the default configurations that comes with Ubuntu. Is this load normal for such powerful server ? is there any optimization i can make to Apache/MySQL to minimize the load ? What do you recommend ?

EDIT: LOAD AVERAGE at 52!!!!!!! http://zertux.com/IMG_0117.PNG

****UPDATE**** It turns out that the DBA did not add indexes to the tables, after adding indexes the Load average dramatically went down from 93 to 1.2 :) Everything is super awesome, thanks everyone for the help!

23
  • Can you post the output of top? I wanna see your iowaits. Sep 26, 2012 at 6:54
  • The output from "vmstat 1" will be helpful. If you are using default configuration for with website with this amount of users then I think you can do some tune that will make server running smoothly.
    – B14D3
    Sep 26, 2012 at 7:02
  • pastie.org/4803616 top output
    – zertux
    Sep 26, 2012 at 7:03
  • vmstat -1 output pastebin.com/Vusa3wJC
    – zertux
    Sep 26, 2012 at 7:07
  • How's the site? Still working? Performant? Fast? Sep 26, 2012 at 7:09

5 Answers 5

6

Looks OK to me.

You've got 12 cores.. across 2x 6-core CPUs. So at 100% performance, your load average should be 12.

Load average is funny. I do not think it means what you think it means.

Load average is really an indication of how many processes are running at any one time, averaged over 1, 5 and 15 minute windows.

Looks to me like you're a little overcommitted, but not drastically.

Perhaps use http://mysqltuner.pl/mysqltuner.pl to get some idea of how your mysqld settings equate to real usage amounts.

The next logical step of course is to separate MySQL and Apache onto different boxes. I'm not sure you're at that level yet, because you've still got a pantload of RAM free for MySQL to suck it up into. You might find some benefit from making query caches and key buffers bigger, and probably have a deeper look at MySQL's slow query log, and see if you can optimise the tables at all .

There's loads of information about how to read load averages, and really it's more sensible to divide the load average number by the number of cores, so you've got some idea of how utilised the server actually is.

I can see now you've got 33% iowait. I suspect.. that you've got a fairly write-heavy database, and this is causing tables to be locked when you're writing, meaning that concurrent writes cannot happen.

Having had a sniff at my.cnf, it looks like the max_connections is quite high, but that's not a huge concern, but it does mean that if you're using all of them, you'll need 27GB of RAM to allow this. Which is loads, but not a huge concern, again.

Consider turning on PHP APC Opcode caching.

** Edit **

Having seen the query log now, I'm inclined to think that there's a few things that might benefit the server.

  1. PHP APC Opcode caching (makes apache more efficient generally)
  2. Convert all tables to InnoDB unless you've got a really good reason. IF that reason is fulltext searching, find a better way to do it, and move to InnoDB.
  3. Buy another server, and make it a dedicated DB host. Fit it with SAS disks, and separate it into partitions so that logging and data are on seperate spindles (or rather, RAID arrays).

Without a much deeper look into what the hell is going on, it's difficult to actually say.

Might be worth a trial run with NewRelic for PHP. It's free for a month, and does tend to give good insight into bad code smells.

Alternatively, I am available for consultancy ;)

8
  • 10 min ago the (15 - min load average) was 23
    – zertux
    Sep 26, 2012 at 7:05
  • The web developers said there wont be such heavy load on the database but i agree with you, what can i do about it ?
    – zertux
    Sep 26, 2012 at 7:12
  • 3
    Stop listening to developers who don't know about production environments. Sep 26, 2012 at 7:13
  • The RAM used atm is 2GB/32GB
    – zertux
    Sep 26, 2012 at 7:30
  • 2
    @zertux a web developer does not know how to run an RDBMS, per definition. I probably don't, either, but I'd have split MySQL and Apache+PHP on two (maybe less powerful) boxes rather than putting everything on a single big thing. Or at least be sure to put the db on its own spindles, many of them (you didn't tell us what I/O system you're using).
    – Luke404
    Sep 26, 2012 at 8:00
2

There is one striking point int your top output and that is number of processes in D state. A good chunk of apache2 and even kjournald is even in D state. D state processes are known to increse CPU load.

Most usually, a process goes into D state when it waits on IO. After getting the IO, it again comes to R state or S state from D. The next thing you can do to perform live debugging is to check for how much time these D state processes are running. If for quite some time, a problem.

Anyway, your problem, if it is high load average lies in IOwait as 33.1% is the value of iowait reported by top. %usr and %sys are not much, so we can safely ignore that processes are going haywire or CPU is under performing or there is a bottleneck with memory. The problem is iowait, apparently. I mostly work with RHEL, so I am not 100% sure of ubuntu and if any inbuilt tools are there.

What I do mostly is to collect, several iterations of top, vmstat for some time, iostat for some time (with proper switches that show device break up), one iteration of ps and ps -xv and check them. Often, the first level of debugging can be done from this much. Next, I might collect some oprofile, perf outputs depending on RHEL version but that is another story.

Irrespective, please check all the debugging commands at the same time to get a more finely granular view.

4
  • 1
    I suspect (although having no real evidence) that mysql is writing lots of stuff to a single spindle, and this is causing lots of stuff to block on IO, hence the D "Uninterruptible Sleep" States. Sep 26, 2012 at 7:23
  • Tom, agree with you man. Perhaps we can look at the slow query logs. Anyway, an iostat would be good. Is the tool blktrace available in ubuntu. I have recently got to that tool and that is much better than regular iostat. Sep 26, 2012 at 7:30
  • Yes its available, should i run it ?
    – zertux
    Sep 26, 2012 at 7:50
  • Yes, if you mean blktrace is available, I can certainly take a look. Run against the filesystem having the problem, blktrace /dev/sdX and attach the file. Sep 26, 2012 at 9:19
0

I suspect process maybe waiting on IO which maybe causing the load average to go high. After all load average relies on the queue of runnable processes for cpu. Do you see any high value in iostat command ?

1
  • Sometimes its around 20% and then goes down below 1%, am really lost.
    – zertux
    Sep 26, 2012 at 20:34
0

I've had this kind of situation before when basically on minimal server load the website seems to be responding very slow.

Top did not shwo any process that is consuming way too much of ram or cpu. But the alarming thing was Server Laod and waiting time of cpu. As in your case it's 33%.

In my case the problem turned out to be on Server Hosting Company's. Their SAN has been performing really slow for weeks before they finally decided to change their storage. Only after my server started to work fine.

Mine was a VPS not a dedicated server.

0

I had a similar issue with the desktop version of Ubuntu 12.04 (it was being used as a server (not my choice)) .

If you have installed a desktop manager on your box you might find that vsync is the problem.

Unity dm was causing me a permanantly raised CPU load for me switching vsync off instantly fixed this. I don't know if other dm's are causing this issue also.

You must log in to answer this question.

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