Databases Reference
In-Depth Information
32-bit Linux kernels limit the amount of memory any one process can address to a value
that is typically between 2.5 and 2.7 GB. Running out of address space is very dangerous
and can cause MySQL to crash. This is pretty rare to see these days, but it used to be
common.
There are many other operating system-specific parameters and oddities that must be
taken into account, including not just the per-process limits, but also stack sizes and
other settings. The system's
glibc
libraries can also impose limits per single allocation.
For example, you might not be able to set
innodb_buffer_pool
larger than 2 GB if that's
all your
glibc
libraries support in a single allocation.
Even on 64-bit servers, some limitations still apply. For example, many of the buffers
we discuss, such as the key buffer, are limited to 4 GB on a 64-bit server in 5.0 and
older MySQL versions. Some of these restrictions are lifted in MySQL 5.1, and the
MySQL manual documents each variable's maximum value.
Per-Connection Memory Needs
MySQL needs a small amount of memory just to hold a connection (thread) open. It
also requires a base amount of memory to execute any given query. You'll need to set
aside enough memory for MySQL to execute queries during peak load times. Other-
wise, your queries will be starved for memory, and they will run poorly or fail.
It's useful to know how much memory MySQL will consume during peak usage, but
some usage patterns can unexpectedly consume a lot of memory, which makes this
hard to predict. Prepared statements are one example, because you can have many of
them open at once. Another example is the InnoDB data dictionary (more about this
later).
You don't need to assume a worst-case scenario when trying to predict peak memory
consumption. For example, if you configure MySQL to allow a maximum of 100 con-
nections, it theoretically might be possible to simultaneously run large queries on all
100 connections, but in reality this probably won't happen. For example, if you set
myisam_sort_buffer_size
to
256M
, your worst-case usage is at least 25 GB, but this level
of consumption is highly unlikely to actually occur. Queries that use many large tem-
porary tables, or complex stored procedures, are the most likely causes of high per-
connection memory consumption.
Rather than calculating worst cases, a better approach is to watch your server under a
real workload and see how much memory it uses, which you can see by watching the
process's virtual memory size. In many Unix-like systems, this is reported in the
VIRT
column in
top
, or
VSZ
in
ps
. The next chapter has more information on how to monitor
memory usage.
