Mysql crashing, oom-killer, out of memory, tuning issues?

Question

I just moved all of my websites to a new server with 4GB RAM. Almost immediately, mysql started crashing, and at one point, didn't reboot which caused a major outage (since I didn't notice until someone pointed it out to me).

Here is the log with CRON jobs stripped: http://pastebin.com/9SAUBcFp (apache2 invoked oom-killer, Out of memory errors, etc.)

Nothing in df -h is over 4% usage.

Here's an output of free -m

             total       used       free     shared    buffers     cached
Mem:          4002       2090       1911          0        168       1015
-/+ buffers/cache:        906       3095
Swap:          255          8        247

Here's an output of mysqlreport

__ Key _________________________________________________________________
Buffer used   849.00k of  16.00M  %Used:   5.18
  Current       2.99M            %Usage:  18.71
Write hit      44.87%
Read hit       98.84%

__ Questions ___________________________________________________________
Total         198.55k    33.8/s
  QC Hits     147.94k    25.1/s  %Total:  74.51
  DMS          31.35k     5.3/s           15.79
  Com_         14.20k     2.4/s            7.15
  COM_QUIT      5.07k     0.9/s            2.55
  -Unknown          9     0.0/s            0.00
Slow 2 s            0       0/s            0.00  %DMS:   0.00  Log:  ON
DMS            31.35k     5.3/s           15.79
  SELECT       27.65k     4.7/s           13.93         88.19
  UPDATE        1.78k     0.3/s            0.89          5.66
  INSERT        1.73k     0.3/s            0.87          5.51
  DELETE          199     0.0/s            0.10          0.63
  REPLACE           0       0/s            0.00          0.00
Com_           14.20k     2.4/s            7.15
  set_option    9.29k     1.6/s            4.68
  change_db     4.63k     0.8/s            2.33
  show_tables     260     0.0/s            0.13

__ SELECT and Sort _____________________________________________________
Scan              850     0.1/s %SELECT:   3.07
Range             398     0.1/s            1.44
Full join           0       0/s            0.00
Range check         0       0/s            0.00
Full rng join       0       0/s            0.00
Sort scan       1.01k     0.2/s
Sort range        361     0.1/s
Sort mrg pass       0       0/s

__ Query Cache _________________________________________________________
Memory usage   15.09M of  16.00M  %Used:  94.30
Block Fragmnt   2.31%
Hits          147.94k    25.1/s
Inserts        21.70k     3.7/s
Insrt:Prune    2.86:1     2.4/s
Hit:Insert     6.82:1

__ Table Locks _________________________________________________________
Waited              0       0/s  %Total:   0.00
Immediate      35.51k     6.0/s

__ Tables ______________________________________________________________
Open              400 of  400    %Cache: 100.00
Opened          5.55k     0.9/s

__ Connections _________________________________________________________
Max used            9 of  151      %Max:   5.96
Total           5.07k     0.9/s

__ Created Temp ________________________________________________________
Disk table        554     0.1/s
Table           1.61k     0.3/s    Size:  16.0M
File                6     0.0/s

__ Threads _____________________________________________________________
Running             1 of    1
Cached              7 of    8      %Hit:  99.82
Created             9     0.0/s
Slow                0       0/s

__ Aborted _____________________________________________________________
Clients             0       0/s
Connects            5     0.0/s

__ Bytes _______________________________________________________________
Sent            3.57G  607.2k/s
Received       34.01M    5.8k/s

__ InnoDB Buffer Pool __________________________________________________
Usage          98.28M of 127.98M  %Used:  76.79
Read hit       99.98%
Pages
  Free          1.90k            %Total:  23.21
  Data          5.61k                     68.50 %Drty:   0.00
  Misc            679                      8.29
  Latched           0                      0.00
Reads          21.60M    3.7k/s
  From file     4.62k     0.8/s            0.02
  Ahead Rnd         0       0/s
  Ahead Sql                 0/s
Writes         10.83k     1.8/s
Flushes         5.27k     0.9/s
Wait Free           0       0/s

__ InnoDB Lock _________________________________________________________
Waits               0       0/s
Current             0
Time acquiring
  Total             0 ms
  Average           0 ms
  Max               0 ms

__ InnoDB Data, Pages, Rows ____________________________________________
Data
  Reads         5.57k     0.9/s
  Writes        7.95k     1.4/s
  fsync         3.10k     0.5/s
  Pending
    Reads           0
    Writes          0
    fsync           0

Pages
  Created          48     0.0/s
  Read          5.56k     0.9/s
  Written       5.27k     0.9/s

Rows
  Deleted         190     0.0/s
  Inserted        242     0.0/s
  Read          7.47M    1.3k/s
  Updated       1.36k     0.2/s

Here's an output of mysqltuner

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.35-0+wheezy1-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster 
[--] Data in MyISAM tables: 1005M (Tables: 335)
[--] Data in InnoDB tables: 143M (Tables: 68)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 76

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1h 28m 55s (154K q [28.899 qps], 4K conn, TX: 3B, RX: 25M)
[--] Reads / Writes: 83% / 17%
[--] Total buffers: 192.0M global + 2.7M per thread (151 max threads)
[OK] Maximum possible memory usage: 597.8M (14% of installed RAM)
[OK] Slow queries: 0% (0/154K)
[OK] Highest usage of available connections: 5% (9/151)
[OK] Key buffer size / total MyISAM indexes: 16.0M/264.4M
[OK] Key buffer hit rate: 98.8% (77K cached / 912 reads)
[OK] Query cache efficiency: 87.2% (116K cached / 133K selects)
[!!] Query cache prunes per day: 5182
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 1K sorts)
[OK] Temporary tables created on disk: 24% (427 on disk / 1K total)
[OK] Thread cache hit rate: 99% (9 created / 4K connections)
[!!] Table cache hit rate: 9% (400 open / 4K opened)
[OK] Open file limit used: 61% (631/1K)
[OK] Table locks acquired immediately: 100% (18K immediate / 18K locks)
[!!] InnoDB data size / buffer pool: 143.3M/128.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    query_cache_size (> 16M)
    table_cache (> 400)
    innodb_buffer_pool_size (>= 143M)

Given the "Variables to adjust" above, I've made the following changes to /etc/mysqld/my.cnf:

• Added line: key_buffer_size = 280M
• Added line: innodb_buffer_pool_size = 150M
• Uncommented table_cache and changed it to 100 (which I will continuously increase until it's over 400)
• Changed the value of query_cache_size from 16M to 32M

Are there any glaring issues here that I'm overlooking or anything that I should be doing?

Solution 1

You have hardly any swap (256M), as a temporary measure, I would add more swap and turn swappiness (vm.swappiness) down so as to avoid useless I/O wait. SWAP is slow, but it can keep your PIDS from crashing. Also, grep out your OOM's and check the timestamps to see if there is any regularity with the crashes over time. I have had to sleuth through some poorly crafted cron jobs in my time. I would make sure to have at least 2GB of swap if you have < 8GB of RAM. Like I said, swapping will slow things down, but it is better than crashing the DB and losing transactions and having to check/repair tables on start up.

Solution 2

I would guess your problem is actually too many Apache clients for your server's memory. When you get a large surge in traffic the Apache processes build up eventually using up all the RAM . This forces the OS to begin forcibly swapping processes in and out which tends to make things much worse. Eventually your swap memory runs out and the OS kills whatever it thinks is best...in this case MySQL since it uses a lot of memory. Note that increasing swap memory will just delay the inevitable crash.

I would start by reducing Apache's MAXCLIENTS to a more reasonable value based on your server's capability. You can do a rough estimate by running top and looking at the RES/SHR columns for the httpd processes. The difference between them is roughly how much each child process uses. For example, on my servers the average memory use per child is 40MB and I want Apache to only use 3GB so my MAXCLIENTS is set to 75 (3000/40). You can play with this value and some of the of the other Apache connection settings a bit as needed to prevent Apache from using up all the RAM and begin hitting the swap.

You can also look at it from the perspective of what is causing the large number of Apache clients. If it is a real traffic surge/spike then you're likely going to need a larger server or more servers, or add a caching layer to decrease the load on Apache. If your server is simply too slow to handle the normal number of incoming requests you'll want to decrease Apache's MAXCLIENTS to a level it can actually handle without requests backing up. Or maybe there is an server, app or database issue that is causing things to lock up or freeze which needs to be found and fixed.

Comments

[runningonplants]

The websites all ran on one server with 8GB of ram previously - AND, the main site's MySQL database was hosted on a different server (which I found weird).

[mdpc]

Then halving memory AND taking on a MySQL database seems a little confusing to me. I don't think that you're going to be able to tune your way out of this.

[runningonplants]

Okay, I will upgrade the box. I was trying to save money (it's for a non-profit) and it seemed like 12 websites (and only 1 which is high load) should be something that 4GB of memory should be able to handle. Thanks a lot for your input!

[David Schwartz]

Swapping will not slow things down, that's a myth. There are two kinds of swapping -- required and opportunistic. Required swapping is an alternative to crashing which, as this question shows, slows things down a lot more. Opportunistic swapping (what you're suggesting he reduce by turning swapiness down) is done specifically because it speeds thing up by making more physical memory available for cache. I gave you a +1 though because you are probably right about the key issue -- he needs more swap.

[runningonplants]

Thanks for both of your input - so, is it agreed that I should up swap from 256M to 2GB? This setup is sufficient for 12 websites (1 of them semi-high load), as opposed to having to upgrade RAM (which I'd like to avoid to save $$$), correct?

[burf]

Regardless of whether you add RAM, you should have more swap than you currently do. It looks like your DB's aren't very big, so what I would do is add swap and do some performance monitoring. The default swappiness is usually around 60 and it can safely be lowered to around 10 or so. (You can lower it all the way to 0, but that means absolutely no swapping unless necessary) which isn't always the best bet for performance. If you have sysstat installed, check out Ksar sourceforge.net/projects/ksar as it can help you visualize longterm performance trends and bottlenecks from the SAR logs

[burf]

Here are some docs:

[burf]

Okay, Debian / Ubuntu documentation is a bit informal. To set swappiness: sysctl -w vm.swappiness=10 <--Example set to 10--> To resize swap is an effort that depends on a number of factors, but to add swap, you can use dd to make the file, mkswap to format it, add the file to the fstab and swapon to activate it.