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?

Answer 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.

Answer 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.

Answer 3

It would seem to be the logical conclusion to add more memory.

BTW, you never did mention if you were consolating your websites from a number of different machines to one or that all of websites ran on an old server (of how much memory). This information would be VERY helpful however I still think that you just need more memory.