Databases Reference
In-Depth Information
Another option is to use MySQL's
memlock
configuration option, which locks MySQL
in memory. This will avoid swapping, but it can be dangerous: if there's not enough
lockable memory left, MySQL can crash when it tries to allocate more memory. Prob-
lems can also be caused if too much memory is locked and there's not enough left for
the operating system.
Many of the tricks are specific to a kernel version, so be careful, especially when you
upgrade. In some workloads, it's hard to make the operating system behave sensibly,
and your only recourse might be to lower the buffer sizes to suboptimal values.
Operating System Status
Your operating system provides tools to help you find out what the operating system
and hardware are doing. In this section we'll show you examples of how to use two
widely available tools,
iostat
and
vmstat
. If your system doesn't provide either of these
tools, chances are it will provide something similar. Thus, our goal isn't to make you
an expert at using
iostat
or
vmstat
, but simply to show you what to look for when you're
trying to diagnose problems with tools such as these.
In addition to these tools, your operating system might provide others, such as
mpstat
or
sar
. If you're interested in other parts of your system, such as the network, you might
want to instead use tools such as
ifconfig
(which shows how many network errors have
occurred, among other things) or
netstat
.
By default,
vmstat
and
iostat
produce just one report showing the average values of
various counters since the server was started, which is not very useful. However, you
can give both tools an interval argument. This makes them generate incremental reports
showing what the server is doing right now, which is much more relevant. (The first
line shows the statistics since the system was started; you can just ignore this line.)
How to Read vmstat Output
Let's look at an example of
vmstat
first. To make it print out a new report every five
seconds, use the following command:
$
vmstat 5
procs -----------memory---------- ---swap-- -----io---- -system-- ----cpu----
r b swpd free buff cache si so bi bo in cs us sy id wa
0 0 2632 25728 23176 740244 0 0 527 521 11 3 10 1 86 3
0 0 2632 27808 23180 738248 0 0 2 430 222 66 2 0 97 0
You can stop
vmstat
with Ctrl-C. The output you see depends on your operating system,
so you might need to read the manual page to figure it out.
As stated earlier, even though we asked for incremental output, the first line of values
shows the averages since the server was booted. The second line shows what's hap-
pening right now, and subsequent lines will show what's happening at five-second
intervals. The columns are grouped by headers:
