intro
The innodbbufferpool_size parameter is the most important parameter you need to optimize in your MySQL Server. Learn why, how, and when to optimize this parameter with us!
The innodb_buffer_pool_size parameter is the most important parameter in any MySQL Server infrastructure. This MySQL Server parameter defines the size of one of the most important components of the MySQL database management system – the buffer pool. The buffer pool is used for a wide variety of operations involving MySQL. But about everything from the beginning…
How innodb_buffer_pool_size is a component of InnoDB
InnoDB is frequently called the king of the storage engine world – that’s the case because it’s the most important storage engine powering MySQL – InnoDB replaced MyISAM as of MySQL 5.5 which was released more than a decade ago – in 2010.
InnoDB is widely known as the only ACID-compliant storage engine in MySQL Server: it’s ACID compliance capabilities can be switched on or exchanged for more speed by fiddling with the innodb_flush_log_at_trx_commit variable, it stores all of its data in a file called ibdata1 (which can become very big if not taken care of properly – make sure to enable the innodb_file_per_table variable and clean up your InnoDB instance if necessary), and once done, keep in mind that InnoDB relies on a file called ibdata1 to store:
InnoDB has a couple of parts – one of the largest is the buffer pool. The buffer pool acts as a temporary storage unit for our most precious data.
What is the Buffer Pool in InnoDB, known as innodb_buffer_pool_size?
The InnoDB buffer pool is an area in memory where the storage engine caches necessary data and indexes. The InnoDB buffer pool (innodb_buffer_pool_size) is the most important parameter you need to look at while optimizing your MySQL Server database instance because it caches table and index data allowing for faster data retrieval.
In other words, the InnoDB buffer pool is a key component of MySQL Server, MariaDB Server, and Percona Server – you want it to be as large as possible without obstructing your ordinary server operations. Our experts recommend to set the innodb_buffer_pool_size value upwards to 60-80% of the available RAM on your system and setting all of the other values (innodb_log_file_size, innodb_log_buffer_size) to around a quarter (25%) of what you’ve set the buffer pool to be. The reason for this is that the buffer size is used to write to the log files on disk, and the faster these will be performed, the faster writes will complete as well.
Older versions of MySQL and MariaDB also had a parameter called innodb_buffer_pool_instances that acted as a variable that allowed us to split our InnoDB buffer pool into multiple instances. This parameter has long been deprecated since, according to the original developers of MySQL, there’s not many reasons left to split the buffer pool into a couple of pieces in the first place.
Other interesting parameters for the InnoDB buffer pool include innodb_old_blocks_pct and innodb_old_blocks_time. The innodb_old_blocks_pct variable controls the amount of Least Recently Used (LRU) data that is brought into the buffer pool and it can be changed to control the percentage of blocks inside of the InnoDB buffer pool as a whole. The default value of this parameter is 37, whereas allowed values range from 5 to 95. It’s not advised to change this parameter if you don’t know what you’re doing.
innodb_old_blocks_time, as the name suggests, specifies the time (miliseconds) after which the “block” of a transaction (the database page that accessed the transaction) is moved to the beginning (“front”) of the InnoDB buffer pool.
Optimizing the innodb_buffer_pool_size
As stated above, the most efficient technique to optimize the innodb_buffer_pool_size in any MySQL Server instance is to make it as big as possible without it consuming all of the memory on the server. 60 to 80% of the available RAM on the system is a safe bet. That’s not it, though – after doing that, make sure to set the values of the variables dealing with logs to around a quarter (25%) of the value you set in the innodb_buffer_pool_size variable.
Also keep in mind that the InnoDB buffer pool can also be backed up (“dumped”) or restored if necessary.
To do that, look into the innodb_buffer_pool_dump_at_shutdown and the innodb_buffer_pool_load_at_startup variables. These variables can be set to ON or OFF to control what MySQL Server does with innodb_buffer_pool_size.
To dump the buffer pool while your MySQL Server is running, use the innodb_buffer_pool_dump_now variable and set it to ON.
Other variables you should keep an eye on when optimizing the value of innodb_buffer_pool_size in MySQL Server include:
Both MySQL and MariaDB Server provide many more parameters that can be used to optimize the performance of the innodb_buffer_pool_size variable. The fact of the matter is, not all of them are necessary and most DBAs will be able to drastically improve their MySQL database performance by setting the value of the innodb_buffer_pool_size variable to 60-80% of the available RAM on their system, set the log file size to 25% of the buffer pool within InnoDB, and forget about optimization for the foreseeable future. We hope that you’ve enjoyed this blog and that you’ll stick around for the upcoming ones!
For now, grab a free 21-day trial of DbVisualizer and start visualizing your data in a quick, performant, and secure fashion, and until next time.
Frequently Asked Questions
What is innodb_buffer_pool_size?
The InnoDB buffer pool is an area within ibdata1 (the primary file related to the InnoDB storage engine in MySQL) that deals with data, indexes, MVCC data, and other InnoDB-related operations within MySQL Server.
How Best to Optimize the InnoDB Buffer Pool?
It’s best to optimize the innodb_buffer_pool_size by setting it to at least 60% of the available memory on your operating system.
Can I Get Rid of the Buffer Pool? Why Do I Need It?
You cannot get rid of the buffer pool – it’s an integral part of the InnoDB storage engine within MySQL Server. InnoDB is the default storage engine within MySQL Server and it’s sisters MariaDB Server and Percona Server.
Properly optimizing the buffer pool (and the log files related to it) will make your SQL queries significantly faster.
