Knowledge BaseSystem DatabaseFine Tuning MySQL Parameters

Fine Tuning MySQL Parameters

Adjusting MySQL parameters in /etc/mysql/conf.d

Metric Insights deploys an 01-insightsettings.cnf file in /etc/mysql/conf.d with standard mysql parameters. If your dba feels the need to fine tune these parameters* after running mysqltuner for example, do not edit 01-insightsettings.cnf. 

Each new deploy of MI will overwrite 01-insightsettings.cnf with our default parameters. However, MI will load additional cnf files found in the directory alphabetically/sequentially if there's more than one. In this case 01-insightsettings gets loaded first.

To apply your own parameter settings, create an 02-insightsettings.cnf file (if there already isn't a second *.cnf file). It can also be yourcompanyname.cnf because that will get loaded after 01.

MySQL paramenters include:

key_buffer_size = 
join_buffer_size = 
sort_buffer_size = 
read_buffer_size = 
read_rnd_buffer_size = 
innodb_buffer_pool_size = 
innodb_log_file_size = 


NOTE: The example configuration files below are for servers with 12/16/32GB RAM and should be considered baseline specs. Additional fine tuning may be necessary if performance is still an issue (consult with your dba).

If using the 32gb_settings.cnf file, please see the section below on resizing the innodb_log_file parameter SAFELY.

(Do not use a file that is provisioned for more RAM than your server has.)


Resizing the Innodb Log Files Safely

Simply changing the innodb_log_file_size parameter and restarting mysql will not work. If you do, mysql will actually fail to restart, producing an error log. 

To properly adjust the parameter:

1.) Make sure innodb_fast_shutdown is set to 1 (it is by default from Metric Insights). To check, log on to mysql in the MI Instance (or MI Instance db server) and enter:

show variables like '%innodb_fast_shutdown%';

It should be set to 1. If not, set it to 1 by entering:

set global innodb_fast_shutdown=1;

2.) Next, shut down mysql

Debian - service mysql stop

Centos - service mysqld stop

3.) Remove (but don't delete) the innodb log files ib_logfile0 and ib_logfile1 from /var/lib/mysql/

4.) Modify* the innodb_log_file_size parameter in your second cnf file, e.g., 02-insightsettings.cnf or yourcompanyname.cnf

5.) Start mysql

Debian - service mysql start

Centos - service mysqld start


NOTE: if you do not see an innodb_log_file_size parameter, simply enter it in the cnf file (without it explicitely stated, the log file size is set to 5MB by default). For any additional questions, please contact