14

Can anyone tell me what is innodb_buffer_pool_size & innodb_log_file_size? What will happen after increasing it's sizes..

Vee
  • 139
  • 1
  • 1
  • 4

2 Answers2

16

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.

MarkR
  • 62,604
  • 14
  • 116
  • 151
  • 2
    +1! I might just note that the innodb_log_file_size can affect performance quite a lot (but not everyone will see it if their database is entirely reads). There are also more concerns with blindly raising this, since a very large value will increase recovery time. – Morgan Tocker Jul 24 '10 at 14:19
  • 1
    Actually, the `innodb_log_file_size` value impacts performance greatly. Unfortunately, most system administrators think that the buffer pool size is the only thing that matters, but this is not true. – itoctopus Jul 08 '16 at 04:23
  • Whats a good size to set these for local testing on a dev machine with 16GB RAM? The default size 50m log + 512m buffer causes the process to use 900MB ram - and ram is in short supply with everything else running. Would 16m log + 64m buffer be sufficient for this purpose? It reduces process usage to around 350MB. But I'm not sure if I'm actually making things worse with such small sizes – Derek Ziemba Sep 05 '19 at 13:57
6

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.

swayamraina
  • 2,958
  • 26
  • 28