MySQLConfigurer is a script that will assist you prepare performance optimized configuration of your MySQL server based on the MySQLTuner report (MySQL status and system information).
Releem is an online service for automatic optimization MySQL configuration to improve performance and reduce costs. Releem analyzes the MySQLTuner report, MySQL status and system information of your server and provides settings recommendations in the form of a MySQL configuration file.
To get your Releem API Key please sign up.
Always test recommended configuration on staging environments, and always keep in mind that improvements in one area can negatively affect MySQL in other areas.
It's also important to wait at least a day of uptime to get accurate results.
Always store credentials only in
~/.my.cnf to prevent sending passwords to our service.
In other cases MySQLTuner stores passwords manualy entered while running script in "MySQL Client" section of the MySQLTuner report.
*** MINIMAL REQUIREMENTS ***
MySQLConfigurer is a Bash script which
We tested the results with Sysbench on a virtual server running Debian 9 (2 CPU, 2GB Ram) the table contained 10 million entries. Two configurations were tested, the MySQL default configuration and the configuration recommended by the Releem service. The tests were two-step: read (test1) only and read/write (test2).
Recommended configuration delivered a 30% boost to MySQL performance compared to the default configuration.
-k [Releem API KEY] - API Key to Releem platform. To get your Releem API Key please sign up.
-m [MYSQL_MEMORY_LIMIT] - set maximum memory limit for MySQL. Used when there are installed different applications on the server.
RELEEM_API_KEY=[YOUR_RELEEM_API_KEY] bash -c "$(curl -L https://releem.s3.amazonaws.com/install.sh)"
[client] user=root password=[your password]
/bin/bash /opt/releem/mysqlconfigurer.sh -k [RELEEM_API_KEY]
10 */12 * * * /bin/bash /opt/releem/mysqlconfigurer.sh -k [RELEEM_API_KEY]
Releem recommends the best performance optimized MySQL configuration at this time. You can apply Recommended Configuration in case of MySQL Performance Score of your server goes down.
Releem Agent automatically stores Recommended Configuration at /tmp/.mysqlconfigurer/z_aiops_mysql.cnf
Perform the following steps to safely apply recommended configuration:
Copy the Recommended Configuration to MySQL configuration folder:
cp /tmp/.mysqlconfigurer/z_aiops_mysql.cnf /etc/mysql/conf.d/
/etc/mysql/conf.dfolder can vary according to Linux distro.
Restart MySQL to apply the Recommended configuration:
service mysqld restart
WARNING! In case of change 'innodb_log_file_size' only in MySQL 5.6.7 or earlier set parameter 'innodb_fast_shutdown' to 1 (Official documentation), stop MySQL server, copy old log files into a safe place and delete it from log directory, copy recommended configuration and start MySQL server:
mysql -e"SET GLOBAL innodb_fast_shutdown = 1" service mysql stop mv /var/lib/mysql/ib_logfile /tmp service mysql start
Perform the folowing steps to safely setup
open_files_limit in MySQL
Find out if any other .conf files are being used with MySQL that overrides the values for open limits. Run
systemctl status mysqld/mysql/mariadb command and it will show something like this
Drop-In: /etc/systemd/system/(mysqld/mysql/mariadb).service.d └─limits.conf
This means there is
/etc/systemd/system/(mysqld/mysql/mariadb).service.d/limits.conf file which is loaded with MySQL Server. If this file does not exist, you should create create it.
mysqld/mysql/mariadb is selected depending on the name of the running service name on the server, which is also defined in the output of the command
systemctl status mysqld/mysql/mariadb
Edit the file and add the following and change
[table_open_cache] to your value
[Service] LimitNOFILE=([table_open_cache] * 2)
open_files_limitshould be no less than
[table_open_cache] * 2.
Run the following command to apply the changes.
Reboot your mysql server.
After the successful reboot of the server, we will again run below SQL Queries.
SHOW VARIABLES LIKE 'open_files_limit';
You should see the following:
+------------------+--------+ | Variable_name | Value | +------------------+--------+ | open_files_limit | 102400 | +------------------+--------+ 1 row in set (0.00 sec)
Example of the recommended configuration file /tmp/.mysqlconfigurer/z_aiops_mysql.cnf:
[mysqld] query_cache_type = 1 ### Previous value : ON query_cache_size = 128M ### Previous value : 134217728 query_cache_limit = 16M ### Previous value : 16777216 thread_cache_size = 8 ### Previous value : 8 key_buffer_size = 205520896 ### Previous value : 205520896 max_allowed_packet = 1073741824 ### Previous value : 67108864 sort_buffer_size = 16777216 ### Previous value : 25165824 read_rnd_buffer_size = 4194304 ### Previous value : 4194304 bulk_insert_buffer_size = 8M ### Previous value : 2097152 myisam_sort_buffer_size = 8388608 ### Previous value : 25165824 innodb_buffer_pool_instances = 2 ### Previous value : 3 innodb_buffer_pool_size = 3019898880 ### Previous value : 3019898880 max_heap_table_size = 256M ### Previous value : 268435456 tmp_table_size = 256M ### Previous value : 268435456 join_buffer_size = 8M ### Previous value : 8388608 max_connections = 151 ### Previous value : 151 table_open_cache = 3072 ### Previous value : 3072 table_definition_cache = 1920 ### Previous value : 1920 innodb_flush_log_at_trx_commit = 2 ### Previous value : 2 innodb_log_file_size = 377487360 ### Previous value : 805306368 innodb_write_io_threads = 4 ### Previous value : 4 innodb_read_io_threads = 4 ### Previous value : 4 innodb_file_per_table = 1 ### Previous value : ON innodb_flush_method = O_DIRECT ### Previous value : innodb_thread_concurrency = 0 ### Previous value : 0
You can help this project by reporting problems, suggestions or contributing to the code.
Go to our issue tracker and check if your problem/suggestion is already reported. If not, create a new issue with a descriptive title and detail your suggestion or steps to reproduce the problem.