How Does A Buffer Register Work

What is an InnoDB Buffer Pool?

InnoDB buffer puddle is the retention space that holds many in-retentiveness data structures of InnoDB, buffers, caches, indexes and even row-information. 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 well-nigh important settings in the MySQL hosting configuration and should be configured based on the available organisation RAM.

In this mail service, nosotros'll walk you through ii approaches of setting your InnoDB buffer pool size value, examine the pros and cons of those practices, and also suggest a unique method to arrive at an optimum value based on the size of your system RAM.

Arroyo 1. Rule of Pollex Method

The most ordinarily followed practice is to ready this value at 70% – 80% of the system RAM. Though it works well in about cases, this method may not be optimal in all configurations. Permit'southward take the example of a organisation with 192GB of RAM. Based on the above method, we arrive at virtually 150GB for the buffer puddle size. However, this isn't really an optimal number as it does non fully leverage the large RAM size that's available in the system, and leaves behind about 40GB of memory. This deviation can exist even more pregnant as we move to systems with larger configurations where we should be utilizing the available RAM to a greater extent.

Approach ii. A More Nuanced Approach

This arroyo 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.

Allow'due south look at the following method to compute the InnoDB buffer pool size.

Kickoff with total RAM bachelor.
Subtract suitable amount for the OS needs.
Decrease suitable corporeality for all MySQL needs (like various MySQL buffers, temporary tables, connectedness pools, and replication related buffers).
Divide the issue past 105%, which is an approximation of the overhead required to manage the buffer pool itself.

For case, let'due south wait at a organisation with 192GB RAM using just InnoDB and having a total log file size of well-nigh 4GB. We can use a rule similar 'maximum of 2GB or 5% of full RAM' for Bone needs allocation every bit recommended in the above book, which comes to well-nigh 9.6GB. So, we'll besides allocate about 4GB for other MySQL needs, mainly taking into business relationship the log file size. This method results in near 170GB for our InnoDB buffer puddle 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 retentivity allocation for OS needs above, the aforementioned rule does not work very well in all cases, specifically for systems with medium-sized RAMs betwixt 2GB and 32GB. For instance, in a organization with 3GB RAM, allocating 2GB for OS needs does non exit much for the InnoDB buffer pool, while allocating five% of RAM is simply too little for our Os needs.

So, allow's fine-tune the above Bone allocation rule and examine the InnoDB computation method across various RAM configurations:

For Systems with Pocket-sized-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 tin can compute Bone needs using this rough heuristics:

256MB + 256 * log2(RAM size in GB)

The rationalization here is that, for low RAM configurations, we commencement with a base of operations value of 256MB for OS needs and increase this allocation in a logarithmic scale every bit the amount of RAM increases. This mode, we tin come up up with a deterministic formula to allocate RAM for our Bone needs. We'll also allocate the same amount of memory for our MySQL other needs. For example, in a organisation with 3GB of RAM, we would make a fair allotment of 660MB for Os needs, and some other 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 five% of our system RAM size, and the same corporeality for MySQL other needs. So, for a system with a RAM size of 192GB, our method will state at about 165GB for InnoDB buffer pool size, which is again, an optimal value to exist used.

InnoDB Buffer Pool Size Figurer

Calculate the optimal value for any-sized RAM:

InnoDB Buffer Pool Size Estimator - Compute the optimal number for any arrangement RAM size Click To Tweet

Plot of InnoDB Buffer Pool Size for Various RAM Sizes

Word of Caution for InnoDB Buffer Pool Size Calculations

The considerations in this web log mail service are for Linux systems that are dedicated for MySQL. For Windows systems or systems that run multiple applications forth with MySQL, these observations can be inaccurate. It's also important to notation that, though we tin can use these tools every bit references, it really takes good experience, experimentation, continuous monitoring, and fine-tuning to get the right sizing for your innodb_buffer_pool_size.

Learn more about ScaleGrid

Prasad Nagaraj is currently VP, Applied science at ScaleGrid. Previously he has served in various engineering science and management positions at NetApp and has extensive experience in storage and data protection domains.