MySQL swapping tricks

What is swap?

Swap space is the area on a hard disk which is part of the Virtual Memory of your machine, which is a combination of accessible physical memory (RAM) and the swap space. Swap space temporarily holds memory pages that are inactive. Swap space is used when your system decides that it needs physical memory for active processes and there is insufficient unused physical memory available. If the system happens to need more memory resources or space, inactive pages in physical memory are then moved to the swap space therefore freeing up that physical memory for other uses. Note that the access time for swap is slower therefore do not consider it to be a complete replacement for the physical memory. Swap space can be a dedicated swap partition (recommended), a swap file, or a combination of swap partitions and swap
files.

Why do I need swap?

Memory consuming programs Sometimes, a large program (like MySQL) make the entire system need extra memory. A significant number of the pages used by these large programs during its startup may only be used for initialization and then never used again. The system can swap out those pages and free the memory for other programs or even for the disk cache. In these cases, swap will be used to help the system handle any extra load.

Hibernation (suspend-to-disk) The hibernation feature (suspend-to-disk) writes out the contents of RAM to the swap partition before turning off the machine. Therefore, your swap partition should be at least as big as your RAM size. The hibernation implementation currently used in Ubuntu, swsusp, needs a swap or suspend partition. It cannot use a swap file on an active file system.

Unforeseeable Circumstances Unforeseeable events can and will happen (a program going crazy, some action needing much more space than you thought, or any other unpredictable combination of events). In these cases, swap can give you an extra delay to figure out what happened, or to finish what you are working on.

Symptoms

MySQL is being slow and when we run programs like ‘top’ we can see that our process (or server) is using a large quantity of swap.

What is this affecting the MySQL performance ?

MySQL will change the way works since it will replace the internal algorithms to be optimized for Disk I/O due the navy use of swap. This will make the system slower.

What we can do ?

First we need to figure out how much swap is the system using and how much free memory does it have.

1) The first trick we can do is to drop disk caches, this will free some more memory:

echo 3 > /proc/sys/vm/drop_caches

2) If we can move the swap into real memory (swap usage < free memory) we can run:

swapoff -a (to disable the swap usage, this will force to move data to memory)
swapon -a (will re-enable use of swap)

3) Check to see how aggressive is the system setup to use swap by using:

cat /proc/sys/vm/swappiness

I personally prefer to have this value around 10 (0 no not use swap  – 100 use swap)

to change this

edit /etc/sysctl.conf and add a new line “vm.swappiness=10“. Save the file and run

sysctl -f /etc/sysctl.conf

4) Make sure that mysql is allocating memory close to equal for each physical CPU (not core), you may check this by typing

numactl –hardware

due the NUMA architecture this may be one of the main issue why mysql is swapping in the first place.

More >