What happens if you run out of RAM and swap?

Running MySQL on a box with 32 gigs of RAM. Swap is 99% full, and there is about 200 MB of RAM free. MySQL is the culprit of course. What happens when both RAM and swap are completely filled? Thanks!

Solution 1:

9/10 OOM killer will come by and kill the program with the biggest memory consumption. Else your system will crash.

I'm assuming you are on Linux.

Solution 2:

That depends on the OS, but when you actually run out something dies. More specifically, something says to the OS "please give me more memory", and the OS says "no".

(That can be by virtue of just touching some memory that it already got told was allocated, causing the page to copy-on-write, so it isn't as simple as "call the kernel memory allocator")

Technically, the process could handle those failures and recover, but practically it results in a crash.

Linux tries hard to guess what a good candidate to kill is, and I think Windows does the same, before you quite get to that state, though - kill something marginally more cleanly so that memory is freed up.

Solution 3:

Since free RAM is wasted RAM, Linux tries to use as much of it as possible. It is possible that there are lots of cached disk pages and other things kept in RAM just because there was space. Run free -m to see the how the RAM is used on your machine. It will also show you the number of megs that could be used by applications if needed (column 'free', second row.)

Solution 4:

Open some spare ssh sessions to the host until it's not too late. When system have no free RAM/swap, no new process can be launched.

Seems that some query produce unexpected amount of data stored in the temporary table(s) - in memory. Try to kill oldest thread launched by mysql:

SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, LEFT(INFO, 51200) AS Info FROM information_schema.PROCESSLIST;

Field TIME shows seconds mysql spent performing query. Those having largest TIME are likely the culprits.

Solution 5:

Try deploying a custom agent like New Relic to see what processes are unnecessary and start killing them. That can save your server from crashing