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.cnffile on the host under
- 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).
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