MySQL log buffer parameters I have a 150 GB PROD MySQL Database (version 5.6.13).

What should be the values for below parameters:

innodb_log_buffer_size
innodb_log_file_size
innodb_buffer_pool_size (RAM - 20 GB)
Innodb_buffer_pool_size the maximum you can have. If this is a dedicated box to mysql something around 16Gb should be a good start.

innodb_log_file_size depends on the amount of write you have. Run the following commands in your mysql console while database is being used:

&gt; pager grep sequence
&gt; show engine innodb status\G select sleep(60); show engine innodb status\G


Output will be something like this:

Log sequence number 8622728172243
1 row in set (0.00 sec)

1 row in set (59.99 sec)

Log sequence number 8622740654048
1 row in set (0.00 sec)


Now given the numbers run:

&gt; nopager
&gt; select (8622740654048 - 8622728172243) / 1024 / 1024 * 60; 


That returns you the amount of MBs written to the log file in an hour which is the rule of thumb for smoothing out write IO efficiently.

To my.cnf:

innodb_log_files_in_group = 2
innodb_log_file_size = [the calculated amount / 2]  # Because you have two logfiles


innodb_log_buffer_size depends mostly on the size of your transactions. If you don't have big blobs in large transactions somewhere around 16M to 64M should be sufficient. See what works best for your workload. Start from a low size and keep an eye on innodb_log_waits status:

SHOW GLOBAL STATUS LIKE 'innodb_log_waits';


If that increases than increase the size of the buffer.