Optimizing MySQL with MySQLTuner

MySQLTuner is a tool that analyzes how MySQL is performing and gives recommendations for optimizing it.

To download the tool, run the following command:

wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl

Before running the tool, the MySQL server should have been running for about a day without restarts or configuration changes so that MySQLTuner can collect enough data for a meaningful analysis. Otherwise, the tool's results will not be informative.

Run the tool with the command:

perl mysqltuner.pl

The MySQLTuner output contains several sections and looks roughly like this (the actual output will be more detailed and longer):

root@server:~# perl mysqltuner.pl
 >>  MySQLTuner 1.7.19 - Major Hayden <[email protected]>;
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.pl/
 >>  Run with '--help' for additional options and output filtering
[--] Skipped version check for MySQLTuner script
[OK] Logged in using credentials from Debian maintenance account.
-------- Log file Recommendations ---------------------------------------------
[OK] Log file /var/log/mysql/error.log exists
[--] Log file: /var/log/mysql/error.log(0B)
[OK] Log file /var/log/mysql/error.log is readable.
[!!] Log file /var/log/mysql/error.log is empty
-------- Security Recommendations ---------------------------------------------
[OK] There are no anonymous accounts for any database users
[!!] User 'root@localhost' has no password set.
[!!] User 'user1@%' does not specify hostname restrictions.
[!!] User 'user@%' does not specify hostname restrictions.
-------- Performance Metrics --------------------------------------------------
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 243.2M (12.20% of installed RAM)
[!!] Maximum possible memory usage: 2.7G (138.90% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
-------- InnoDB Metrics -------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 128.0M/416.0K
-------- Recommendations ------------------------------------------------------
General recommendations:
    Set up a Secure Password for root@localhost user: SET PASSWORD FOR 'root'@'SpecificDNSorIp' = PASSWORD('secure_password');
    Restrict Host for 'user1'@% to user1@SpecificDNSorIp
    UPDATE mysql.user SET host ='SpecificDNSorIp' WHERE user='user1' AND host ='%'; FLUSH PRIVILEGES;
    Dedicate this server to your database for highest performance.
    Reduce or eliminate unclosed connections and network issues
    Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
    Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: https://bit.ly/2TcGgtU
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    query_cache_size (=0)
    query_cache_type (=0)
    query_cache_limit (> 1M, or use smaller result sets)
    innodb_log_file_size should be (=16M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.

Carefully review the lines marked with [!!], and also pay attention to the summary of recommendations in the final Recommendations block.

The parameters listed under Variables to adjust should be changed in the MySQL configuration file. If a parameter is not in the file, add it manually.

The location of the MySQL configuration file may vary depending on the operating system.

On Debian/Ubuntu, the file is located at:

/etc/mysql/my.cnf 

/etc/mysql/mysql.conf.d/mysqld.cnf 

On CentOS, the configuration file is located here:

/etc/my.cnf 

/etc/mysql/my.cnf

After every change to the configuration file, restart the MySQL service:

systemctl restart mysql

Additional tips

1. Before changing the configuration file, make a backup copy of it (specify the actual path in the command):

cp /etc/mysql/my.cnf ~/my.cnf.backup

2. After making changes, let the server run for about a day, then run MySQLTuner again. This lets you analyze how MySQL performs with the new settings and continue optimizing.

3. Apply MySQLTuner's recommendations gradually and watch the server closely after each change. This will help you quickly spot and fix any problems and, if necessary, roll back unwanted changes.

Don't forget to restart the MySQL service after changing the settings.