Fine-Tuning MySQL Parameters (v6.2+)

Note: If MySQL is on AWS RDS, you need to set gtid-mode and gtid_mode parameters to OFF. If set to other values, this parameters will add SQL queries to the MI backup tar ball that won't allow a successful restore.

Refer to RDS configuration article to check all required parameters for an AWS RDS.

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

For Metric Insights v6+ Simple Installations, mysql parameters are optimized at the time of installation based on the amount of memory available on the host server. These parameters are stored in /etc/mysql/conf.d/00-metricinsights.cnf inside the mysql container. 

However, should further fine tuning of the parameters be required, please do the following:

  1. Create a 01-metricinsights.cnf file (can be named anything but must not start with 00) on the host with a [mysqld] tag and custom parameters. Place the file under /opt/mi/config/mysql/external_config/
  2. Restart the mysql container with mi-control restart mysql

For Metric Insights v6+ Orchestrated Environments, a remote MySQL database is required. For the remote database, you must manually create a mysql parameters file and apply it to the database. Continue below.

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 Administrator or contact Metric Insights Support, [email protected]).

If you are using the 32gb_settings.cnf or 64gb_settings.cnf file for standalone MySQL installation, please see the section below on resizing the innodb_log_file parameter safely.

Do not use a file that is provisioned for more memory than your database server has.

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