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 email@example.com.