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 itA 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:
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.
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…
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:
count(*) as TABLES,
WHERE table_schema not in ('mysql', 'performance_schema', 'information_schema')
GROUP BY engine
ORDER BY sum(data_length+index_length) DESC LIMIT 10;
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:
$ mysqladmin ext -ri1 | grep Innodb_buffer_pool_reads
| Innodb_buffer_pool_reads | 1832098003 |
| Innodb_buffer_pool_reads | 595 |
| Innodb_buffer_pool_reads | 915 |
| Innodb_buffer_pool_reads | 734 |
| Innodb_buffer_pool_reads | 622 |
| Innodb_buffer_pool_reads | 710 |
| Innodb_buffer_pool_reads | 664 |
| Innodb_buffer_pool_reads | 987 |
| Innodb_buffer_pool_reads | 1287 |
| Innodb_buffer_pool_reads | 967 |
| Innodb_buffer_pool_reads | 1181 |
| Innodb_buffer_pool_reads | 949 |
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:
mysql> SET GLOBAL innodb_buffer_pool_size=size_in_bytes;
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:
[Note] InnoDB: Resizing buffer pool from 134217728 to 21474836480. (unit=134217728)
[Note] InnoDB: disabled adaptive hash index.
[Note] InnoDB: buffer pool 0 : 159 chunks (1302369 blocks) were added.
[Note] InnoDB: buffer pool 0 : hash tables were resized.
[Note] InnoDB: Resized hash tables at lock_sys, adaptive hash index, dictionary.
[Note] InnoDB: Completed to resize buffer pool from 134217728 to 21474836480.
[Note] InnoDB: Re-enabled adaptive hash index.
All earlier versions of MySQL do require a restart, so:
- set an appropriate innodb_buffer_pool_size in my.cnf
- restart mysql server
- 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