-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
  • 1
  • 12
  • 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, 1&2 are roughly tied]

  1. Adding more RAM to the machine.
  2. Splitting the mySQL server onto another dedicated machine. This is a different flavor of "add more RAM", but also a good idea to segregate your workloads.
  3. Migrating your swap space to a high-speed device like an SSD. [this kills the SSD]

2023 edit

Removed the "optimize queries/indexes" suggestion as it's not likely to make much impact.

The importance of having extra RAM that is actual RAM, not swap, cannot be understated. The OS will use unallocated memory to cache blocks from the filesystem as they're read/written. Ultimately this leads to your frequently accessed data living in memory and decreasing your IO load significantly.

For something like a DB workload the ideal is to have enough memory to fit your entire DB inside of it, or as near as your budget can manage.

As the memory available for cache shrinks, IO load ticks up and can impact overall performance quite severely. Once you start getting active memory pages bouncing in and out of swap you're in the second-deepest circle of performance hell, at best.

I should probably also walk back the "ideally you should be using zero swap" statement as having a "medium" vm.swappiness setting and letting the OS move stale pages over to swap can get you a reasonable chunk of recovered RAM to work with. But a 10GB swap file in this case is just excessive.

Sammitch
  • 2,111
  • 1
  • 21
  • 35
  • 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
  • IMHO Option 1 above isn't going to make much difference to the behaviour - it will have a small impact by making stuff faster and thereby reducing the concurrent httpd processes. OTOH reducing the memory footprint of mysqld (innodb_buffer_pool?) such that it all fits in RAM will (and option 1 is part of that process). – symcbean Aug 09 '23 at 11:02
  • @symc thanks for the note, updated the answer. – Sammitch Aug 09 '23 at 19:40
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
  • 51,649
  • 7
  • 54
  • 136
  • 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,856
  • 28
  • 53
  • 67
  • 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,880
  • 9
  • 58
  • 93
  • 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