Can anyone tell me what is innodb_buffer_pool_size & innodb_log_file_size? What will happen after increasing it's sizes..
+1
A:
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
2010-01-20 12:14:39
+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
2010-07-24 14:19:06