-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

1
  • 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, 2012 at 18:57

4 Answers 4

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.

9
  • 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, 2012 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.) Dec 31, 2012 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, 2012 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, 2012 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 for the details. Dec 31, 2012 at 19:11
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.

2
  • 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? Dec 31, 2012 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, 2012 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.

2
  • Increasing the swap partition usually means you have to reinstall the OS, which is a pain in the b-hole.
    – Sammitch
    Dec 31, 2012 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, 2012 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.

10
  • 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? Jan 22, 2013 at 19:19
  • More RAM definitely. Jan 22, 2013 at 19:20
  • Can I ask why? Can you please explain It? Jan 22, 2013 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. Jan 22, 2013 at 19:24
  • No, my swap is almost free. I added the free command result at bottom of my question. Jan 22, 2013 at 19:28

You must log in to answer this question.

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