Mysql crashing, oom-killer, out of memory, tuning issues?
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_cacheand changed it to 100 (which I will continuously increase until it's over 400) - Changed the value of
query_cache_sizefrom 16M to 32M
Are there any glaring issues here that I'm overlooking or anything that I should be doing?
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.
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.