Knowledge BaseMetric InsightsSystemDatabaseFine-Tuning MySQL Parameters (prior to v6.2)

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).

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