Fine-Tuning MySQL Parameters (prior to v6.2)

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

For Metric Insights v6+ Simple Installation:

  • put the 01-metricinsights.cnf file on the host under /opt/mi/config/mysql/external_config/
  • restart mysql container with mi-control restart mysql

For Metric Insights v5.x

Metric Insights deploys an 01-metricinsights.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-metricinsights.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-metricinsights.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 parameters 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 = 

If you are using the 32gb_settings.cnf or 64gb_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.

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

12gb_settings.cnf
16gb_settings.cnf
32gb_settings.cnf
64gb_settings.cnf
Resizing the Innodb Log Files Safely

Simply changing the innodb_log_file_size parameter and restarting MySQL will not work. If you do so, 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

This is a default value from Metric Insights. To check that it's set correctly, 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. 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

For instance, 02-insightsettings.cnf or yourcompanyname.cnf.

If you do not see an innodb_log_file_size parameter, simply enter it in the cnf file (without it explicitly stated, the log file size is set to 5MB by default). For any additional questions, please contact [email protected].

5. Start MySQL

  • Debian:

service mysql start

  • Centos:

 service mysqld start