Knowledge BaseMetric InsightsSystem DatabaseFine-Tuning MySQL Parameters (v6.2+)

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, support@metricinsights.com).

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.

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 support@metricinsights.com.

5. Start MySQL

  • Debian:

service mysql start

  • Centos:

 service mysqld start