What is innodb_buffer_pool_size & innodb_log_file_size?

Question

Can anyone tell me what are the innodb_buffer_pool_size and innodb_log_file_size MySQL parameters?

What will happen after increasing their sizes?

Answer 1

innodb_buffer_pool_size is the amount of memory to use to cache tables, indexes and a few other things. This is the main tunable and you will want to set it quite high for performance reasons - there are plenty of resources discussing this.

innodb_log_file_size is the size of the commit log files (there are normally two of these), which does impact performance but not as much. Read the docs for more information.

The innodb_log_file_size should not be set to a value different from the size of the current files or the server won't start. This means that if you want to change it, you should shut the server down cleanly, delete the existing logs and it will create new ones.

On the other hand the logs should not be deleted if the database didn't shut down cleanly as it needs them to recover.

Answer 2

Just adding more details to the existing answer,

innodb_buffer_pool_size is the total cache size for MySQL.
Whenever there is a read request, MySQL server caches the data in the RAM in the form of LinkedList of BufferPages. When the write to a row happens which happens to be already loaded in memory (RAM), MySQL updates the row in-memory and shifts the page (now dirty) to another LinkedList, member of the BufferPool.
Another component called PageCleaner, runs in the background and frees the memory and writes data in the disk.

innodb_log_file_size is the size of a log file (in bytes). There exist multiple log file for MySQL to write the data to. This particular system-variable tells the file size of one of such log files. These log files are required for recovery and log-replication i.e. for ensuring that if the master node dies, the slave node has the required data.