Databases Reference
In-Depth Information
multipurpose MySQL instances that serve variable workloads. If the latter describes
you, be sure to give your server some breathing room by setting aside enough swap
space.
Another thing that frequently happens under extreme virtual memory pressure is that
the out-of-memory (OOM) killer process will kick in and kill something. This is fre-
quently MySQL, but it can also be another process such as SSH, which can leave you
with a system that's not accessible from the network. You can prevent this by setting
the SSH process's
oom_adj
or
oom_score_adj
value.
You can solve most swapping problems by configuring your MySQL buffers correctly,
but sometimes the operating system's virtual memory system decides to swap MySQL
anyway. This usually happens when the operating system sees a lot of I/O from MySQL,
so it tries to increase the file cache to hold more data. If there's not enough memory,
something must be swapped out, and that something might be MySQL itself. Some
older Linux kernel versions also have counterproductive priorities that swap things
when they shouldn't, but this has been alleviated a bit in more recent kernels.
Some people advocate disabling the swap file entirely. Although this sometimes works
in extreme cases where the kernel just refuses to behave, it can degrade the operating
system's performance. (It shouldn't in theory, but in practice it can.) It's also dangerous,
because disabling swapping places an inflexible limit on virtual memory. If MySQL has
a temporary spike in memory requirements, or if there are memory-hungry processes
running on the same machine (nightly batch jobs, for example), MySQL can run out
of memory, crash, or be killed by the operating system.
Operating systems usually allow some control over virtual memory and I/O. We men-
tion a few ways to control them on GNU/Linux. The most basic is to change the value
of
/proc/sys/vm/swappiness
to a low value, such as
0
or
1
. This tells the kernel not to
swap unless the need for virtual memory is extreme. For example, here's how to check
the current value:
$
cat /proc/sys/vm/swappiness
60
The value shown, 60, is the default swappiness setting (the range is from 0 to 100). This
is a very bad default for servers. It's only appropriate for laptops. Servers should be set
to
0
:
$
echo 0 > /proc/sys/vm/swappiness
Another option is to change how the storage engines read and write data. For example,
using
innodb_flush_method=O_DIRECT
relieves I/O pressure. Direct I/O is not cached, so
the operating system doesn't see it as a reason to increase the size of the file cache. This
parameter works only for InnoDB. You can also use large pages, which are not swap-
pable. This works for MyISAM and InnoDB.
