Databases Reference
In-Depth Information
Reserving Memory for the Operating System
Just as with queries, you need to reserve enough memory for the operating system to
do its work. The best indication that the operating system has enough memory is that
it's not actively swapping (paging) virtual memory to disk. (See the next chapter for
more on this topic.)
You should reserve at least a gigabyte or two for the operating system—more for ma-
chines with a lot of memory. We suggest starting with 2 GB or 5% of total memory as
the baseline, whichever is greater. Add in some extra for safety, and add in some more
if you'll be running periodic memory-intensive jobs on the machine (such as backups).
Don't add any memory for the operating system's caches, because they can be very
large. The operating system will generally use any leftover memory for these caches,
and we consider them separately from the operating system's own needs in the follow-
ing sections.
Allocating Memory for Caches
If the server is dedicated to MySQL, any memory you don't reserve for the operating
system or for query processing is available for caches.
MySQL needs more memory for caches than anything else. It uses caches to avoid disk
access, which is orders of magnitude slower than accessing data in memory. The op-
erating system might cache some data on MySQL's behalf (especially for MyISAM),
but MySQL needs lots of memory for itself, too.
The following are the most important caches to consider for most installations:
• The InnoDB buffer pool
• The operating system caches for InnoDB log files and MyISAM data
• MyISAM key caches
• The query cache
• Caches you can't really configure, such as the operating system's caches of binary
logs and table definition files
There are other caches, but they generally don't use much memory. We discussed the
query cache in detail in the previous chapter, so the following sections concentrate on
the caches InnoDB and MyISAM need to work well.
It is much easier to configure a server if you're using only one storage engine. If you're
using only MyISAM tables, you can disable InnoDB completely, and if you're using
only InnoDB, you need to allocate only minimal resources for MyISAM (MySQL uses
MyISAM tables internally for some operations). But if you're using a mixture of storage
engines, it can be very hard to figure out the right balance between them. The best
approach we've found is to make an educated guess and then observe the server in
operation.
