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:
- Create a
01-metricinsights.cnffile (can be named anything but must not start with 00) on the host with a [mysqld] tag and custom parameters. Place the file under
- 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
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.
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
service mysql stop
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
service mysql start
service mysqld start