What is an InnoDB Buffer Pool?
InnoDB buffer pool is the memory space that holds many in-memory data structures of InnoDB, buffers, caches, indexes and even row-data. innodb_buffer_pool_size is the MySQL configuration parameter that specifies the amount of memory allocated to the InnoDB buffer pool by MySQL. This is one of the most important settings in the MySQL hosting configuration and should be configured based on the available system RAM.
In this post, we’ll walk you through two approaches of setting your InnoDB buffer pool size value, examine the pros and cons of those practices, and also propose a unique method to arrive at an optimum value based on the size of your system RAM.
Approach 1. Rule of Thumb Method
The most commonly followed practice is to set this value at 70% – 80% of the system RAM. Though it works well in most cases, this method may not be optimal in all configurations. Let’s take the example of a system with 192GB of RAM. Based on the above method, we arrive at about 150GB for the buffer pool size. However, this isn’t really an optimal number as it does not fully leverage the large RAM size that’s available in the system, and leaves behind about 40GB of memory. This difference can be even more significant as we move to systems with larger configurations where we should be utilizing the available RAM to a greater extent.
Approach 2. A More Nuanced Approach
This approach is based on a more detailed understanding of the internals of the InnoDB buffer pool and its interactions, which is described very well in the book High Performance MySQL.
Let’s look at the following method to compute the InnoDB buffer pool size.
- Start with total RAM available.
- Subtract suitable amount for the OS needs.
- Subtract suitable amount for all MySQL needs (like various MySQL buffers, temporary tables, connection pools, and replication related buffers).
- Divide the result by 105%, which is an approximation of the overhead required to manage the buffer pool itself.
For example, let’s look at a system with 192GB RAM using only InnoDB and having a total log file size of about 4GB. We can use a rule like ‘maximum of 2GB or 5% of total RAM’ for OS needs allocation as recommended in the above book, which comes to about 9.6GB. Then, we’ll also allocate about 4GB for other MySQL needs, mainly taking into account the log file size. This method results in about 170GB for our InnoDB buffer pool size, which is about 88.5% utilization of the available RAM size.
Though we used the ‘maximum of 2GB or 5% of total RAM’ rule to compute our memory allocation for OS needs above, the same rule does not work very well in all cases, specifically for systems with medium-sized RAMs between 2GB and 32GB. For instance, in a system with 3GB RAM, allocating 2GB for OS needs does not leave much for the InnoDB buffer pool, while allocating 5% of RAM is just too little for our OS needs.
So, let’s fine-tune the above OS allocation rule and examine the InnoDB computation method across various RAM configurations:
For Systems with Small-Sized RAM (<= 1GB)
For systems running with less than 1GB of RAM, it is better to go with the MySQL default configuration value of 128MB for InnoDB buffer pool size.
For Systems with Medium-Sized RAM (1GB – 32GB)
Considering the case of systems with a RAM size of 1GB – 32GB, we can compute OS needs using this rough heuristics:
256MB + 256 * log2(RAM size in GB)
The rationalization here is that, for low RAM configurations, we start with a base value of 256MB for OS needs and increase this allocation in a logarithmic scale as the amount of RAM increases. This way, we can come up with a deterministic formula to allocate RAM for our OS needs. We’ll also allocate the same amount of memory for our MySQL other needs. For example, in a system with 3GB of RAM, we would make a fair allocation of 660MB for OS needs, and another 660MB for MySQL other needs, resulting in a value of about 1.6GB for our InnoDB buffer pool size.
For Systems with Higher-Sized RAM (> 32GB)
For systems with RAM sizes greater than 32GB, we would revert back to calculating OS needs as 5% of our system RAM size, and the same amount for MySQL other needs. So, for a system with a RAM size of 192GB, our method will land at about 165GB for InnoDB buffer pool size, which is again, an optimal value to be used.
Plot of InnoDB Buffer Pool Size for Various RAM Sizes
Word of Caution for InnoDB Buffer Pool Size Calculations
The considerations in this blog post are for Linux systems that are dedicated for MySQL. For Windows systems or systems that run multiple applications along with MySQL, these observations can be inaccurate. It’s also important to note that, though we can use these tools as references, it really takes good experience, experimentation, continuous monitoring, and fine-tuning to get the right sizing for your innodb_buffer_pool_size.
