Databases Reference
In-Depth Information
5. Subtract enough for any other buffers and caches that you configure inside MySQL,
such as the MyISAM key cache or the query cache.
6. Divide by 105%, which is an approximation of the overhead InnoDB adds on to
manage the buffer pool itself.
7. Round the result down to a sensible number. Rounding down won't change things
much, but overallocating can be a bad thing.
We were a bit blasé about some of the amounts of memory involved here—what
exactly is “a bit for the operating system,” anyway? That varies, and we'll discuss it in
some depth later in this chapter and the rest of this topic. You need to understand your
system and estimate how much memory you think it'll need to run well. This is why
one-size-fits-all configuration files are not possible. Experience and sometimes a bit of
math will be your guide.
Here's an example. Suppose you have a server with 192 GB of memory, and you want
to dedicate it to MySQL and to use only InnoDB, with no query cache and not very
many connections to the server. If your log files are 4 GB in total, you might proceed
as follows: “I think that 2 GB or 5% of overall memory, whichever is larger, should be
enough for the OS and for MySQL's other memory needs; subtract 4 GB for the log
files; use everything else for InnoDB.” The result is about 177 GB, but it's probably a
good idea to round that down a bit. You might configure the server with 168 GB or so
of buffer pool. If the server tends to run with a fair amount of unallocated memory in
practice, you might set the buffer pool larger when there is an opportunity to restart it
for some other purpose.
The result would be very different if you had a number of MyISAM tables and needed
to cache their indexes, naturally. It would also be quite different on Windows, which
has trouble using large amounts of memory in most MySQL versions (although it's
improved in MySQL 5.5), or if you chose not to use
O_DIRECT
for some reason.
As you can see, it's not crucial to get this setting precisely right from the beginning. It's
better to start with a safe value that's larger than the default but not as large as it could
be, run the server for a while, and see how much memory it really uses. These things
can be hard to anticipate, because MySQL's memory usage isn't always predictable: it
can depend on factors such as the query complexity and concurrency. With a simple
workload, MySQL's memory needs are pretty minimal—around 256 KB per connec-
tion. But complex queries that use temporary tables, sorting, stored procedures, and
so forth can use a lot more RAM.
That's why we chose a pretty safe starting point. You can see that even the conservative
setting for InnoDB's buffer pool is actually 87.5% of the server's installed RAM—more
than 75%, which is why we said that simple ratios aren't the right approach.
We suggest that when it comes to configuring the memory buffers, you err on the side
of caution, rather than making them too large. If you make the buffer pool 20% smaller
than it could be, you'll likely impact performance only a small amount—maybe a few
