Now that we’re settled with the storage engine (and if you’re still in doubt, hit me up in comments), let’s talk about the most important InnoDB variable: innodb_buffer_pool_size.

What is InnoDB Buffer Pool?

Computers use most of their memory to improve access to most commonly used data. This is known as caching and it is a very important part of computing, because accessing data on a disk can be 100 to 100,000 times slower, depending on the amount of data being accessed.

Just think about it
A report that takes 1 second to generate when all data is in memory could take over a day to generate if all data had to be read from disk every single time (assuming also random I/O).

MyISAM is using OS file system cache to cache the data that queries are reading over and over again. Whereas InnoDB uses a very different approach.

Instead of relying on OS to do the “right thing”, InnoDB handles caching itself – within the InnoDB Buffer Pool – and throughout this course you will learn how it works and why it was a good idea to implement it that way.

InnoDB Buffer Pool is more than just a cache

InnoDB buffer pool actually serves multiple purposes. It’s used for:

  • Data caching – this is definitely a big part of it
  • Indices caching – yes, these share the same buffer pool
  • Buffering – modified data (often called “dirty data”) lives here before it’s flushed
  • Storing internal structures – some structures such as Adaptive Hash Index (we’ll get to it) or row locks are also stored in the InnoDB Buffer Pool

Here’s a very typical InnoDB Buffer Pool page distrubition from a customer machine with innodb-buffer-pool-size set to 62G:

Typical InnoDB Buffer Pool page distribution

As you can see, Buffer Pool is mostly filled with regular InnoDB pages, but about 10% of it is used for other purposes.

Oh and in case you’re wondering what units are used in this graph, that’s InnoDB pages. A single page is typically 16 kilobytes in size, so you can multiply these numbers by 16,384 to get a sense of usage in bytes.

Sizing InnoDB Buffer Pool

So what should innodb-buffer-pool-size be set to? I’m glad you asked! I was just about to get there.

Dedicated server

On a dedicated MySQL server running all InnoDB, as a rule of thumb, recommendation is to set the innodb-buffer-pool-size to 80% of the total available memory on the server.

Why not 90% or 100%?

Because other things need memory too:

  • Every query needs at least few kilobytes of memory (and sometimes – few megabytes!)
  • There’s various other internal MySQL structures and caches
  • InnoDB has a number of structures using memory beyond the buffer pool (Dictionary cache, File system, Lock system and Page hash tables, etc.
  • There’s also some MySQL files that must be in OS cache (binary logs, relay logs, innodb transaction logs).
  • Plus, you want to leave some room for the operating system memory structures.

By the way, this number is NOT pulled out of the hat – I’ve seen hundreds of systems large and small, and even on the servers with 512GB of RAM, we found 80% to be about the right size for sustainable operation.

If you see a lot of free memory, sure you can bump it up a bit (especially as MySQL 5.7 makes this much easier), but do not let MySQL consume all memory, or you will face problems. BIG problems. Namely, swapping.

Don't let your database server swap!
Swapping is the worst thing that can happen to a database server – it’s much worse than having buffer pool size that’s not large enough. One example how this may go wrong is InnoDB using a lock that it typically uses when accessing a page in memory (100ns access time) to access the page that is swapped out (10ms access time). This would cause all currently running queries to stall and as these things usually don’t walk alone, you can guess where this is going…

Shared server

If your MySQL server shares resources with application, rules of thumb no longer work.

In such an environment it’s much harder to find the right number. On the other hand, it’s usually less so important to find the right size and a good enough number is often good enough in a shared environment.

In any case, first I check the actual size of the InnoDB tables. Chances are, you don’t really need much memory. Remember this query from one of the earlier posts:

This will give you an idea how much memory you’d need for InnoDB buffer pool if you wanted to cache the entire dataset. And note that in many cases you don’t need that, you only want to cache your working set (actively used data).

If it all fits in, say, half the memory on the server, great – set it to the size of all InnoDB tables combined and forget it (for some time). If not, let me teach you a very simple trick to determine if the InnoDB buffer pool is well sized.

Using server command line, run the following:

What you see here is the number of reads from disk into the buffer pool (per second). These numbers above are pretty darn high (luckily, this server has an IO device that can handle 4000 IO operations per second) and if this was an OLTP system, I would highly recommend to increase the innodb buffer pool size and add more memory to the server if needed.

If you don’t have access to the command line, I highly suggest you get one as you’re a lot more flexible there. But if you want a GUI alternative, MySQL Workbench is your friend. Under PERFORMANCE, open the Dashboard and you will see both “InnoDB buffer pool disk reads” and also “InnoDB Disk Reads” (most of the time they go hand in hand).

Changing InnoDB Buffer Pool

Finally, here’s how you actually change the innodb-buffer-pool-size.

If you’re already on MySQL 5.7, you are extremely lucky, because that means you can change it online! Just run the following command as a SUPER user (i.e. root) and you’re done:

Well not exactly done – you still need to change it in my.cnf file too, but at least you will not need to restart the server as the innodb buffer pool will be resized automatically with something along these lines in the error log:

All earlier versions of MySQL do require a restart, so:

  1. set an appropriate innodb_buffer_pool_size in my.cnf
  2. restart mysql server
  3. celebrate improved MySQL performance

P.S. If you have stumbled on this post by accident and you’re not sure what other posts I am talking about, start here.

Share this Post

About the Author

Aurimas Mikalauskas

Facebook Twitter Google+

Linux hacker since the age of 0xE, deeply passionate about high performance and scalability. Former architect of scalable systems at Percona - largest independent MySQL and MongoDB consultancy company (for 9 years). Audiobook addict, piano & guitar virtuoso, scuba diver, wind surfer and a wannabe glider pilot. Learn more about the author.