-3

What should i do if I want to prevent this high load.
My website becomes slow when load is higher than 8.
Please see iotop result too.

top - 20:23:10 up 127 days,  3:22,  1 user,  load average: 9.92, 9.87, 9.81
Tasks: 1031 total,   3 running, 1027 sleeping,   0 stopped,   1 zombie
Cpu(s): 14.7%us,  0.7%sy,  0.1%ni, 79.6%id,  4.7%wa,  0.0%hi,  0.2%si,  0.0%st
Mem:  16413676k total, 16312548k used,   101128k free,   110836k buffers
Swap: 10190840k total,  3182468k used,  7008372k free,  2033604k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
 2776 mysql     15   0 14.6g  10g 5220 S 405.7 67.4  21710:40 mysqld
 6201 apache    15   0  369m  16m 5356 S  3.7  0.1   0:00.40 httpd
 8447 apache    15   0  368m  15m 5364 S  3.7  0.1   0:00.27 httpd
10807 apache    16   0  368m  14m 4640 S  3.7  0.1   0:00.04 httpd
11074 root      15   0 11548 1668  676 R  3.7  0.0   0:00.04 top
 2088 apache    15   0  368m  15m 5972 S  1.8  0.1   0:00.93 httpd
 2737 root      15   0  3824  392  336 S  1.8  0.0 433:37.40 klogd
 6879 apache    15   0  367m  15m 5652 S  1.8  0.1   0:00.39 httpd
 8457 apache    15   0  367m  15m 5744 S  1.8  0.1   0:00.54 httpd
 8866 apache    15   0  369m  15m 4832 S  1.8  0.1   0:00.18 httpd
 9147 apache    15   0  368m  14m 4960 S  1.8  0.1   0:00.15 httpd
 9884 apache    16   0  368m  16m 6508 S  1.8  0.1   0:03.97 httpd
10583 apache    16   0  368m  15m 5148 S  1.8  0.1   0:00.10 httpd
10731 apache    15   0  367m  13m 3484 S  1.8  0.1   0:00.16 httpd
10782 apache    16   0  367m  12m 3544 S  1.8  0.1   0:00.04 httpd
14618 apache    15   0  367m  16m 6736 S  1.8  0.1   0:03.24 httpd
18697 apache    15   0  368m  16m 6744 S  1.8  0.1   0:02.47 httpd

 vmstat
procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu------
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
 2  1 3182636 468436 111776 2012016    0    0    94   274    0    0 15  1 80  5  0

iostat
Linux 2.6.18-274.12.1.el5 ()        12/31/2012

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          14.66    0.12    0.93    4.73    0.00   79.56

Device:            tps   Blk_read/s   Blk_wrtn/s   Blk_read   Blk_wrtn
sda             122.97      1504.32      4383.82 16525670026 48158254126
sda1              0.00         0.04         0.00     435456        230
sda2            122.97      1504.28      4383.82 16525231218 48158253896
dm-0            570.94      1502.31      4381.94 16503546226 48137527328
dm-1              0.48         1.97         1.89   21684608   20727136


io top
Total DISK READ: 126.48 K/s | Total DISK WRITE: 6.79 M/s (is'nt  this too high?)
  TID  PRIO  USER     DISK READ  DISK WRITE  SWAPIN     IO>    COMMAND
15610 be/4 mysql       0.00 B/s    0.00 B/s 99.99 % 25.76 % mysqld --basedi~mysql/mysql.sock
15622 be/4 mysql       0.00 B/s    0.00 B/s -7.28 % 24.92 % mysqld --basedi~mysql/mysql.sock
 3659 be/4 mysql       7.44 K/s    5.88 M/s  0.00 % 13.71 % mysqld --basedi~mysql/mysql.sock
 2822 be/4 mysql       0.00 B/s    0.00 B/s  2.88 % 10.22 % mysqld --basedi~mysql/mysql.sock
 2819 be/4 mysql       0.00 B/s    0.00 B/s  0.00 %  9.71 % mysqld --basedi~mysql/mysql.sock
15635 be/4 mysql       0.00 B/s    0.00 B/s -3.72 %  8.09 % mysqld --basedi~mysql/mysql.sock
15629 be/4 mysql       0.00 B/s    0.00 B/s -2.42 %  5.58 % mysqld --basedi~mysql/mysql.sock
  801 be/3 root        0.00 B/s  208.31 K/s  0.00 %  3.97 % [kjournald]
 2842 be/4 root        0.00 B/s    0.00 B/s  8.09 %  3.81 % irqbalance
 2820 be/4 mysql       0.00 B/s    0.00 B/s  0.00 %  2.88 % mysqld --basedi~mysql/mysql.sock
 7001 be/4 apache      0.00 B/s    0.00 B/s  0.00 %  2.70 % httpd
15636 be/4 mysql       0.00 B/s    0.00 B/s 10.32 %  2.51 % mysqld --basedi~mysql/mysql.sock
 8034 be/4 apache      0.00 B/s    0.00 B/s  0.00 %  2.42 % httpd
 2140 be/4 apache      0.00 B/s    0.00 B/s  0.00 %  2.32 % httpd
Mehdi Azizi
  • 51
  • 11
  • Sammitch already gave an excellent answer. But I would like to add that it may be that you simply have a terrible mysql configuration. mysql is visibly allowed to take more ram than you realistically can provide. If you can't decrease mysql's usage, increase ram. – Grumpy Dec 31 '12 at 18:57

4 Answers4

6

Your particular problem is most certainly I/O-related due to how deep into your swap space mySQL has pushed you. Ideally you should be using zero swap the vast majority of the time since the instant the OS starts swapping memory to disk your performance will nosedive as processes wait for I/O. Think of it like Performance = 1 / N ^ swap where as the OS uses more swap space the performance generally gets exponentially worse. You appear to be 3 gigabytes deep into your swap space, which is just plain horrifying to me.

If, after you've adjusted your mySQL settings as you've mentioned, you're still averaging more than a few megabytes in swap you should consider: [best to worst idea, 2&3 are roughly tied]

  1. Optimizing your database schema and queries.
  2. Adding more RAM to the machine.
  3. Splitting the mySQL server onto another dedicated machine.
  4. Migrating your swap space to a high-speed device like an SSD. [this kills the SSD]
Sammitch
  • 2,072
  • 1
  • 20
  • 34
  • Hate to say, but "deep into swap" is not an Problem, it is too Little RAM, for whatever reason. Swapp and thus IO Problems are the symptom, not the cause. – TomTom Dec 31 '12 at 18:58
  • And *zero* swap usage makes performance worse rather than better -- it means even data that hasn't been accessed in hours is still occupying precious physical memory. (But you're right, his performance problem is due to mysql swapping.) – David Schwartz Dec 31 '12 at 18:58
  • @DavidSchwartz It's a bit over-the-top to say that zero swap usage is bad. Stale pages in physical memory aren't necessarily a good thing, but I don't see the point in swapping them to disk and generating unnecessary I/O when there is still a reasonable amount of unused RAM left. Not to mention that when you *do* need to access that data after a few hours it's orders of magnitude slower to bring it back from disk. – Sammitch Dec 31 '12 at 19:08
  • @Tomtom, a user is "deep into swap" if and only if there's too little ram. So, I see no difference between the two methods of saying. – Grumpy Dec 31 '12 at 19:08
  • @Sammitch: I think you're misunderstanding what I'm saying. I'm not saying you should discard mapped pages used by a process when under no memory pressure just so that you'll have to page them back in later. I'm saying you should write them to swap so that you *can* discard them later if you *are* under memory pressure. See [this answer](http://serverfault.com/a/420793/91987) for the details. – David Schwartz Dec 31 '12 at 19:11
  • So, this is either a programming mistake (i.e. using too much memory, must be located and reprogrammed for less memory), a configuation error (large caches where memory should be used for other things, hardcoded) or simply too small hardware, though seriously - 16gb on that number of web servers does come out somehow funny. If i read that right, MySql uses 14 gigabytes - should possibly be nailed down to 8, then see what happens. Not sure how that is configuraed, but that may be a "too large cache statically assigned" type of thing. Yes, memory is good, but the moment you force swapping that.. – TomTom Jan 01 '13 at 10:54
  • ..is not really memory anymore but slow disc. – TomTom Jan 01 '13 at 10:55
1

What should i do if I want to prevent this high load.

FAster server, better programming?

seriously, you look at it from the wrong side. LIke "I am a taxi driver, I drive too many km per day, how do i cut that down". Your request side is fixed (not taking into consideration thigns like ddos attacks) and items must be served.

So it runs down into - get a beefier server, assign more ram for caching (beefier server) or hit some programmers with the "fix your bugs" stick to make them write better code.

In this case it could be a mySql issue, but then this is something that would go back to the developers of the site as "use indices, dudes" or "wrie better queries". Not that I have not seen that (sql server, portal, ZERO indices, server "died" with IO eating everything). But that is something the developers of the site(s) have to patch.

TomTom
  • 50,857
  • 7
  • 52
  • 134
  • I am developer of the website(and everything else of website too). I have consumed so much of time for making optimized queries and better structure. But now I think I must upgrade my server. I think my problem is IO not cpu. So for now I can buy seprate hard disk for mysql. what do you think? – Mehdi Azizi Dec 31 '12 at 17:23
  • No idea. I have no clue what you talk about, when you talk of a server - you dont give any information. Your IPS numbers are very low for a powerfull server -looks like a misused desktop or smaller virtual machine. – TomTom Dec 31 '12 at 17:27
0

From the data here, you are SWAPPING. That is really killing your performance as that is what any type of swapping does.

My immediate suggestion is to double the amount of memory you have in the machine....and of course increase the swapping space to match the memory size.

mdpc
  • 11,698
  • 28
  • 51
  • 65
  • Increasing the swap partition usually means you have to reinstall the OS, which is a pain in the b-hole. – Sammitch Dec 31 '12 at 18:45
  • 2
    I would have up voted, but don't agree with recommendation to increase swap. There's just no beneficial point of doing that at this stage. – Grumpy Dec 31 '12 at 18:55
0

I think Mysql is your problem. You are using all of your memory which causes swap. Swap causes a higher cpu because your swap partition is filling and your IO can't handle anything else. Your swap is being used as you can see, but it's not full yet. While a faster disk is interesting, you should increase memory as well. Swap is only meant for occasional and not constant usage.

Lucas Kauffman
  • 16,818
  • 9
  • 57
  • 92
  • I know mysql is using almost all of resources but my question is what hardware should I upgrade? better CPU, more RAM, or faster disk? – Mehdi Azizi Jan 22 '13 at 19:19
  • More RAM definitely. – Lucas Kauffman Jan 22 '13 at 19:20
  • Can I ask why? Can you please explain It? – Mehdi Azizi Jan 22 '13 at 19:23
  • Like I said, your swap is full which means you have too few memory, when your swap is full your IO and cpu will go through the roof. – Lucas Kauffman Jan 22 '13 at 19:24
  • No, my swap is almost free. I added the free command result at bottom of my question. – Mehdi Azizi Jan 22 '13 at 19:28
  • Where, I do not see your swap. – Lucas Kauffman Jan 22 '13 at 19:32
  • my new question is here http://serverfault.com/questions/471483/high-load-cause they have closed that. This results are from my old config. they thought I am double posting my question and they have closed it – Mehdi Azizi Jan 22 '13 at 19:36
  • The problem remains the same, increase your memory – Lucas Kauffman Jan 22 '13 at 19:38
  • but why? swap is free? Are you sure It is not disk? – Mehdi Azizi Jan 22 '13 at 19:40
  • @MehdiAzizi Your new question got folded into this one because they appeared to be substantially identical, with the same end answer. If we missed a key difference please post another new question *referencing this one* and ***showing us only the relevant, new information pertaining to your new problem*** - It's entirely possible we missed something when we merged them because frankly your new question was a sea of gray text and I wasn't going to go line-by-line to parse differences. Make it easier for us to spot what's wrong and we may be able to give you better answers... – voretaq7 Jan 22 '13 at 20:42